Re: "SHOW GRANTS FOR username" or why \z is not enough for me
От | Christian Hammers |
---|---|
Тема | Re: "SHOW GRANTS FOR username" or why \z is not enough for me |
Дата | |
Msg-id | 20120703014228.11c82a45@james.intern обсуждение исходный текст |
Ответ на | Re: "SHOW GRANTS FOR username" or why \z is not enough for me (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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-
В списке pgsql-novice по дате отправления: