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

Hello

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?

CREATE OR REPLACE VIEW view_all_grants AS
SELECT
use.usename as subject,
nsp.nspname as namespace,
c.relname as item,
c.relkind as type,
use2.usename as owner,
c.relacl,
(use2.usename != use.usename and c.relacl::text !~ ('({|,)' || use.usename || '=')) as public
FROM
pg_user use
cross join pg_class c
left join pg_namespace nsp on (c.relnamespace = nsp.oid)
left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE
c.relowner = use.usesysid or
c.relacl::text ~ ('({|,)(|' || use.usename || ')=')
ORDER BY
subject,
namespace,
item
;

SELECT * FROM view_all_grants WHERE subject = 'root' and public = false;

BTW, are there any functions to work with the "aclitem" type?

bye,

-christian-

Christian Hammers wrote:
> 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

"Postgres" commands?

These are psql commands, or more precisely, meta-commands. They are
documented, unsurprisingly, in the psql documentation.
<http://www.postgresql.org/docs/9.1/static/app-psql.html>

It looks like "\dp" is what you want. It won't format things the way you did,
but it might have the information you seek.

The manual is your first choice for information, is it not?

> with WHERE though and are more useful to show the owner of an object
> not to show all objects owned by a user.

Of course they don't work with WHERE clauses because they aren't SQL.

> 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?
>
> CREATE OR REPLACE VIEW view_all_grants AS
> SELECT
> use.usename as subject,
> nsp.nspname as namespace,
> c.relname as item,
> c.relkind as type,
> use2.usename as owner,
> c.relacl,
> (use2.usename != use.usename and c.relacl::text !~ ('({|,)' || use.usename || '=')) as public
> FROM
> pg_user use
> cross join pg_class c
> left join pg_namespace nsp on (c.relnamespace = nsp.oid)
> left join pg_user use2 on (c.relowner = use2.usesysid)
> WHERE
> c.relowner = use.usesysid or
> c.relacl::text ~ ('({|,)(|' || use.usename || ')=')
> ORDER BY
> subject,
> namespace,
> item
> ;
>
>
> SELECT * FROM view_all_grants WHERE subject = 'root' and public = false;

It is very unwise to repurpose SQL keywords like PUBLIC.

Assuming you have a logical quantity, call it "ispublic", you don't need to
compare to FALSE, just use the quantity:

... AND NOT ispublic

> BTW, are there any functions to work with the "aclitem" type?

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

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.

regards, tom lane

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-