打印数据库级别权限的 Postgresql 查询

Question

我现在试图只显示用户，模式和表，但也显示模式和表所属的数据库。关于我应该在此查询中添加什么的任何建议？

SELECT
u.usename as user, t.schemaname as schema, t.tablename as table,
has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'select') AS "Select",
has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'insert') AS "Insert",
has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'update') AS "Update",
has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'delete') AS "Delete",
has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'references') AS "Reference"
FROM pg_user u
CROSS JOIN pg_tables t
WHERE t.schemaname != 'information_schema' and t.schemaname != 'pg_internal' and t.schemaname != 'pg_catalog' and t.tablename not like '% %'
ORDER BY u.usename, t.schemaname, t.tablename;

Answer 1

我建议研究一下information_schema.*。这些是SQL标准，information_schema.tables应该有你想要的。

您可以按如下方式替换pg_tables的使用：

select
u.usename                                                                           as user,
t.table_catalog                                                                     as database,
t.table_schema                                                                      as schema,
t.table_name                                                                        as table,
has_table_privilege(u.usename, t.table_schema || '.' || t.table_name, 'select')     as "Select",
has_table_privilege(u.usename, t.table_schema || '.' || t.table_name, 'insert')     as "Insert",
has_table_privilege(u.usename, t.table_schema || '.' || t.table_name, 'update')     as "Update",
has_table_privilege(u.usename, t.table_schema || '.' || t.table_name, 'delete')     as "Delete",
has_table_privilege(u.usename, t.table_schema || '.' || t.table_name, 'references') as "Reference"
from
pg_user                              u
cross join information_schema.tables t
where
t.table_schema != 'information_schema' and t.table_schema != 'pg_internal' and t.table_schema != 'pg_catalog'
and t.table_schema not like '% %'
order by
u.usename, t.table_schema, t.table_name;