27

I have a bit of a funny situation in Amazon Redshift where I have a user X who has grant select on all tables in schema public, but once a new table is created, this grant doesn't seem to apply to the new table. Is this normal behaviour? If yes, how does one deal with it such that the schema level grants are maintained. Thank you.

elvikingo
  • 947
  • 1
  • 11
  • 20

4 Answers4

21

Executing the following command as super user (master): 

alter default privileges 
  for user staging_user 
  in schema staging 
  grant select on tables 
  to reporting_user;

will allow reporting_user to select data from all future tables created by staging_user in schema staging.

hibernado
  • 1,690
  • 1
  • 18
  • 19
  • 8
    Thanks for explaining. It would have been great if AWS could come up with a feature to also execute the alter privileges for a group. i.e. ALTER DEFAULT PRIVILIEGES FOR GROUP in SCHEMA <> GRANT SELECT SELECT ON ALL TABLE TO – Kfactor21 Jul 10 '18 at 02:14
  • yes exactly - you can have a list of the users: `for user ` – Merlin Feb 05 '19 at 23:53
17

In Redshift tables and views do not automatically inherit the permissions of their parent schema. Your newly created tables are only accessible to the user who created them, and the superuser.

In a recent patch to Redshift a new feature to grant default privileges was implemented that addresses this issue.

Alter Default Privileges

The following code snippet will grant select privileges only for all future tables in the sales schema to the sales_admin group. If you want this to apply to existing tables in a schema you will need to combine it with a second grant statement.

alter default privileges in schema sales grant select on tables to group sales_admin;
John
  • 443
  • 1
  • 4
  • 8
  • 9
    It seems like "alter default privileges" only applies when a superuser creates the object in question; when a normal user w/ create permissions creates an object, the default privileges aren't being added. Anyone seen this? – Jesse MacNett Jan 18 '17 at 19:52
  • Same thing happens for me @JesseMacNett – unpairestgood Mar 27 '17 at 19:45
  • 14
    @JesseMacNett, the doc doesn't do a good job of describing how that works, but you have to add the `FOR USER user` clause to the statement, and if you don't it assumes the current user. So if you have a user X that creates a table, and you run `ALTER DEFAULT PRIVILEGES ...` it assumes you mean `ALTER DEFAULT PRIVILEGES FOR USER current_user ...`, not `ALTER DEFAULT PRIVILEGES FOR USER user_X ...`, so only new tables created by `current_user` will have their default privileges altered. – Steve S May 30 '17 at 22:50
  • 1
    In the docs it says you `ALTER DEFAULT PRIVILEGES` for either or both `FOR USER target_user` and `IN SCHEMA target_schema` and then you grant or revoke `SELECT` or something else to a `USER` or `GROUP`. What's confusing there is that `target_user` is not the target of the `grant` or `revoke`, it is the owner (usually the creator) of the table. So you really need to run something like `alter default privileges for user table_owner in schema sales grant select on tables to group sales_admin;` ... what @SteveS said – Davos Mar 01 '19 at 01:58
1

This is a normal behavior. Only the object owner/superuser have permission to use the object by default.

http://docs.aws.amazon.com/redshift/latest/dg/r_Privileges.html

You can add grant command to your create table statement and grant needed privileges for the user.

vtuhtan
  • 1,056
  • 7
  • 18
1

When we first spotted new tables not appearing in our reporting tool, I discovered a quick workaround is to re-execute the following SQL statement for the groups/users impacted:

ALTER DEFAULT PRIVILEGES IN SCHEMA <SCHEMANAME> GRANT SELECT ON TABLES TO GROUP <USER/GROUPNAME>;
jww
  • 97,681
  • 90
  • 411
  • 885
Skippy
  • 21
  • 1
  • 1
    This does not provide an answer to the question. Once you have sufficient [reputation](http://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](http://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](http://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/low-quality-posts/15294962) – Micho Feb 22 '17 at 02:15
  • Strangely I can't reproduce this now but will keep the thread posted if I discover anything new ! – Skippy Feb 22 '17 at 03:52
  • 3
    @Micho - I wasn't able to comment previously but now I can, was my only option at the time, wow give a new gal a break ! – Skippy Feb 22 '17 at 03:53