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

From: Lew <noone(at)lewscanon(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: "SHOW GRANTS FOR username" or why \z is not enough for me
Date: 2012-07-01 17:39:43
Message-ID: jsq20p$ct2$1@news.albasani.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2012-07-01 20:03:08 Re: "SHOW GRANTS FOR username" or why \z is not enough for me
Previous Message Christian Hammers 2012-07-01 17:20:05 "SHOW GRANTS FOR username" or why \z is not enough for me