Liste der Zuschüsse und Privilegien für eine materialisierte Ansicht in PostgreSQL

Ich brauche, um zu bestimmen, welche Berechtigungen werden gewährt für einige materialisierten sichten in meiner Datenbank.

Die Abfrage für eine Tabelle oder standard-Ansicht ist ziemlich 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;

Sagte, es scheint nicht zu sein, eine analoge Tabelle für materialisierte Ansichten. Wo kommt PostgreSQL speichern Sie diese Informationen?

Deine Abfrage funktioniert für normale Ansichten, aber nicht für materialisierte Ansichten.

InformationsquelleAutor Luke Sapan | 2016-08-02

In Postgres system-Kataloge sind die grundlegenden vollständige Informationen über die installation und Datenbanken. System-Katalogen sind die zuverlässigste Quelle von Informationen.
Informationen schema als Hilfs-Funktion ist abhängig von den system-Katalogen und wird für Kompatibilität mit anderen RDBMs:

Informationen-schema definiert ist, die im SQL-standard und kann daher erwartet werden tragbar sein und stabil bleiben — im Gegensatz zu den system-Kataloge, die speziell auf PostgreSQL und modelliert werden, die nach Umsetzung betrifft. Die informationsschemasichten sind jedoch nicht enthalten Informationen über PostgreSQL-spezifischen Funktionen, zu Fragen, über diejenigen, die Sie brauchen, um eine Abfrage der system-Kataloge oder andere PostgreSQL-spezifische sichten.

Materialisierte Ansichten sind nicht SQL-standard-Objekte, damit die information schema nicht enthalten Informationen über Sie.

Den system-Katalog pg_class enthält alle Informationen über die Vorrechte in der Spalte relacl.

Wenn die Spalte null dann der Eigentümer hat alle Privilegien.

Einen leeren string als user-name in acl string 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)

Benötigen Sie eine Funktion, um zu zeigen, Privilegien in lesbar 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 
$$;

Verwenden:

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)

Vielen Dank, das half ungemein. Ich finde es sehr interessant, dass PostgreSQL können Sie gewähren Privilegien, wie z.B. UPDATE, INSERT und DELETE auf einer materialisierten Sicht. Ist es nur, weil Sie planen, die zur Unterstützung dieser Vorgänge in der Zukunft?
Das ist eher ein feature von Postgres-Privilegien-system. In der Regel, ein Privileg gewährt werden können, auf ein Objekt, auch wenn es nicht anwendbar ist.
"System-Katalogen sind die zuverlässigste Quelle von Informationen." Wo sind postgresqls informationsschemasichten Rückkehr unzuverlässige Informationen?
Echo @MikeSherrill'CatRecall s point, vielleicht wäre es richtiger zu sagen, dass die systemkataloge sind die definitive Quelle der Informationen, oder die komplett Quelle der information? Die Informationen schema ist nur so genau, es ist einfach unvollständig. Richtig?
genau den Punkt. Ich habe entfernt mein vorherigen Kommentar als etwas irreführend.

InformationsquelleAutor klin

Folgenden klin die hilfreiche Antwort, ich habe eine Ansicht mit einer Auflistung eine Zusammenfassung aller Privilegien für alle Beziehungen angezeigt, die in pg_class (Tabellen, Ansichten, m. views, Indizes, Sequenzen, fremde Tabellen, zusammengesetzten Typen) für alle Rollen:

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
);

InformationsquelleAutor gilad mayani

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.