如何在Postgresql中查询用户组特权? [英] How to query user group privileges in postgresql?

问题描述

因此在postgresql中，我可以做类似的事情:

So in postgresql I can do something like:

SELECT has_table_privilege('myuser', 'mytable', 'select')

以查看myuser是否具有对mytable的选择访问权限.用户组有类似之处吗?基本上，我希望能够提交查询，以查看组在指定表上是否具有某些特权.

to see whether myuser has select access on mytable. Is there something similar for user groups? Basically, I'd like to be able to submit a query to see if a group has certain privileges on a specified table.

谢谢！

推荐答案

Joe的视图看起来像是从

Joe's view looks like it was derived from How do I view grants on Redshift. Note this other useful query for finding missing permissions where in this example I want to grant read privs to the select_group to objects that don't have it:

select 'grant select on '||namespace||'.'||item||' to group select_group;' 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')
and ((array_to_string(relacl, '|') not like '%select_group%' or relacl is null)
    or 
   (array_to_string(relacl, '|') like '%select_group%' and CHARINDEX('r', SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), 'select_group', 2 ) , '/', 1 ) ) = 0 )
        )
and c.relkind   <> 'i'
ORDER BY  subject,   namespace,   item 
)

这篇关于如何在Postgresql中查询用户组特权?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！