Granting a new user read-only access to all the tables in a schema in Redshift

Often you want to give a user read only permissions to all the tables in e.g. the atomic schema. (E.g. credentials for a BI / frontend on the data.) Granting permissions on individual tables, especially if you have lots of them in the schema, can be tedious.

A quick way is to execute the following query:

SELECT
'GRANT SELECT ON ' ||
schemaname ||
'.' ||
tablename ||
' TO {{ username }};'
FROM pg_tables
WHERE schemaname = 'atomic';

This generates the list of GRANT statements that can be copied and pasted at the psql command line to grant access to each individual table.

1 Like

Hi Yali,

You can also use: GRANT SELECT ON ALL TABLES IN SCHEMA atomic TO {username};

2 Likes

Wow! Thanks for sharing @Gal - that would have saved me a lot of time if Iā€™d known earlier :slight_smile:

1 Like

We do something very similar except use groups to determine privileges

GRANT SELECT ON ALL TABLES IN SCHEMA atomic TO GROUP new_users;

Suppose that you want to allow any user in the user group report_readers to view all tables created by the user report_admin.
In this case, execute the following command as a superuser.

alter default privileges for user report_admin in schema atomic grant select on tables to group report_readers;

For more details [AWS Doc](http://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_DEFAULT_PRIVILEGES.html)

1 Like