PostgreSQL PG_AUTHID system catalog VIEW
PG_AUTHID is views its contains information about rolename and user password,user password validity,user connection limit and Role automatically inherits privileges of roles it is a member of,detailed information about user and privilege management.
| Name | Type | Description |
| rolname | name | Role name |
| rolsuper | bool | Role has superuser privileges |
| rolinherit | bool | Role automatically inherits privileges of roles it is a member of |
| rolcreaterole | bool | Role can create more roles |
| rolcreatedb | bool | Role can create databases |
| rolcatupdate | bool | Role can update system catalogs directly. (Even a superuser cannot do this unless this column is true) |
| rolcanlogin | bool | Role can log in. That is, this role can be given as the initial session authorization identifier |
| rolreplication | bool | Role is a replication role. That is, this role can initiate streaming replication and set/unset the system backup mode usingpg_start_backup and pg_stop_backup |
| rolconnlimit | int4 | For roles that can log in, this sets maximum number of concurrent connections this role can make. -1 means no limit. |
| rolpassword | text | 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. |
| rolvaliduntil | timestamptz | Password expiry time (only used for password authentication); null if no expiration |
Examples
–create the unencrypted user
create user u2 WITH UNENCRYPTED PASSWORD 'u2';–create the user with password validation time
CREATE USER u3 WITH PASSWORD 'u3' VALID UNTIL '2017-06-06';–Describe the pg_authid view
postgres=# \d pg_authid
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"–list the user and show the users password and password validatation time
postgres=# select rolname,rolpassword,rolvaliduntil from pg_authid;
rolname | rolpassword | rolvaliduntil
----------+-------------------------------------+------------------------
postgres | md505ea766c2bc9e19f34b66114ace97598 |
rep | md5df2c887bcb2c49b903aa33bdbc5c2984 |
u1 | |
u2 | u2 |
u3 | md5dad1ef51b879799793dc38d714b97063 | 2017-06-06 00:00:00-04
(5 rows)