Create and grant access to Protecto user

A user needs to be created (protecto_user) and access to certain tables need to be given for extracting the Redshift data source metadata.

User needs to have read access to the below tables:

1) To take Table details:

• information_schema.tables

• svv_tables

• svv_table_info

2) To take Log details:

• stl_query

• svl_user_info

3) To take User details:

• pg_user

4) To take Group details:

• pg_group

5) To take User access privilege details:

• pg_tables

• pg_internal

• pg_views

6) To take Schema details:

• pg_catalog.pg_namespace

• pg_catalog.pg_user

Reference queries:

CREATE USER protecto_user WITH PASSWORD '<password>';

GRANT USAGE ON SCHEMA information_schema TO protecto_user;

GRANT SELECT ON information_schema.tables TO protecto_user;

GRANT SELECT ON svv_tables TO protecto_user;

GRANT SELECT ON svv_table_info TO protecto_user;

GRANT SELECT ON stl_query TO protecto_user;

GRANT SELECT ON svl_user_info TO protecto_user;

GRANT SELECT ON pg_user TO protecto_user;

GRANT SELECT ON pg_group TO protecto_user;

GRANT SELECT ON pg_tables TO protecto_user;

GRANT USAGE ON SCHEMA pg_internal TO protecto_user;

GRANT SELECT ON pg_views TO protecto_user;

GRANT SELECT ON pg_catalog.pg_namespace TO protecto_user;

GRANT SELECT ON pg_catalog.pg_user TO protecto_user;

ALTER USER protecto_user WITH SYSLOG ACCESS UNRESTRICTED;

List of accesses needed for tables:

• Grant "SELECT" and "USAGE" privilege for all schemas and tables in the database for protecto_user.

Reference queries:

GRANT USAGE ON SCHEMA <schema_name> TO protecto_user;

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO protecto_user;

Note:

• The above commands should be executed on all the future schemas and tables on which Protecto should analyze.

• Once select access is given, if any new table is added in future, the select access should be given for all newly added tables to perform scanning.