Skip site navigation (1) Skip section navigation (2)

Re: "SHOW GRANTS FOR username" or why \z is not enough for me

From: Christian Hammers <ch(at)lathspell(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: "SHOW GRANTS FOR username" or why \z is not enough for me
Date: 2012-07-02 23:42:28
Message-ID: 20120703014228.11c82a45@james.intern (view raw or flat)
Thread:
Lists: pgsql-novice
Am Sun, 01 Jul 2012 16:03:08 -0400
schrieb Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Christian Hammers <ch(at)lathspell(dot)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(at)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-

In response to

pgsql-novice by date

Next:From: Lasma SietinsoneDate: 2012-07-03 10:55:58
Subject: PLDOC for PostgreSQL?
Previous:From: Monte MilanukDate: 2012-07-02 20:18:12
Subject: Re: SQL - learning trail?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group