As of today, Redshift does not have a REASSIGN functionality and you cannot drop a user if the user -
- is the owner of an object; and/or
- has some privilege on any object.
Quoting from DROP USER :
DROP USER doesn't return an error if the user owns database objects or has any privileges on objects in another database. If you drop a user that owns objects in another database, the owner for those objects is changed to 'unknown'.
You can follow below steps to to revoke all the privileges assigned to that user and later drop user from the database -
- Change owner of all tables owned by user x(to be dropped) to someone else (y) -
select 'alter table '+schemaname+'.'+tablename+' owner to y;' from pg_tables where tableowner like 'x'
Redirect output of this SQL to a file, say alterowner.sql.
- Revoke all schema level privileges from that user
select distinct 'revoke all on schema '+schemaname+' from x;' from admin.v_get_obj_priv_by_user where usename like 'x'
Save the output to another file, revokeschemapriv.sql
- Revoke table level privileges -
select distinct 'revoke all on all tables in schema '+schemaname+' from x;' from admin.v_get_obj_priv_by_user where usename like 'x'
Save this to a file revoketablepriv.sql
Now run all the 3 SQL files against your database. It will solve your purpose.
Drop the user
DROP USER x;
You can also put all this together in a script which will make things a lot easier.
NOTE - admin.v_get_obj_priv_by_user is a view provided as an amazon utility. You can see the view definition here
and create it in your database.