Grant SELECT on all tables in Redshift
You need the USAGE
privilege (at least) for the schema as well:
GRANT USAGE ON SCHEMA something TO GROUP data_viewers;
Related Postgres example:
- Permission for sequence in another schema
Remember you only granted permissions to already existing tables. Does not apply to tables created later. To cover those, too:
ALTER DEFAULT PRIVILEGES FOR USER role_that_creates_tables
IN SCHEMA public
GRANT SELECT ON TABLES TO GROUP data_viewers;
Amazon Redshift implemented DEFAULT PRIVILEGES
as well.
Here is a complete cookbook for Postgres:
- How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
Be aware of some differences between mainline Postgres and Redshift! Redshift sticks to separate users and groups, while Postgres replaced that with the universal concept of roles:
- Why did PostgreSQL merge users and groups into roles?
And I am not sure how Redshift handles sequences ...
I had the same need for a Redshift read-only user. After reading the docs, I came up with a set of queries:
-- Create Read-Only Group
CREATE GROUP ro_group;
-- Create User
CREATE USER ro_user WITH password PASSWORD;
-- Add User to Read-Only Group
ALTER GROUP ro_group ADD USER ro_user;
-- Grant Usage permission to Read-Only Group to specific Schema
GRANT USAGE ON SCHEMA "ro_schema" TO GROUP ro_group;
-- Grant Select permission to Read-Only Group to specific Schema
GRANT SELECT ON ALL TABLES IN SCHEMA "ro_schema" TO GROUP ro_group;
-- Alter Default Privileges to maintain the permissions on new tables
ALTER DEFAULT PRIVILEGES IN SCHEMA "ro_schema" GRANT SELECT ON TABLES TO GROUP ro_group;
-- Revoke CREATE privileges from group
REVOKE CREATE ON SCHEMA "ro_schema" FROM GROUP ro_group;
If you want to actually remove the user later on, you have to pretty much go backwards.