View to show privileges on views/tables/sequences/foreign tables - Mailing list pgsql-general

A while back I was looking for a way to display object privileges
quickly with a bit better readibility. The following view is what I
came up with. Suggestions and improvements welcome (or comments
stating that there are much easi\er ways to get the same details).

(was created in a utility "admin" schema)

create or replace view admin.object_privileges as
select  objtype,
        schemaname,
        objname,
        owner,
        objuser,
        privs,
        string_agg(
            (case   privs_individual
                    when 'arwdDxt' then 'All'
                    when '*' then 'Grant'
                    when 'r' then 'SELECT'
                    when 'w' then 'UPDATE'
                    when 'a' then 'INSERT'
                    when 'd' then 'DELETE'
                    when 'D' then 'TRUNCATE'
                    when 'x' then 'REFERENCES'
                    when 't' then 'TRIGGER'
                    when 'X' then 'EXECUTE'
                    when 'U' then 'USAGE'
                    when 'C' then 'CREATE'
                    when 'c' then 'CONNECT'
                    when 'T' then 'TEMPORARY'
            else 'Unknown: '||privs end
            ), ', ' ORDER BY privs_individual) as privileges_pretty
from    (select objtype,
                schemaname,
                objname,
                owner,
                privileges,
                (case when coalesce(objuser,'') is not distinct from
'' then 'public' else objuser end)
                    || (case when pr2.rolsuper then '*' else '' end)
                as objuser,
                privs,
                (case   when privs in ('*','arwdDxt') then privs
                        else regexp_split_to_table(privs,E'\\s*')
                end) as privs_individual
        from    (select distinct
                        objtype,
                        schemaname,
                        objname,
                        coalesce(owner,'') || (case when pr.rolsuper
then '*' else '' end) as owner,
                        regexp_replace(privileges,E'\/.*','') as privileges,

(regexp_split_to_array(regexp_replace(privileges,E'\/.*',''),'='))[1]
as objuser,

(regexp_split_to_array(regexp_replace(privileges,E'\/.*',''),'='))[2]
as privs
                from    (SELECT n.nspname as schemaname,
                                c.relname as objname,
                                CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'S' THEN 'sequence' END as objtype,

regexp_split_to_table(array_to_string(c.relacl,','),',') as
privileges,
                                pg_catalog.pg_get_userbyid(c.relowner) as Owner
                        FROM pg_catalog.pg_class c
                        LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
                        WHERE c.relkind IN ('r', 'v', 'S', 'f')
                        AND n.nspname !~ '(pg_catalog|information_schema)'
                        --AND pg_catalog.pg_table_is_visible(c.oid) /*
Uncomment to show only objects */
                        ) as y                                      /*
visible in search path */
                left join pg_roles pr on (pr.rolname = y.owner)
                ) as p2
        left join pg_roles pr2 on (pr2.rolname = p2.objuser)
        --where coalesce(p2.objuser,'') is distinct from '' /*
Uncomment to hide "public" role */
        ) as p3
group by objtype, schemaname,objname, owner, objuser, privs
order by objtype,schemaname,objname,objuser,privileges_pretty;

comment on column admin.object_privileges.owner is '"*" after the
owner indicates that the owner is a superuser';
comment on column admin.object_privileges.objuser is '"*" after the
objuser indicates that the objuser is a superuser';


select * from admin.object_privileges limit 10;

 objtype  | schemaname |        objname          |   owner   |
objuser    |  privs  |       privileges_pretty
----------+------------+-------------------------+-----------+-------------+---------+--------------------------------
 sequence | public     | event_id_seq            | postgres* |
postgres*   | rwU     | SELECT, USAGE, UPDATE
 sequence | public     | event_id_seq            | postgres* | foobar
    | rw      | SELECT, UPDATE
 table    | public     | network_events          | postgres* |
postgres*   | arwdDxt | All
 table    | public     | network_events          | postgres* | foobar
    | ar      | INSERT, SELECT
 table    | public     | network_events_201301   | postgres* |
postgres*   | arwdDxt | All
 table    | public     | network_events_201301   | postgres* | foobar
    | arwd    | INSERT, DELETE, SELECT, UPDATE
 table    | public     | network_events_201302   | postgres* |
postgres*   | arwdDxt | All
 table    | public     | network_events_201302   | postgres* | foobar
    | arwd    | INSERT, DELETE, SELECT, UPDATE
 table    | public     | network_events_20130211 | postgres* |
postgres*   | arwdDxt | All
 table    | public     | event                   | postgres* | foobar
    | ar*     | Grant, INSERT, SELECT