CREATE PROFILE v17

Suggest edits

Name

CREATE PROFILE — Create a profile.

Synopsis

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

Description

CREATE PROFILE create a profile. Include the LIMIT clause and one or more space-delimited parameter/value pairs to specify the rules enforced by EDB Postgres Advanced Server.

EDB Postgres Advanced Server creates a default profile named DEFAULT. When you use the CREATE ROLE command to create a role, the new role is associated with the DEFAULT profile. If you upgrade from a previous version of EDB Postgres Advanced Server to EDB Postgres Advanced Server 10, the upgrade process creates the roles in the upgraded version to the DEFAULT profile.

You must be a superuser to use CREATE PROFILE.

Include the LIMIT clause and one or more space-delimited parameter/value pairs to specify the rules enforced by EDB Postgres Advanced Server.

Parameters

profile_name

The name of the profile.

parameter

The password attribute for the rule to monitor.

value

The value the parameter must reach before an action is taken by the server.

EDB Postgres Advanced Server supports these values for each parameter:

FAILED_LOGIN_ATTEMPTS specifies the number of failed login attempts that a user can make before the server locks them out of their account for the length of time specified by PASSWORD_LOCK_TIME. Supported values are:

  • An INTEGER value greater than 0.
  • DEFAULT — The value of FAILED_LOGIN_ATTEMPTS specified in the DEFAULT profile.
  • UNLIMITED — The connecting user can make an unlimited number of failed login attempts.

PASSWORD_LOCK_TIME specifies the length of time that must pass before the server unlocks an account that was locked because of FAILED_LOGIN_ATTEMPTS. Supported values are:

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

PASSWORD_LIFE_TIME specifies the number of days that the current password can be used before the user is prompted to provide a new password. Include the PASSWORD_GRACE_TIME clause when using the PASSWORD_LIFE_TIME clause to specify the number of days to pass after the password expires before the user is forced to change their password. If you don't specify PASSWORD_GRACE_TIME, the password expires on the day specified by the default value of PASSWORD_GRACE_TIME, and the user isn't allowed to execute any command until a new password is provided. Supported values are:

  • A NUMERIC value greater of 0 or greater. To specify a fractional portion of a day, specify a decimal value. For example, use the value 4.5 to specify 4 days, 12 hours.
  • DEFAULT — The value of PASSWORD_LIFE_TIME specified in the DEFAULT profile.
  • UNLIMITED — The password doesn't have an expiration date.

PASSWORD_GRACE_TIME specifies the length of the grace period after a password expires until the user is forced to change their password. When the grace period expires, a user is allowed to connect but isn't allowed to execute any command until they update their expired password. Supported values are:

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

PASSWORD_REUSE_TIME specifies the number of days a user must wait before reusing a password. Use the PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters together. If you specify a finite value for one of these parameters while the other is UNLIMITED, old passwords can never be reused. If both parameters are set to UNLIMITED, there are no restrictions on password reuse. Supported values are:

  • A NUMERIC value of 0 or greater. To specify a fractional portion of a day, specify a decimal value. For example, use the value 4.5 to specify 4 days, 12 hours.
  • DEFAULT — The value of PASSWORD_REUSE_TIME specified in the DEFAULT profile.
  • UNLIMITED — 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. Use the PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters together. If you specify a finite value for one of these parameters while the other is UNLIMITED, old passwords can never be reused. If both parameters are set to UNLIMITED there are no restrictions on password reuse. Supported values are:

  • An INTEGER value of 0 or greater.
  • DEFAULT — The value of PASSWORD_REUSE_MAX specified in the DEFAULT profile.
  • UNLIMITED — The password can be reused without restrictions.

PASSWORD_VERIFY_FUNCTION specifies password complexity. Supported values are:

  • 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 is allowed for use. 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, then a password must be specified in a plain-text form to be validated. Otherwise an error is thrown if a server receives an encrypted password. Supported values are:

  • A BOOLEAN value TRUE/ON/YES/1 or FALSE/OFF/NO/0.
  • DEFAULT — The value of PASSWORD_ALLOW_HASHED specified in the DEFAULT profile.
Note

The PASSWORD_ALLOW_HASHED isn't compatible with Oracle.

To avoid issues when checking passwords, use PASSWORD_VERIFY_FUNCTION along with PASSWORD_ALLOW_HASHED. Make sure PASSWORD_ALLOW_HASHED is set to false so that the password can be checked properly instead of being accepted in hashed form.

Notes

Use the DROP PROFILE command to remove the profile.

Examples

This command creates a profile named acctg. The profile specifies that if a user hasn't authenticated with the correct password in five attempts, the account is locked for one day:

CREATE PROFILE acctg LIMIT
       FAILED_LOGIN_ATTEMPTS 5
       PASSWORD_LOCK_TIME 1;

This command creates 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 doesn't change their password before the 90 days specified in the profile passes, they are issued a warning at login. After a grace period of three days, their account isn't allowed to invoke any commands until they change their password.

This command creates a profile named accts. The profile specifies that a user can't reuse a password within 180 days of the last use of the password and must change their password at least five times before reusingit:

CREATE PROFILE accts LIMIT
       PASSWORD_REUSE_TIME 180
       PASSWORD_REUSE_MAX 5;

This command creates a profile named resources. The profile calls a user-defined function named password_rules that verifies that the password provided meets their standards for complexity:

CREATE PROFILE resources LIMIT
       PASSWORD_VERIFY_FUNCTION password_rules;

See also

ALTER PROFILE, DROP PROFILE

← Prev

CREATE PROCEDURE

↑ Up

Compatible SQL commands

Next →

CREATE QUEUE