postgresql - view schema 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
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
in console util psql:
\dn+
will show you
Name | Owner | Access privileges | Description
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;
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)