Re: catalog views to check on grants

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-TABLE

Regards,

Juan José Santamaría Flecha

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012