postgresql - view schema privileges

Solution 1:

in console util psql:

\dn+

will show you

     Name      |  Owner   |   Access privileges   |      Description   

Solution 2:

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)

Solution 3:

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 
union
select 
'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 

Solution 4:

This is what psql uses internally :)

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_' AND n.nspname <> 'information_schema'
ORDER BY 1;