Can't drop user from Redshift

Question

I am trying to drop user form Redshift but it always fails with the same message

user "XXX" cannot be dropped because the user has a privilege on some object;

Following a google search on it I found out that I need to revoke the user's permissions so I run several revoke queries but I still fail with the same message:

The queries I ran:

revoke all on schema YYY from XXX;
revoke usage on schema ZZZ from XXX;
revoke all on database LLL from XXX;

Any idea why I still get this failure message ?

Answer 1

Please deploy this view from github "v_get_obj_priv_by_user"

Once done , follow below steps

1. A_user ---User that has to drop

   B_user ---Table ownership of old table need to map to this user.

   If you wish to to change owner of all tables belong to A_user, then

   select schemaname,tablename from pg_tables where tableowner like 'A_user';

   For retrieved above tables run

   alter table schemaname.tablename owner to B_user;

2. Revoke all on schema where A_user has some privileges

   select distinct schemaname from admin.v_get_obj_priv_by_user where usename like 'A_user';

   For retrieved above tables run

   revoke all on schema XXXX from A_user;

3. Revoke all on tables where A_user has some privileges

   select distinct tables from admin.v_get_obj_priv_by_user where usename like 'A_user';

   For retrieved above tables run

   revoke all on all tables in schema XXXX from A_user;

4. Drop user usename;

If there are two database in one cluster, please do this for both databases.

Answer 2

The v_generate_user_grant_revoke_ddl admin view allows you to see existing grants and their corresponding revokes.

With this sql:

SELECT
  ddl
FROM admin.v_generate_user_grant_revoke_ddl
WHERE ddltype = 'revoke'
  AND (grantee = '<USERNAME>' OR grantor = '<USERNAME>')
ORDER BY
  objseq,
  grantseq desc;

I was finally able to find all grants and generate the revoke statements.