The SHOW ROLES statement lists the roles for all databases.
Since the keywords ROLES and USERS can now be used interchangeably in SQL statements for enhanced PostgreSQL compatibility, SHOW ROLES is now an alias for SHOW USERS.
Synopsis
Required privileges
The role must have the SELECT privilege on the system.users and system.role_members tables.
Example
> SHOW ROLES;
username | options | member_of | estimated_last_login_time
-----------+----------------+-----------+------------------------------
admin | {CREATEROLE} | {} | NULL
carl | {NOLOGIN} | {} | NULL
petee | {} | {} | 2025-08-04 19:18:00.201402+00
root | {CREATEROLE} | {admin} | NULL
(4 rows)
See also
SHOW ROLES
Synopsis: ```sql SHOW ROLES [WITH option [, ...]] [LIMIT count]
-- Available options: -- SOURCE = 'source_string' -- LAST LOGIN BEFORE timestamp_expr ```
Description: Lists all roles in the cluster, including both user and non-user roles. The SHOW ROLES statement now supports the same optional filtering clauses as SHOW USERS, allowing you to filter roles by their provisioning source, last login time, and limit the number of returned rows.
Required privileges: The user must be a member of the admin role or have the VIEWACTIVITY privilege.
Parameters:
| Parameter | Description | Required |
|---|---|---|
SOURCE = 'source_string' |
filters roles by their provisioning source (matches the PROVISIONSRC role option value) |
No |
LAST LOGIN BEFORE timestamp_expr |
filters roles whose estimated last login time is before the given timestamp | No |
count |
limits the number of returned rows to the specified count | No |
Examples:
-- Show all roles
SHOW ROLES;
-- Show roles provisioned from a specific LDAP source
SHOW ROLES WITH SOURCE = 'ldap:ldap.example.com';
-- Show roles that haven't logged in since a specific date
SHOW ROLES WITH LAST LOGIN BEFORE '2025-01-01';
-- Combine multiple filters and limit results
SHOW ROLES WITH SOURCE = 'ldap:ldap.example.com', LAST LOGIN BEFORE '2025-01-01' LIMIT 10;
-- Limit results without other filters
SHOW ROLES LIMIT 5;
The estimated_last_login_time field is computed on a best-effort basis and is not guaranteed to capture every login event. Roles with a NULL estimated_last_login_time are excluded from results when using the LAST LOGIN BEFORE filter.
Duplicate options (e.g., SOURCE = 'a', SOURCE = 'b') produce a parse error.
See also:
- SHOW USERS
- CREATE ROLE
- DROP ROLE
Updated SHOW USERS
The SHOW USERS statement syntax should be updated to reflect the same options (if not already documented):
Synopsis: ```sql SHOW USERS [WITH option [, ...]] [LIMIT count]
-- Available options: -- SOURCE = 'source_string' -- LAST LOGIN BEFORE timestamp_expr ```
Description: Lists all users in the cluster. Supports optional filtering clauses to filter users by their provisioning source, last login time, and limit the number of returned rows.
Parameters: [Same as SHOW ROLES above]
Examples:
-- Show users provisioned from a specific OIDC source
SHOW USERS WITH SOURCE = 'oidc:okta.example.com';
-- Show users that haven't logged in recently, limited to 5 results
SHOW USERS WITH LAST LOGIN BEFORE '2024-12-01' LIMIT 5;
SHOW USERS and SHOW ROLES are interchangeable — both statements return the same data and now support the same filtering syntax.
See also:
- SHOW ROLES
- CREATE USER
- ALTER USER
