sys.user

A system view that returns information about the users in the system. See also sys.role.

Superusers can see all users in this view. Users and roles with CREATE ROLE, ALTER ANY ROLE, or DROP ANY ROLE privilege can also see all users in this view.

Users and roles that are not superusers and do not have (or inherit) CREATE ROLE, ALTER ANY ROLE, or DROP ANY ROLE privilege can only see their own accounts in this view.

Column Name Data Type Description
user_id bigint The unique ID for the user or role.
name text Name of the user or role.
search_path text Value of the search_path parameter.
create_role boolean Whether the user can create other users.
create_database boolean Whether the user can create databases.
superuser boolean Whether the user has superuser privileges.
has_local_password boolean true means that the user has a local password set via ALTER USER (or CREATE USER). false means that the user does not have a local password and may depend on an LDAP password for login.
ldap_synchronized boolean true means that this user exists in the database via LDAP synchronization.
memberof name[] Roles that the user is a member of.

Example

premdb=# select * from sys.user where name not like 'sys%' and user_id>16100;
 user_id |   name   | search_path | create_role | create_database | superuser | has_local_password | ldap_synchronized | memberof 
---------+----------+-------------+-------------+-----------------+-----------+--------------------+-------------------+----------
   16475 | backup2  | [NULL]      | f           | f               | f         | t                  | f                 | {bar}
   16473 | bar      | [NULL]      | f           | f               | f         | t                  | f                 | {}
   16387 | ybd_test | [NULL]      | f           | f               | f         | t                  | f                 | {}
   16474 | backup1  | [NULL]      | f           | f               | f         | t                  | f                 | {bar}
   16476 | restore1 | [NULL]      | f           | f               | f         | t                  | f                 | {bar}
   16472 | bobr     | [NULL]      | t           | t               | t         | t                  | f                 | {}
   16477 | restore2 | [NULL]      | f           | f               | f         | t                  | f                 | {bar}
(7 rows)