SHOW USERS

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

Parameters

Parameter	Description
`SOURCE = <string>`	Filter users by their provisioning source. Only users whose `PROVISIONSRC` role option exactly matches the full string are returned. For example, `'ldap:ldap.example.com'`. Pattern matching is not supported.
`LAST LOGIN BEFORE <timestamp>`	Filter users whose `estimated_last_login_time` is before the specified timestamp. Users who have never logged in (`NULL` value) are excluded.
`LIMIT <n>`	Limit the number of rows returned. Can be used with or without `WITH` options.

Required privileges

The user must have the SELECT privilege on the system.users and system.role_members tables.

Examples

Show all users

> 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)

Filter users by provisioning source

To find all users provisioned by a specific source, such as an LDAP server:

> SHOW USERS WITH SOURCE = 'ldap:ldap.example.com';

  username   |                options                 | member_of | estimated_last_login_time
-------------+----------------------------------------+-----------+------------------------------
  prov_user1 | {PROVISIONSRC=ldap:ldap.example.com}   | {}        | 2025-07-01 10:30:00.000000+00
  prov_user2 | {PROVISIONSRC=ldap:ldap.example.com}   | {}        | 2025-06-15 08:45:00.000000+00
(2 rows)

The SOURCE filter matches against the PROVISIONSRC role option, which is set automatically for users provisioned through LDAP, OIDC, or JWT authentication. To find users who have not logged in since a specific date:

> SHOW USERS WITH LAST LOGIN BEFORE '2026-01-01';

  username | options | member_of |   estimated_last_login_time
-----------+---------+-----------+--------------------------------
  petee    | {}      | {}        | 2025-08-04 19:18:00.201402+00
(1 row)

Users who have never logged in (NULL estimated_last_login_time) are excluded from LAST LOGIN BEFORE results.

Combine filters with a limit

You can combine multiple WITH options (comma-separated) and add a LIMIT clause:

> SHOW USERS WITH SOURCE = 'ldap:ldap.example.com', LAST LOGIN BEFORE '2025-06-01' LIMIT 100;

Limit results

The LIMIT clause can also be used without WITH options:

> SHOW USERS LIMIT 5;

Statements

Syntax

Data Types

Spatial Data

Constraints

Synopsis

Parameters

Required privileges

Examples

Show all users

Filter users by provisioning source

Combine filters with a limit

Limit results

See also

​Synopsis

​Parameters

​Required privileges

​Examples

​Show all users

​Filter users by provisioning source

​Filter users by last login time

​Combine filters with a limit

​Limit results

​See also

Synopsis

Parameters

Required privileges

Examples

Show all users

Filter users by provisioning source

Filter users by last login time

Combine filters with a limit

Limit results

See also