Reshift grants verification HAS_TABLE_PRIVILEGE fails

Question

I'm fighting with Redshit grants with groups, schemas, default privileges. When I try to check is everything is correct using the 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

I get this :

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.

What the hell is happening here ?

fez · Accepted Answer · 2016-11-10T09:24:12.273

I tried this and the query failed on the first result from pg_tables. In my case it was because I needed to explicitly state which schema each table was in. This worked for me:

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;

Reshift grants verification HAS_TABLE_PRIVILEGE fails

1 Answers1