How to find the privileges granted to a user in AWS Redshift?

amazon-redshift

Solution

To be able to drop a user, you have to (at least)

- if they own any objects, change the owner to a different user

- remove grants from any objects

- remove them from groups

- remove grants from schemas

You can use this to find any tables they own (then run "alter table owner to "):

select * from pg_tables where tableowner = 'test_55'

You can use this to build the script to revoke any grants:

select relacl , 
'revoke ' || substring(
            case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',select ' else '' end 
          ||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',update ' else '' end 
          ||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',insert ' else '' end 
          ||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',delete ' else '' end 
          ||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',rule ' else '' end 
          ||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',references ' else '' end 
          ||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',trigger ' else '' end 
          ||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',execute ' else '' end 
          ||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',usage ' else '' end 
          ||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end 
          ||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',temporary ' else '' end 
       , 2,10000)
|| ' on '||namespace||'.'||item ||' from "'||pu.usename||'";' as grantsql
from 
(SELECT 
 use.usename as subject, 
 nsp.nspname as namespace, 
 c.relname as item, 
 c.relkind as type, 
 use2.usename as owner, 
 c.relacl 
 FROM 
 pg_user use 
 cross join pg_class c 
 left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
 left join pg_user use2 on (c.relowner = use2.usesysid)
 WHERE 
 c.relowner = use.usesysid  
 and  nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
 ORDER BY   subject,   namespace,   item 
) join pg_user pu on array_to_string(relacl, '|') like '%'||pu.usename||'%' 
where relacl is not null
 and pu.usename='test_55'

You can use a variation of this query to see if a user belongs to any groups (then use "alter group drop user "):

select usesysid, usename, nvl(groname,'default') from pg_user u 
left join pg_group g on ','||array_to_string(grolist,',')||','
  like '%,'||cast(usesysid as varchar(10))||',%' 
where usename='test_55' order by 2,1;

You can use this query to see if they have any schema grants:

select * from pg_namespace where nspowner > 1 and array_to_string(nspacl,',') like '%test_55%';

Problem

I am using a Redshift cluster. Version: ``` PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.735 ``` I just need to drop a user but it gives the following error message: ``` redshiftpocdb=# drop user test_55; ERROR: user "test_55" cannot be dropped because the user has a privilege on some object ``` Here is the output of the `\dp` command: ``` redshiftpocdb=# \dp Access privileges schema | name | type | access privileges --------+---------+-------+------------------- public | company | table | public | test2 | table | public | test22 | table | public | test222 | table | public | v_date | table | (5 rows) ``` In a Postgresql environment, we have the command `DROP OWNED BY` but it does not work in Redshift. How can I find out what privileges were granted to the `TEST_55` user? Is there any system view we can query ( for e..g in Oracle, we have DBA_ROLE_PRIVS, DBA_TAB_PRIVS...DBA_SYS_PRIVS .etc )?

Original source