Re: Ways to edit users and permissions for database

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