pg_shadow

A system view showing properties of all login roles

pg_shadow is a system catalogue view showing properties of all roles that are marked as rolcanlogin in pg_authid.

pg_shadow was created for backwards compatibility in PostgreSQL 8.1 when the former pg_shadow table was replaced by pg_authid.

Definition by PostgreSQL version

pg_shadow (PostgreSQL 15)

                       View "pg_catalog.pg_shadow"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 usename      | name                     |           |          | 
 usesysid     | oid                      |           |          | 
 usecreatedb  | boolean                  |           |          | 
 usesuper     | boolean                  |           |          | 
 userepl      | boolean                  |           |          | 
 usebypassrls | boolean                  |           |          | 
 passwd       | text                     | C         |          | 
 valuntil     | timestamp with time zone |           |          | 
 useconfig    | text[]                   | C         |          |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 14)

                       View "pg_catalog.pg_shadow"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 usename      | name                     |           |          | 
 usesysid     | oid                      |           |          | 
 usecreatedb  | boolean                  |           |          | 
 usesuper     | boolean                  |           |          | 
 userepl      | boolean                  |           |          | 
 usebypassrls | boolean                  |           |          | 
 passwd       | text                     | C         |          | 
 valuntil     | timestamp with time zone |           |          | 
 useconfig    | text[]                   | C         |          |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 13)

                       View "pg_catalog.pg_shadow"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 usename      | name                     |           |          | 
 usesysid     | oid                      |           |          | 
 usecreatedb  | boolean                  |           |          | 
 usesuper     | boolean                  |           |          | 
 userepl      | boolean                  |           |          | 
 usebypassrls | boolean                  |           |          | 
 passwd       | text                     | C         |          | 
 valuntil     | timestamp with time zone |           |          | 
 useconfig    | text[]                   | C         |          |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 12)

                       View "pg_catalog.pg_shadow"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 usename      | name                     |           |          | 
 usesysid     | oid                      |           |          | 
 usecreatedb  | boolean                  |           |          | 
 usesuper     | boolean                  |           |          | 
 userepl      | boolean                  |           |          | 
 usebypassrls | boolean                  |           |          | 
 passwd       | text                     | C         |          | 
 valuntil     | timestamp with time zone |           |          | 
 useconfig    | text[]                   | C         |          |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 11)

               View "pg_catalog.pg_shadow"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 usename      | name    |           |          | 
 usesysid     | oid     |           |          | 
 usecreatedb  | boolean |           |          | 
 usesuper     | boolean |           |          | 
 userepl      | boolean |           |          | 
 usebypassrls | boolean |           |          | 
 passwd       | text    |           |          | 
 valuntil     | abstime |           |          | 
 useconfig    | text[]  |           |          |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 10)

               View "pg_catalog.pg_shadow"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 usename      | name    |           |          | 
 usesysid     | oid     |           |          | 
 usecreatedb  | boolean |           |          | 
 usesuper     | boolean |           |          | 
 userepl      | boolean |           |          | 
 usebypassrls | boolean |           |          | 
 passwd       | text    |           |          | 
 valuntil     | abstime |           |          | 
 useconfig    | text[]  |           |          |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 9.6)

    View "pg_catalog.pg_shadow"
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 usename      | name    | 
 usesysid     | oid     | 
 usecreatedb  | boolean | 
 usesuper     | boolean | 
 userepl      | boolean | 
 usebypassrls | boolean | 
 passwd       | text    | 
 valuntil     | abstime | 
 useconfig    | text[]  |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 9.5)

    View "pg_catalog.pg_shadow"
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 usename      | name    | 
 usesysid     | oid     | 
 usecreatedb  | boolean | 
 usesuper     | boolean | 
 userepl      | boolean | 
 usebypassrls | boolean | 
 passwd       | text    | 
 valuntil     | abstime | 
 useconfig    | text[]  |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 9.4)

    View "pg_catalog.pg_shadow"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 usename     | name    | 
 usesysid    | oid     | 
 usecreatedb | boolean | 
 usesuper    | boolean | 
 usecatupd   | boolean | 
 userepl     | boolean | 
 passwd      | text    | 
 valuntil    | abstime | 
 useconfig   | text[]  |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 9.3)

    View "pg_catalog.pg_shadow"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 usename     | name    | 
 usesysid    | oid     | 
 usecreatedb | boolean | 
 usesuper    | boolean | 
 usecatupd   | boolean | 
 userepl     | boolean | 
 passwd      | text    | 
 valuntil    | abstime | 
 useconfig   | text[]  |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 9.2)

    View "pg_catalog.pg_shadow"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 usename     | name    | 
 usesysid    | oid     | 
 usecreatedb | boolean | 
 usesuper    | boolean | 
 usecatupd   | boolean | 
 userepl     | boolean | 
 passwd      | text    | 
 valuntil    | abstime | 
 useconfig   | text[]  |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 9.1)

    View "pg_catalog.pg_shadow"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 usename     | name    | 
 usesysid    | oid     | 
 usecreatedb | boolean | 
 usesuper    | boolean | 
 usecatupd   | boolean | 
 userepl     | boolean | 
 passwd      | text    | 
 valuntil    | abstime | 
 useconfig   | text[]  |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 9.0)

    View "pg_catalog.pg_shadow"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 usename     | name    | 
 usesysid    | oid     | 
 usecreatedb | boolean | 
 usesuper    | boolean | 
 usecatupd   | boolean | 
 passwd      | text    | 
 valuntil    | abstime | 
 useconfig   | text[]  |
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 8.4)

    View "pg_catalog.pg_shadow"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 usename     | name    | 
 usesysid    | oid     | 
 usecreatedb | boolean | 
 usesuper    | boolean | 
 usecatupd   | boolean | 
 passwd      | text    | 
 valuntil    | abstime | 
 useconfig   | text[]  | 
View definition:
 SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, pg_authid.rolvaliduntil::abstime AS valuntil, pg_authid.rolconfig AS useconfig
   FROM pg_authid
  WHERE pg_authid.rolcanlogin;
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 8.3)

    View "pg_catalog.pg_shadow"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 usename     | name    | 
 usesysid    | oid     | 
 usecreatedb | boolean | 
 usesuper    | boolean | 
 usecatupd   | boolean | 
 passwd      | text    | 
 valuntil    | abstime | 
 useconfig   | text[]  | 
View definition:
 SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, pg_authid.rolvaliduntil::abstime AS valuntil, pg_authid.rolconfig AS useconfig
   FROM pg_authid
  WHERE pg_authid.rolcanlogin;
    

Documentation: pg_shadow

pg_shadow (PostgreSQL 8.2)

    View "pg_catalog.pg_shadow"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 usename     | name    | 
 usesysid    | oid     | 
 usecreatedb | boolean | 
 usesuper    | boolean | 
 usecatupd   | boolean | 
 passwd      | text    | 
 valuntil    | abstime | 
 useconfig   | text[]  | 
View definition:
 SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, pg_authid.rolvaliduntil::abstime AS valuntil, pg_authid.rolconfig AS useconfig
   FROM pg_authid
  WHERE pg_authid.rolcanlogin;
    

Documentation: pg_shadow

Change history

Examples

Sample pg_shadow contents on a freshly-initialized cluster:

postgres=# SELECT * FROM pg_shadow\gx
-[ RECORD 1 ]+---------
usename      | postgres
usesysid     | 10
usecreatedb  | t
usesuper     | t
userepl      | t
usebypassrls | t
passwd       | 
valuntil     | 
useconfig    | 

Categories

System catalogue, User management

See also

pg_authid, pg_group, pg_get_userbyid()