postgresql - view schema privileges

in console util psql:

\dn+

will show you

     Name      |  Owner   |   Access privileges   |      Description   

List all schemas with their priveleges for current user:

WITH "names"("name") AS (  SELECT n.nspname AS "name"    FROM pg_catalog.pg_namespace n      WHERE n.nspname !~ '^pg_'        AND n.nspname <> 'information_schema') SELECT "name",  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"    FROM "names";

The response will be for example:

  name   | create | usage ---------+--------+------- public  | t      | t test    | t      | t awesome | f      | f(3 rows)

In this example current user is not owner of the awesome schema.

As you could guess, similar request for particular schema:

SELECT  pg_catalog.has_schema_privilege(    current_user, 'awesome', 'CREATE') AS "create",  pg_catalog.has_schema_privilege(    current_user, 'awesome', 'USAGE') AS "usage";

and response:

 create | usage --------+------- f      | f

As you know, it's possible to use pg_catalog.current_schema() for current schema.

Of all the possible privileges

-- SELECT-- INSERT-- UPDATE-- DELETE-- TRUNCATE-- REFERENCES-- TRIGGER-- CREATE-- CONNECT-- TEMP-- EXECUTE-- USAGE

the only CREATE and USAGE allowed for schemas.

Like the current_schema() the current_user can be replaced with particular role.

BONUS with current column

WITH "names"("name") AS (  SELECT n.nspname AS "name"    FROM pg_catalog.pg_namespace n      WHERE n.nspname !~ '^pg_'        AND n.nspname <> 'information_schema') SELECT "name",  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE')  AS "usage",  "name" = pg_catalog.current_schema() AS "current"    FROM "names";--   name   | create | usage | current-- ---------+--------+-------+-----------  public  | t      | t     | t--  test    | t      | t     | f--  awesome | f      | f     | f-- (3 rows)

WITH | System Information Functions | GRANT (privileges)

The privileges are stored in the nspacl field of pg_namespace. Since it's an array field, you have to do a little fancy coding to parse it. This query will give you the grant statements used for users and groups:

select 'grant ' || substring(          case when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',usage ' else '' end           ||case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end        , 2,10000)|| ' on schema '||nspname||' to "'||pu.usename||'";' from pg_namespace pn,pg_user pu where  array_to_string(nspacl,',') like '%'||pu.usename||'%' --and pu.usename='<username>' and nspowner > 1 unionselect 'grant ' || substring(          case when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),pg.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end           ||case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),pg.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end        , 2,10000)|| ' on schema '||nspname||' to group "'||pg.groname||'";' from pg_namespace pn,pg_group pg where array_to_string(nspacl,',') like '%'||pg.groname||'%' --and pg.groname='<username>'  and nspowner > 1