postgresql 如何在postgresql中找到授予用户的权限

how to find the privileges granted to user in postgresql

提问by user3258784

I am using redshift cluster db.

我正在使用 redshift 集群数据库。

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 one user. It gives below error message:

我只需要删除一个用户。它给出了以下错误消息：

redshiftpocdb=# drop user test_55;
ERROR:  user "test_55" cannot be dropped because the user has a privilege on some object

i am pasing \dp output:

我正在传递 \dp 输出：

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 physical postgresql environment, we have a command DROP OWNED BY. but this command is not working in redshift.

在物理 postgresql 环境中，我们有一个命令 DROP OWNED BY。但是这个命令在红移中不起作用。

so, further how to find out the privielges granted to TEST_55 ? is there any views to query it ( for e..g in Oracle, we have DBA_ROLE_PRIVS, DBA_TAB_PRIVS...DBA_SYS_PRIVS .etc )

那么，进一步如何找出授予 TEST_55 的特权？是否有任何视图可以查询它（例如在 Oracle 中，我们有 DBA_ROLE_PRIVS、DBA_TAB_PRIVS...DBA_SYS_PRIVS .etc）

Thank you

谢谢

回答by mike_pdb

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 "):

您可以使用它来查找他们拥有的任何表（然后运行“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 "):

您可以使用此查询的变体来查看用户是否属于任何组（然后使用“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%';

回答by drtf

Another variation, to get all users' privilege organized together:

另一个变体，将所有用户的权限组织在一起：

WITH 
usrs as (SELECT * FROM pg_user),
objs as (
  SELECT 
    schemaname, 't' AS obj_type,
    tablename AS objectname,
    schemaname + '.' + tablename AS fullobj
  FROM pg_tables
  WHERE schemaname not in ('pg_internal')
  UNION
  SELECT 
    schemaname, 'v' AS obj_type, 
    viewname AS objectname, 
    schemaname + '.' + viewname AS fullobj 
  FROM pg_views
  WHERE schemaname NOT IN ('pg_internal')
),
query as (
  SELECT 
    schemaname,
    objectname,
    usename,
    HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AS sel,
    HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AS ins,
    HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AS upd,
    HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AS del,
    HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AS ref
  FROM objs, usrs
  ORDER BY fullobj
)
SELECT * FROM query
WHERE (
  sel = TRUE 
  OR ins = TRUE 
  OR upd = TRUE 
  OR del = TRUE 
  OR ref = TRUE
) AND schemaname='[optional schemaname]'
  AND usename = '[optional username]';

回答by Fabio Comune di Firenze

I had to use || to concatenate strings, and a little difference as I have case-sensitive object names '"' || schemaname || '"."' || tablename || '"' AS fullobj '"' || schemaname || '"."' || viewname || '"' AS fullobj

我不得不使用 || 连接字符串，有点不同，因为我有区分大小写的对象名称 '"' || schemaname || '"."' || tablename || '"' AS fullobj '"' || schemaname || '"。 "' || 视图名 || '"' AS fullobj

instead of schemaname + '.' + tablename AS fullobj schemaname + '.' + viewname AS fullobj

而不是 schemaname + '.' + 表名 AS fullobj 模式名 + '.' + viewname AS fullobj