列出PostgreSQL中实例化视图的授予和特权 [英] List grants and privileges for a materialized view in PostgreSQL

问题描述

我需要确定当前为数据库中某些物化视图授予的特权。

I need to determine what privileges are currently granted for some materialized views in my database.

对于表或标准视图执行此查询非常简单：

The query to do this for a table or standard view is pretty straight forward:

SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.table_privileges
WHERE table_schema = 'some_schema' AND table_name = 'some_table'
GROUP by grantee;

话说回来，对于物化视图似乎没有类似的表格。 PostgreSQL在哪里存储此信息？

That said, there doesn't seem to be an analogous table for materialized views. Where does PostgreSQL store this information?

推荐答案

在Postgres中系统目录是有关安装和数据库的完整信息的基本集合。系统目录是最可靠的信息源。
信息模式作为辅助功能基于在系统目录上提供，并与其他RDBM兼容：

In Postgres system catalogs are the basic set of complete information about the installation and databases. System catalogs are the most reliable source of information. Information schema as an auxiliary feature is based on system catalogs and is provided for compatibility with other RDBMs:

信息模式在SQL标准中定义，因此可以预期是可移植且保持稳定-与系统目录不同，后者是PostgreSQL特有的，是根据实现问题建模的。但是，信息模式视图不包含有关PostgreSQL特定功能的信息。查询那些您需要查询系统目录或其他特定于PostgreSQL的视图。

The information schema is defined in the SQL standard and can therefore be expected to be portable and remain stable — unlike the system catalogs, which are specific to PostgreSQL and are modeled after implementation concerns. The information schema views do not, however, contain information about PostgreSQL-specific features; to inquire about those you need to query the system catalogs or other PostgreSQL-specific views.

材料化视图不是SQL标准对象，因此信息模式不包含有关它们的信息。

Materialized views are not SQL-standard objects hence the information schema does not contain information about them.

系统目录 pg_class 在 relacl 。

如果列为 null ，则所有者拥有所有特权。

If the column is null then the owner has all privileges.

在 acl 字符串中作为用户名的空字符串表示 public 。

An empty string as a user name in acl string means 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)

您需要一个函数以可读的方式显示特权格式：

You need a function to show privileges in 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 
$$;

使用：

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)

这篇关于列出PostgreSQL中实例化视图的授予和特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！