Подтвердить что ты не робот

Список грантов и привилегий для материализованного представления в PostgreSQL

Мне нужно определить, какие привилегии в настоящее время предоставляются для некоторых материализованных представлений в моей базе данных.

Запрос для этого для таблицы или стандартного представления довольно прост:

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 хранит эту информацию?

Ответ 1

В Postgres системные каталоги являются базовым набором полной информации об установке и базах данных. Системные каталоги являются наиболее надежным источником информации. Информационная схема как вспомогательная функция основана на системных каталогах и предоставляется для совместимости с другими 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)

Вам нужна функция для отображения привилегий в читаемом формате:

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)

Ответ 2

После полезного ответа на klin я представил представление, в котором перечислены все привилегии для всех отношений, которые появляются в pg_class (таблицы, представления, м. представления, индексы, последовательности, внешние таблицы, составные типы ) для всех ролей:

CREATE VIEW show_privileges AS (
    SELECT
        grantee,
        string_agg(relname, ', ' ORDER BY relname) AS rel_names,
        privileges
    FROM (
        SELECT 
            relname,
            coalesce(nullif(s[1], ''), 'public') grantee, 
            (SELECT string_agg(privilege, ', ' ORDER BY privilege ASC)
                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 AS privilege
                    FROM regexp_split_to_table(s[2], '') ch
                ) s
            ) AS privileges
        FROM 
            pg_class
            JOIN pg_namespace ON pg_namespace.oid = relnamespace
            JOIN pg_roles ON pg_roles.oid = relowner,
            unnest(coalesce(relacl::text[], format('{%s=arwdDxt/%s}', rolname, rolname)::text[])) AS acl,
            regexp_split_to_array(acl, '=|/') AS s
        WHERE nspname = 'public'
    ) AS t
    GROUP BY grantee, privileges
    ORDER BY grantee, privileges, rel_names
);