Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jPQHp-0006gy-K9 for pgsql-admin@arkaria.postgresql.org; Fri, 17 Apr 2020 12:40:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jPQHn-0002Tj-PG for pgsql-admin@arkaria.postgresql.org; Fri, 17 Apr 2020 12:39:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jPQHn-0002Tc-FO for pgsql-admin@lists.postgresql.org; Fri, 17 Apr 2020 12:39:59 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jPQHj-0001hS-Hb for pgsql-admin@lists.postgresql.org; Fri, 17 Apr 2020 12:39:59 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id E595DBE0D36 for ; Fri, 17 Apr 2020 14:39:53 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at rs.plausibolo.de Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Ko7bK6aO00nX; Fri, 17 Apr 2020 14:39:49 +0200 (CEST) Received: from [192.168.178.86] (i59F678A8.versanet.de [89.246.120.168]) by rs.plausibolo.de (Postfix) with ESMTPSA id 85216BE087B; Fri, 17 Apr 2020 14:39:49 +0200 (CEST) Subject: Re: catalog views to check on grants To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs Message-ID: Date: Fri, 17 Apr 2020 14:39:49 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.4.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------EEF97FBD3BEB8B7B94F0C490" Content-Language: de-DE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------EEF97FBD3BEB8B7B94F0C490 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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; Am 16.04.20 um 15:39 schrieb Juan José Santamaría Flecha: > > On Thu, Apr 16, 2020 at 2:11 PM Ankush Chawla > > 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 --------------EEF97FBD3BEB8B7B94F0C490 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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;



Am 16.04.20 um 15:39 schrieb Juan José Santamaría Flecha:

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