Redshift Permissions

For data modelers & consumers Redshift
1

I’m having what is obviously a really simple issue. I’ve created a new schema and some tables in Redshift, and granted SELECT on all tables in a schema to a read only user that we use in our BI tools.

However, when I try to use the tables in our tools (Tableau and Metabase) I get the same error:

ERROR: permission denied for schema <schema_name>

I’ve ran the following query to see if the user does indeed have SELECT privileges:

SELECT has_table_privilege('read_only_user', 'schema_name.table_name', 'select')

Which has returned TRUE, so I’m a bit at a loss as to why this user cannot access the table or its columns in our BI tools.

TIA

2

Hey @jrpeck1989,

I think you’ll also need to grant the user usage on the schema:

GRANT USAGE ON SCHEMA <schema_name> TO <redshift_user>;

Best,
Colm

1 Like
3

That’s the one! Knew it would be straightforward.

Thanks!
JP

4

What if I have to provide permission to a group rather than user? Should the following query work -

GRANT USAGE ON SCHEMA <schema_name> TO <group_name>;

Also, I created a new group “abcd” and added users to this group. I then created a view and granted access to this view to the new group.

GRANT SELECT ON <schema_name.view_name> TO GROUP <group_name>

Now, when the users (belonging to “abcd” group) try to query on the view, they get “permission denied for schema <schema_name>” error!

5

Should the following query work -

GRANT USAGE ON SCHEMA <schema_name> TO <group_name>;

Yes, that should work. Here’s the AWS documentation on the GRANT statement.

Also, I created a new group “abcd” and added users to this group. I then created a view and granted access to this view to the new group.

GRANT SELECT ON <schema_name.view_name> TO GROUP <group_name>

Now, when the users (belonging to “abcd” group) try to query on the view, they get “permission denied for schema <schema_name>” error!

You need to grant usage on the schema as well as the table. I’m not 100% sure how this works but my incling is that you may need to re-run this when you add a user to a group - since they weren’t part of the group when it was first run. It should be easy to test whether or not that’s the case.

6

If you add a user to a group you don’t have to re-run the GRANT queries.

If you have a group that has permissions for a schema for example:
GRANT USAGE,CREATE ON SCHEMA temp TO GROUP xxxx;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA temp TO GROUP xxx;
and you add a table in that schema you have to re-run the GRANT query even when you have a GRANT like ON ALL TABLES.

1 Like
7

Thanks for clarifying @tclass - I had myself mixed up.

8

If you want you can avoid the re-running grants (for example if you’ve added a new table to atomic that you want all users in a group to have access to) by using ALTER DEFAULT PRIVILEGES e.g.,

ALTER DEFAULT PRIVILEGES IN SCHEMA atomic grant SELECT on tables to group snowplow_users;

This will mean that users in the group will have select access on any new tables added to the atomic schema without have to run another grant statement. This will only work for future objects so if users don’t already have access to the tables you’ll need to run the query from @tclass first, followed by the ALTER DEFAULT.

9

GRANT USAGE ON SCHEMA <schema_name> TO <redshift_user>;

This worked for me…Thanks a lot !!!