在 Postgres 中系统目录 https://www.postgresql.org/docs/9.5/static/catalogs.html是有关安装和数据库的完整信息的基本集。系统目录是最可靠的信息来源。信息图式 https://www.postgresql.org/docs/9.5/static/information-schema.html作为辅助功能基于系统目录,并提供与其他 RDBM 的兼容性:
信息模式是在 SQL 标准中定义的,因此可以预期是可移植的并保持稳定 - 与系统目录不同,系统目录特定于 PostgreSQL,并根据实现问题进行建模。然而,信息模式视图不包含有关 PostgreSQL 特定功能的信息;要查询这些信息,您需要查询系统目录或其他 PostgreSQL 特定的视图。
物化视图不是 SQL 标准对象,因此信息模式不包含有关它们的信息。
系统目录pg_class
包含该列中有关权限的所有信息relacl
.
如果该列是null
那么所有者拥有所有特权。
空字符串作为用户名acl
字符串表示public
.
create materialized view test_view as select 1;
grant select on test_view to public;
grant delete on test_view to a_user;
select
coalesce(nullif(s[1], ''), 'public') as grantee,
s[2] as privileges
from
pg_class c
join pg_namespace n on n.oid = relnamespace
join pg_roles r on r.oid = relowner,
unnest(coalesce(relacl::text[], format('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl,
regexp_split_to_array(acl, '=|/') s
where nspname = 'public' and relname = 'test_view';
grantee | privileges
----------+------------
postgres | arwdDxt
public | r
a_user | d
(3 rows)
您需要一个功能来显示权限readable format:
create or replace function priviliges_from_acl(text)
returns text language sql as $$
select string_agg(privilege, ', ')
from (
select
case ch
when 'r' then 'SELECT'
when 'w' then 'UPDATE'
when 'a' then 'INSERT'
when 'd' then 'DELETE'
when 'D' then 'TRUNCATE'
when 'x' then 'REFERENCES'
when 't' then 'TRIGGER'
end privilege
from
regexp_split_to_table($1, '') ch
) s
$$;
Use:
select
coalesce(nullif(s[1], ''), 'public') as grantee,
priviliges_from_acl(s[2]) as privileges
from
pg_class c
join pg_namespace n on n.oid = relnamespace
join pg_roles r on r.oid = relowner,
unnest(coalesce(relacl::text[], format('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl,
regexp_split_to_array(acl, '=|/') s
where nspname = 'public' and relname = 'test_view';
grantee | privileges
----------+---------------------------------------------------------------
postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
public | SELECT
a_user | DELETE
(3 rows)