CODEWITHSUNDEEP
amazon-redshiftuser-permissions
SwapSays
SwapSays

Reputation: 465

Error while querying user grants "relation "sql_features" does not exist"

I want to identify all the grants my Redshift user have on various objects in the schema for which I am using the following code, but getting the error.

SELECT
    u.usename,
    t.schemaname||'.'||t.tablename,
    has_table_privilege(u.usename,t.tablename,'select') AS user_has_select_permission
FROM
    pg_user u
CROSS JOIN
    pg_tables t
WHERE
    u.usename = 'userid'

ERROR: 42P01: relation "sql_features" does not exist

Upvotes: 3

Views: 4503

Answers (1)

SwapSays
SwapSays

Reputation: 465

Thanks @blamblam for pointing me to a working solution.

Moreover, I also got to know from that post, is that I did a mistake of just passing the object name whereas I need to pass the fully qualified object name (schema_name.object_name).

So, after the above change, the query looks likes this and working:

SELECT
    u.usename,
    t.schemaname||'.'||t.tablename,
    has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'select') AS user_has_select_permission
FROM
    pg_user u
CROSS JOIN
    pg_tables t
WHERE
    u.usename = 'userid'

Upvotes: 4

Related Questions