Обсуждение: "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 <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. regards, tom lane
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
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-