postgresql - view schema privileges

Sql Problem Overview

Is there a query I can run to show currently assigned privileges on a particular schema?

i.e. privileges that were assigned like so:

GRANT USAGE ON SCHEMA dbo TO MyUser

I have tried

SELECT *
FROM information_schema.usage_privileges;

but this only returns grants to the built-in PUBLIC role. Instead, I want to see which users have been granted privileges on the various schema.

Note: I'm actually using Amazon Redshift rather than pure PostgreSQL, although I will accept a pure PostgreSQL answer if this is not possible in Amazon Redshift. (Though I suspect it is)

Sql Solutions

Solution 1 - Sql

in console util psql:

\dn+

will show you

     Name      |  Owner   |   Access privileges   |      Description

Solution 2 - Sql

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 - Sql

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 - Sql

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;

Solution 5 - Sql

Try this one (works for PUBLIC role):

SELECT nspname,
       coalesce(nullif(role.name,''), 'PUBLIC') AS name,
       substring(
          CASE WHEN position('U' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', USAGE' ELSE '' END 
          || CASE WHEN position('C' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', CREATE' ELSE '' END 
       , 3,10000) AS privileges
FROM pg_namespace pn, (SELECT pg_roles.rolname AS name
   FROM pg_roles UNION ALL SELECT '' AS name) AS role
 WHERE (','||array_to_string(nspacl,',')) LIKE '%,'||role.name||'=%'
 AND nspowner > 1;

Solution 6 - Sql

Combined version (groups, users, PUBLIC) that works for AWS Redshift:

    SELECT *
FROM (SELECT 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 AS rights,
             nspname AS schema,
             '' AS role,
             pu.usename AS user
      FROM pg_namespace pn,
           pg_user pu
      WHERE ARRAY_TO_STRING(nspacl,',') LIKE '%' ||pu.usename|| '%'
      --and pu.usename='<username>' 
      AND   nspowner > 1
      
  UNION
      
      SELECT 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 as rights,
             nspname AS schema,
             pg.groname AS role,
             '' AS user
      FROM pg_namespace pn,
           pg_group pg
      WHERE ARRAY_TO_STRING(nspacl,',') LIKE '%' ||pg.groname|| '%'
      --and pg.groname='<username>' 
      AND   nspowner > 1
      
  UNION
      
      SELECT CASE
               WHEN POSITION('U' IN SPLIT_PART(SPLIT_PART((',' ||array_to_string (nspacl,',')),',' ||roles.name|| '=',2),'/',1)) > 0 THEN ' USAGE'
               ELSE ''
             END 
      || CASE
               WHEN POSITION('C' IN SPLIT_PART(SPLIT_PART((',' ||array_to_string (nspacl,',')),',' ||roles.name|| '=',2),'/',1)) > 0 THEN ' CREATE'
               ELSE ''
             END AS rights,
             nspname AS schema,
             COALESCE(NULLIF(roles.name,''),'PUBLIC') AS role,
             '' AS user
      FROM pg_namespace pn,
           (SELECT pg_group.groname AS name
            FROM pg_group
            UNION ALL
            SELECT '' AS name) AS roles
      WHERE (',' ||array_to_string (nspacl,',')) LIKE '%,' ||roles.name|| '=%'
      AND   nspowner > 1) privs
      
ORDER BY schema,rights

Solution 7 - Sql

For current question can try this one:

SELECT r.rolname AS role_name,
	   n.nspname AS schema_name,
	   p.perm AS privilege
FROM pg_catalog.pg_namespace AS n
	CROSS JOIN pg_catalog.pg_roles AS r
	CROSS JOIN (VALUES ('USAGE'), ('CREATE')) AS p(perm)
WHERE has_schema_privilege(r.oid, n.oid, p.perm)
--		AND n.nspname <> 'information_schema'
--		AND n.nspname !~~ 'pg\_%'
--		AND NOT r.rolsuper

Could be pretty low in performance at database with a lot of objects and users with which I have come across. So i've got possible workaround using aclexplode() default function like this:

SELECT 	oid_to_rolname(a.grantee) AS role_name,
	    n.nspname AS schema_name,
		a.privilege_type AS privilege_type
FROM pg_catalog.pg_namespace AS n,
		aclexplode(nspacl) a
WHERE n.nspacl IS NOT NULL 
		AND oid_to_rolname(a.grantee) IS NOT NULL 
--		AND n.nspname <> 'information_schema'
--		AND n.nspname !~~ 'pg\_%'

But, be careful, last one doesn't include privileges which users have obtained from PUBLIC role. Where oid_to_rolname() is simple custom function SELECT rolname FROM pg_roles WHERE oid = $1.

And, like @Jaisus, my task required to have all privileges which all users have. So i have similar to schema privileges queries for table, views, columns, sequences, functions, database and even default privileges.

Also, there is helpful extension pg_permission where I get logic for provided queries and just upgraded it for my purposes.

Solution 8 - Sql

I know this post is old but I made another query based on the different answers to have one that is short and easy to use afterward :

select
	nspname as schema_name
	, r.rolname as role_name
	, pg_catalog.has_schema_privilege(r.rolname, nspname, 'CREATE') as create_grant
	, pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE') as usage_grant
from pg_namespace pn,pg_catalog.pg_roles r
where array_to_string(nspacl,',') like '%'||r.rolname||'%' 
	and nspowner > 1

I keep thinking one day I will make a query to have all rights in only one view... One day. ;)

Solution 9 - Sql

Even more concisely, one can do:

  SELECT 
    n.nspname AS schema_name
   FROM pg_namespace n
  WHERE  has_schema_privilege('my_user',n.nspname, 'CREATE, USAGE');

Solution 10 - Sql

For the last year there was no update on this question. However as I see it there is one more answers to this question.

SELECT grantor, grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'userName';

This can give a detailed look on the table privileges.

I saw this answer fit for the databases that are not owned by the supposed user. For partial access grant you can use this to verify the access on the schema's tables.

Content Type	Original Author	Original Content on Stackoverflow
Question	mwrichardson	View Question on Stackoverflow
Solution 1 - Sql	senz	View Answer on Stackoverflow
Solution 2 - Sql	Ivan Black	View Answer on Stackoverflow
Solution 3 - Sql	mike_pdb	View Answer on Stackoverflow
Solution 4 - Sql	Denys	View Answer on Stackoverflow
Solution 5 - Sql	Andrei Petrovici	View Answer on Stackoverflow
Solution 6 - Sql	xvga	View Answer on Stackoverflow
Solution 7 - Sql	Volodymyr Vintonyak	View Answer on Stackoverflow
Solution 8 - Sql	Jaisus	View Answer on Stackoverflow
Solution 9 - Sql	JGH	View Answer on Stackoverflow
Solution 10 - Sql	Catastrophe	View Answer on Stackoverflow