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
Required privileges
The user must have the VIEWACTIVITY privilege or be a member of the admin role.
Synopsis
SHOW USERS [WITH <options>] [LIMIT <n>]
Where <options> can include:
SOURCE = <string>LAST LOGIN BEFORE <expr>
Multiple options can be comma-separated.
Parameters
| Parameter | Description | Required |
|---|---|---|
SOURCE |
Filters users by their provisioning source (PROVISIONSRC role option value). Must match the exact provisioning source string. |
No |
LAST LOGIN BEFORE |
Filters users whose estimated last login time is before the specified timestamp. Users who have never logged in (NULL estimated_last_login_time) are excluded from results. |
No |
LIMIT |
Restricts the number of returned rows to the specified count. | No |
Examples
Show all users
SHOW USERS;
Filter by provisioning source
SHOW USERS WITH SOURCE = 'ldap:ldap.example.com';
Filter 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-06-01';
Use LIMIT with filters
SHOW USERS WITH SOURCE = 'ldap:ldap.example.com' LIMIT 100;
Use LIMIT without filters
SHOW USERS LIMIT 10;
The estimated_last_login_time column is computed on a best-effort basis and is not guaranteed to capture every login event. Users who have never logged in will have NULL for this value and are excluded when using the LAST LOGIN BEFORE filter.
