Ways to edit users and permissions for database

On 03/14/12 12:38 AM, Alexander Reichstadt wrote:

this was probably asked dozens of times before, but I couldn't find where, and neither in the docs and what I found on the web didn't make sense. I found how to create users and check their permissions using terminal. But I need to alter and create users and permissions through libpq or SQL directly. I also found there to be a reference on the INFORMATION_SCHEMA, but still couldn't make sense out of these tables in the given context. This is to make a user administration inside the client frontend. What approach would be recommended for this purpose?

SQL commands like...

CREATE USER freddy WITH PASSWORD 'something';
CREATe DATABASE freddb OWNER freddy;

issued same as any other SQL queries, via libpq etc.

note, the INFORMATION_SCHEMA is read only as its all implemented as
VIEW's...

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

Thanks, creation works fine, but how do I read existing permissions through SQL, is there some SELECT-statement I can use?

Am 14.03.2012 um 08:43 schrieb John R Pierce:

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

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

Am 14.03.2012 um 14:26 schrieb Adrian Klaver: