Setting the validity period of user password in Postgresql

Test environment:
Server 192.168.137.222 highgodb v4.1.1 corresponds to PostgreSQL V9.5
Client 192.168.137.220

1) Create a test user at the highgo user of host 192.168.137.222 and set the password Expiration:

[highgo@hgdb01 ~]$ psql
psql (4.1.1)

PSQL: Release 4.1.1
Connected to:
HighGo Database V4.1 Enterprise Edition Release 4.1.1 - 64-bit Production

Type "help" for help.
highgo=# create user rep with password '123';' 
highgo=# alter role rep VALID UNTIL '2018-4-11 19:10';
highgo=# \du+ rep
                                   List of roles
 Role name |                 Attributes                  | Member of | Description 
-----------+---------------------------------------------+-----------+-------------
 rep       | Password valid until 2018-04-11 19:10:00+08 | {}        | 

After the password expires, it will not be able to pass the authentication. Here, you must go to the client to access the database server to verify the validity period of the password. If the user access of the server is not limited by the validity period.

In the psql environment of the highgo user of the host 192.168.137.220, access to the server database of another server and report an error.

highgo=#psql -h 192.168.137.222 -U rep highgo
Password for user rep:
psql: FATAL: password authentication failed for user "rep"

2) In PG user or PG shadow:
The value of column valintil is infinity or null, indicating that the user password will never expire; the default value is null

In the host 192.168.137.222, the user's password will never expire when the user changes it:

highgo=# alter user rep with valid until 'infinity';
ALTER ROLE

The following sql can view the change of the value of the modified field valintil.

highgo=# select * from pg_user;
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 highgo  |       10 | t           | t        | t       | t            | ******** |          | 
 rep     |   131293 | f           | f        | f       | f            | ******** | infinity | 

highgo=#  select * from pg_shadow;
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig 
---------+----------+-------------+----------+---------+--------------+-------------------------------------+--
--------+-----------
 highgo  |       10 | t           | t        | t       | t            | md5ba209e52a1dadc55df77edf498312146 |  
        | 
 rep     |   131293 | f           | f        | f       | f          md5a6f8e01c46849ed88d55e3c347ec318a | infinity | 
(10 rows)

The psql environment of the highgo user of the host 192.168.137.220 accesses another server database, which can be accessed normally.

[highgo@hgdb01 ~]$ psql -h 192.168.137.222 -U rep highgo
Password for user rep: 
psql (4.1.1)

PSQL: Release 4.1.1
Connected to:
HighGo Database V4.1 Enterprise Edition Release 4.1.1 - 64-bit Production

Type "help" for help.

highgo=> \dt
         List of relations
 Schema |  Name   | Type  | Owner  
--------+---------+-------+--------
 public | people  | table | highgo
 public | tb1     | table | highgo
 public | toast_t | table | highgo
(3 rows)

By boluo

Tags: Database PostgreSQL SQL

Posted on Fri, 03 Apr 2020 17:55:50 -0700 by harrymanjan