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

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 (view raw or flat)
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

pgsql-novice by date

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

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