有没有查询可以运行，以显示特定模式上当前分配的权限？
例如，像这样分配的权限：
GRANT USAGE ON SCHEMA dbo TO MyUser
我已经尝试过
SELECT *
FROM information_schema.usage_privileges;
但是这只会将授权返回给内置的PUBLIC角色。相反，我想查看哪些用户已被授予对各种模式的特权。
注意：实际上我正在使用Amazon Redshift而不是纯PostgreSQL，尽管如果在Amazon Redshift中无法实现，我将接受一个纯PostgreSQL答案。（尽管我认为它是可能的）
在控制台实用程序psql中：
\dn+
将展示给你
Name | Owner | Access privileges | Description
lemon_service=U/postgres，但是 =U/postgres 是什么意思？ - J86U = 使用 (https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE)，而 /postgres 是关于谁授予了这个权限的信息。 - Clément Moulin - SimpleRezoWITH "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";
name | create | usage
---------+--------+-------
public | t | t
test | t | t
awesome | f | f
(3 rows)
awesome模式的所有者。SELECT
pg_catalog.has_schema_privilege(
current_user, 'awesome', 'CREATE') AS "create",
pg_catalog.has_schema_privilege(
current_user, 'awesome', 'USAGE') AS "usage";
并且回复：
create | usage
--------+-------
f | f
如你所知，可以使用pg_catalog.current_schema()来获取当前模式。
在所有可能的权限中
-- SELECT
-- INSERT
-- UPDATE
-- DELETE
-- TRUNCATE
-- REFERENCES
-- TRIGGER
-- CREATE
-- CONNECT
-- TEMP
-- EXECUTE
-- USAGE
模式只允许CREATE和USAGE。
与current_schema()类似，current_user可以被特定角色替换。
BONUS使用current列
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)
RECURSIVE 关键字在那里是否有任何非平凡的作用？PostgreSQL 文档似乎暗示一个真正的“递归”查询应该在其中有一个 UNION。 - user1071847RECURSIVE。感谢您指出。 - Ivan Black这就是psql在内部使用的方式 :)
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;
权限存储在 pg_namespace 的 nspacl 字段中。由于它是数组字段，您需要进行一些花式编码来解析它。此查询将为您提供用于用户和组的授予语句：
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
针对当前的问题，您可以尝试以下方法：
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
我曾经遇到在具有大量对象和用户的数据库上性能相当低的情况。因此，我找到了一个可能的解决方法，使用类似以下方式的默认函数aclexplode():
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\_%'
但要小心，最后一个查询语句并不包括用户从PUBLIC角色中获得的权限。
其中oid_to_rolname()是一个简单的自定义函数，使用SELECT rolname FROM pg_roles WHERE oid = $1语句。
就像@Jaisus一样，我的任务要求获取所有用户具有的权限。因此，我编写了类似于schema权限查询的table、views、columns、sequences、functions、database和甚至default权限查询。
此外，还有一个很有用的扩展程序pg_permission，我从中获取提供查询所需的逻辑，并仅仅是根据自己的需求进行升级。
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;
以下是适用于AWS Redshift的组合版本（组、用户、PUBLIC）：
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
在过去的一年中，这个问题没有更新。然而，据我所见，这个问题还有一个答案。
SELECT grantor, grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'userName';
这可以详细查看表的权限。
我认为这个答案适用于不属于预期用户的数据库。对于部分访问授权，您可以使用此方法验证对模式表的访问权限。
SELECT
n.nspname AS schema_name
FROM pg_namespace n
WHERE has_schema_privilege('my_user',n.nspname, 'CREATE, USAGE');
我知道这篇文章比较旧，但是基于不同的回答，我制作了另一个查询，以便后续使用时更加简短易用。
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
我一直在想，总有一天我会查询出所有权限并将其放在一个视图中...总有一天。 ;)
-E标志运行psql命令，查看执行\dp viewname查询时psql的操作。这应该可以帮助您入门。我不使用 Redshift，因此不会继续追究。 - Craig Ringer