How to grant usage on all schemas in Redshift?

Question

I want to create a read only user which should have SELECT access to all tables in all schemas. I checked the redshift documentation but it looks like we can only grant access to a specific schema in a single sql statement. (not all schemas at once This read only user should have read only access to all tables/schemas that will be created in future. I am not sure how to do that.

Am I missing something or this is done on purpose by AWS? If this is a limitation then what is the possible workaround to solve this?

you are correct, this is how it works. its not been done purposefully by AWS, it is a feature of postgres. You could set up a script outside of redshift to automate the process. — Jon Scott
I see. Thank you for your response. I will then write a udf to get it done. — conetfun
Right - but not a redshift UDF. It needs to be outside redshift. — Jon Scott
I see but out of my curiosity - Why it can't be a redshift UDF? I was thinking of fetching all schemas from system tables and running the command in a loop. — conetfun
You can not access to tables in a Redshift UDF docs.aws.amazon.com/redshift/latest/dg/… — demircioglu

Nathan Griffiths Nathan Griffiths · Accepted Answer · 2018-09-24T02:26:26

You cannot grant SELECT ("read only") permission on multiple schemas at once in Redshift, as you already found this can only be done on a per-schema basis. It is also not possible to set permissions such that the user would automatically gain any kind of permissions on newly created schemas, unless that user is a "superuser". This is typical of most database platforms, i.e. schema level permissions must be created after the schema is created.

I suggest your best option is to look at the process that is creating new schemas and see if the permissions GRANTs can be done as a part of that process. Consider using a user group rather than a user, this will make it easier to manage should you need to add or change the user that has these permissions.

e.g.

CREATE GROUP readonlyusers;
CREATE USER my_readonly_user WITH PASSWORD 'abc' IN GROUP readonlyusers;

-- when creating a new schema
CREATE SCHEMA IF NOT EXISTS myschema;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO GROUP readonlyusers;

How to grant usage on all schemas in Redshift?

1 Answers