pg_shadow — PostgreSQL system view

The PostgreSQL pg_shadow system view: full column reference (names, types, descriptions), catalog relationships and version support.

The Sev-1 Admin
The Sev-1 Admin  ·  2 min read

Summary

The view pg_shadow exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows properties of all roles that are marked as rolcanlogin in pg_authid.

(Description quoted from the official PostgreSQL documentation.)

Columns

The pg_shadow system view exposes the following columns (names, types and descriptions are taken verbatim from the PostgreSQL documentation):

  • usename name references pg_authid.rolname
    User name
  • usesysid oid references pg_authid.oid
    ID of this user
  • usecreatedb bool
    User can create databases
  • usesuper bool
    User is a superuser
  • userepl bool
    User can initiate streaming replication and put the system in and out of backup mode.
  • usebypassrls bool
    User bypasses every row-level security policy, see ddl_rowsecurity for more information.
  • passwd text
    Encrypted password; null if none. See pg_authid for details of how encrypted passwords are stored.
  • valuntil timestamptz
    Password expiry time (only used for password authentication)
  • useconfig text[]
    Session defaults for run-time configuration variables

Related catalogs

This object references the following other system catalogs:

Version applicability

Present in PostgreSQL 17, 18, 19 (verified against each release’s documentation). This is a long-standing system object that also exists in earlier PostgreSQL releases.

Related & references

Reference: PostgreSQL documentation — pg_shadow.