pg_roles
pg_roles
is a system catalogue view listing available roles
pg_roles
was added in PostgreSQL 8.1.
Usage
pg_roles
provides a publicly readable view of the underlying pg_authid table, but with the password column containing asterisks. Note that a fixed number of asterisks is displayed, regardless of whether the role has a password set or not.
Definition by PostgreSQL version
pg_roles (PostgreSQL 16)
View "pg_catalog.pg_roles" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-----------+----------+--------- rolname | name | | | rolsuper | boolean | | | rolinherit | boolean | | | rolcreaterole | boolean | | | rolcreatedb | boolean | | | rolcanlogin | boolean | | | rolreplication | boolean | | | rolconnlimit | integer | | | rolpassword | text | | | rolvaliduntil | timestamp with time zone | | | rolbypassrls | boolean | | | rolconfig | text[] | C | | oid | oid | | |
Documentation: pg_roles
pg_roles (PostgreSQL 15)
View "pg_catalog.pg_roles" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-----------+----------+--------- rolname | name | | | rolsuper | boolean | | | rolinherit | boolean | | | rolcreaterole | boolean | | | rolcreatedb | boolean | | | rolcanlogin | boolean | | | rolreplication | boolean | | | rolconnlimit | integer | | | rolpassword | text | | | rolvaliduntil | timestamp with time zone | | | rolbypassrls | boolean | | | rolconfig | text[] | C | | oid | oid | | |
Documentation: pg_roles
pg_roles (PostgreSQL 14)
View "pg_catalog.pg_roles" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-----------+----------+--------- rolname | name | | | rolsuper | boolean | | | rolinherit | boolean | | | rolcreaterole | boolean | | | rolcreatedb | boolean | | | rolcanlogin | boolean | | | rolreplication | boolean | | | rolconnlimit | integer | | | rolpassword | text | | | rolvaliduntil | timestamp with time zone | | | rolbypassrls | boolean | | | rolconfig | text[] | C | | oid | oid | | |
Documentation: pg_roles
pg_roles (PostgreSQL 13)
View "pg_catalog.pg_roles" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-----------+----------+--------- rolname | name | | | rolsuper | boolean | | | rolinherit | boolean | | | rolcreaterole | boolean | | | rolcreatedb | boolean | | | rolcanlogin | boolean | | | rolreplication | boolean | | | rolconnlimit | integer | | | rolpassword | text | | | rolvaliduntil | timestamp with time zone | | | rolbypassrls | boolean | | | rolconfig | text[] | C | | oid | oid | | |
Documentation: pg_roles
pg_roles (PostgreSQL 12)
View "pg_catalog.pg_roles" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-----------+----------+--------- rolname | name | | | rolsuper | boolean | | | rolinherit | boolean | | | rolcreaterole | boolean | | | rolcreatedb | boolean | | | rolcanlogin | boolean | | | rolreplication | boolean | | | rolconnlimit | integer | | | rolpassword | text | | | rolvaliduntil | timestamp with time zone | | | rolbypassrls | boolean | | | rolconfig | text[] | C | | oid | oid | | |
Documentation: pg_roles
pg_roles (PostgreSQL 11)
View "pg_catalog.pg_roles" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-----------+----------+--------- rolname | name | | | rolsuper | boolean | | | rolinherit | boolean | | | rolcreaterole | boolean | | | rolcreatedb | boolean | | | rolcanlogin | boolean | | | rolreplication | boolean | | | rolconnlimit | integer | | | rolpassword | text | | | rolvaliduntil | timestamp with time zone | | | rolbypassrls | boolean | | | rolconfig | text[] | | | oid | oid | | |
Documentation: pg_roles
pg_roles (PostgreSQL 10)
View "pg_catalog.pg_roles" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-----------+----------+--------- rolname | name | | | rolsuper | boolean | | | rolinherit | boolean | | | rolcreaterole | boolean | | | rolcreatedb | boolean | | | rolcanlogin | boolean | | | rolreplication | boolean | | | rolconnlimit | integer | | | rolpassword | text | | | rolvaliduntil | timestamp with time zone | | | rolbypassrls | boolean | | | rolconfig | text[] | | | oid | oid | | |
Documentation: pg_roles
pg_roles (PostgreSQL 9.6)
View "pg_catalog.pg_roles" Column | Type | Modifiers ----------------+--------------------------+----------- rolname | name | rolsuper | boolean | rolinherit | boolean | rolcreaterole | boolean | rolcreatedb | boolean | rolcanlogin | boolean | rolreplication | boolean | rolconnlimit | integer | rolpassword | text | rolvaliduntil | timestamp with time zone | rolbypassrls | boolean | rolconfig | text[] | oid | oid |
Documentation: pg_roles
pg_roles (PostgreSQL 9.5)
View "pg_catalog.pg_roles" Column | Type | Modifiers ----------------+--------------------------+----------- rolname | name | rolsuper | boolean | rolinherit | boolean | rolcreaterole | boolean | rolcreatedb | boolean | rolcanlogin | boolean | rolreplication | boolean | rolconnlimit | integer | rolpassword | text | rolvaliduntil | timestamp with time zone | rolbypassrls | boolean | rolconfig | text[] | oid | oid |
Documentation: pg_roles
pg_roles (PostgreSQL 9.4)
View "pg_catalog.pg_roles" Column | Type | Modifiers ----------------+--------------------------+----------- rolname | name | rolsuper | boolean | rolinherit | boolean | rolcreaterole | boolean | rolcreatedb | boolean | rolcatupdate | boolean | rolcanlogin | boolean | rolreplication | boolean | rolconnlimit | integer | rolpassword | text | rolvaliduntil | timestamp with time zone | rolconfig | text[] | oid | oid |
Documentation: pg_roles
pg_roles (PostgreSQL 9.3)
View "pg_catalog.pg_roles" Column | Type | Modifiers ----------------+--------------------------+----------- rolname | name | rolsuper | boolean | rolinherit | boolean | rolcreaterole | boolean | rolcreatedb | boolean | rolcatupdate | boolean | rolcanlogin | boolean | rolreplication | boolean | rolconnlimit | integer | rolpassword | text | rolvaliduntil | timestamp with time zone | rolconfig | text[] | oid | oid |
Documentation: pg_roles
pg_roles (PostgreSQL 9.2)
View "pg_catalog.pg_roles" Column | Type | Modifiers ----------------+--------------------------+----------- rolname | name | rolsuper | boolean | rolinherit | boolean | rolcreaterole | boolean | rolcreatedb | boolean | rolcatupdate | boolean | rolcanlogin | boolean | rolreplication | boolean | rolconnlimit | integer | rolpassword | text | rolvaliduntil | timestamp with time zone | rolconfig | text[] | oid | oid |
Documentation: pg_roles
pg_roles (PostgreSQL 9.1)
View "pg_catalog.pg_roles" Column | Type | Modifiers ----------------+--------------------------+----------- rolname | name | rolsuper | boolean | rolinherit | boolean | rolcreaterole | boolean | rolcreatedb | boolean | rolcatupdate | boolean | rolcanlogin | boolean | rolreplication | boolean | rolconnlimit | integer | rolpassword | text | rolvaliduntil | timestamp with time zone | rolconfig | text[] | oid | oid |
Documentation: pg_roles
pg_roles (PostgreSQL 9.0)
View "pg_catalog.pg_roles" Column | Type | Modifiers ---------------+--------------------------+----------- rolname | name | rolsuper | boolean | rolinherit | boolean | rolcreaterole | boolean | rolcreatedb | boolean | rolcatupdate | boolean | rolcanlogin | boolean | rolconnlimit | integer | rolpassword | text | rolvaliduntil | timestamp with time zone | rolconfig | text[] | oid | oid |
Documentation: pg_roles
pg_roles (PostgreSQL 8.4)
View "pg_catalog.pg_roles" Column | Type | Modifiers ---------------+--------------------------+----------- rolname | name | rolsuper | boolean | rolinherit | boolean | rolcreaterole | boolean | rolcreatedb | boolean | rolcatupdate | boolean | rolcanlogin | boolean | rolconnlimit | integer | rolpassword | text | rolvaliduntil | timestamp with time zone | rolconfig | text[] | oid | oid | View definition: SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid;
Documentation: pg_roles
pg_roles (PostgreSQL 8.3)
View "pg_catalog.pg_roles" Column | Type | Modifiers ---------------+--------------------------+----------- rolname | name | rolsuper | boolean | rolinherit | boolean | rolcreaterole | boolean | rolcreatedb | boolean | rolcatupdate | boolean | rolcanlogin | boolean | rolconnlimit | integer | rolpassword | text | rolvaliduntil | timestamp with time zone | rolconfig | text[] | oid | oid | View definition: SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid;
Documentation: pg_roles
pg_roles (PostgreSQL 8.2)
View "pg_catalog.pg_roles" Column | Type | Modifiers ---------------+--------------------------+----------- rolname | name | rolsuper | boolean | rolinherit | boolean | rolcreaterole | boolean | rolcreatedb | boolean | rolcatupdate | boolean | rolcanlogin | boolean | rolconnlimit | integer | rolpassword | text | rolvaliduntil | timestamp with time zone | rolconfig | text[] | oid | oid | View definition: SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid;
Documentation: pg_roles
Change history
- PostgreSQL 9.5
- PostgreSQL 9.1
- column
rolreplication
added (commit 9b8aff8c)
- column
- PostgreSQL 8.1
- added (commit 7762619e)
Examples
Sample row from pg_roles
:
postgres=# SELECT * FROM pg_roles WHERE rolname = 'postgres'\gx -[ RECORD 1 ]--+--------- rolname | postgres rolsuper | t rolinherit | t rolcreaterole | t rolcreatedb | t rolcanlogin | t rolreplication | t rolconnlimit | -1 rolpassword | ******** rolvaliduntil | rolbypassrls | t rolconfig | oid | 10
The displayed password is always set to a fixed string consisting of eight asterisks:
postgres=# ALTER ROLE postgres PASSWORD 'foo'; ALTER ROLE postgres=# SELECT rolpassword FROM pg_roles WHERE rolname = 'postgres'\gx -[ RECORD 1 ]--------- rolpassword | ******** postgres=# ALTER ROLE postgres PASSWORD NULL; ALTER ROLE postgres=# SELECT rolpassword FROM pg_roles WHERE rolname = 'postgres'\gx -[ RECORD 1 ]--------- rolpassword | ********
References
- PostgreSQL documentation: pg_roles