CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
DaDeem
DaDeem

Reputation: 191

Redshift Revoke Permission not Working

I have an Amazon Redshift cluster with four schemas (Schema1, Schema2, Schema3 and Schema 4).

I created a user User1 in this cluster. I want this user to have only read-only access to all the tables in Schema1. Currently, this user has access(Select, Insert, Update, Delete) to all the tables from all the schemas.

I tried all the commands from the Redshift manual, but looks like nothing is working.

Example:

REVOKE ALL on schema schema1 from User1
REVOKE ALL on schema schema2 from User1
REVOKE ALL on schema schema3 from User1
REVOKE ALL on schema schema4 from User1

I also tried to revoke individual permissions (Insert, Update, Delete).

I also tried to revoke permissions (Insert, Update, Delete) from individual table

Tried all the combinations from the manual. I am using SQL Workbench and all the statements were successfully executed without any syntax error.

Not able to figure it. Any help is appreciated.

P.S. I have 15 years of database experience working on roles and permissions.

Upvotes: 10

Views: 11413

Answers (3)

totooooo
totooooo

Reputation: 1415

This might not be what caused the issue of the OP, but it solved the issue for me, and could solve it for people who encounter the same situation and end up on this thread.

In addition to George V's answer, note that there is in Redshift a PUBLIC group, that grants permissions to every user.

PUBLIC represents a group that always includes all users. An individual user's privileges consist of the sum of privileges granted to PUBLIC, privileges granted to any groups that the user belongs to, and any privileges granted to the user individually.

(from the doc on GRANT)

So if you want to make sure that User1 doesn't have access to tables in schema2 for example, you should run:

REVOKE ALL on schema schema2 from User1;
REVOKE ALL on schema schema2 from Group1;  --assuming this is the only group of User1
REVOKE ALL on schema schema2 from PUBLIC;

Upvotes: 2

George V
George V

Reputation: 21

In my case the issue I had was that I had 3 users belonging to the same group. The group had been granted ALL privileges to all the tables of the schema.

Therefore revoking the permissions for a single user did not work since group permissions supersede user permissions.

TL;DR The solution in my case was to create a group for each user and revoke access to the schema for the other groups.

REVOKE ALL ON SCHEMA my_schema FROM group my_group;

Upvotes: 2

John Rotenstein
John Rotenstein

Reputation: 270039

These commands seem to work:

CREATE SCHEMA schema1;
CREATE TABLE schema1.foo (name TEXT);
CREATE USER user1 PASSWORD 'Abcd1234';
GRANT USAGE ON SCHEMA schema1 TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA schema1 TO user1;

However, it might not automatically grant access on tables created in future.

Since Amazon Redshift is based on PostgreSQL 8.0.2, see: How do you create a read-only user in PostgreSQL?

Upvotes: 1

Related Questions