以下是适用于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 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 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
-E
标志运行psql
命令,查看执行\dp viewname
查询时psql
的操作。这应该可以帮助您入门。我不使用 Redshift,因此不会继续追究。 - Craig Ringer