我想查看红移补助金。
CREATE OR REPLACE VIEW view_all_grants AS
SELECT
use.usename as subject,nsp.nspname as namespace,c.relname as item,c.relkind as type,use2.usename as owner,c.relacl,(use2.usename != use.usename and c.relacl::text !~ ('({|,)' || use.usename || '=')) as public
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 or
c.relacl::text ~ ('({|,)(|' || use.usename || ')=')
ORDER BY
subject,namespace,item
哪个不起作用，因为relacl的:: text强制转换失败，并带有以下内容：
ERROR: cannot cast type aclitem[] to character varying [sql State=42846]
CREATE OR REPLACE VIEW view_all_grants AS
SELECT
use.usename as subject,c.relacl
--,)' || use.usename || '=')) as public
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
-- or c.relacl::text ~ ('({|,item
允许创建视图，但我担心这不会显示所有相关数据。
解决方法
另一种变化如下：
SELECT *
FROM
(
SELECT
schemaname,objectname,usename,HAS_TABLE_PRIVILEGE(usrs.usename,fullobj,'select') AND has_schema_privilege(usrs.usename,schemaname,'usage') AS sel,'insert') AND has_schema_privilege(usrs.usename,'usage') AS ins,'update') AND has_schema_privilege(usrs.usename,'usage') AS upd,'delete') AND has_schema_privilege(usrs.usename,'usage') AS del,'references') AND has_schema_privilege(usrs.usename,'usage') AS ref
FROM
(
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')
) AS objs,(SELECT * FROM pg_user) AS usrs
ORDER BY fullobj
)
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
and schemaname='<opt schema>'
and usename = '<opt username>';
