9

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.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
DaDeem
  • 161
  • 1
  • 2
  • 5
  • 1
    Can you possibly provide us with a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) so that we can reproduce your situation and try to diagnose? This could be done by showing the commands to create your existing configuration and the commands that are failing to produce the result you seek. Feel free to Edit your question to add this information. – John Rotenstein Sep 01 '17 at 06:00
  • Side-note: Make sure you turn on **auto-commit** in SQL Workbench to see any error message, otherwise the first error will prevent future commands from being executed until the transaction is closed. – John Rotenstein Sep 01 '17 at 06:19
  • Thanks John, I'll provide the details – DaDeem Sep 01 '17 at 16:39
  • "looks like nothing is working." - how are you testing whether or not these commands have worked? Note that you must be a superuser or the owner of an object in order to change the permissions. – Nathan Griffiths Sep 02 '17 at 08:00
  • Thanks John and Nathan for the responses. – DaDeem Sep 05 '17 at 21:24
  • I am running all these commands as an administrator. I have auto-commit turned on in SQL Workbench. I tried the following CREATE SCHEMA schema1; CREATE SCHEMA schema2; CREATE TABLE schema1.foo (name TEXT); CREATE USER user1 PASSWORD 'Abcd1234'; – DaDeem Sep 05 '17 at 21:26
  • CREATE SCHEMA schema1; CREATE TABLE schema1.foo (name TEXT); CREATE USER user1 PASSWORD 'Abcd1234'; Without granting any permission the USER "user1" has all the access to the tables in SCHEMA "schema1". Not sure how to control that. After i noticed the permissions, I tried to revoke it. That does not work. I get the message as successfully executed the statements, but the permission still remains the same. Not sure whats going on. – DaDeem Sep 05 '17 at 21:29
  • 3
    @DaDeem May I ask whether you solved this problem? Because I'm experiencing the same issue right now and it's quite baffling... – Pranasas Jun 22 '18 at 12:00

3 Answers3

2

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;

George V
  • 21
  • 4
  • This did not work for me. I am struggling to drop a user that has privileges on some objects. When I run a query to identify these objects, it seems like the user has privileges on some system tables. I have tried to revoke them, but still unable to drop the user. Creating a tmp group for this user, then granting and revoking privileges to this tmp group did not help me to drop the user.. – femeloper May 25 '21 at 09:40
1

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?

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
1

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;
totooooo
  • 1,050
  • 1
  • 12
  • 32