Can't revoke permissions on Redshift

Question

I'm having a issue revoking privileges that is stopping me from droping users

select usename, acl.*
from pg_default_acl as acl join pg_user on usesysid = defacluser
where usename = 'user1';

gives me:

|usename|defacluser|defaclnamespace|defaclobjtype|defaclacl             |
|-------|----------|---------------|-------------|----------------------|
|user1 |143       |0              |r            |{user1=awdRxt/user1}|

With a view provided by amazon (v_get_obj_priv_by_user) I execute this to get the schemas/databases with permissions to this user:

select 'alter table '+schemaname+'.'+tablename+' owner to y;' from pg_tables where tableowner like 'user1';

select distinct 'revoke all on schema '+schemaname+' from user1;' from admin.v_get_obj_priv_by_user where usename like 'user1';

select distinct 'revoke all on all tables in schema '+schemaname+' from user1;' from admin.v_get_obj_priv_by_user where usename like 'user1'

Got this 2 responses from previous queries

revoke all on schema xxx from user1;

revoke all on all tables in schema xxx from user1;

I ran them, have no errors, but the permissions are still there and the queries using v_get_obj_priv_by_user keep returning the same results.

Obviously when later I try to drop the user have this error:

SQL Error [2BP01]: ERROR: user "user1" cannot be dropped because some objects depend on it Detail: owner of default privileges on new relations belonging to user user1

Any idea of what could I been doing wrong? Thanks!

Answer 1

This worked for me:

alter default privileges for user <user> revoke all on tables from <user>;
alter default privileges for user <user> grant all on tables to <user>;
drop user <user>;