CODEWITHSUNDEEP
postgresqlamazon-redshift
Karl Anka
Karl Anka

Reputation: 2869

Unable to drop user because I cannot revoke default priviliges in Redshift

I am having issues with dropping a user becauase it has default privileges, but I am as well unable to revoke those privileges.

To reproduce my issue:

-- executed with master user redshift_master
CREATE USER anton_test_user PASSWORD '***' IN GROUP redshift_dev;

Then using anton_test_user

CREATE SCHEMA anton_test_schema;
CREATE TABLE anton_test_schema.anton_test_table AS SELECT 1 AS anton;
ALTER DEFAULT PRIVILEGES IN SCHEMA anton_test_schema 
GRANT SELECT ON TABLES TO GROUP redshift_readonly;

Again with redshift_master

ALTER SCHEMA anton_test_schema OWNER TO redshift_master;
ALTER TABLE anton_test_schema.anton_test_table OWNER TO redshift_master;

Now trying to drop the user it complains about default privileges:

DROP USER anton_test_user;

Result as expected:

owner of default privileges on new relations belonging to user 
anton_test_user in schema anton_test_schema;

Now to the weird part, still with redshift_master

ALTER DEFAULT PRIVILEGES FOR USER anton_test_user IN SCHEMA anton_test_schema
REVOKE ALL ON TABLES FROM redshift_readonly;

Gives Invalid operation: permission denied for schema anton_test_schema. What?

If running with anton_test_user

ALTER DEFAULT PRIVILEGES IN SCHEMA anton_test_schema
REVOKE ALL ON TABLES FROM redshift_readonly;

As well gives Invalid operation: permission denied for schema anton_test_schema.

The only way for me to solve this and being able to drop anton_test_user was to, with redshift_master drop the schema and table completely

DROP TABLE anton_test_schema.anton_test_table;
DROP SCHEMA anton_test_schema;
DROP USER anton_test_user; -- it works now

Transfering ownership back to anton_test_user and then revoking default privileges did not help - dropping the table and schema was the only solution I could find.

My completely uninformed guess is that anton_test_user had lost permissions to the schema, so no grants for the user could be applied or revoked in that schema.

Question(s):

This is a bit of a follow up to a question already asked, to which I gave an answer - but I have no idea what is going on even though I came up with a "solution" ("" because dropping objects was a solution, albeit a pretty poor one). It might be that I have completely misunderstood user privileges in Redshift as well.

The original question is not completely the same as this - and I would like to know what is going on, so it is not really a repost even though it might look like it.

Upvotes: 2

Views: 1333

Answers (1)

p5k6
p5k6

Reputation: 111

I had the same issue myself. I was able to avoid dropping the user/schema by first re-granting access of the schema to my end user (my version of anton_test_user).

grant all on schema analyst_data to anton_test_user;

After doing so, I was able to run my alter default privileges command ALTER DEFAULT PRIVILEGES for user <user> in schema <schema> REVOKE ALL on tables FROM group <group>;

Your uninformed guess was spot on 😀

Upvotes: 4

Related Questions