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
