CODEWITHSUNDEEP
databaseamazon-web-servicesamazon-redshiftdevops
conetfun
conetfun

Reputation: 1615

How to grant usage on all schemas in Redshift?

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?

Upvotes: 1

Views: 24611

Answers (1)

Nathan Griffiths
Nathan Griffiths

Reputation: 12766

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;

Upvotes: 8

Related Questions