On 08/16/2013 10:27 AM, David Salisbury wrote:
Hello,
Is there a query out there where I can get a list of permissions
associated to a schema?
Something like the below query that I can do for a table, but for a
schema instead?
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='sites' order by grantee,
privilege_type;
I'm not seeing anything on the net or anything useful in
information_schema like a
'role_schema_grants' view, and it doesn't appear I can do a \dp on a
schema.
Nor the query psql uses for \dp on a table doesn't seem to have a nice
way to
convert it to a schema permissions list. The secret is escaping me. :(
aklaver@killi:~> psql -d test -U postgres -E
psql (9.0.13)
Type "help" for help.
test=# \dn+
********* QUERY **********
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE (n.nspname !~ '^pg_temp_' OR
n.nspname = (pg_catalog.current_schemas(true))[1])
ORDER BY 1;
**************************
List of schemas
Name | Owner | Access privileges |
Description
--------------------+----------+----------------------+----------------------------------
information_schema | postgres | postgres=UC/postgres+|
| | =U/postgres |
pg_catalog | postgres | postgres=UC/postgres+| system catalog
schema
| | =U/postgres |
pg_toast | postgres | | reserved schema
for TOAST tables
pg_toast_temp_1 | postgres | |
public | postgres | postgres=UC/postgres+| standard public
schema
| | =UC/postgres |
(5 rows)
-ds
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
