Thread: catalog views to check on grants
Please share the dictionary tables to view the grants on objects, system and object privileges etc.
Ankush Chawla
Please share the dictionary tables to view the grants on objects, system and object privileges etc.
select relname,
(select rolname from pg_roles where oid = grantor) as grantor,
(select rolname from pg_roles where oid = grantee) as grantee,
privilege_type,
is_grantable
from (select relname,
(aclexplode(relacl)).grantor,
(aclexplode(relacl)).grantee,
(aclexplode(relacl)).privilege_type,
(aclexplode(relacl)).is_grantable
from pg_class
where relacl is not null) as pg_class_privs;
[1] https://www.postgresql.org/docs/current/catalogs.html
[2] https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE
Maybe this helps:
DROP FUNCTION IF EXISTS table_privs(text);
CREATE FUNCTION table_privs(text) RETURNS TABLE (rolename text, tablename regclass, privs text)
AS $body$
SELECT $1,c.oid::regclass, array_to_string(ARRAY(
SELECT privs FROM unnest(ARRAY [
(CASE WHEN has_table_privilege($1, c.oid, 'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1, c.oid, 'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1, c.oid, 'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1, c.oid, 'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1, c.oid, 'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1, c.oid, 'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1, c.oid, 'TRIGGER') THEN 'TRIGGER' ELSE NULL END)])
foo(privs)
WHERE privs IS NOT NULL), ' ')
FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid
WHERE n.nspname NOT IN ('information_schema','pg_catalog','sys')
AND c.relkind='r'
AND has_table_privilege($1,c.oid,'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
AND has_schema_privilege($1,c.relnamespace,'USAGE')
$body$ LANGUAGE SQL;
-- =# select * from table_privs('test_user');
DROP FUNCTION IF EXISTS database_privs(text);
CREATE FUNCTION database_privs(text) RETURNS TABLE(rolename text, dbname name, privs text)
AS $body$
SELECT $1, datname, array_to_string(ARRAY(SELECT privs FROM unnest(ARRAY[
(CASE WHEN has_database_privilege($1,c.oid,'CONNECT') THEN 'CONNECT' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMPORARY') THEN 'TEMPORARY' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMP') THEN 'CONNECT' ELSE NULL END)])
foo(privs)
WHERE privs IS NOT NULL), ' ')
FROM pg_database c
WHERE has_database_privilege ($1, c.oid, 'CONNECT,CREATE,TEMPORARY,TEMP') AND datname <> 'template0';
$body$ LANGUAGE SQL;
-- =# select * from database_privs('test_user');
DROP FUNCTION IF EXISTS tablespace_privs(text);
CREATE FUNCTION tablespace_privs(text) RETURNS TABLE(rolename text, tablespacename name, privs text)
AS $body$
SELECT $1, spcname, array_to_string(
ARRAY[(CASE
WHEN has_tablespace_privilege($1,spcname,'CREATE')
THEN 'CREATE'
ELSE NULL
END)], ' ')
FROM pg_tablespace
WHERE has_tablespace_privilege($1,spcname,'CREATE');
$body$ LANGUAGE SQL;
-- =# select * from tablespace_privs('test_user');
DROP FUNCTION IF EXISTS fdw_wrapper_privs(text);
CREATE FUNCTION fdw_wrapper_privs(text) RETURNS TABLE(rolename text, fdwname name, privs text)
AS $body$
SELECT $1, fdwname, array_to_string(
ARRAY[(CASE
WHEN has_foreign_data_wrapper_privilege($1,fdwname,'USAGE')
THEN 'USAGE'
ELSE NULL
END)], ' ')
FROM pg_catalog.pg_foreign_data_wrapper
WHERE has_foreign_data_wrapper_privilege($1,fdwname,'USAGE');
$body$ LANGUAGE SQL;
-- =# select * from fdw_wrapper_privs('test_user');
DROP FUNCTION IF EXISTS foreign_server_privs(text);
CREATE FUNCTION foreign_server_privs(text) RETURNS table(rolename text, servername name, privs text)
AS $body$
SELECT $1, srvname, array_to_string(
ARRAY[(CASE
WHEN has_server_privilege($1,srvname,'USAGE')
THEN 'USAGE'
ELSE NULL
END)], ' ')
FROM pg_catalog.pg_foreign_server WHERE has_server_privilege ($1,srvname,'USAGE');
$body$ LANGUAGE SQL;
-- =# select * from foreign_server_privs('test_user');
DROP FUNCTION IF EXISTS language_privs(text);
CREATE FUNCTION language_privs(text) RETURNS TABLE(rolename text, languagename name, privs text)
AS $body$
SELECT $1, lanname, array_to_string(
ARRAY[(CASE
WHEN has_language_privilege($1,lanname,'USAGE')
THEN 'USAGE'
ELSE NULL
END)], ' ')
FROM pg_catalog.pg_language
WHERE has_language_privilege($1,lanname,'USAGE');
$body$ LANGUAGE SQL;
-- =# select * from language_privs('test_user');
DROP TYPE priv_t CASCADE;
CREATE TYPE priv_t AS (rolename text, functionname text, privs text);
DROP FUNCTION IF EXISTS function_privs(text);
CREATE FUNCTION function_privs(text) RETURNS SETOF priv_t
AS $body$
DECLARE
funcname text;
funcoid oid;
proc_rec record;
parmoid text;
parmlist text;
BEGIN
FOR proc_rec IN SELECT * from pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
LOOP
funcoid = proc_rec.oid;
SELECT string_agg (typname, ',') INTO parmlist
FROM pg_type
WHERE oid::text IN (select unnest(regexp_split_to_array(proc_rec.proargtypes::text, ' '::text)));
funcname = proc_rec.proname || '(' || parmlist || ')';
BEGIN
RETURN QUERY SELECT $1, funcname, array_to_string(
ARRAY[(CASE
WHEN has_function_privilege($1, funcname, 'EXECUTE')
THEN 'EXECUTE'
ELSE NULL
END)], ' ')
FROM pg_catalog.pg_proc
WHERE oid = funcoid
AND has_function_privilege($1,funcname,'EXECUTE');
EXCEPTION WHEN OTHERS THEN
END;
END LOOP;
RETURN;
END;
$body$ LANGUAGE PLpgSQL;
-- =# select * from function_privs('postgres');
DROP FUNCTION IF EXISTS schema_privs(text);
CREATE FUNCTION schema_privs(text) RETURNS TABLE(rolename text, schemaname name, privs text)
AS $body$
SELECT $1, c.nspname, array_to_string(
ARRAY(
SELECT privs FROM unnest(ARRAY[
(CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])
foo(privs)
WHERE privs IS NOT NULL), ' ')
FROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE');
$body$ LANGUAGE SQL;
-- =# select * from schema_privs('test_user');
DROP FUNCTION IF EXISTS view_privs(text);
CREATE FUNCTION view_privs(text) RETURNS TABLE(rolename text, viewname name, privs text)
AS $body$
SELECT $1, c.oid::name, array_to_string(
ARRAY(SELECT privs FROM unnest(ARRAY [
(CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)])
foo(privs)
WHERE privs is not null), ' ')
FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid
WHERE n.nspname NOT IN ('information_schema','pg_catalog','sys')
AND c.relkind='v'
AND has_table_privilege($1,c.oid,'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
AND has_schema_privilege($1,c.relnamespace,'USAGE')
$body$ LANGUAGE SQL;
DROP FUNCTION IF EXISTS sequence_privs(text);
CREATE FUNCTION sequence_privs(text) RETURNS TABLE(rolename text, sequencename name, privs text)
AS $body$
SELECT $1, c.oid::name, array_to_string(
ARRAY(SELECT privs FROM unnest(ARRAY [
(CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'USAGE') THEN 'UPDATE' ELSE NULL END)])
foo(privs)
WHERE privs IS NOT NULL), ' ')
FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid
WHERE n.nspname NOT IN ('information_schema','pg_catalog','sys')
AND c.relkind='S'
AND has_table_privilege($1,c.oid,'SELECT,UPDATE,USAGE')
AND has_schema_privilege($1,c.relnamespace,'USAGE')
$body$ LANGUAGE SQL;
-- You can also form a union of the results of above functions to view the privileges on objects something like given below:
/*
SELECT * FROM (
SELECT rolename, 'SCHEMA' as object_type, schemaname as object_name, privs
FROM schema_privs('test_user')
UNION ALL
SELECT rolename, 'TABLE' as object_type, relname::name as object_name ,privs
FROM table_privs('test_user')
) x order by 2;
*/
DROP FUNCTION IF EXISTS all_privileges (text, text);
CREATE FUNCTION all_privileges (rolenames text, objecttypes text)
RETURNS TABLE (rolename text, objecttype text, objectname name, privs text)
AS $body$
DECLARE
rolenames_arr text[];
rn text;
objecttypes_arr text[];
ot text;
BEGIN
rolenames_arr := regexp_split_to_array(rolenames, ',');
objecttypes_arr := regexp_split_to_array(objecttypes, ',');
FOREACH rn IN ARRAY rolenames_arr
LOOP
rn = trim(rn);
FOREACH ot IN ARRAY objecttypes_arr
LOOP
ot = trim(ot);
CASE upper(ot)
WHEN 'TABLE' THEN
RETURN QUERY
SELECT table_privs.rolename, 'TABLE'::text, tablename::name, table_privs.privs
FROM table_privs(rn);
WHEN 'DATABASE' THEN
RETURN QUERY
SELECT database_privs.rolename, 'DATABASE'::text, dbname::name, database_privs.privs
FROM database_privs(rn);
WHEN 'TABLESPACE' THEN
RETURN QUERY
SELECT tablespace_privs.rolename, 'TABLESPACE'::text, tablespacename::name, tablespace_privs.privs
FROM tablespace_privs(rn);
WHEN 'FDW_WRAPPER' THEN
RETURN QUERY
SELECT fdw_wrapper_privs.rolename, 'FDW_WRAPPER'::text, fdwname::name, fdw_wrapper_privs.privs
FROM fdw_wrapper_privs(rn);
WHEN 'FOREIGN_SERVER' THEN
RETURN QUERY
SELECT foreign_server_privs.rolename, 'FOREIGN_SERVER'::text, servername::name, foreign_server_privs.privs
FROM foreign_server_privs(rn);
WHEN 'LANGUAGE' THEN
RETURN QUERY
SELECT language_privs.rolename, 'LANGUAGE'::text, languagename::name, language_privs.privs
FROM language_privs(rn);
WHEN 'FUNCTION' THEN
RETURN QUERY
SELECT function_privs.rolename, 'FUNCTION'::text, functionname::name, function_privs.privs
FROM function_privs(rn);
WHEN 'SCHEMA' THEN
RETURN QUERY
SELECT schema_privs.rolename, 'SCHEMA'::text, schemaname::name, schema_privs.privs
FROM schema_privs(rn);
WHEN 'VIEW' THEN
RETURN QUERY
SELECT view_privs.rolename, 'VIEW'::text, viewname::name, view_privs.privs
FROM view_privs(rn);
WHEN 'SEQUENCE' THEN
RETURN QUERY
SELECT sequence_privs.rolename, 'SEQUENCE'::text, sequencename::name, sequence_privs.privs
FROM sequence_privs(rn);
ELSE
RAISE EXCEPTION 'Nonexistent object type --> "%"', ot;
END CASE;
END LOOP;
END LOOP;
RETURN;
END;
$body$ LANGUAGE PLpgSQL;
SELECT * FROM all_privileges ('comma-separated list of role names', 'TABLE,SCHEMA');
DROP FUNCTION IF EXISTS agg_privileges (text, text);
CREATE FUNCTION agg_privileges (rolenames text, objecttypes text)
RETURNS TABLE (rolename text, objecttype text, noofobjects bigint, objectname text, privs text)
AS $body$
SELECT rolename, objecttype, count(*) as number_of_objects, string_agg (objectname, ', ') as objects, privs
FROM all_privileges (rolenames, objecttypes)
GROUP BY rolename, objecttype, privs
$body$ LANGUAGE SQL;
On Thu, Apr 16, 2020 at 2:11 PM Ankush Chawla <ankushchawla03@gmail.com> wrote:
Please share the dictionary tables to view the grants on objects, system and object privileges etc.Different objects have a different set of privileges, and there is not a single view for then all. You can check the system catalogs [1] for a specific object and check its privileges as an aclitem[] [2], e.g. for relations you can check pg_class grants using a query like so:
select relname,
(select rolname from pg_roles where oid = grantor) as grantor,
(select rolname from pg_roles where oid = grantee) as grantee,
privilege_type,
is_grantable
from (select relname,
(aclexplode(relacl)).grantor,
(aclexplode(relacl)).grantee,
(aclexplode(relacl)).privilege_type,
(aclexplode(relacl)).is_grantable
from pg_class
where relacl is not null) as pg_class_privs;
[1] https://www.postgresql.org/docs/current/catalogs.html
[2] https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLERegards,Juan José Santamaría Flecha
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012