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.klaver@gmail.com