如上所述，我想列出所有用户 + 他们的模式（他们获得 USAGE 权限的地方）。
所以我快完成了，
让我告诉你我得到了什么：
SELECT
u.usesysid as idUser,
u.usename as login,
u.passwd as password,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as member_of,
ARRAY(SELECT nspname
FROM pg_namespace pn,pg_catalog.pg_roles b
JOIN pg_catalog.pg_auth_members m ON (m.member = b.oid)
WHERE pg_catalog.has_schema_privilege(b.rolname, nspname, 'USAGE')= true
AND m.member = r.oid
AND nspname <> 'information_schema'
AND nspname !~~ 'pg\_%') as usage_schemas
FROM
pg_catalog.pg_roles r
JOIN
pg_catalog.pg_user u on(r.rolname=u.usename)
WHERE
usename !~ 'postgres'
ORDER BY
1;
这就是我得到的：https : //image.noelshack.com/fichiers/2019/38/5/1568980459-capture5.png
这就是我想要的：https : //image.noelshack.com/fichiers/2019/38/5/1568967833-capture4.png
有谁能够帮我 ？
这是正确的：
SELECT
u.usesysid as idUtilisateur,
u.usename as login,
u.passwd as motDePasse,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as groupes,
ARRAY(SELECT nspname
FROM pg_namespace pn
WHERE pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE')= true
AND nspname <> 'information_schema'
AND nspname !~~ 'pg_%') as schemas
FROM
pg_catalog.pg_roles r
JOIN
pg_catalog.pg_user u on(r.rolname=u.usename)
WHERE
usename !~ 'postgres'
ORDER BY
1;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句