Re: Listing privileges on a schema

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(at)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(dot)klaver(at)gmail(dot)com