CODEWITHSUNDEEP
databasepostgresqlamazon-redshiftpsqlprivileges
pkaramol
pkaramol

Reputation: 19402

Remove empty acl user from a database on a redshift cluster

I cannot drop a user from a Redshift cluster due to its presence in some dbs' default ACLs

# select * from pg_default_acl;
 defacluser | defaclnamespace | defaclobjtype | defaclacl
------------+-----------------+---------------+-----------
        109 |               0 | r             | {}

How can I remove this entry?

Upvotes: 1

Views: 439

Answers (2)

Pranasas
Pranasas

Reputation: 596

Try v_generate_user_grant_revoke_ddl.sql - a script from Amazon Redshift Utilities that generates revoke DDLs for existing permissions.

I have just dealt with the same situation. Ironically, the solution required granting privileges to remove the empty ACL, like:

ALTER DEFAULT PRIVILEGES FOR USER foo
GRANT ALL ON TABLES TO foo;

My best explanation for all of this is that Redshift mandates that owners have some privileges (namely on their objects) by default, and removing such privileges results in an empty ACL in the system and blocks the removal.

Upvotes: 0

Jaap van der Herberg
Jaap van der Herberg

Reputation: 177

This might be caused by default privileges granted to other users.

You could try to find out more about the privileges granted to or by the user using https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminScripts/user_to_be_dropped_privs.sql

Upvotes: -1

Related Questions