Various queries for finding and generating statements to revoke all privileges and drop a user from AWS Redshift.

redshift_revoke_drop_user.sql

-- check if they own any databases
select d.datname as name,
	pg_catalog.pg_get_userbyid(d.datdba) as db_owner,
	'alter database '+d.datname+' owner to <user>;' as chg_owner
from pg_catalog.pg_database d
where pg_catalog.pg_get_userbyid(d.datdba) = '<user>'
order by d.datname;

-- check if they own any schemas
select nspname, usename, 'alter schema '+nspname+' owner to <user>;' as chg_owner
from pg_namespace p 
join pg_user u on u.usesysid = nspowner
	and u.usename = '<user>';

-- see if they belong to any groups
select usesysid, usename, 
	nvl(groname,'default'),
	case nvl(groname,'default') when 'default' then ''
	else 'alter group '+groname+' drop user <user>;'
	end as drop_group
from pg_user u 
left join pg_group g on ','||array_to_string(grolist,',')||','
  like '%,'||cast(usesysid as varchar(10))||',%' 
where usename='<user>' order by 2,1;

-- change owners on any tables they own
select 'alter table '||schemaname||'.'||tablename||' owner to <newowner>;' 
from pg_tables
where tableowner = '<user>';

-- revoke any schema level permissions
select 
'revoke ' || substring(
          case when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',usage ' else '' end 
          ||case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end 
       , 2,10000)
|| ' on schema '||nspname||' from "'||pu.usename||'";' 
from pg_namespace pn,pg_user pu
where pu.usename='<user>' and array_to_string(nspacl,',') like '%'||pu.usename||'%'
and nspowner > 1 ;

-- revoke privileges on all tables in schemas
with obj_priv_by_user as (
 select derived_table1.schemaname, derived_table1.objectname, derived_table1.usename, derived_table1.sel, derived_table1.ins, derived_table1.upd, derived_table1.del, derived_table1.ref
   from ( select objs.schemaname, objs.objectname, usrs.usename, has_table_privilege(usrs.usename, objs.fullobj::text, 'select'::text) AS sel, has_table_privilege(usrs.usename, objs.fullobj::text, 'insert'::text) AS ins, has_table_privilege(usrs.usename, objs.fullobj::text, 'update'::text) AS upd, has_table_privilege(usrs.usename, objs.fullobj::text, 'delete'::text) AS del, has_table_privilege(usrs.usename, objs.fullobj::text, 'references'::text) AS ref
           from ( select pg_tables.schemaname, 't'::character varying AS obj_type, pg_tables.tablename AS objectname, ((quote_ident(pg_tables.schemaname::text) || '.'::text) || quote_ident(pg_tables.tablename::text))::character varying AS fullobj
                   from pg_tables
                  where pg_tables.schemaname <> 'pg_internal'::name
        union 
                 select pg_views.schemaname, 'v'::character varying AS obj_type, pg_views.viewname AS objectname, ((quote_ident(pg_views.schemaname::text) || '.'::text) || quote_ident(pg_views.viewname::text))::character varying AS fullobj
                   from pg_views
                  where pg_views.schemaname <> 'pg_internal'::name) objs, ( select pg_user.usename, pg_user.usesysid, pg_user.usecreatedb, pg_user.usesuper, pg_user.usecatupd, pg_user.passwd, pg_user.valuntil, pg_user.useconfig
                   from pg_user) usrs
          order by objs.fullobj) derived_table1
  where derived_table1.sel = true OR derived_table1.ins = true OR derived_table1.upd = true OR derived_table1.del = true OR derived_table1.ref = true
)
select distinct 'revoke all on all tables in schema '+schemaname+' from <user>;' 
from obj_priv_by_user 
where usename like '<user>';


-- finally drop the user
drop user <user>;