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@xxxxxxxxx> 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-TABLE
Regards,
Juan José Santamaría Flecha
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
