Amazon Redshift Grants - New table can't be accessed even though user has grants to all tables in schema

amazon-redshift database-permissions
elvikingo picture elvikingo · Feb 3, 2016 · Viewed 16.2k times · Source

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.

Answer

hibernado picture hibernado · Jan 30, 2018

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.

Related questions

Show tables, describe tables equivalent in redshift

I'm new to aws, can anyone tell me what are redshifts' equivalents to mysql commands? show tables -- redshift command describe table_name -- redshift command

Read More
Alter column data type in Amazon Redshift

How to alter column data type in Amazon Redshift database? I am not able to alter the column data type in Redshift; is there any way to modify the data type in Amazon Redshift?

Read More
In Redshift/Postgres, how to count rows that meet a condition?

I'm trying to write a query that count only the rows that meet a condition. For example, in MySQL I would write it like this: SELECT COUNT(IF(grade < 70), 1, NULL) FROM grades ORDER BY id DESC; However, when I …

Read More