Using user_profile

Use user_profile to enforce password and login rules on database roles. Before you start, install and register user_profile in your database.

Understanding the user_profile parameters

A profile is a set of security attributes applied to a group of roles to enforce authentication rules such as password complexity, expiration, and lockout policies.

Each profile defines password management policies designed to strengthen authentication security. These profiles allow you to:

  • Track failed login attempts.
  • Lock accounts automatically after a specified threshold of failures.
  • Enforce password expiration and grace periods for renewals.
  • Define complexity requirements to ensure strong credentials.
  • Restrict password reuse to prevent credential cycling.

After creating a profile, you can associate it with one or more user roles. When a user initiates a connection, the WarehousePG (WHPG) server acts as the enforcement engine, applying the specific security rules defined in the profile associated with that user's login role.

Although a single WHPG cluster can support multiple profiles, these profiles are cluster-wide resources. This means a profile is shared by all databases within the cluster; a user accessing different databases under the same cluster will always be subject to the same profile constraints.

To update your security requirements, you only need to modify the profile. The WHPG server will then automatically apply those updated standards to every user associated with that profile across the entire cluster.

By default, WHPG creates a system profile named default. When a new role is created, the server automatically assigns it to the default profile unless an alternative is explicitly specified. Upon installing the user_profile extension, all existing roles are retroactively assigned to the default profile to ensure immediate coverage. The default profile is a mandatory system component and cannot be deleted.

Each profile consists of several parameters that govern how passwords and logins are handled:

Parameter NameDescriptionSupported values
FAILED_LOGIN_ATTEMPTSThe maximum number of failed login attempts permitted before the server locks the account for the duration defined by PASSWORD_LOCK_TIME.INTEGER > 0, or UNLIMITED (no lockout threshold).
PASSWORD_LOCK_TIMEThe number of days an account remains locked after exceeding FAILED_LOGIN_ATTEMPTS.NUMERIC >= 0 (supports decimals for partial days), or UNLIMITED (requires superuser unlock).
PASSWORD_LIFE_TIMEThe password's validity period in days. If used with PASSWORD_GRACE_TIME, the user can keep working with a warning during the grace period.NUMERIC >= 0 (supports decimals for partial days), or UNLIMITED (no expiration).
PASSWORD_GRACE_TIMEThe grace period in days starting after password expiration. During this time, users can connect and run commands but get a warning. After it ends, they must change their password before running any other command.NUMERIC >= 0 (supports decimals for partial days), or UNLIMITED (infinite grace period).
PASSWORD_REUSE_TIMEThe minimum number of days that must pass before a previous password can be reused. Works in conjunction with PASSWORD_REUSE_MAX. If both parameters are set to UNLIMITED, there are no restrictions on password reuse.NUMERIC >= 0 (supports decimals for partial days), or UNLIMITED (no time restriction).
PASSWORD_REUSE_MAXThe number of password changes required before a previous password can be reused. Works in conjunction with PASSWORD_REUSE_TIME. If both parameters are set to UNLIMITED, there are no restrictions on password reuse.INTEGER >= 1, or UNLIMITED (no count restriction).
PASSWORD_MINIMUM_LENGTHThe minimum character count required for a password.INTEGER >= 1, or UNLIMITED (no length check).
PASSWORD_REQUIRE_UPPEREnforces the inclusion of at least one uppercase character in the password string.TRUE (required) or FALSE (optional).
PASSWORD_REQUIRE_LOWEREnforces the inclusion of at least one lowercase character in the password string.TRUE (required) or FALSE (optional).
PASSWORD_REQUIRE_NUMBEREnforces the inclusion of at least one numeric character (0-9) in the password string.TRUE (required) or FALSE (optional).
PASSWORD_SPECIAL_CHARSDefines a set of special characters; the password must contain at least one character from this specified string.A string of characters, or '' (empty string) for no restriction.

The default profile initially has all parameters set to UNLIMITED or FALSE.

Creating a new profile

Use the up_create_profile() User Defined Function (UDF) to create a new profile:

up_create_profile( <profile_name>
       [<param1>, <value1>, <param2>, <value2> ... ]);

Where:

  • profile_name specifies the name of the profile.
  • parameter specifies the attribute limited by the profile.
  • value specifies the parameter limit.

Examples

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

SELECT up_create_profile('acctg',
        'FAILED_LOGIN_ATTEMPTS', '5',
        'PASSWORD_LOCK_TIME', '1');

The following command creates a profile named sales. The profile specifies that a user must change their password every 90 days:

SELECT up_create_profile('sales',
        'PASSWORD_LIFE_TIME', '90',
        'PASSWORD_GRACE_TIME', '3');

The following 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 reusing the password:

SELECT up_create_profile('accts',
        'PASSWORD_REUSE_TIME', '180',
        'PASSWORD_REUSE_MAX', '5');

The following command creates a profile named resource. When a user associated with this profile wants to change the password, the password must contain at least one number character and one special character:

SELECT up_create_profile('resource',
        'PASSWORD_REQUIRE_NUMBER', 'TRUE',
        'PASSWORD_SPECIAL_CHARS', '!@#$%^&*');

Altering a profile

Use the up_alter_profile() UDF to modify a user profile:

up_alter_profile( <profile_name>
       [<param1>, <value1>, <param2>, <value2> ... ]);

Where:

  • profile_name specifies the name of the profile.
  • parameter specifies the attribute limited by the profile. Use name to rename the profile.
  • value specifies the parameter limit.

A database superuser can use the up_alter_profile() UDF to modify the values specified by the default profile.

Examples

The following command modifies a profile named acctg_profile to specify that if a user doesn't successfully authenticate within three attempts, the account is locked for one day.

SELECT up_alter_profile('acctg_profile',
        'FAILED_LOGIN_ATTEMPTS', '3',
        'PASSWORD_LOCK_TIME', '1');

The following command changes the name of acctg_profile to payables_profile:

SELECT up_alter_profile('acctg_profile', 'name', 'payables_profile');

Inspecting a profile

Use the up_show_profile() UDF to list one or all profiles:

up_show_profile( [profile_name] );

Where:

  • profile_name specifies the name of the profile. NULL lists all profiles.

Dropping a profile

Use the up_drop_profile() UDF to drop a profile:

up_drop_profile(<profile_name>);

Where:

  • profile_name specifies the name of the profile being dropped.

You cannot drop a profile if there are one or more users associated with it.

Examples

The following command drops a profile named acctg_profile:

SELECT up_drop_profile('acctg_profile');

Associating a profile with an existing role

Use the up_alter_role_profile() UDF to associate a profile with an existing role:

up_alter_role_profile(<role_name|role_id>, <profile_name>);

Where:

  • role_id or role_name specify the role to associate the profile with.
  • profile_name specifies the name of the profile.

Only a database superuser can use the up_alter_role_profile() UDF.

Each login role can have only one profile. To discover the profile that's currently associated with a login role, use the up_show_role_info() UDF:

up_show_role_info([role_name|role_id])

Where:

  • role_id or role_name specify the specific role to show (use NULL to show all roles).

Examples

The following command associates a profile named acctg with a user named john:

SELECT up_alter_role_profile('john', 'acctg');

Locking and unlocking a role

A database superuser can use the up_lock() and up_unlock() UDFs to lock or unlock a role:

up_lock(<role_name|role_id>)
up_unlock(<role_name|role_id>)

Where:

  • role_name or role_id specifies the role to be locked or unlocked.

Use up_lock() to immediately lock a user role. Once locked, a role’s LOGIN functionality is disabled. The role remains in this locked state until a superuser unlocks it with up_unlock().

Use up_unlock() to unlock a role that was locked by up_lock(), or a role that was locked through the profile FAILED_LOGIN_ATTEMPTS setting.

Examples

The following command locks a role named john. The account remains locked until the account is unlocked by calling up_unlock().

SELECT up_lock('john');

The following command unlocks a role named john:

SELECT up_unlock('john');

Limitations

  • Failed-login locking and password expiration apply only to password authentication methods (md5, scram-sha-256, and password) in pg_hba.conf. Logins that use other methods, such as trust, ident, or ldap, skip these checks, including for roles locked with up_lock().
  • user_profile doesn't have a maximum password length setting, only PASSWORD_MINIMUM_LENGTH.
  • up_show_profile() shows times in whole days and rounds down, so a one-hour lock shows as 0. The setting is still applied correctly.
  • user_profile isn't a drop-in replacement for advanced_password_check. Roles stay as they are, but you must create profiles and assign them to roles again.

Could this page be better? Report a problem or suggest an addition!