Redshift Revoke Permission not Working

Question

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.

Answer 1

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;