Viewing User Roles

You can obtain information about roles in two ways:

• Verify specific role assignments with the function HAS_ROLE.
• View all available (granted) and enabled roles.
• Obtain comprehensive information about roles, the users assigned to them, and the privileges granted to those roles by querying system tables ROLES, USERS, and GRANTS, respectively.

  System tables do not show whether a role is available to a user indirectly through other roles. Call HAS_ROLE to obtain that information.

Verifying Role Assignments

The function HAS_ROLE checks whether a Vertica role is granted to the specified user or role. Non-superusers can use this function to check their own role membership. Superusers can use it to determine role assignments for other users and roles. You can also use Management Console to check role assignments.

In the following example, a dbadmin user checks whether user MikeL is assigned the admnistrator role:

=> \c
You are now connected as user "dbadmin".
=> SELECT HAS_ROLE('MikeL', 'administrator');
 HAS_ROLE
----------
 t
(1 row)

User MikeL checks whether he has the regional_manager role:

=> \c - MikeL
You are now connected as user "MikeL".
=> SELECT HAS_ROLE('regional_manager');
 HAS_ROLE
----------
 f
(1 row)

The dbadmin grants the regional_manager role to the administrator role. On checking again, MikeL verifies that he now has the regional_manager role:

dbadmin=> \c
You are now connected as user "dbadmin".
dbadmin=> GRANT regional_manager to administrator;
GRANT ROLE
dbadmin=> \c - MikeL
You are now connected as user "MikeL".
dbadmin=> SELECT HAS_ROLE('regional_manager');
 HAS_ROLE
----------
 t
(1 row)

Viewing Available and Enabled Roles

SHOW AVAILABLE ROLES lists all roles that are granted to you:

=> SHOW AVAILABLE ROLES;      
      name       |           setting
-----------------+-----------------------------
 available roles | logreader, logwriter
(1 row)

SHOW ENABLED ROLES lists the roles that are enabled in your session:

=> SHOW ENABLED ROLES;     
     name      | setting
---------------+----------
 enabled roles | logreader
(1 row)

Querying System Tables

You can query tables ROLES, USERS, and GRANTS, either separately or joined, to obtain detailed information about user roles, users assigned to those roles, and the privileges granted to them.

For example, the following query on system table USERS returns all users with the DBADMIN role. An asterisk (*) appended to a role name indicates that the user can grant this role to other users:

=> SELECT user_name, is_super_user, default_roles, all_roles FROM v_catalog.users WHERE all_roles ILIKE '%dbadmin%';
 user_name | is_super_user |            default_roles             |              all_roles
-----------+---------------+--------------------------------------+--------------------------------------
 dbadmin   | t             | dbduser*, dbadmin*, pseudosuperuser* | dbduser*, dbadmin*, pseudosuperuser*
 u1        | f             |                                      | dbadmin*
 u2        | f             |                                      | dbadmin
(3 rows)

The following query on ROLES returns the names of all roles users can access, and the roles that are granted (assigned) to those roles. An asterisk (*) appended to an assigned role indicates that the user can grant this role to other users:

=> SELECT * FROM roles;      
      name       | assigned_roles
-----------------+----------------
 public          |
 dbduser         |
 dbadmin         | dbduser*
 pseudosuperuser | dbadmin*
 logreader       |
 logwriter       |
 logadmin        | logreader, logwriter
(7 rows)