CREATE PROFILE
CREATE PROFILE — define a new profile
Synopsis
CREATE PROFILE [ IF NOT EXISTS ]name[ LIMITparametervalue[ ... ] ]whereparametercan 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_COMPLEXCREATE PROFILE [ IF NOT EXISTS ]nameFROMexisting_profile
Description
TheCREATE PROFILE command adds a new profile for thePostgres Pro database cluster. You must be a database superuser or have the privileges of thepg_manage_profiles role to use this command.
Aprofile 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 toDEFAULT, which inherits the actual values from the built-indefault profile. Initially, thedefault profile provides no usage restrictions, but it can be changed by theALTER PROFILE command. TheUNLIMITED 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 thedefault profile, unless you explicitly assign a different profile to this role.
Parameters
nameThe name of the new profile.
FAILED_LOGIN_ATTEMPTSvalueSpecifies the number of failed login attempts before the role is locked. A superuser can unlock the locked role by running theALTER ROLE command with the
ACCOUNT UNLOCKclause.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 withSSL enabled, libpq-based clients by default also make a non-SSL connection attempt if anSSL connection fails.
Possible values are integers greater than
0,DEFAULT, orUNLIMITED.PASSWORD_REUSE_TIMEvalueSpecifies for how long an old password cannot be reused. Measured in days. Possible values are real numbers greater than or equal to
0,intervalvalues,DEFAULT, orUNLIMITED.Set this parameter together with
PASSWORD_REUSE_MAXas its effect depends on the combination. If both these parameters are set toUNLIMITED, there are no restrictions on password reuse. If only one of them is set toUNLIMITED, password reuse is always forbidden.PASSWORD_REUSE_MAXvalueSpecifies the number of password changes required before the current password can be reused. Possible values are integers greater than or equal to
0,DEFAULT, orUNLIMITED.Set this parameter together with
PASSWORD_REUSE_TIMEas its effect depends on the combination. If both these parameters are set toUNLIMITED, there are no restrictions on password reuse. If only one of them is set toUNLIMITED, password reuse is always forbidden.PASSWORD_LIFE_TIMEvalueSpecifies for how long the password can be used for authentication on the primary server. Measured in days. Possible values are real numbers,
intervalvalues,DEFAULT, orUNLIMITED. The resulting value in seconds must be greater than0. Once the password expires, all further connections of the corresponding role are rejected. The role can be unlocked with theALTER ROLE command.However, if theLDAP authentication is configured, this role still can connect to standby servers, unless it is locked inLDAP too.
If
PASSWORD_GRACE_TIMEparameter 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_TIMEvalueSpecifies 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 UNTILattribute of the role or in thePASSWORD_LIFE_TIMEparameter of the profile.Possible values are real numbers greater than or equal to
0,intervalvalues,DEFAULT, orUNLIMITED. If thePASSWORD_GRACE_TIMEparameter is set toUNLIMITED, the password life time effectively becomes unlimited.USER_INACTIVE_TIMEvalueSpecifies 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 theALTER ROLE command with the
ACCOUNT UNLOCKclause. Possible values are real numbers,intervalvalues,DEFAULT, orUNLIMITED. The resulting value in seconds must be greater than0.FAILED_AUTH_KEEP_TIMEvalueSpecifies for how long the information on the user's first authentication failure is kept. Measured in days. Possible values are real numbers,
intervalvalues,DEFAULT, orUNLIMITED. The resulting value in seconds must be greater than0. When the user attempts to log in after this time interval expires, the failed login attempts counter (seeFAILED_LOGIN_ATTEMPTSparameter) is reset and the user is unlocked if they were locked previously due to authentication failures.PASSWORD_MIN_UNIQUE_CHARSvalueSpecifies minimum number of unique characters for a password. Possible values are integers greater than
0,DEFAULT, orUNLIMITED.PASSWORD_MIN_LENvalueSpecifies minimum number of characters for a password. Possible values are positive integers,
DEFAULT, orUNLIMITED.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 astrue.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.
Important
All letters of languages without case distinction (Hindi, Chinese, etc.) in the UTF-8 encoding are considered lowercase.
Notes
UseALTER PROFILE to change the parameter values of a profile, andDROP PROFILE to remove a profile. All the parameters specified byCREATE PROFILE can be modified later by running theALTER PROFILE command.
Warning
PASSWORD_REUSE_TIME andPASSWORD_REUSE_MAX parameters might not work if the password is passed in an encrypted form during the password change. The\password command ofpsql encrypts the password (seethe section called “Meta-Commands” for details). If the password is MD5 encrypted, it can be checked for equivalence, andPASSWORD_REUSE_TIME orPASSWORD_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, andPASSWORD_REQUIRE_COMPLEX parameters don't work if the password is passed in an encrypted form during the password change.
Examples
Create a profileadmin_profile:
CREATE PROFILE admin_profile LIMIT PASSWORD_REUSE_MAX 10 PASSWORD_REUSE_TIME 30;
Create a role havingadmin_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.