PostgreSQL Password Changing and Views

change the Database User Password

local      all     postgres     peer

create user u3 WITH ENCRYPTED PASSWORD 'u3';
create user u4 WITH ENCRYPTED PASSWORD 'u4';

postgres=# select usename,usesysid,passwd,valuntil,useconfig from PG_SHADOW;
 usename  | usesysid |               passwd                | valuntil | useconfig
----------+----------+-------------------------------------+----------+-----------
 u5       |    16393 | md507a832ae72c9e818c5297f366284fb8a |          |
 postgres |       10 | md53175bce1d3201d16594cebf9d7eb3f9d |          |
 u3       |    16416 | md5dad1ef51b879799793dc38d714b97063 |          |
 u4       |    16417 | md54af10c3137cf79c12265e8d288070711 |          |

psql -U postgres -d postgres

ALTER USER u3 WITH PASSWORD 'MyNew_Password';

psql -U postgres -d postgres

postgres=# \password u4
Enter new password:
Enter it again:

postgres=# select usename,usesysid,passwd,valuntil,useconfig from PG_SHADOW;
 usename  | usesysid |               passwd                | valuntil | useconfig
----------+----------+-------------------------------------+----------+-----------
 u5       |    16393 | md507a832ae72c9e818c5297f366284fb8a |          |
 postgres |       10 | md53175bce1d3201d16594cebf9d7eb3f9d |          |
 u3       |    16416 | md5e3da90548c84e1c04f27cc674aa19a28 |          |
 u4       |    16417 | md5ce9d5d4c1d5d47af4bc532d53a7fc0d7 |          |
(4 rows)

AWARENESS ABOUT PG_SHADOW & PG_AUTHID VIEWS:

PG_SHADOW 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.

Examples

— Creating password unencrypted user

postgres=# create user u2 WITH UNENCRYPTED PASSWORD 'u2';
CREATE ROLE

— Creating the user with password validation time ,this is one of the method in security maintenance once 06-06-2019 is reached means dba need to reset his/her validation time

postgres=# CREATE USER u3 WITH PASSWORD 'u3' VALID UNTIL '2019-06-06';
CREATE ROLE

— listing  users password and password validation time

postgres=# select usename,usesysid,passwd,valuntil,useconfig from PG_SHADOW;
 usename  | usesysid |               passwd                |        valuntil        | useconfig 
----------+----------+-------------------------------------+------------------------+-----------
 postgres |       10 | md505ea766c2bc9e19f34b66114ace97598 |                        | 
 rep      |    24576 | md5df2c887bcb2c49b903aa33bdbc5c2984 |                        | 
 u1       |    24583 |                                     |                        | 
 u2       |    24584 | u2                                  |                        | 
 u3       |    24585 | md5dad1ef51b879799793dc38d714b97063 | 2019-06-06 00:00:00-04 | 
 nijam    |    24586 |                                     |                        | 
(6 rows)

 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.

Examples

— creating unencrypted user 

postgres=# create user u2 WITH UNENCRYPTED PASSWORD 'u2';
CREATE ROLE

— create the user with password validation time

postgres=# CREATE USER u3 WITH PASSWORD 'u3' VALID UNTIL '2017-06-06';
CREATE ROLE

— listing  users password and password validation 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)

In PostgreSQL 10:

UNENCRYPTED PASSWORD is no longer supported after postgresql 10 Before postgresql 10 allowed to create user with unencrypted password so password will be stored as md5 encrypted type

Here i used postgresql 10.4 previous above example was postgresql 9.3

postgres=# create user u3 WITH UNENCRYPTED PASSWORD 'u3';
ERROR:  UNENCRYPTED PASSWORD is no longer supported
LINE 1: create user u3 WITH UNENCRYPTED PASSWORD 'u3';
                            ^
HINT:  Remove UNENCRYPTED to store the password in encrypted form instead.
postgres=#

UNENCRYPTED PASSWORD is no longer supported after postgresql 10 , Before postgresql 10 allowed to create user with unencrypted password