CODEWITHSUNDEEP
amazon-redshiftprivilegesuser-permissions
user6855694
user6855694

Reputation:

Dropping a User, Handling Blank Default Privileges in Redshift, pg_default_acl

I am looking to reset permissions in a Development environment and I noticed two users I could not drop. I checked all of the permissions etc and the only reference to these users I found was in pg_default_acl.

select * from pg_default_acl;

Returns:

defacluser   |   defaclnamespace   |   defaclobjtype   |   defaclacl
----------------------------------------------------------------------
101          |   0                 |               r   |   {}
122          |   0                 |               r   |   {}

Are these empty permissions preventing me from dropping these users?

Upvotes: 2

Views: 1023

Answers (2)

user6855694
user6855694

Reputation:

I ended up finding a resolution when I came across these admin scripts on github:

awslabs/amazon-redshift-utils

The issue ended up being default privileges that were granted to other users.

Upvotes: 1

jbasko
jbasko

Reputation: 7330

What is the exact error with which the drop fails? These users could be owning tables or views or functions.

You can find the tables with select * from pg_tables where tableowner in ('one', 'two');

The views with: select * from pg_views where viewowner in ('one', 'two');

The functions with: select * from pg_proc where proowner in (select usesysid from pg_user where usename in ('one', 'two'));

And then your users may own schemas too: select * from pg_namespace where nspowner in (select usesysid from pg_user where usename in ('one', 'two'));

Upvotes: 0

Related Questions