Re: "SHOW GRANTS FOR username" or why \z is not enough for me - Mailing list pgsql-novice

Am Sun, 01 Jul 2012 16:03:08 -0400
schrieb Tom Lane <tgl@sss.pgh.pa.us>:

> Christian Hammers <ch@lathspell.de> writes:
> > As a newbie Postgres admin I like to double check that my users have
> > all necessary rights and more important only those and no more.
>
> > All Postgres commands like \dp, \dt, \dn etc. cannot be filtered
> > with WHERE though and are more useful to show the owner of an object
> > not to show all objects owned by a user.
>
> > My best approach so far is the following but I took me a while to
> > build and I somehow think that there must be a more elegant solution
> > like "SHOW GRANTS FOR foo" in MySQL. Any ideas?
>
> has_table_privilege() and sibling functions might help you with that.
> The approach you propose is full of holes --- most importantly, that
> it will not report privileges held by virtue of being a member of a
> group, such as PUBLIC.

has_table_privilege() has the disadvantage that it needs a privilege
as parameter and I don't want to test all possible values in a loop.

Therefore I still try to extract the roles from pg_class.relacl but now
check them with pg_has_role() which luckily checks recursive which
also makes it possible to report "group" memberships.

Below is my improved version which seems to work quite well now and
produces the following output:

postgres@root=# SELECT * FROM view_all_grants WHERE subject = 'root';
 subject | namespace |    relname    | relkind |  owner   |                    relacl                    |
relaclitemuser| via_owner | via_groupowner | via_user | via_group | via_public  

---------+-----------+---------------+---------+----------+----------------------------------------------+----------------+-----------+----------------+----------+-----------+------------
 root    | public    | by_group      | r       | postgres | {postgres=arwdDxt/postgres,wheel=r/postgres} | wheel
 | f         | f              | f        | t         | f 
 root    | public    | by_groupowner | r       | wheel    |                                              | !NULL!
 | f         | t              | f        | f         | f 
 root    | public    | by_owner      | r       | root     |                                              | !NULL!
 | t         | f              | f        | f         | f 
 root    | public    | by_public     | r       | postgres | {postgres=arwdDxt/postgres,=r/postgres}      |
 | f         | f              | f        | f         | t 
 root    | public    | by_user       | r       | postgres | {postgres=arwdDxt/postgres,root=r/postgres}  | root
 | f         | f              | t        | f         | f 
...

CREATE OR REPLACE VIEW view_all_grants AS
SELECT * FROM (
    SELECT
      use.usename as subject,
      nsp.nspname as namespace,
      c.relname,
      c.relkind,
      pg_authid.rolname as owner,
      c.relacl,
      c.relaclitemuser,
      use.usename = pg_authid.rolname as via_owner,
      case
        when use.usename = pg_authid.rolname then false
        else pg_has_role(use.usename, pg_authid.rolname, 'member')
      end as via_groupowner,
      use.usename = c.relaclitemuser as via_user,
      case
        when c.relaclitemuser = '' then false -- acl for public role
        when c.relaclitemuser = '!NULL!' then false -- pg_class.relacl was null
        when c.relaclitemuser = use.usename then false -- pg_has_role(x,x) is always true
        else pg_has_role(use.usename, c.relaclitemuser, 'member') -- does recursive lookup
      end as via_group,
      relaclitemuser = '' as via_public
    FROM
      pg_user use
      cross join (
          SELECT
            *,
            split_part(relaclitem, '=', 1) as relaclitemuser
          FROM (
          SELECT
        relnamespace,
        relname,
        relkind,
        relowner,
        relacl,
        CASE
          WHEN relacl is null THEN '!NULL!='
          ELSE unnest(relacl::text[])
        END as relaclitem
              FROM
        pg_class
          ) as sub_c
      ) as c
      left join pg_namespace nsp on (c.relnamespace = nsp.oid)
      left join pg_authid on (c.relowner = pg_authid.oid) -- users and groups
    ) as via
WHERE
  via_owner or via_groupowner or via_user or via_group or via_public
ORDER BY
  subject,
  namespace,
  relname
;

bye,

-christian-