HGDB modify user password and validity period

1. Security version

1.1 Modify user password

1) When the three powers are turned on

a. Modify non-admin user password

• Security and confidentiality administrator syssso User to modify the password of ordinary users (non-administrator users).
• Users connect to change their own passwords.

--syssso user to modify the password of ordinary users. (cannot be modified to a historical password within 5 times)
[root@dj ~]# psql -U syssso
psql (4.5.7)
Type "help" for help.
highgo=> alter user u1 password 'Xiaosong@1231';
ALTER ROLE

--Users connect to change their own passwords. (cannot be modified to a historical password within 5 times)
highgo=> \c - u1
Password for user u1:
You are now connected to database "highgo" as user "u1".
highgo=> alter user u1 password 'Xiaosong@1232';
ALTER ROLE

b. Change administrator user password

• Users connect to change their own passwords.

--Users connect to modify their own passwords. (cannot be modified to a historical password within 5 times)
[root@dj ~]# psql -U sysdba
psql (4.5.7)
Type "help" for help.
highgo=# alter user sysdba password 'Xiaosong@1231';
ALTER ROLE
highgo=# \c - syssso
You are now connected to database "highgo" as user "syssso".
highgo=> alter user syssso password 'Xiaosong@1231';
ALTER ROLE
highgo=> \c - syssao
You are now connected to database "highgo" as user "syssao".
highgo=> alter user syssao password 'Xiaosong@1231';
ALTER ROLE

2) When the three rights are closed

• The system administrator sysdba user can modify all user passwords.
• Users connect to change their own passwords.

--sysdba user modify all user passwords
[root@dj ~]# psql -U sysdba
psql (4.5.7)
Type "help" for help.
highgo=# alter user sysdba password 'Xiaosong@123';
ALTER ROLE
highgo=# alter user syssso password 'Xiaosong@123';
ALTER ROLE
highgo=# alter user syssao password 'Xiaosong@123';
ALTER ROLE
highgo=# alter user u1 password 'Xiaosong@123';
ALTER ROLE

--Users connect to change their own passwords.
[root@dj ~]# psql -U sysdba
psql (4.5.7)
Type "help" for help.
highgo=# alter user sysdba password 'Xiaosong@123';
ALTER ROLE
highgo=# \c - syssso
You are now connected to database "highgo" as user "syssso".
highgo=> alter user syssso password 'Xiaosong@123';
ALTER ROLE
highgo=> \c - syssao
You are now connected to database "highgo" as user "syssao".
highgo=> alter user syssao password 'Xiaosong@123';
ALTER ROLE
highgo=> \c - u1
Password for user u1:
You are now connected to database "highgo" as user "u1".
highgo=> alter user u1 password 'Xiaosong@123';
ALTER ROLE

3) When the administrator password is forgotten (unconventional means)

• Connect in single-user mode to change the administrator password (need to stop the library).

--Change password in single-user mode. (Regardless of whether the three rights are enabled or not, they cannot be modified to the historical password within 5 times. After pressing Enter, there is no echo information, just press ctrl + d to exit directly)
[root@dj ~]# pg_ctl stop
waiting for server to shut down....... done
server stopped
[root@dj ~]# postgres --single
2023-05-09 17:53:10.256 CST [8400] LOG: data encryption performed by sm4
2023-05-09 17:53:10.510 CST [8400] LOG: Switchover the SSHA Role. Current is NONE
2023-05-09 17:53:10.512 CST [8400] FATAL: database "root" does not exist
[root@dj ~]# postgres --single highgo
2023-05-09 17:54:26.414 CST [8412] LOG: data encryption performed by sm4
2023-05-09 17:54:26.624 CST [8412] LOG: Switchover the SSHA Role. Current is NONE
PostgreSQL stand-alone backend 12.7
backend> alter user sysdba password 'Xiaosong@123456';
backend> alter user syssso password 'Xiaosong@123456';

1.2 Modify user validity period

1) When the three powers are turned on

a. Modify the validity period of non-administrator users

• Ordinary users (non-admin users) can use the security administrator syssso user extension.

--Use the syssso user to extend the normal user to the specified time
highgo=> alter user u1 valid until '2099-09-09';
ALTER ROLE
highgo => \du
                                List of roles
 Role name | Attributes |
----------- + -------------------------------------- --------------- + -----------
 sysdba | No inheritance, Create role, Create DB, Replication + | {}
           | Password valid until 2023-05-16 17:14:03 + 08 |
 syssao | No inheritance + | {}
           | Password valid until 2023-05-16 17:14:26 + 08 |
 syssso | No inheritance + | {}
           | Password valid until 2023-05-16 17:14:15 + 08 |
 u1 | Password valid until 2099-09-09 00:00:00 + 08 | {}

-- Use the syssso user to defer normal users to perpetual
highgo=> alter user u1 valid until 'infinity';
ALTER ROLE
highgo => \du
                                List of roles
 Role name | Attributes |
----------- + -------------------------------------- --------------- + -----------
 sysdba | No inheritance, Create role, Create DB, Replication + | {}
           | Password valid until 2023-05-16 17:14:03 + 08 |
 syssao | No inheritance + | {}
           | Password valid until 2023-05-16 17:14:26 + 08 |
 syssso | No inheritance + | {}
           | Password valid until 2023-05-16 17:14:15 + 08 |
 u1 | Password valid until infinity | {}

b. Modify the validity period of the administrator user

• Adjust the password policy and modify the password effective policy through the security administrator syssso user.

--The password validity period policy is adjusted to the specified time of 180 days. After the policy is adjusted, it is only valid for subsequent new users. If an existing user wants to apply this policy, the password needs to be reset.
[root@dj ~]# psql -U syssso
psql (4.5.7)
Type "help" for help.
highgo => select show_secure_param();
            show_secure_param
-----------------------------------------
   hg_sepofpowers = on, +
   hg_macontrol = min, +
   hg_rowsecure = off, +
   hg_showlogininfo = off, +
   hg_clientnoinput = 30 min(s), +
   hg_idcheck.enable = on, +
   hg_idcheck.pwdlock = 5 time(s), +
   hg_idcheck.pwdlocktime = 24 hour(s), +
   hg_idcheck.pwdvaliduntil = 7 day(s), +
   hg_idcheck.pwdpolicy = highest, +
   hg_sepv4 = v4, +
(1 row)

highgo=>select set_secure_param('hg_idcheck.pwdvaliduntil','180');
        set_secure_param
---------------------------------
 set configuration successfully.
(1 row)

highgo => select show_secure_param();
             show_secure_param
-----------------------------------------------
   hg_sepofpowers = on, +
   hg_macontrol = min, +
   hg_rowsecure = off, +
   hg_showlogininfo = off, +
   hg_clientnoinput = 30 min(s), +
   hg_idcheck.enable = on, +
   hg_idcheck.pwdlock = 5 time(s), +
   hg_idcheck.pwdlocktime = 24 hour(s), +
   hg_idcheck.pwdvaliduntil = 180 day(s), +
   hg_idcheck.pwdpolicy = highest, +
   hg_sepv4 = v4, +
(1 row)

--Administrative users connect to change their own passwords. (Due to the opening of the three rights, you cannot change the historical password within 5 times. If you want to change the password to the same as the original one, you need to change other passwords five times first)
highgo => \du
                                List of roles
 Role name | Attributes |
----------- + -------------------------------------- --------------- + -----------
 sysdba | No inheritance, Create role, Create DB, Replication + | {}
           | Password valid until 2023-05-17 09:16:09 + 08 |
 syssao | No inheritance + | {}
           | Password valid until 2023-05-16 17:33:36 + 08 |
 syssso | No inheritance + | {}
           | Password valid until 2023-05-16 17:33:23 + 08 |
 u1 | Password valid until infinity | {}

highgo=> alter user syssso password 'Xiaosong@12311';
ALTER ROLE
highgo=> alter user syssso password 'Xiaosong@12312';
ALTER ROLE
highgo=> alter user syssso password 'Xiaosong@12313';
ALTER ROLE
highgo=> alter user syssso password 'Xiaosong@12314';
ALTER ROLE
highgo=> alter user syssso password 'Xiaosong@12315';
ALTER ROLE
highgo=> alter user syssso password 'Xiaosong@123';
ALTER ROLE
highgo => \c - sysdba
You are now connected to database "highgo" as user "sysdba".
highgo=# alter user sysdba password 'Xiaosong@12311';
ALTER ROLE
highgo=# alter user sysdba password 'Xiaosong@12312';
ALTER ROLE
highgo=# alter user sysdba password 'Xiaosong@12313';
ALTER ROLE
highgo=# alter user sysdba password 'Xiaosong@12314';
ALTER ROLE
highgo=# alter user sysdba password 'Xiaosong@12315';
ALTER ROLE
highgo=# alter user sysdba password 'Xiaosong@123';
ALTER ROLE
highgo=# \c - syssao
You are now connected to database "highgo" as user "syssao".
highgo=> alter user syssao password 'Xiaosong@12311';
ALTER ROLE
highgo=> alter user syssao password 'Xiaosong@12312';
ALTER ROLE
highgo=> alter user syssao password 'Xiaosong@12313';
ALTER ROLE
highgo=> alter user syssao password 'Xiaosong@12314';
ALTER ROLE
highgo=> alter user syssao password 'Xiaosong@12315';
ALTER ROLE
highgo=> alter user syssao password 'Xiaosong@123';
ALTER ROLE
highgo => \du
                                List of roles
 Role name | Attributes |
----------- + -------------------------------------- --------------- + -----------
 sysdba | No inheritance, Create role, Create DB, Replication + | {}
           | Password valid until 2023-11-06 09:18:51 + 08 |
 syssao | No inheritance + | {}
           | Password valid until 2023-11-06 09:19:02 + 08 |
 syssso | No inheritance + | {}
           | Password valid until 2023-11-06 09:18:31 + 08 |
 u1 | Password valid until infinity | {}

--The password validity period policy is adjusted to indefinite. After the policy is adjusted, it is only valid for subsequent new users. If an existing user wants to apply this policy, the password needs to be reset.
[root@dj ~]# psql -U syssso
psql (4.5.7)
Type "help" for help.
highgo => select show_secure_param();
             show_secure_param
-----------------------------------------------
   hg_sepofpowers = on, +
   hg_macontrol = min, +
   hg_rowsecure = off, +
   hg_showlogininfo = off, +
   hg_clientnoinput = 30 min(s), +
   hg_idcheck.enable = on, +
   hg_idcheck.pwdlock = 5 time(s), +
   hg_idcheck.pwdlocktime = 24 hour(s), +
   hg_idcheck.pwdvaliduntil = 180 day(s), +
   hg_idcheck.pwdpolicy = highest, +
   hg_sepv4 = v4, +
(1 row)

highgo => select set_secure_param('hg_idcheck.pwdvaliduntil','0');
        set_secure_param
---------------------------------
 set configuration successfully.
(1 row)

highgo => select show_secure_param();
            show_secure_param
-----------------------------------------
   hg_sepofpowers = on, +
   hg_macontrol = min, +
   hg_rowsecure = off, +
   hg_showlogininfo = off, +
   hg_clientnoinput = 30 min(s), +
   hg_idcheck.enable = on, +
   hg_idcheck.pwdlock = 5 time(s), +
   hg_idcheck.pwdlocktime = 24 hour(s), +
   hg_idcheck.pwdvaliduntil = 0 day(s), +
   hg_idcheck.pwdpolicy = highest, +
   hg_sepv4 = v4, +
(1 row)

highgo=> alter user syssso password 'Xiaosong@12311';
ALTER ROLE
highgo=> alter user syssso password 'Xiaosong@12312';
ALTER ROLE
highgo=> alter user syssso password 'Xiaosong@12313';
ALTER ROLE
highgo=> alter user syssso password 'Xiaosong@12314';
ALTER ROLE
highgo=> alter user syssso password 'Xiaosong@12315';
ALTER ROLE
highgo=> alter user syssso password 'Xiaosong@123';
ALTER ROLE
highgo => \c - sysdba
You are now connected to database "highgo" as user "sysdba".
highgo=# alter user sysdba password 'Xiaosong@12311';
ALTER ROLE
highgo=# alter user sysdba password 'Xiaosong@12312';
ALTER ROLE
highgo=# alter user sysdba password 'Xiaosong@12313';
ALTER ROLE
highgo=# alter user sysdba password 'Xiaosong@12314';
ALTER ROLE
highgo=# alter user sysdba password 'Xiaosong@12315';
ALTER ROLE
highgo=# alter user sysdba password 'Xiaosong@123';
ALTER ROLE
highgo=# \c - syssao
You are now connected to database "highgo" as user "syssao".
highgo=> alter user syssao password 'Xiaosong@12311';
ALTER ROLE
highgo=> alter user syssao password 'Xiaosong@12312';
ALTER ROLE
highgo=> alter user syssao password 'Xiaosong@12313';
ALTER ROLE
highgo=> alter user syssao password 'Xiaosong@12314';
ALTER ROLE
highgo=> alter user syssao password 'Xiaosong@12315';
ALTER ROLE
highgo=> alter user syssao password 'Xiaosong@123';
ALTER ROLE
highgo => \du
                                List of roles
 Role name | Attributes |
----------- + -------------------------------------- --------------- + -----------
 sysdba | No inheritance, Create role, Create DB, Replication + | {}
           | Password valid until infinity |
 syssao | No inheritance + | {}
           | Password valid until infinity |
 syssso | No inheritance + | {}
           | Password valid until infinity |
 u1 | Password valid until infinity | {}

2) When the three rights are closed

• The validity period of the user is not limited, even if the expiration time is displayed in the query result, it will not be affected.

[root@dj ~]# psql -U syssso
psql (4.5.7)
Type "help" for help.
highgo => select show_secure_param();
            show_secure_param
-----------------------------------------
   hg_sepofpowers = off, +
   hg_macontrol = min, +
   hg_rowsecure = off, +
   hg_showlogininfo = off, +
   hg_clientnoinput = 30 min(s), +
   hg_idcheck.enable = off, +
   hg_idcheck.pwdlock = 5 time(s), +
   hg_idcheck.pwdlocktime = 24 hour(s), +
   hg_idcheck.pwdvaliduntil = 7 day(s), +
   hg_idcheck.pwdpolicy = highest, +
   hg_sepv4 = off, +
(1 row)

highgo => \du
                                List of roles
 Role name | Attributes |
----------- + -------------------------------------- --------------- + -----------
 sysdba | No inheritance, Create role, Create DB, Replication + | {}
           | Password valid until infinity |
 syssao | No inheritance + | {}
           | Password valid until infinity |
 syssso | No inheritance + | {}
           | Password valid until infinity |
 u1 | Password valid until 2022-09-09 00:00:00 + 08 | {}
highgo => \! date
Wed May 10 09:40:03 CST 2023
highgo=> \c - u1
Password for user u1:
You are now connected to database "highgo" as user "u1".
highgo=> create table t1(id int);
CREATE TABLE
highgo => \dt
       List of relations
 Schema | Name | Type |
-------- + ------ + ------- + -------
 public | t1 | table | u1
(1 row)
highgo=> drop table t1;
DROP TABLE

2. Enterprise Edition

2.1 Modify user password

1) Change the password under normal circumstances

• The highgo administrator user modifies all passwords.
• Users connect to change their own passwords.

--highgo admin user to modify all user passwords. (cannot be modified to a historical password within 5 times)
[highgo@dj ~]$ psql -U highgo
psql (HighGo Database 6 Release 6.0.4-64-bit Production)

PSQL: Release HighGo Database 6 Release 6.0.4-64-bit Production
Type "help" for help.
highgo=# alter user highgo password 'Xiaosong@1231';
ALTER ROLE
highgo=# alter user test password 'Xiaosong@1231';
ALTER ROLE

--Users connect to change their own passwords. (cannot be modified to a historical password within 5 times)
highgo=# \c - test
Password for user test:
PSQL: Release HighGo Database 6 Release 6.0.4-64-bit Production
You are now connected to database "highgo" as user "test".
highgo=> alter user test password 'Xiaosong@1232';
ALTER ROLE

2) When the administrator password is forgotten (unconventional means)

• Modify the authentication method, password-free connection to the database to change the password.

--Modify the configuration file, and change the local login authentication method to trust
[highgo@dj ~]$ vi $PGDATA/pg_hba.conf
[highgo@dj ~]$ cat $PGDATA/pg_hba.conf|sed -n 80,100p

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all md5
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
host all all 0.0.0.0/0 md5

--Effective modification
[highgo@dj ~]$ pg_ctl reload
server signaled

-- Password-free connection to the database to change the password
[highgo@dj ~]$ psql -U highgo
psql (HighGo Database 6 Release 6.0.4-64-bit Production)

PSQL: Release HighGo Database 6 Release 6.0.4-64-bit Production
Type "help" for help.

highgo=# alter user highgo password 'Xiaosong@123456';
ALTER ROLE
highgo=# exit

-- Call back the previous authentication policy
[highgo@dj ~]$ vi $PGDATA/pg_hba.conf
[highgo@dj ~]$ cat $PGDATA/pg_hba.conf|sed -n 80,100p

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all md5
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
host all all 0.0.0.0/0 md5
[highgo@dj ~]$ pg_ctl reload
server signaled

2.2 Modify user validity period

• The highgo administrator user validity period is indefinite by default (unmodifiable).
• The validity period of common users (non-administrator users) can be modified by highgo administrator users.

[highgo@dj ~]$ psql -U highgo
psql (HighGo Database 6 Release 6.0.4-64-bit Production)
PSQL: Release HighGo Database 6 Release 6.0.4-64-bit Production
Type "help" for help.

highgo=# \du
                                   List of roles
 Role name | Attributes |
----------- + -------------------------------------- ---------------------- + -----------
 highgo | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test | Password valid until 2023-05-17 09:58:41.10164 + 08 | {}

highgo=# alter user test valid until '2023-09-09';
ALTER ROLE
highgo=# \du
                                   List of roles
 Role name | Attributes |
----------- + -------------------------------------- ---------------------- + -----------
 highgo | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test | Password valid until 2023-09-09 00:00:00 + 08 | {}

highgo=# alter user test valid until 'infinity';
ALTER ROLE
highgo=# \du
                                   List of roles
 Role name | Attributes |
----------- + -------------------------------------- ---------------------- + -----------
 highgo | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test | Password valid until infinity | {}

2.3 Policy adjustment of user validity period

• The highgo administrator user connects to adjust the validity period policy. After the adjustment, it is only valid for subsequent newly created users. If an existing user wants to apply this policy, the password needs to be reset. (highgo administrator users are not limited by the validity period policy)

--Adjust the user limited period policy to 180 days
[highgo@dj~]$ psql
psql (HighGo Database 6 Release 6.0.4-64-bit Production)
PSQL: Release HighGo Database 6 Release 6.0.4-64-bit Production
Type "help" for help.

highgo=# select show_secure_param();
           show_secure_param
---------------------------------------
 hg_idcheck.pwdlock = 5 time(s), +
 hg_idcheck.pwdlocktime = 24 hour(s), +
 hg_idcheck.pwdvaliduntil = 7 day(s), +
(1 row)

highgo=# select set_secure_param('hg_idcheck.pwdvaliduntil','180');
        set_secure_param
---------------------------------
 set configuration successfully.
(1 row)

highgo=# select show_secure_param();
            show_secure_param
-----------------------------------------
 hg_idcheck.pwdlock = 5 time(s), +
 hg_idcheck.pwdlocktime = 24 hour(s), +
 hg_idcheck.pwdvaliduntil = 180 day(s), +
(1 row)

highgo=# \du
                                   List of roles
 Role name | Attributes |
----------- + -------------------------------------- ---------------------- + -----------
 highgo | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test | Password valid until infinity | {}

highgo=# create user u1 password 'Xiaosong@123';
CREATE ROLE
highgo=# alter user test password 'Xiaosong@1234123';
ALTER ROLE
highgo=# \du
                                   List of roles
 Role name | Attributes |
----------- + -------------------------------------- ---------------------- + -----------
 highgo | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test | Password valid until 2023-11-06 10:26:56.715074 + 08 | {}
 u1 | Password valid until 2023-11-06 10:26:38.088548 + 08 | {}

--Adjust the user's limited period policy to unlimited
highgo=# select set_secure_param('hg_idcheck.pwdvaliduntil','0');
        set_secure_param
---------------------------------
 set configuration successfully.
(1 row)

highgo=# select show_secure_param();
           show_secure_param
---------------------------------------
 hg_idcheck.pwdlock = 5 time(s), +
 hg_idcheck.pwdlocktime = 24 hour(s), +
 hg_idcheck.pwdvaliduntil = 0 day(s), +
(1 row)

highgo=# \du
                                   List of roles
 Role name | Attributes |
----------- + -------------------------------------- ---------------------- + -----------
 highgo | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test | Password valid until 2023-11-06 10:26:56.715074 + 08 | {}
 u1 | Password valid until 2023-11-06 10:26:38.088548 + 08 | {}

highgo=# create user u2 password 'Xiaosong@123';
CREATE ROLE
highgo=# alter user test password 'Xiaosong@111';
ALTER ROLE
highgo=# alter user u1 password 'Xiaosong@111';
ALTER ROLE
highgo=# \du
                                   List of roles
 Role name | Attributes |
----------- + -------------------------------------- ---------------------- + -----------
 highgo | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test | Password valid until infinity | {}
 u1 | Password valid until infinity | {}
 u2 | Password valid until infinity | {}