SHOW ROLES

The SHOW ROLES 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 .

Synopsis

Parameters

Parameter	Description
`SOURCE = <string>`	Filter roles by their provisioning source. Only roles whose 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 roles whose `estimated_last_login_time` is before the specified timestamp. Roles that 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 role must have the on the system.users and system.role_members tables.

Examples

Show all roles

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

Filter roles by provisioning source

To find all roles provisioned by a specific source, such as an :

> SHOW ROLES 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 role option, which is set automatically for roles provisioned through , , or authentication. To find roles that have not logged in since a specific date:

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

Roles that 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 ROLES 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 ROLES LIMIT 5;

Statements

Syntax

Data Types

Spatial Data

Constraints

Synopsis

Parameters

Required privileges

Examples

Show all roles

Filter roles by provisioning source

Combine filters with a limit

Limit results

See also

​Synopsis

​Parameters

​Required privileges

​Examples

​Show all roles

​Filter roles by provisioning source

​Filter roles by last login time

​Combine filters with a limit

​Limit results

​See also

Synopsis

Parameters

Required privileges

Examples

Show all roles

Filter roles by provisioning source

Filter roles by last login time

Combine filters with a limit

Limit results

See also