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-
