Roles defined in Redshift

Question

Hello,
There are several people in our group that create their own redshift database in a provisioned cluster. I created a database in that cluster as well. Now I want to define roles in database and attach RLS (Row level security) policy to the roles I create. After creating first role I got error that role already exists.
1) How do I see what roles are already created in a Redshift database? 
2) Can I see info about the roles - created/updated date and any policies attached to the roles in Redshift? 
3)  Is it possible to use same roles across different Redshift databases in same cluster and databases across different clusters?

Answer

To fetch the list of roles and the role owner you can use the below query:
```
SELECT role_name,role_owner FROM svv_roles;
```
Use SVV_RLS_POLICY to view a list of all row-level security policies created on the Amazon Redshift cluster.

```
SELECT * FROM svv_rls_policy;
```
Use SVV_RLS_RELATION to view a list of all relations that are RLS-protected. For example the below query should help you find the relation name, database name , schema name etc.

```
SELECT datname, relschema, relname, relkind, is_rls_on FROM svv_rls_relation ORDER BY relname;
```

Below are some links which would help you understand more about the tables & their respective columns.

https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_RLS_RELATION.html

https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_RLS_POLICY.html

https://docs.amazonaws.cn/en_us/redshift/latest/dg/r_SVV_ROLES.html

Roles defined in Redshift

Relevant content