ALTER PROFILE Password Policy & Account Lockout Control - PolarDB

Modifies an existing profile in PolarDB for PostgreSQL (Compatible with Oracle).

Syntax

ALTER PROFILE profile_name RENAME TO new_name;

ALTER PROFILE profile_name
      LIMIT {parameter value}[...];

Description

ALTER PROFILE supports two forms:

RENAME TO — renames a profile.
LIMIT — modifies the password and account-lockout rules enforced by the profile. Include one or more space-delimited parameter value pairs after the LIMIT keyword.

Parameters

Parameter	Description
`profile_name`	The name of the profile to modify.
`new_name`	The new name for the profile (used with `RENAME TO`).
`parameter`	A `LIMIT` parameter name. See the supported parameters below.
`value`	The value to assign to the parameter.

Supported LIMIT parameters

FAILED_LOGIN_ATTEMPTS

Specifies the maximum number of failed logon attempts before the server locks the account for the duration set by PASSWORD_LOCK_TIME.

Value	Description
`INTEGER > 0`	Maximum number of failed attempts.
`DEFAULT`	Uses the value set in the DEFAULT profile.
`UNLIMITED`	No limit on failed attempts.

PASSWORD_LOCK_TIME

Specifies how long the server keeps an account locked after too many failed logon attempts.

Value	Description
`NUMERIC >= 0`	Number of days. Use a decimal to specify fractional days (for example, `4.5` = 4 days and 12 hours).
`DEFAULT`	Uses the value set in the DEFAULT profile.
`UNLIMITED`	The account stays locked until a database superuser unlocks it.

PASSWORD_LIFE_TIME

Specifies how many days a password remains valid before the user must set a new one. Combine with PASSWORD_GRACE_TIME to allow a grace period after expiry.

If PASSWORD_GRACE_TIME is not set, the server uses the default value from the DEFAULT profile. After the grace period ends, the user can still connect but cannot run any command until the password is changed.

Value	Description
`NUMERIC >= 0`	Number of days. Use a decimal for fractional days.
`DEFAULT`	Uses the value set in the DEFAULT profile.
`UNLIMITED`	The password never expires.

PASSWORD_GRACE_TIME

Specifies the grace period after a password expires until the user is required to change the password. After the grace period ends, the user is allowed to connect to the server but cannot run any command until the expired password is updated.

Value	Description
`NUMERIC >= 0`	Number of days. Use a decimal for fractional days.
`DEFAULT`	Uses the value set in the DEFAULT profile.
`UNLIMITED`	The grace period has no end date.

PASSWORD_REUSE_TIME

Specifies the number of days a user must wait before reusing a password. Must be used together with PASSWORD_REUSE_MAX.

If one parameter has a finite value and the other is UNLIMITED, previous passwords cannot be reused.
If both are UNLIMITED, no reuse restriction applies.

Value	Description
`NUMERIC >= 0`	Number of days. Use a decimal for fractional days.
`DEFAULT`	Uses the value set in the DEFAULT profile.
`UNLIMITED`	No waiting period for password reuse.

PASSWORD_REUSE_MAX

Specifies how many password changes must occur before a password can be reused. Must be used together with PASSWORD_REUSE_TIME. The same UNLIMITED interaction rules as PASSWORD_REUSE_TIME apply.

Value	Description
`INTEGER > 0`	Number of password changes required.
`DEFAULT`	Uses the value set in the DEFAULT profile.
`UNLIMITED`	No limit on password reuse.

PASSWORD_VERIFY_FUNCTION

Specifies a PL/SQL function used to validate password complexity.

Value	Description
Function name	The name of a PL/SQL function.
`DEFAULT`	Uses the function set in the DEFAULT profile.
`NULL`	Disables password complexity verification.

PASSWORD_ALLOW_HASHED

Specifies whether the server accepts a pre-hashed password from the client.

TRUE / ON / YES / 1 — allows the client to supply a pre-hashed password.
FALSE / OFF / NO / 0 — requires a plain-text password. The server returns an error if it receives a hashed password.

Value	Description
Boolean (`TRUE`/`ON`/`YES`/`1` or `FALSE`/`OFF`/`NO`/`0`)	Whether to allow hashed passwords.
`DEFAULT`	Uses the value set in the DEFAULT profile.

Note

PASSWORD_ALLOW_HASHED is not supported by Oracle.

Examples

Limit failed logon attempts and set a lockout period

The following example modifies acctg_profile so that after 3 consecutive failed logon attempts, the account is locked for 1 day.

ALTER PROFILE acctg_profile
       LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;

Rename a profile

The following example renames acctg_profile to payables_profile.

ALTER PROFILE acctg_profile RENAME TO payables_profile;