pg_authid

A system catalogue table containing information about database roles

pg_authid is a system catalogue table containing information about database roles.

It replaces the previous system catalogue table pg_shadow, which became a view for backwards compatibility.

This table is shared across all databases in a cluster.

Accessibility

This table can only be read by superusers. The system catalogue view pg_roles is accessible to non-superusers and provides the same information, with the exception of user passwords.

Definition by PostgreSQL version

pg_authid (PostgreSQL 13)

                        Table "pg_catalog.pg_authid"
     Column     |           Type           | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
 oid            | oid                      |           | not null |
 rolname        | name                     |           | not null |
 rolsuper       | boolean                  |           | not null |
 rolinherit     | boolean                  |           | not null |
 rolcreaterole  | boolean                  |           | not null |
 rolcreatedb    | boolean                  |           | not null |
 rolcanlogin    | boolean                  |           | not null |
 rolreplication | boolean                  |           | not null |
 rolbypassrls   | boolean                  |           | not null |
 rolconnlimit   | integer                  |           | not null |
 rolpassword    | text                     | C         |          |
 rolvaliduntil  | timestamp with time zone |           |          |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 12)

                        Table "pg_catalog.pg_authid"
     Column     |           Type           | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
 oid            | oid                      |           | not null |
 rolname        | name                     |           | not null |
 rolsuper       | boolean                  |           | not null |
 rolinherit     | boolean                  |           | not null |
 rolcreaterole  | boolean                  |           | not null |
 rolcreatedb    | boolean                  |           | not null |
 rolcanlogin    | boolean                  |           | not null |
 rolreplication | boolean                  |           | not null |
 rolbypassrls   | boolean                  |           | not null |
 rolconnlimit   | integer                  |           | not null |
 rolpassword    | text                     | C         |          |
 rolvaliduntil  | timestamp with time zone |           |          |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 11)

                        Table "pg_catalog.pg_authid"
     Column     |           Type           | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
 rolname        | name                     |           | not null |
 rolsuper       | boolean                  |           | not null |
 rolinherit     | boolean                  |           | not null |
 rolcreaterole  | boolean                  |           | not null |
 rolcreatedb    | boolean                  |           | not null |
 rolcanlogin    | boolean                  |           | not null |
 rolreplication | boolean                  |           | not null |
 rolbypassrls   | boolean                  |           | not null |
 rolconnlimit   | integer                  |           | not null |
 rolpassword    | text                     |           |          |
 rolvaliduntil  | timestamp with time zone |           |          |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 10)

                        Table "pg_catalog.pg_authid"
     Column     |           Type           | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
 rolname        | name                     |           | not null |
 rolsuper       | boolean                  |           | not null |
 rolinherit     | boolean                  |           | not null |
 rolcreaterole  | boolean                  |           | not null |
 rolcreatedb    | boolean                  |           | not null |
 rolcanlogin    | boolean                  |           | not null |
 rolreplication | boolean                  |           | not null |
 rolbypassrls   | boolean                  |           | not null |
 rolconnlimit   | integer                  |           | not null |
 rolpassword    | text                     |           |          |
 rolvaliduntil  | timestamp with time zone |           |          |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 9.6)

             Table "pg_catalog.pg_authid"
     Column     |           Type           | Modifiers
----------------+--------------------------+-----------
 rolname        | name                     | not null
 rolsuper       | boolean                  | not null
 rolinherit     | boolean                  | not null
 rolcreaterole  | boolean                  | not null
 rolcreatedb    | boolean                  | not null
 rolcanlogin    | boolean                  | not null
 rolreplication | boolean                  | not null
 rolbypassrls   | boolean                  | not null
 rolconnlimit   | integer                  | not null
 rolpassword    | text                     |
 rolvaliduntil  | timestamp with time zone |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 9.5)

             Table "pg_catalog.pg_authid"
     Column     |           Type           | Modifiers
----------------+--------------------------+-----------
 rolname        | name                     | not null
 rolsuper       | boolean                  | not null
 rolinherit     | boolean                  | not null
 rolcreaterole  | boolean                  | not null
 rolcreatedb    | boolean                  | not null
 rolcanlogin    | boolean                  | not null
 rolreplication | boolean                  | not null
 rolbypassrls   | boolean                  | not null
 rolconnlimit   | integer                  | not null
 rolpassword    | text                     |
 rolvaliduntil  | timestamp with time zone |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 9.4)

             Table "pg_catalog.pg_authid"
     Column     |           Type           | Modifiers
----------------+--------------------------+-----------
 rolname        | name                     | not null
 rolsuper       | boolean                  | not null
 rolinherit     | boolean                  | not null
 rolcreaterole  | boolean                  | not null
 rolcreatedb    | boolean                  | not null
 rolcatupdate   | boolean                  | not null
 rolcanlogin    | boolean                  | not null
 rolreplication | boolean                  | not null
 rolconnlimit   | integer                  | not null
 rolpassword    | text                     |
 rolvaliduntil  | timestamp with time zone |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 9.3)

             Table "pg_catalog.pg_authid"
     Column     |           Type           | Modifiers
----------------+--------------------------+-----------
 rolname        | name                     | not null
 rolsuper       | boolean                  | not null
 rolinherit     | boolean                  | not null
 rolcreaterole  | boolean                  | not null
 rolcreatedb    | boolean                  | not null
 rolcatupdate   | boolean                  | not null
 rolcanlogin    | boolean                  | not null
 rolreplication | boolean                  | not null
 rolconnlimit   | integer                  | not null
 rolpassword    | text                     |
 rolvaliduntil  | timestamp with time zone |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 9.2)

             Table "pg_catalog.pg_authid"
     Column     |           Type           | Modifiers
----------------+--------------------------+-----------
 rolname        | name                     | not null
 rolsuper       | boolean                  | not null
 rolinherit     | boolean                  | not null
 rolcreaterole  | boolean                  | not null
 rolcreatedb    | boolean                  | not null
 rolcatupdate   | boolean                  | not null
 rolcanlogin    | boolean                  | not null
 rolreplication | boolean                  | not null
 rolconnlimit   | integer                  | not null
 rolpassword    | text                     |
 rolvaliduntil  | timestamp with time zone |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 9.1)

             Table "pg_catalog.pg_authid"
     Column     |           Type           | Modifiers
----------------+--------------------------+-----------
 rolname        | name                     | not null
 rolsuper       | boolean                  | not null
 rolinherit     | boolean                  | not null
 rolcreaterole  | boolean                  | not null
 rolcreatedb    | boolean                  | not null
 rolcatupdate   | boolean                  | not null
 rolcanlogin    | boolean                  | not null
 rolreplication | boolean                  | not null
 rolconnlimit   | integer                  | not null
 rolpassword    | text                     |
 rolvaliduntil  | timestamp with time zone |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 9.0)

             Table "pg_catalog.pg_authid"
    Column     |           Type           | Modifiers
---------------+--------------------------+-----------
 rolname       | name                     | not null
 rolsuper      | boolean                  | not null
 rolinherit    | boolean                  | not null
 rolcreaterole | boolean                  | not null
 rolcreatedb   | boolean                  | not null
 rolcatupdate  | boolean                  | not null
 rolcanlogin   | boolean                  | not null
 rolconnlimit  | integer                  | not null
 rolpassword   | text                     |
 rolvaliduntil | timestamp with time zone |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 8.4)

             Table "pg_catalog.pg_authid"
    Column     |           Type           | Modifiers
---------------+--------------------------+-----------
 rolname       | name                     | not null
 rolsuper      | boolean                  | not null
 rolinherit    | boolean                  | not null
 rolcreaterole | boolean                  | not null
 rolcreatedb   | boolean                  | not null
 rolcatupdate  | boolean                  | not null
 rolcanlogin   | boolean                  | not null
 rolconnlimit  | integer                  | not null
 rolpassword   | text                     |
 rolvaliduntil | timestamp with time zone |
 rolconfig     | text[]                   |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Triggers:
    pg_sync_pg_authid AFTER INSERT OR DELETE OR UPDATE ON pg_authid FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger()
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 8.3)

             Table "pg_catalog.pg_authid"
    Column     |           Type           | Modifiers
---------------+--------------------------+-----------
 rolname       | name                     | not null
 rolsuper      | boolean                  | not null
 rolinherit    | boolean                  | not null
 rolcreaterole | boolean                  | not null
 rolcreatedb   | boolean                  | not null
 rolcatupdate  | boolean                  | not null
 rolcanlogin   | boolean                  | not null
 rolconnlimit  | integer                  | not null
 rolpassword   | text                     |
 rolvaliduntil | timestamp with time zone |
 rolconfig     | text[]                   |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Triggers:
    pg_sync_pg_authid AFTER INSERT OR DELETE OR UPDATE ON pg_authid FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger()
Tablespace: "pg_global"
    

Documentation: pg_authid

pg_authid (PostgreSQL 8.2)

             Table "pg_catalog.pg_authid"
    Column     |           Type           | Modifiers
---------------+--------------------------+-----------
 rolname       | name                     | not null
 rolsuper      | boolean                  | not null
 rolinherit    | boolean                  | not null
 rolcreaterole | boolean                  | not null
 rolcreatedb   | boolean                  | not null
 rolcatupdate  | boolean                  | not null
 rolcanlogin   | boolean                  | not null
 rolconnlimit  | integer                  | not null
 rolpassword   | text                     |
 rolvaliduntil | timestamp with time zone |
 rolconfig     | text[]                   |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Triggers:
    pg_sync_pg_authid AFTER INSERT OR DELETE OR UPDATE ON pg_authid FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger()
Tablespace: "pg_global"
    

Documentation: pg_authid

Change history

Examples

postgres=# SELECT * FROM pg_authid WHERE rolname='postgres';
-[ RECORD 1 ]--+---------
rolname | postgres
rolsuper | t
rolinherit | t
rolcreaterole | t
rolcreatedb | t
rolcanlogin | t
rolreplication | t
rolbypassrls | t
rolconnlimit | -1
rolpassword |
rolvaliduntil |

Categories

Cluster-wide table, System catalogue, User management

See also

pg_auth_members, pg_roles