CREATE PROFILE — define a new profile
CREATE PROFILE [ IF NOT EXISTS ]name[ LIMITparametervalue[ ... ] ] whereparametercan be: FAILED_LOGIN_ATTEMPTS | PASSWORD_REUSE_TIME | PASSWORD_REUSE_MAX | PASSWORD_LIFE_TIME | PASSWORD_GRACE_TIME | OLD_PASSWORD_TIME | OLD_PASSWORD_MAX | USER_INACTIVE_TIME | FAILED_AUTH_KEEP_TIME | PASSWORD_MIN_UNIQUE_CHARS | PASSWORD_MIN_LEN | PASSWORD_REQUIRE_COMPLEX CREATE PROFILE [ IF NOT EXISTS ]nameFROMexisting_profile
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.
nameThe 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 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 on the primary server. Measured in days.
Possible values are 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.
However, if the LDAP authentication is configured, this role still can connect to standby servers, unless it is locked in LDAP too.
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 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.
OLD_PASSWORD_TIME value
Specifies for how long the previous password can be used for authentication
alongside the new one. If the previous password is used, a corresponding
warning is raised. It is possible to use only the most recent previous
password, which is stored in the pg_role_password
catalog.
Possible values are real numbers greater than or equal to
0, interval values,
DEFAULT, or UNLIMITED. If this
parameter is set to 0 (default), previous passwords are
not stored and cannot be used for authentication.
Set this parameter together with OLD_PASSWORD_MAX
as its effect depends on the combination. It is possible to use the previous
password only if values of both these parameters differ from
0. If both parameters are set to UNLIMITED,
there are no restrictions on using the previous password. However, this is
not recommended as it may result in security issues.
OLD_PASSWORD_MAX value
Specifies the number of times a previous password can be used for
authentication. The count of used login attempts is stored in the pg_role_password
catalog and cannot be reset.
Possible values are integers greater than or equal to
0, DEFAULT, or
UNLIMITED. If this parameter is set to
0 (default), authentication with the previous password
fails and a corresponding error message is raised.
Set this parameter together with OLD_PASSWORD_TIME
as its effect depends on the combination. It is possible to use the previous
password only if values of both parameters differ from 0.
If both parameters are set to UNLIMITED,
there are no restrictions on using the previous password. However, this is
not recommended, as it may result in security issues.
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 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 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 at least one character from three of the following groups: lowercase letters, uppercase letters, digits, and special 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 EXISTSDo not throw an error if a profile with the same name already exists.
existing_profileThe 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.
All letters of languages without case distinction (Hindi, Chinese, etc.) in the UTF-8 encoding are considered lowercase.
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.
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.
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.
OLD_PASSWORD_TIME and OLD_PASSWORD_MAX
support all available password-based
authentication methods. However, correct operation of these
parameters is not guaranteed if the authentication or the encryption method
of the new password differs from those of the previous password. Thus, if
you change the authentication method in pg_hba.conf or
the value of password_encryption in
postgresql.conf, change the password twice afterwards
to ensure that both the new and the previous password can be used for
authentication.
Note that OLD_PASSWORD_TIME and
OLD_PASSWORD_MAX are incompatible with the use_scram_passthrough
connection management option of the postgres_fdw
module.
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.