Postgres Pro Standard : Documentation: 16: CREATE PROFILE : Postgres Professional

CREATE PROFILE
Prev	Up	SQL Commands	Home	Next

CREATE PROFILE

CREATE PROFILE — define a new profile

Synopsis

CREATE PROFILE [ IF NOT EXISTS ] name [ LIMIT parameter value [ ... ] ]

where parameter can be:

      FAILED_LOGIN_ATTEMPTS
    | PASSWORD_REUSE_TIME
    | PASSWORD_REUSE_MAX
    | PASSWORD_LIFE_TIME
    | PASSWORD_GRACE_TIME
    | USER_INACTIVE_TIME
    | FAILED_AUTH_KEEP_TIME
    | PASSWORD_MIN_UNIQUE_CHARS
    | PASSWORD_MIN_LEN
    | PASSWORD_REQUIRE_COMPLEX

CREATE PROFILE [ IF NOT EXISTS ] name FROM existing_profile

Description

The CREATE PROFILE command adds a new profile for the Postgres Pro database cluster. You must be a database superuser or have the privileges of the pg_manage_profiles role to use this command.

A profile defines a set of parameters that restrict database usage. In particular, Postgres Pro profiles enforce password management policy for the roles assigned to them. Profiles are defined at the database cluster level, so they apply to all databases in the cluster.

By default, all parameter values of a new profile are set to DEFAULT, which inherits the actual values from the built-in default profile. Initially, the default profile provides no usage restrictions, but it can be changed by the ALTER PROFILE command. The UNLIMITED value indicates that no restrictions apply to a particular parameter.

Once a profile is assigned to a role, all restrictions of this profile apply to this role. All newly created roles have the default profile, unless you explicitly assign a different profile to this role.

Parameters

name

The name of the new profile.

FAILED_LOGIN_ATTEMPTS value

Specifies the number of failed login attempts before the role is locked. A superuser can unlock the locked role by running the ALTER ROLE command with the ACCOUNT UNLOCK clause.

Note that there can be several actual login attempts made behind each user-perceived login attempt. For example, when the user tries to log in with SSL enabled, libpq-based clients by default also make a non-SSL connection attempt if an SSL connection fails.

Possible values are integers greater than 0, DEFAULT, or UNLIMITED.

PASSWORD_REUSE_TIME value

Specifies for how long an old password cannot be reused. Measured in days. Possible values are real numbers greater than or equal to 0, interval values, DEFAULT, or UNLIMITED.

Set this parameter together with PASSWORD_REUSE_MAX as its effect depends on the combination. If both these parameters are set to UNLIMITED, there are no restrictions on password reuse. If only one of them is set to UNLIMITED, password reuse is always forbidden.

PASSWORD_REUSE_MAX value

Specifies the number of password changes required before the current password can be reused. Possible values are integers greater than or equal to 0, DEFAULT, or UNLIMITED.

Set this parameter together with PASSWORD_REUSE_TIME as its effect depends on the combination. If both these parameters are set to UNLIMITED, there are no restrictions on password reuse. If only one of them is set to UNLIMITED, password reuse is always forbidden.

PASSWORD_LIFE_TIME value

Specifies for how long the password can be used for authentication. Measured in days. Possible values are real numbers, interval values, DEFAULT, or UNLIMITED. The resulting value in seconds must be greater than 0. Once the password expires, all further connections of the corresponding role are rejected. The role can be unlocked with the ALTER ROLE command.

If PASSWORD_GRACE_TIME parameter is also set, the specified grace period is added to the password lifetime. During the grace period, the role is prompted to change the password while still allowed to log in.

PASSWORD_GRACE_TIME value

Specifies for how long a warning is raised that the password is going to expire while login is still allowed, measured in days. You can set the password life time in the VALID UNTIL attribute of the role or in the PASSWORD_LIFE_TIME parameter of the profile.

Possible values are real numbers greater than or equal to 0, interval values, DEFAULT, or UNLIMITED. If the PASSWORD_GRACE_TIME parameter is set to UNLIMITED, the password life time effectively becomes unlimited.

USER_INACTIVE_TIME value

Specifies for how long the user can be inactive since the last login before the role is locked. Measured in days. A superuser can unlock the locked role by running the ALTER ROLE command with the ACCOUNT UNLOCK clause. Possible values are real numbers, interval values, DEFAULT, or UNLIMITED. The resulting value in seconds must be greater than 0.

FAILED_AUTH_KEEP_TIME value

Specifies for how long the information on the user's first authentication failure is kept. Measured in days. Possible values are real numbers, interval values, DEFAULT, or UNLIMITED. The resulting value in seconds must be greater than 0. When the user attempts to log in after this time interval expires, the failed login attempts counter (see FAILED_LOGIN_ATTEMPTS parameter) is reset and the user is unlocked if they were locked previously due to authentication failures.

PASSWORD_MIN_UNIQUE_CHARS value

Specifies minimum number of unique characters for a password. Possible values are integers greater than 0, DEFAULT, or UNLIMITED.

PASSWORD_MIN_LEN value

Specifies minimum number of characters for a password. Possible values are positive integers, DEFAULT, or UNLIMITED.

PASSWORD_REQUIRE_COMPLEX [value]

Specifies whether password complexity is checked. If this check is enabled, a password must meet the following requirements:

Password contains both letters and non-letter characters
Password doesn't contain the user name

Possible values are booleans or DEFAULT. If the parameter is used without a value, it is set as true.

IF NOT EXISTS

Do not throw an error if a profile with the same name already exists.

existing_profile

The name of an existing profile to copy. The new profile will have the same properties as the existing one, but it will be an independent object.

Notes

Use ALTER PROFILE to change the parameter values of a profile, and DROP PROFILE to remove a profile. All the parameters specified by CREATE PROFILE can be modified later by running the ALTER PROFILE command.

Warning

PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters might not work if the password is passed in an encrypted form during the password change. The \password command of psql encrypts the password (see the section called “Meta-Commands” for details). If the password is MD5 encrypted, it can be checked for equivalence, and PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX parameters can be applied. But there is no way to check for equivalence for SCRAM-SHA-256 encrypted passwords.

Warning

PASSWORD_MIN_UNIQUE_CHARS, PASSWORD_MIN_LEN, and PASSWORD_REQUIRE_COMPLEX parameters don't work if the password is passed in an encrypted form during the password change.

Examples

Create a profile admin_profile:

CREATE PROFILE admin_profile
    LIMIT PASSWORD_REUSE_MAX 10
          PASSWORD_REUSE_TIME 30;

Create a role having admin_profile:

CREATE ROLE admin WITH PROFILE admin_profile;

Create a profile from an existing profile:

CREATE PROFILE administrator FROM admin_profile;

This can be convenient to be able to use an existing profile as a template for a new one.

CREATE PROFILE

Synopsis

Description

Parameters

Notes

Warning

Warning

Examples

See Also