2

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?

Michael Black
  • 661
  • 11
  • 24

2 Answers2

4

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.

Edgars T.
  • 947
  • 8
  • 14
0

Any GRANTS you provide are only for the existing tables/views. Anytime a new table is created, you need to explicitly provide the required permissions to users/groups. My suggestion is to probably include the appropriate grants statements in the DDL file.

SwapSays
  • 407
  • 7
  • 18