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?
Another way to do this is to use the information_schema schema and query the table_privileges table as: $ SELECT * FROM information_schema. table_privileges LIMIT 5; The above query will show detailed information about user privileges on databases as well as tables.
To create a materialized view in another user's schema: You must have the CREATE ANY MATERIALIZED VIEW system privilege. The owner of the materialized view must have the CREATE TABLE system privilege.
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)
Following klin's helpful answer, I've come up with a view that lists a summary of all privileges for all relations that appear in pg_class
(tables, views, m. views, indexes, sequences, foreign tables, composite types) for all roles:
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
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With