To apply a policy for login security to a user, define the policy as a profile and assign the profile to the user. The contents of the profile are saved as database objects.
The items that can be set for a profile are described below.
You can automatically lock users who have not been connected to the database for a long time.
This setting is for users with the LOGIN attribute.
You can set the following policies for users who use password authentication (password, md5, scram-sha-256).
Set a password life time
Restrict password reuse
Lock accounts that have failed to login continuously
Allow passwords to be set in encrypted form
Set the gradual password rollover time
Gradual password rollover is when you change a password and then keep the old password in effect for a while.
This setting specifies the valid period.
This is useful, for example, if it is difficult to make a new password available system-wide instantly.
Ensure that the postgres database exists and that you can connect to it.
If no connections are possible, and if using a database other than the default postgres database, specify the name of the database to which you can connect in the userprofile_database parameter in the postgresql.conf file.
Refer to "Parameters for the Policy-based Login Security" for parameters.
When you create a database user with CREATE ROLE, the database user is assigned the default profile. When you create the default profile, all password configuration and authentication restrictions and the gradual password rollover feature are disabled, so change the parameter values to suit your policy. Change the value with the pgx_alter_profile function.
Users with CREATEROLE privilege can modify the contents of a profile.
Refer to "B.3.1 Profile Management Functions" for functions.
Refer to "7.7 Profile parameters" for more information on parameters.
[Example]
SELECT pgx_alter_profile('default',
‘{
"INACTIVE_USER_TIME": 30,
"PASSWORD_LIFE_TIME": 50,
"PASSWORD_GRACE_TIME": 10,
"PASSWORD_REUSE_MAX": 5,
"PASSWORD_LOCK_TIME": 0.5,
"PASSWORD_ALLOW_HASHED": true,
"PASSWORD_ROLLOVER_TIME": 0.125
}’);If you want to apply a different policy than the default profile, create a new profile and assign it to the user.
The pgx_create_profile function creates a profile.
To assign a profile to a user, use the pgx_assign_profile_to_user function.
Users with CREATEROLE privilege can create and assign profiles.
Refer to "B.3.1 Profile Management Functions" and "B.3.2 User Management Functions" for functions.
When a user is locked
Locked in the following cases:
Restriction by INACTIVE_USER_TIME
Restriction by FAILED_LOGIN_ATTEMPTS
If you want to allow a locked user to login again, unlock the user. Unlocking is performed using the pgx_unlock_user function. Operations can be performed by users with CREATEROLE privilege.
Refer to "B.3.2 User Management Functions" for functions.
[Example]
SELECT pgx_unlock_user('user1');To release a lock when a database administrator is locked, users other than the locked user with CREATEROLE privilege can do so. If the user does not exist, Fujitsu Enterprise Postgres must be started in single-user mode and the lock released.
Point
You can set PASSWORD_LOCK_TIME to automatically release locks due to FAILED_LOGIN_ATTEMPTS.
When the password expires
When a password life time is over, the password expires and cannot be used to connect to and operate on the database until the password is changed.
The password can be changed by the expired user itself, or by a user who has the CREATEROLE privilege and who is an administrator of that user (who has ADMIN privilege for that user).
For streaming replication, the policy is enabled on both the primary and standby servers.
Perform changing the contents of the profile, creating and assigning profiles, unlock, and change the password can only be done on the primary server.
This section describes how to setting streaming replication configuration.
Grant privilege to users for streaming replication
The standby server connects to the primary server and propagates any state changes it detects to the primary server. The used users for streaming replication (specified in primary_conninfo) is used to connect for state change. Grant privilege to this user. Allow the user to SET ROLE directly to the group role pgx_update_profile_status.
[Example]
# GRANT pgx_update_profile_status TO repluser WITH SET TRUE;
This action enables users for streaming replication to lock all other database users or unlock the lock state due to policy deviations. Also, membership in the pgx_update_profile_status group role should only be granted to users for streaming replication.
Add a record to pg_hba.conf
Add a record to accept connections from the standby server. Duplicate the record for streaming replication and specify the database name in the userprofile_database parameter as the destination database name. Authentication methods other than password authentication are recommended.
host replication repluser standbyServerAddress authenticationMethod # For Streaming Replication host postgres repluser standbyServerAddress authenticationMethod # For state change propagation
Information
If the primary and standby servers are disconnected, profile-based restrictions still apply on each server. However, the standby server itself cannot change its password or unlock it explicitly. After removing the cause of the disconnect, restore the connection to the primary server and business operations, and then change the password or explicitly unlock.
Connection information for reflecting status changes to the primary server or standby server is output as "User profile status sender" to application_name in the pg_stat_replication view. When monitoring the streaming replication status with the pg_stat_replication view, exclude the "User profile status sender" line from the monitoring target.
Backup
Profile contents, user and profile assignment status, and password history can be backed up with physical backups and the pg_dumpall command.
When dumping with SQL statements using the pg_dumpall command, database role information is backed up at the same time it is dumped.
Information
When the pg_dumpall command is run without the --no-role-passwords option, the database user's password is included in the backup file in the form of a encrypted form. Therefore, when recovering the database user's password, temporarily set this parameter to true in the default profile of the restore destination. If the restore is successful, the contents of the default profile are restored to the contents at the time of the backup.
Recovery
If you recover using older backup data, you may be considered a dormant user at the time of recovery, or your password may have expired. In this case, unlock or change the password after recovery.
Details of the parameters set in the profile are explained.
INACTIVE_USER_TIME
Number of days before auto-locking users who have not been connected to the database for a long time (users who cannot see their sessions)
The unit is days. The maximum value is 24855 days.
This setting is for users with the LOGIN attribute.
If a session to the database cannot be verified for at least INACTIVE_USER_TIME, the user is locked out and cannot log in.
Sessions are checked into the database every hour and the information is saved.
If the system shuts down before the save, it is assumed that there were no logins between the last save and the shutdown. As a result, the lock period might be shorter than the value specified in this parameter.
When using streaming replication, the session confirmation to the database is performed every hour on each server. The primary server makes a locking decision while the standby server sends session confirmation information to the upstream server. This can cause a time lag of several hours before the session confirmation information from the standby server is communicated to the primary server. This lag can result in locking even when connected to a standby server.
You can determine when each user's session was last seen by the system by looking at the value of the userprlastactivetime column in the pgx_user_profile system catalog.
If the device is locked by the setting of this parameter, use the pgx_unlock_user function to unlock the device. Automatic cancellation by PASSWORD_LOCK_TIME is not performed.
When a logged-in user changes to another role with SET ROLE, the new role is not considered logged in to the database. It is assumed that the old role is still logged in to the database.
PASSWORD_LIFE_TIME
Number of days the same password can be used for authentication
The unit is days. Hours and seconds can be specified with decimal places (e.g. 4.5 is equivalent to "4 days and 12 hours"). Precision is 1 second. The maximum value is 24855 days.
The password life time will be over after PASSWORD_LIFE_TIME days from the last time the password was updated for the target user. The timing of updating the profile is not the starting point. Therefore, if you specify an extremely short number of days (such as 1 day), it may already be past the life time at the time of renewal.
It is possible to specify when a password becomes invalid using the VALID UNTIL clause of CREATE ROLE or ALTER ROLE. If you specify both the VALID UNTIL clause and PASSWORD_LIFE_TIME, both values are valid. Note that in this case, you can login only if both constraints are met.
If there is no grace period, the password expires when the password life time is over. If you login using password authentication in this state, you will receive a "password expired" warning and you will not be able to execute commands other than changing your password. You can change the password to resume normal operations. Other than password authentication, you can connect to and work with the database.
For streaming replication, users will not be able to connect to the standby server after the password life time is over. There is no grace period for password expiration on standby servers. Password changes must be made on the primary server.
PASSWORD_GRACE_TIME
The number of days after a password life time is over before the password expires.
The unit is days. Hours and seconds can be specified with decimal places (e.g. 4.5 is equivalent to "4 days and 12 hours"). Precision is 1 second. The maximum value is 24855 days.
The password expiration grace period is PASSWORD_GRACE_TIME days from the first login time after the password life time is over. You can login with your current password during the grace period, but a warning prompts you to change your password. You can operate normally except that a warning is displayed. If specified as UNLIMITED, the grace period is infinite and you will be warned to change your password at every login. Once transitioned to the grace period, even if you change the profile value after that, you cannot go back to before the grace period, and you cannot change the life time. If 0 is specified, there is no grace period and the password expires at the first login after the password life time is over. In this case, you can perform normal operations again by changing the password.
Because the standby server does not have a grace period before the password expires, users cannot connect to the standby server after the password life time is over. Password changes must be made on the primary server.
PASSWORD_REUSE_TIME
Number of days the same password cannot be reused
The password cannot be reused by the same user for this period from the time the password is updated.
This parameter must be set in combination with PASSWORD_REUSE_MAX.
The unit is days. Hours and seconds can be specified with decimal places (e.g. 4.5 is equivalent to "4 days and 12 hours"). Precision is 1 second. The maximum value is 24855 days.
PASSWORD_REUSE_MAX
Number of password changes required before the same password can be reused
This parameter must be set in combination with PASSWORD_REUSE_TIME.
Both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX constraints must be met to reuse passwords.
For example, if you specify PASSWORD_REUSE_TIME = 30, PASSWORD_REUSE_MAX = 10, a certain password can be reused if 30 days have passed since the update time and the password has been updated 10 times or more. When password update fails due to these parameters, the SQLSTATE will be "22023: invalid_parameter_value".
Also, if one parameter has a value and the other parameter specifies UNLIMITED, the password cannot be reused. If you want to use only one condition for reuse judgment, you need to set the value of the unused parameter to 0. However, if UNLIMITED is specified for both, these parameters are ignored and passwords can be reused without restriction.
When changing a password, if the password is specified in MD5 or SCRAM encrypted format, you cannot check that the password is being reused.
PASSWORD_ALLOW_HASHED
Whether to allow passwords to be specified in MD5 or SCRAM encrypted form when changing passwords Allow if true.
If true, there are no restrictions on how to set or change passwords.
However, note the following when changing passwords in encrypted form:
Unable to check for password reuse (PASSWORD_REUSE_TIME, PASSWORD_REUSE_MAX)
Password rollover disabled (PASSWORD_ROLLOVER_TIME)
Inability to check password complexity using the extension passwordcheck
If false, the password can only be set or changed by specifying the password in clear text in the PASSWORD clause of a CREATE ROLE or ALTER ROLE statement. You cannot change the password using the psql command \password meta-command.
In this case, all password checks that cannot be performed in encrypted form are possible.
Note
Passwords specified in CREATE ROLE or ALTER ROLE statements may, depending on configuration, be logged in the psql command history and the server log.
FAILED_LOGIN_ATTEMPTS
Number of consecutive failed login attempts allowed by the user
If password authentication fails consecutively for the number of times specified by this parameter, the user is locked and cannot login.
The number of failed login attempts is counted separately on each server.
PASSWORD_LOCK_TIME
Number of days after a user is locked due to consecutive login failures before the user is unlocked
The unit is days. Hours and seconds can be specified with decimal places (e.g. 4.5 is equivalent to "4 days and 12 hours"). Precision is 1 second. The maximum value is 24855 days.
If set to UNLIMITED, the user will be locked indefinitely and will not be automatically unlocked. Unlocking requires an explicit pgx_unlock_user function call by a user with CREATEROLE privilege.
PASSWORD_ROLLOVER_TIME
Number of days after password change before old password expires
The unit is days. Hours and seconds can be specified with decimal places (e.g. 4.5 is equivalent to "4 days and 12 hours"). Precision is 1 second. The maximum value is 60 days. The minimum value is 0.0416 days (approximately 1 hour).
From the time the user changes the password until PASSWORD_ROLLOVER_TIME has elapsed, the user can login using the old password.
Specify a value equal to or less than the lesser of the PASSWORD_GRACE_TIME (except when 0 is specified) or PASSWORD_LIFE_TIME. If a larger value is specified, the smaller of these parameters is used. For example, if PASSWORD_GRACE_TIME is 10 and PASSWORD_LIFE_TIME is 50, specify 10 or less for this parameter. If 15 is specified in this parameter, 10 is assumed to be specified.
If 0 is specified, the user cannot login with the old password. The default profile has an initial value of 0.
To expire the combinable period immediately before PASSWORD_ROLLOVER_TIME expires, a user or a user with CREATEROLE privilege can call the pgx_make_password_rollover_expire function.
If you execute the ALTER ROLE statement with PASSWORD NULL, password authentication is disabled. Therefore, you cannot log in with the old password.
In either of the following cases, the password rollover is disabled and the previous password expires immediately:
When changing the password, the password is specified in MD5 or SCRAM encrypted format.
The password method has changed since the last time the password was set.
The relevant parameters are:
password_encryption
scram_iterations
This feature allows up to two background worker processes to reside. This information is displayed in the statistics view pg_stat_activity when it needs to be processed by a worker process. The worker processes added by this feature are those with column backend_type values of "user profile status writer" and "user profile status sender".
These processes may wait on the wait event "UserProFileWorkerMain" of type "Activity".