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.