permissions - Redshift 授予验证 HAS_TABLE_PRIVILEGE 失败

我正在与 Redshit 授予组、模式、默认权限作斗争。
当我尝试使用 HAS_TABLE_PRIVILEGE 检查一切是否正确时

select tablename,
   HAS_TABLE_PRIVILEGE('analyst', tablename, 'select') as select,
   HAS_TABLE_PRIVILEGE('analyst', tablename, 'insert') as insert,
   HAS_TABLE_PRIVILEGE('analyst', tablename, 'update') as update,
   HAS_TABLE_PRIVILEGE('analyst', tablename, 'delete') as delete,
   HAS_TABLE_PRIVILEGE('analyst', tablename, 'references') as references
from pg_tables
where schemaname='datalab'
order by tablename

我明白了:

An error occurred when executing the SQL command:
select tablename,
   HAS_TABLE_PRIVILEGE('analyst', tablename, 'select') as select,
   HAS_TABLE_PRIVILEGE('analyst', tablena...

[Amazon](500310) Invalid operation: relation "dss__transaction" does not exist;

Execution time: 0.2s
1 statement failed.

这里到底发生了什么？

最佳答案

我尝试了这个，但查询在 pg_tables 的第一个结果上失败。就我而言，这是因为我需要明确说明每个表所在的架构。这对我有用:

WITH cte AS
(
SELECT schemaname+'.'+tablename AS table_name
FROM pg_tables
WHERE schemaname = 'datalab'
)
SELECT table_name,
   HAS_TABLE_PRIVILEGE('analyst', table_name, 'select') AS select,
   HAS_TABLE_PRIVILEGE('analyst', table_name, 'insert') AS insert,
   HAS_TABLE_PRIVILEGE('analyst', table_name, 'update') AS update,
   HAS_TABLE_PRIVILEGE('analyst', table_name, 'delete') AS delete,
   HAS_TABLE_PRIVILEGE('analyst', table_name, 'references') AS references
FROM cte
ORDER BY table_name ASC;

关于permissions - Redshift 授予验证 HAS_TABLE_PRIVILEGE 失败，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40482137/