The SHOW USERS statement lists the users for all databases.
Since the keywords ROLES and USERS can now be used interchangeably in SQL statements for enhanced PostgreSQL compatibility, SHOW USERS is now an alias for SHOW ROLES.
Synopsis
Required privileges
The user must have the SELECT privilege on the system.users and system.role_members tables.
Example
> SHOW USERS;
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)
Alternatively, within the built-in SQL shell, you can use the \du shell command:
> \du
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
Updated Documentation for SHOW USERS
SHOW USERS
Use the SHOW USERS statement to list all users in a cluster, with optional filtering and result limiting.
Synopsis:
SHOW USERS [WITH <options>] [LIMIT <n>]
Where <options> can include:
- SOURCE = <string>
- LAST LOGIN BEFORE <expr>
Description:
The SHOW USERS statement lists all database users and roles in the cluster. You can filter the results by provisioning source or last login time, and limit the number of rows returned. The statement displays the username, user options (including provisioning source if set), and role memberships.
Parameters:
| Parameter | Description | Required |
|---|---|---|
SOURCE |
Filter users by provisioning source (e.g., 'ldap:ldap.example.com', 'oidc:okta.example.com') |
No |
LAST LOGIN BEFORE |
Filter users who last logged in before the specified timestamp expression | No |
LIMIT |
Maximum number of rows to return | No |
Examples:
Show all users:
SHOW USERS;
Filter users by provisioning source:
SHOW USERS WITH SOURCE = 'ldap:ldap.example.com';
Filter users by last login time:
SHOW USERS WITH LAST LOGIN BEFORE '2024-01-01';
Combine multiple filters:
SHOW USERS WITH SOURCE = 'ldap:ldap.example.com', LAST LOGIN BEFORE '2024-01-01';
Limit the number of results:
SHOW USERS LIMIT 10;
Combine filtering with limiting:
SHOW USERS WITH SOURCE = 'ldap:ldap.example.com' LIMIT 5;
Required privileges:
The VIEWACTIVITY or VIEWACTIVITYREDACTED privilege on the cluster.
Notes:
The estimated_last_login_time column is computed on a best effort basis and is not guaranteed to capture every login event.
- Each option can only be specified once per statement. Duplicate options will result in a syntax error.
- The
SOURCEfilter matches users created with aPROVISIONSRCvalue exactly matching the specified string. - The
LAST LOGIN BEFOREfilter includes users whoseestimated_last_login_timeis before the specified timestamp, plus users withNULLlogin times. - When no options are specified,
SHOW USERSreturns all users and roles in the cluster.
Upcoming Changes for SHOW ROLES
[NEEDS REVIEW] The SHOW ROLES statement is being enhanced to support the same filtering options as SHOW USERS. The AST structure has been prepared in this release, but the grammar and functionality will be available in subsequent releases.
See also:
