查看pg 用户组_如何查询PostgreSQL中的用户组权限？

So in postgresql I can do something like:

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

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.

Thanks!

解决方案

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

)