postgresqlsecuritypermissionsamazon-redshiftsql-grant

Redshift : New table but Group members can't query

In Redshift I have a data_reader db group defined like this

CREATE GROUP data_reader;
GRANT USAGE ON SCHEMA reports TO GROUP data_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA reports TO GROUP data_reader;

And I've noticed that whenever I create a new table, group members can't automatically query from the table. They get a Permission Denied error. Re-running

GRANT SELECT ON ALL TABLES IN SCHEMA reports TO GROUP data_reader;

Fixes the permission.

Is this just a quirk of Redshift or is there another way I should be defining my db groups?

Solution

  • This statement grants access only to already created tables:

    GRANT SELECT ON ALL TABLES IN SCHEMA reports TO GROUP data_reader;

    In order to grant access by default, you must setup default privileges:

    GRANT USAGE ON SCHEMA reports TO group data_reader; --Most probably already granted
ALTER DEFAULT PRIVILEGES IN SCHEMA reports GRANT SELECT ON TABLES to group data_reader;

    Now all new created tables will be granted to group for select.