How can I create a Redshift user with read-only access?

Question

I'm trying to find a way to create a user that will have read-only access to ALL the existing and newly created schemas in Redshift.

Is that possible?

Astrea

Answer 1

I'm trying to find a way to create a user that will have read-only access to ALL the existing and newly created schemas in Redshift.

Yes and no - yes to read-only access, no to automatically changing the user's access to new schemas to read-only.

---

To create a read-only user, add a user to a group that only has read-only privileges to the specified schemas for a database.

Note that you will still have to initially manually specify all of the schema names & then subsequently modify the group for any new schemas that you may create.

The good news however, is that there is no need to run anything for new tables added to existing schemas as the below will also alter default privileges to maintain the permissions on new tables.

I'm using readonly_schema as the schema name but use public if using the default Amazon Redshift schema.

CREATE GROUP readonly_group;
REVOKE CREATE ON SCHEMA "readonly_schema" FROM GROUP readonly_group;
GRANT USAGE ON SCHEMA "readonly_schema" TO GROUP readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA "readonly_schema" TO GROUP readonly_group;
ALTER DEFAULT PRIVILEGES IN SCHEMA "readonly_schema" GRANT SELECT ON TABLES TO GROUP readonly_group;
CREATE USER readonly_user WITH PASSWORD supersafepassword;
ALTER GROUP readonly_group ADD USER readonly_user;

Bedwarmer