HAS_TABLE_PRIVILEGE query failing

Question

Hi,  
I use the Redshift admin views for quite a lot, and noticed that at least one of them no longer works for some reason. I'm unsure if it's due to a recent maintenance period or something else. The view in question is: https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_get_tbl_priv_by_user.sql  
  
Here's the error I get

```
ERROR:  3F000: schema "pg_temp_118" does not exist
LOCATION:  LookupExplicitNamespace, /home/ec2-user/padb/src/pg/src/backend/catalog/namespace.c:1338
```

I distilled the query down to this, and it still fails:

```
SELECT
        schemaname
        ,tablename
        ,HAS_TABLE_PRIVILEGE('admin', schemaname || '.' || tablename, 'select') AS sel
FROM pg_tables;
```

Removing the cross join (and almost everything else) didn't fix it. However, it works if I remove the HAS_TABLE_PRIVILEGE field, so it appears to be a problem with that function.

AWS-User-2965899 · Answer

I tested this on numerous versions and am not able to reproduce the issue.   
  
Recommend dropping and recreating the view as a first step.  
  
Also check that your user is a superuser or you have limited the results to items on which you have permissions.

mmasseth · Answer

Note that I tested a straight select on pg_tables, and it threw an error, regardless of the view, or what user ran the query.  
  
However testing it now works, which is strange. It must have just been a temporary thing. Nothing appears to have changed.

HAS_TABLE_PRIVILEGE query failing

相关内容