I am trying out Redshift and at first, I am trying to access queries I ran in the past but was hit by this error: ERROR: permission denied for relation stl_query and tried to grant access as well but no help.
AWS explicitly mentions that: STL_QUERY is visible to all users., check here: https://docs.aws.amazon.com/redshift/latest/dg/r_STL_QUERY.html
and when I tried to run the below query, it gave me that permission denied error.
SELECT query, DATEDIFF(microseconds, starttime, endtime)/1000000.0 as duration
FROM STL_QUERY
WHERE query = pg_last_query_id();
I am logged in as an admin user and also ran the below commands to explicitly grant permission on that table but it didn't help, I ran into the same issue.
GRANT SELECT ON STL_QUERY TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO admin;
Redshift Serverless is just that, serverless. The base catalog tables are not accessible to you because you don't have a dedicated cluster - you are on a "timeshare" instance.
For Redshift Serverless you need to use the set of special System Views that allow you to see the save information without seeing anything from another user's Redshift Serverless activity. The one you likely need is SYS_QUERY_HISTORY - https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_HISTORY.html