Skip site navigation (1) Skip section navigation (2)

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

From: Christian Hammers <ch(at)lathspell(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: "SHOW GRANTS FOR username" or why \z is not enough for me
Date: 2012-07-01 17:20:05
Message-ID: 20120701192005.05b83a62@james.intern (view raw or flat)
Thread:
Lists: pgsql-novice
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-

Responses

pgsql-novice by date

Next:From: LewDate: 2012-07-01 17:39:43
Subject: Re: "SHOW GRANTS FOR username" or why \z is not enough for me
Previous:From: Tom LaneDate: 2012-06-29 21:38:47
Subject: Re: Problem installing PostGIS because of pg_config

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group