3

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

barbsan
  • 3,418
  • 11
  • 21
  • 28
SwapSays
  • 407
  • 7
  • 18
  • where did you read that this world work on redshift? provide a link? (postgres <> redshift) – Jon Scott Jul 04 '19 at 15:12
  • Did u mean 'would' instead of 'world' ? Because if not, I do not get the sarcasm :) – SwapSays Jul 05 '19 at 16:22
  • yes it was a typo and does not have any sarcasm even with the misspelling – Jon Scott Jul 05 '19 at 22:19
  • Here it is: https://chartio.com/learn/amazon-redshift/how-to-view-permissions-in-amazon-redshift/ And I even used this earlier and it worked as far as I can remember. – SwapSays Jul 07 '19 at 10:11
  • 1
    You can view your grants as resolved in this question https://stackoverflow.com/questions/18741334/how-do-i-view-grants-on-redshift – blamblam Apr 21 '20 at 07:24
  • Thanks @blamblam I posted an answer just to mark this as solved. – SwapSays Apr 24 '20 at 05:01

1 Answers1

3

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'
SwapSays
  • 407
  • 7
  • 18