7

I am trying to assign SELECT privilege to a group in Redshift. So I created a group and a user in that group:

CREATE GROUP data_viewers;
CREATE USER <user> PASSWORD '<password>' IN GROUP data_viewers;

Now I would like to allow this group to be able to read data from any table:

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO GROUP data_viewers;

The command returns GRANT. Now when I connect to Redshift as my newly created user and issue SELECT * FROM something.something; I get:

permission denied for schema something

I tried granting permissions to something: GRANT SELECT ON ALL TABLES IN SCHEMA something TO GROUP data_viewers; but this has not changed anything.


How can I allow users from my group to SELECT data from any table in the schema?

13

You need the USAGE privilege (at least) for the schema as well:

GRANT USAGE ON SCHEMA something TO GROUP data_viewers;

Related Postgres example:

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:

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:

And I am not sure how Redshift handles sequences ...

  • Thank you!! I didn't even know about the concept of DEFAULT PRIVILEGES in Redshift before reading this answer! It completely solved the issue for me! – Marco Roy Jan 27 '18 at 0:08
9

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.

  • The last revoke on CREATE is actually unnecessary as this permission isn't given by default. – nikel Apr 4 '19 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.