skip to Main Content

I don’t think I’ve every asked such a simple question 🙁

But how does one List Roles in Redshift ?

For groups you simply select from pg_groups.

Not so for roles, and surprisingly little documentation.

What am I missing ?

3

Answers


  1. To list all roles defined in the Redshift cluster you can run

    SELECT rolname FROM pg_roles;
    
    Login or Signup to reply.
  2. You can use the SVV_ROLES system view.

    It does not contain a lot of information, but gives you:

    • role_id (integer) The role ID.
    • role_name (text) The name of the role.
    • role_owner (text) The name of the role owner.
    • external_id (text) The unique identifier of the role in the third-party identity provider.
    Login or Signup to reply.
  3. I am a Redshift specialist.

    This may be of interest;

    https://www.redshiftresearchproject.org/white_papers/downloads/users_groups_roles_and_privileges.pdf

    The official docs for roles are pretty much useless. That PDF will give you what the docs do not.

    Note that roles can be granted to roles, so listing all roles isn’t completely helpful. You need to recurse, building up all the privs held by a role, either by itself, or by roles granted to it.

    The Redshift Combobulator has a first page for roles, which does this, but it’s in closed beta.

    https://www.redshiftresearchproject.org/combobulator/docs/cluster_roles_privs_v1.html

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search