Thread: Ways to edit users and permissions for database
Hi, this was probably asked dozens of times before, but I couldn't find where, and neither in the docs and what I found on theweb didn't make sense. I found how to create users and check their permissions using terminal. But I need to alter andcreate 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 administrationinside the client frontend. What approach would be recommended for this purpose? Thanks Alex
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 onthe web didn't make sense. I found how to create users and check their permissions using terminal. But I need to alterand 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 administrationinside 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/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 onthe web didn't make sense. I found how to create users and check their permissions using terminal. But I need to alterand 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 administrationinside 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 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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: > 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 canuse? >> > > Not sure what you want, all permissions for a user(role), permissions for an object or some other combination but hereare 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general