pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: catalog views to check on grants
Date: Fri, 17 Apr 2020 14:39:49 +0200
Message-ID: <ca94919e-16c6-be54-b5bc-44d67caf6386@jakobs.com> (raw)
In-Reply-To: <CAC+AXB1KNGHMuWs2-FOLjkSxacG=OmCsODDqA1Aomv2s=odenA@mail.gmail.com>
References: <CAOY8JhCoeuFu5QFsiYtCynfnLH6u-YA4xaocV=_OnqtBbJXS=A@mail.gmail.com>
	<CAC+AXB1KNGHMuWs2-FOLjkSxacG=OmCsODDqA1Aomv2s=odenA@mail.gmail.com>

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 <mailto: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

view thread (3+ messages)

Message-ID: <ca94919e-16c6-be54-b5bc-44d67caf6386@jakobs.com>
Permalink:  ../ca94919e-16c6-be54-b5bc-44d67caf6386@jakobs.com/
Also on:    postgresql.org/message-id/ca94919e-16c6-be54-b5bc-44d67caf6386@jakobs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: holger@jakobs.com
  Subject: Re: catalog views to check on grants
  In-Reply-To: <ca94919e-16c6-be54-b5bc-44d67caf6386@jakobs.com>
  References: <CAOY8JhCoeuFu5QFsiYtCynfnLH6u-YA4xaocV=_OnqtBbJXS=A@mail.gmail.com> <CAC+AXB1KNGHMuWs2-FOLjkSxacG=OmCsODDqA1Aomv2s=odenA@mail.gmail.com> <ca94919e-16c6-be54-b5bc-44d67caf6386@jakobs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox