pg_authid

The catalog pg_authid contains information about database authorization identifiers (roles). A role subsumes the concepts of users and groups. A user is essentially just a role with the rolcanlogin flag set. Any role (with or without rolcanlogin) can have other roles as members; see pg_auth_members.

Since this catalog contains passwords, it must not be publicly readable. pg_roles is a publicly readable view on pg_authid that blanks out the password field.

Chapter 20, Database Roles contains detailed information about user and privilege management.

Because user identities are cluster-wide, pg_authid is shared across all databases of a cluster: there is only one copy of pg_authid per cluster, not one per database.

Table 48.8. pg_authid Columns

NameTypeDescription
oidoidRow identifier (hidden attribute; must be explicitly selected)
rolnamenameRole name
rolsuperboolRole has superuser privileges
rolinheritboolRole automatically inherits privileges of roles it is a member of
rolcreateroleboolRole can create more roles
rolcreatedbboolRole can create databases
rolcatupdatebool Role can update system catalogs directly. (Even a superuser cannot do this unless this column is true)
rolcanloginbool Role can log in. That is, this role can be given as the initial session authorization identifier
rolreplicationbool Role is a replication role. That is, this role can initiate streaming replication (see the section called “Streaming Replication”) and set/unset the system backup mode using pg_start_backup and pg_stop_backup
rolconnlimitint4 For roles that can log in, this sets maximum number of concurrent connections this role can make. -1 means no limit.
rolpasswordtext Password (possibly encrypted); null if none. If the password is encrypted, this column will begin with the string md5 followed by a 32-character hexadecimal MD5 hash. The MD5 hash will be of the user's password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL™ will store the md5 hash of xyzzyjoe. A password that does not follow that format is assumed to be unencrypted.
rolvaliduntiltimestamptzPassword expiry time (only used for password authentication); null if no expiration