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;
That said, there doesn't seem to be an analogous table for materialized views. Where does PostgreSQL store this information?
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:
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.
Materialized views are not SQL-standard objects hence the information schema does not contain information about them.
The system catalog pg_class
contains all informations on privileges in the column relacl
.
If the column is null
then the owner has all privileges.
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
$$;
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)