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