You can use the CREATE PROFILE statement to create a new profile.

The statement has the following syntax:

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

You can use the LIMIT clause and one or more space-delimited parameter-value pairs to specify the rules enforced by PolarDB for PostgreSQL(Compatible with Oracle).

Parameters

ParameterDescription
profile_nameSpecifies the name of a profile.
parameterSpecifies the attribute limited by the profile.
valueSpecifies the parameter limit.

PolarDB for PostgreSQL(Compatible with Oracle) support the following values for each parameter:

FAILED_LOGIN_ATTEMPTS specifies the number of failed logon attempts that a user has made before the server locks the account of the user. PASSWORD_LOCK_TIME specifies the period in which the account is locked. Valid values:

  • An INTEGER value greater than 0.
  • DEFAULT: the value of FAILED_LOGIN_ATTEMPTS specified in the DEFAULT profile.
  • UNLIMITED: specifies that the system allows an unlimited number of failed logon attempts.

PASSWORD_LOCK_TIME specifies the period in which an account is locked before the server unlocks the account. This account is locked due to the failed logon attempts more than the value specified by FAILED_LOGIN_ATTEMPTS. Valid values:

  • A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, specify a decimal value. For example, use the value 4.5 to specify 4 days and 12 hours.
  • DEFAULT: the value of PASSWORD_LOCK_TIME specified in the DEFAULT profile.
  • UNLIMITED: the account is locked until it is manually unlocked by a database superuser.

PASSWORD_LIFE_TIME specifies the number of days that the current password are used before the user is prompted to provide a new password. If you use the PASSWORD_LIFE_TIME clause, you can use the PASSWORD_GRACE_TIME clause to specify the period between the time when a password expires and the time when the connection request of the role that uses the password is rejected. If PASSWORD_GRACE_TIME is not specified, the password expires on the day specified by the default value of PASSWORD_GRACE_TIME. Then, the user is not allowed to execute any statement before a new password is provided. Valid values:

  • A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, specify a decimal value. For example, use the value 4.5 to specify 4 days and 12 hours.
  • DEFAULT: the value of PASSWORD_LIFE_TIME specified in the DEFAULT profile.
  • UNLIMITED: specifies that the password never expires.

PASSWORD_GRACE_TIME specifies the grace period between the time when a password expires and the time when the user is forced to change the password. After the grace period, a user is allowed to connect to the service, but cannot execute any statement before the user updates the expired password. Valid values:

  • A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, specify a decimal value. For example, use the value 4.5 to specify 4 days and 12 hours.
  • DEFAULT: the value of PASSWORD_GRACE_TIME specified in the DEFAULT profile.
  • UNLIMITED: specifies that the grace period is infinite.

PASSWORD_REUSE_TIME specifies the number of days a user must wait before the user can reuse a password.

The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters are used together. If you specify a finite value for either of the parameters and the other parameter is set to UNLIMITED, old passwords can never be reused. If both parameters are set to UNLIMITED, passwords can be reused without restrictions. Valid values:

  • A NUMERIC value greater than or equal to 0. To specify a fractional portion of a day, specify a decimal value. For example, use the value 4.5 to specify 4 days and 12 hours.
  • DEFAULT: the value of PASSWORD_REUSE_TIME specified in the DEFAULT profile.
  • UNLIMITED: specifies that the password can be reused without restrictions.

PASSWORD_REUSE_MAX specifies the number of password changes that must occur before a password can be reused.

The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters are used together. If you specify a finite value for either of the parameters and the other parameter is set to UNLIMITED, old passwords can never be reused. If both parameters are set to UNLIMITED, passwords can be reused without restrictions. Valid values:

  • An INTEGER value greater than or equal to 0.
  • DEFAULT: the value of PASSWORD_REUSE_MAX specified in the DEFAULT profile.
  • UNLIMITED: specifies that the password can be reused without restrictions.

PASSWORD_VERIFY_FUNCTION specifies password complexity. Valid values:

  • The name of a PL/SQL function.
  • DEFAULT: the value of PASSWORD_VERIFY_FUNCTION specified in the DEFAULT profile.
  • NULL

PASSWORD_ALLOW_HASHED specifies whether an encrypted password can be used. If you specify TRUE, the system allows a user to change the password by specifying a hash computed encrypted password on the client side. However, if you specify FALSE, a valid password must be in a plain-text form. Otherwise, an error message is returned if a server receives an encrypted password. Valid values:

  • A BOOLEAN value: TRUE, ON, YES, 1, FALSE, OFF, NO, and 0.
  • DEFAULT: the value of PASSWORD_ALLOW_HASHED specified in the DEFAULT profile.
Note The PASSWORD_ALLOW_HASHED parameter is not compatible with Oracle.

Notes

You can run the DROP PROFILE statement to remove the profile.

Examples

You can run the following statement to create a profile named acctg. The profile specifies that an account is locked for one day if the user has not been authenticated with the correct password during five attempts.

CREATE PROFILE acctg LIMIT
       FAILED_LOGIN_ATTEMPTS 5
       PASSWORD_LOCK_TIME 1;

You can run the following statement to create a profile named sales. The profile specifies that a user must change their password every 90 days.

CREATE PROFILE sales LIMIT
       PASSWORD_LIFE_TIME 90
       PASSWORD_GRACE_TIME 3;

If the user has not changed their password during the 90 days specified in the profile, an error message is returned when the user tries to log on to the service. After a grace period of three days, the account is not be allowed to execute any statements before the user change the password.

You can run the following statement to create a profile named accts. The profile specifies that a user cannot reuse a password within 180 days after the password is used, and must change the password at least five times before the password is reused.

CREATE PROFILE accts LIMIT
       PASSWORD_REUSE_TIME 180
       PASSWORD_REUSE_MAX 5;

You can run the following statement to create a profile named resources. The profile calls a user-defined function named password_rules. This function verifies that the provided password meets the complexity requirements:

CREATE PROFILE resources LIMIT
       PASSWORD_VERIFY_FUNCTION password_rules;