Details of the parameters set in the profile are explained.
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 role. 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 log in only if both constraints are met.
If there is no grace period, the password expires when the password life time is over. If you log in 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 log in 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 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.
PASSWORD_ALLOW_HASHED
Whether to allow password specification by hash value when changing password
Allow if true.
When setting or changing a password in a CREATE ROLE or ALTER ROLE statement, you can specify a new password in the form of a hash value, but this is rejected if this parameter is false.
If the specified password is in the form of a hash value that is not plain text, the server cannot verify that the password has not been reused, and user can change it to the same password to extend its lifetime. Specify false to avoid this.
The password is specified in the form of a hash value when changing the password using the psql command's \password meta-command or when dumping the database user's password using the pg_dumpall command.
If this parameter is false, the password cannot be changed by the psql command's \password meta command. Specify the new password in clear text in the PASSWORD clause of the CREATE ROLE or ALTER ROLE statement. At this time, if command input history is enabled on the client side or if the setting to log SQL statements is enabled on the server side, the plaintext password might remain in the log file.
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 hash value. 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.
FAILED_LOGIN_ATTEMPTS
Number of consecutive failed login attempts for a user before the user is locked
If password authentication fails consecutively for the number of times specified by this parameter, the user is locked and cannot log in.
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.
