Re: Ways to edit users and permissions for database

Excellent, Thank you. Exactly what I was looking for.

Am 14.03.2012 um 14:26 schrieb Adrian Klaver:

> On 03/14/2012 12:59 AM, Alexander Reichstadt wrote:
>> Thanks, creation works fine, but how do I read existing permissions through SQL, is there some SELECT-statement I can use?
>>
>
> Not sure what you want, all permissions for a user(role), permissions for an object or some other combination but here are a few suggestions:
>
> http://www.postgresql.org/docs/9.0/static/functions-info.html
>
> Look at table 9-48
>
> If you run psql with the -E switch you get the system queries that are generated by using the various \ commands.
>
> psql -E -d test -U aklaver
>
> So for example finding the privileges for a table :
>
> test=> \dp big_int_test
>
> ********* QUERY **********
> SELECT n.nspname as "Schema",
> c.relname as "Name",
> CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S' THEN 'sequence' END as "Type",
> pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
> pg_catalog.array_to_string(ARRAY(
> SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ')
> FROM pg_catalog.pg_attribute a
> WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
> ), E'\n') AS "Column access privileges"
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r', 'v', 'S')
> AND c.relname ~ '^(big_int_test)$'
> AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1, 2;
> **************************
>
> Access privileges
> Schema | Name | Type | Access privileges | Column access privileges
> --------+--------------+-------+-------------------+--------------------------
> public | big_int_test | table | |
>
>
> As the above indicates the query uses the system catalogs information on which can be found here:
>
> http://www.postgresql.org/docs/9.0/static/catalogs.html
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general