Skip to content

Instantly share code, notes, and snippets.

@schwartzmx
Last active May 26, 2023 17:14
Show Gist options
  • Save schwartzmx/f2c1a66dfb1dd0fbf7fb44263491e3d7 to your computer and use it in GitHub Desktop.
Save schwartzmx/f2c1a66dfb1dd0fbf7fb44263491e3d7 to your computer and use it in GitHub Desktop.
Various queries for finding and generating statements to revoke all privileges and drop a user from AWS Redshift.
-- 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>;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment