How to query the role assigned to table in Redshift? ( & roles assigned to user, permission to role )

Question

I have provisioned the redshift Serverless workspace. I have created sample tables, roles and user. Assigned permission to roles and roles to table. all working fine as accepted. I have to fetch the roles and permission via SQL query . seems like redshift do not give permission to query following key tables like

select * from pg_role;

ERROR: permission denied for relation pg_role [ErrorId: 1-62b24d6a-2506f055101eddb375a1614c]

similar

SELECT user_name,role_name,admin_option FROM svv_user_grants;

ERROR: permission denied for relation svv_user_grants [ErrorId: 1-62b24e36-77998cd06d89764f40fb5589]

SELECT role_name,role_owner FROM svv_roles

ERROR: permission denied for relation svv_roles [ErrorId: 1-62b24e4d-68d1bd5866d49a653339e204]

results for this query is always empty

select * FROM information_schema.role_table_grants

Wondering if someone know how to query users(s) assign to roles and those roles to tables ( plus roles permission like select / update etc ).

thanks !

I'm also very interested in this issue. Did you find any answer? ...Actually, I was just able to list them through svv_roles! — Cristian Scutaru, Dec 17 '22 at 19:25

score 0 · Answer 1 · answered Feb 14 '23 at 13:18

0

To get table-level role access details, you can use svv_relation_privileges view. As you can see output is below.

select * from svv_relation_privileges WHERE namespace_name = 'security_testing'

answered Feb 14 '23 at 13:18

Ankush Bindra

413
2
4
14

How to query the role assigned to table in Redshift? ( & roles assigned to user, permission to role )

1 Answers1