Redshift

The following setup allows Alvin to access Redshift metadata and query history, without being able to touch the underlying data.

1. Create Alvin user permissions

Choose a name for the user, and enable access to Redshift system/usage tables.

Alvin also needs a data warehouse usage permissions for issuing certain queries. When this step is done, Alvin can access queries from the Redshift query history.

-- Set up USER
-- Generate password MD5
select md5('***pass***' || 'alvin_read_only');
d79140576960e66f9dc426c32b005895

-- Create User
-- Copy the returned md5, and create a string like: md5{copied_md5}
create user alvin_read_only password 'md5d79140576960e66f9dc426c32b005895';

2. Grant access to all databases of interest

This step ensures that Alvin is able to access metadata such as tables, views and columns.

The important part here is that this step needs to be performed for all schemas of interest. future grants that anything created within the schema at a later stage is picked up.

GRANT SELECT on svv_table_info TO alvin_read_only;
GRANT SELECT on svl_user_info TO alvin_read_only;
GRANT SELECT on information_schema.columns TO alvin_read_only;
GRANT SELECT on pg_catalog.pg_statio_all_tables TO alvin_read_only;
GRANT SELECT on pg_catalog.pg_description TO alvin_read_only;
GRANT SELECT on stl_query TO alvin_read_only;
GRANT SELECT on SVL_STATEMENTTEXT TO alvin_read_only;
GRANT SELECT ON SVV_COLUMNS to alvin_read_only;
GRANT EXECUTE ON FUNCTION pg_get_late_binding_view_cols() TO alvin_read_only;

ALTER USER alvin_read_only SYSLOG ACCESS UNRESTRICTED;

-- Do this for all schemas of interest
GRANT USAGE ON SCHEMA {schema} TO alvin_read_only;
-- Grant References permission to user
GRANT REFERENCES ON ALL TABLES IN SCHEMA {schema} TO alvin_read_only;
-- Alter Default Privileges to maintain the permissions on new tables
ALTER DEFAULT PRIVILEGES IN SCHEMA {schema} GRANT REFERENCES ON TABLES TO alvin_read_only;