Viewing a User's Role

The HAS_ROLE() function lets you see if a role has been granted to a user.

Non-superusers can check their own role membership using HAS_ROLE('role_name'), but only a superuser can look up other users' memberships using the user_name parameter. Omitting the user_name parameter will return role results for the superuser who is calling the function.

How to View a User's Role

In this example, user Bob wants to see if he's been assigned the logwriter command. The output returns Boolean value t for true, denoting that Bob is assigned the specified logwriter role:

Bob=> SELECT HAS_ROLE('logwriter'); 
 HAS_ROLE
----------
 t
(1 row)

In this example, a superuser wants to verify that the logadmin role has been granted to user Ted:

dbadmin=> SELECT HAS_ROLE('Ted', 'logadmin');

The output returns boolean value t for true, denoting that Ted is assigned the specified logadmin role:

 HAS_ROLE
----------
 t
(1 row)

Note that if a superuser omits the user_name argument, the function looks up that superuser's role. The following output indicates that this superuser is not assigned the logadmin role:

dbadmin=> SELECT HAS_ROLE('logadmin'); 
 HAS_ROLE
----------
 f
(1 row)

Output of the function call with user Alice indicates that she is not granted the logadmin role:

dbadmin=> SELECT HAS_ROLE('Alice', 'logadmin'); 
 HAS_ROLE
----------
 f
(1 row)

To view additional information about users, roles and grants, you can also query the following system tables in the V_CATALOG schema to show directly-assigned roles:

Note that the system tables do not indicate whether a role is available to a user when roles could be available through other roles (indirectly). You need to call the HAS_ROLE() function for that information.

Users

This command returns all columns from the USERS system table:

=> SELECT * FROM users;
-[ RECORD 1 ]
------------------+---------------------------
user_id           | 45035996273704962
user_name         | dbadmin
is_super_user     | t
profile_name      | default
is_locked         | f
lock_time         |
resource_pool     | general
memory_cap_kb     | unlimited
temp_space_cap_kb | unlimited
run_time_cap      | unlimited
all_roles         | dbadmin*, pseudosuperuser*default_roles     
                  | dbadmin*, pseudosuperuser*

Note: An asterisk (*) in table output for all_roles and default_roles columns indicates a role granted WITH ADMIN OPTION.

Roles

The following command returns all columns from the ROLES system table:

=> SELECT * FROM roles;
   role id         |    name         | assigned_roles
-------------------+-----------------+-------------------
 45035996273704964 | public          | 
 45035996273704964 | dbduser         |
 45035996273704964 | dbadmin         | dbduser*
 45035996273704964 | pseudosuperuser | dbadmin*

Grants

The following command returns all columns from the GRANTS system table:

=> SELECT * FROM grants; 
 grantor | privileges_description | object_schema | object_name | grantee
---------+------------------------+---------------+-------------+---------
 dbadmin | USAGE                  |               | public      | public
 dbadmin | USAGE                  |               | v_internal  | public
 dbadmin | USAGE                  |               | v_catalog   | public
 dbadmin | USAGE                  |               | v_monitor   | public
(4 rows)

Viewing User Roles on Management Console

You can see an MC user's roles and database resources through the MC Settings > User management page on the Management Console interface. For more information, see About MC Privileges and Roles.