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

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-