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.
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)