Product Documentation

Appearance

All platforms

Password Policies ​

To make deployments as secure as possible, we recommend using external authentication. See how enable single sign-on for cloud platforms or how to [set up LDAP] for appliance platforms.

In the event that external authentication techniques cannot be used for certain users or deployments, Yellowbrick supports configurable Password Policies. The feature only applies to local database users with credentials stored in the database. The policies provide a number of additional controls to make sure that password complexity, expiry and login attempts meet organizational security guidelines.

Enabling Password Policies ​

Password Policies is a new feature that must be enabled by setting the enable_password_policy configuration parameter, after which a full database restart is required. If the configuration parameter is not set, running ALTER USER or ALTER SYSTEM commands will receive an error such as:

txt
ERROR:  Password policies are not enabled

HINT:  Set enable_password_policy to true to enable this feature

If you disable enable_password_policy (and restart the DB), all password policy checks will be disabled regardless of which options are set for users and system.

Password Policy Rules ​

There are two types of password policy rules:

  • System level
  • User level

Password policies for users override those defined for the system.

Viewing password policies ​

You can view current password policies by querying the system view sys.password_policy.

Setting a System-Level Password Policy ​

Use the following query to set up system-level password policies:

sql
ALTER SYSTEM SET enable_password_policy = true;

-- Restart the database

ALTER [SYSTEM | USER | ROLE ] ALTER PASSWORD_POLICY WITH (
  <options>
) 
Where <options> can be:
  [ MIN_LENGTH [=] <min length> ]
  [ MIN_LETTERS [=] <min letters> ]
  [ MIN_LOWER_CASE [=] <min lower case> ]
  [ MIN_UPPER_CASE [=] <min upper case> ]
  [ MIN_SYMBOLS [=] <min symbols> ]   
  [ MIN_DIGITS [=] <min symbols> ]
  [ MAX_REUSE [=] <max reuse> ]
  [ MAX_ATTEMPTS [=] <max attempts> ]
  [ UNLOCK_AFTER_S [=] <unlock interval s> ]
  [ EXPIRE_USER_AFTER_S [=] <expire user interval s> ]
  [ EXPIRE_PASSWORD_AFTER_S [=] <expire password interval s> ]

The parameters are as follows:

MIN_LENGTH
Minimum length of characters the password must have. If 0, passwords of any length are accepted.
MIN_LETTERS
Minimum number of letters in the password. Letters can be uppercase and/or lowercase. If 0, passwords without letters are accepted.
MIN_LOWER_CASE
The amount of lowercase letters required. If 0, passwords with no lowercase letters are accepted.
MIN_UPPER_CASE
The amount of uppercase letters required. If 0, passwords with no uppercase letters are accepted.
MIN_SYMBOLS
Minimum number of symbols (anything that is not a letter or digit). If 0, passwords with no symbols are accepted.
MIN_DIGITS
Minimum number of digits that must be present in the password. If 0, passwords with no digits are accepted.
MAX_REUSE
Maximum number of times the same password can be used by the user. If set to 0, there is no limit. The maximum limit for MAX_REUSE is 10.
MAX_ATTEMPTS
Maximum number of unsuccessful login attempts in a row before the account is locked out. If 0, there are no maximum unsuccessful login attempts.
UNLOCK_AFTER_S
The amount of time (in seconds) that must pass until an account is reenabled after exceeding the maximum attempts. If 0, a locked out user will not automatically unlock.
EXPIRE_USER_AFTER_S
Time elapsed (in seconds) since the user last logged in before the admin must unlock the account. This can be shorter than the password expiry. If 0, users do not expire.
EXPIRE_PASSWORD_AFTER_S
An interval (in seconds) formatted duration after which the password expires if it is not changed. If 0, passwords do not expire.

For example, to change the minimum length of a password for all users at the system level:

sql
ALTER SYSTEM ALTER PASSWORD_POLICY WITH min_length = 10;

Setting a User-Level Password Policy ​

A system-level policy is not required to set user-level (or role-level) policies. Note that ALTER USER and ALTER ROLE are synonymous below. Administrators can override system-level settings using ALTER USER as described in the grammar above. For example:

sql
-- Specify the minimum password length for a specific user:
ALTER USER "testuser1@yellowbrick.com" ALTER PASSWORD_POLICY WITH min_length = 20;

-- Define the number of failed login attempts for a user before they are locked out:
ALTER USER "testuser1@yellowbrick.com" ALTER PASSWORD_POLICY WITH max_attempts = 2;

-- Allow a user to automatically unlock and log back in after a certain amount of seconds.
ALTER USER "testuser1@yellowbrick.com" ALTER PASSWORD_POLICY WITH unlock_after_s = 180;

Unlocking Locked Out Users ​

A user that is locked out can be unlocked by a superuser by setting the corresponding value to 0:

sql
ALTER USER "testuser1@yellowbrick.com" ALTER PASSWORD_POLICY WITH max_attempts = 0;

The user can now log back in if they exceeded the number of login attempts

Expiring Users After a Time Period ​

We can configure the database to Expire a user automatically after a certain number of seconds This time interval starts counting from the last login.

sql
ALTER USER "testuser1@yellowbrick.com" ALTER PASSWORD_POLICY WITH expire_user_after_s = 300;

In this case, when this expired user logs out and tries to login again, the following message is received:

txt
ybsql: FATAL:  role "testuser1@yellowbrick.com" has expired and is not permitted to log in

Forcing Password Expiry After a Time Interval ​

A user's password can be expired after a time interval, for example:

sql
-- Expire user's password after a certain amount of seconds. This time interval begins from the moment the last password was set.
ALTER USER "testuser1@yellowbrick.com" ALTER PASSWORD_POLICY WITH EXPIRE_PASSWORD_AFTER_S = 300;

The time interval is measured from any of these events:

sql
-- User creation:
CREATE USER "testuser1@yellowbrick.com" WITH PASSWORD "Password1";

-- Last password change:
ALTER USER "testuser1@yellowbrick.com" WITH PASSWORD "Password1";

When users log in, they will receive a friendly reminder because password expiry is set:

txt
WARNING:  the password for role "testuser1@yellowbrick.com" will expire in less than 1 day

Once the password has expired, if this user logs out and attempts to log in again, they will receive the following message:

txt
ybsql: FATAL:  the password for role "testuser1@yellowbrick.com" has expired. Please contact the administrator to unlock

Note: The expiration of the user account or the password can only be determined during authentication.

Password Criteria Error Messages ​

The section below shows a few examples of error messages that are displayed when a password does not meet the specified policy criteria.

Minimum length password criteria is not met ​

-- Specify minimum password length for all users at system level
ALTER SYSTEM ALTER PASSWORD_POLICY WITH min_length = 11;

-- Set the password with length 2
ALTER USER "testuser1@yellowbrick.com" WITH PASSWORD '89';
txt
ERROR:  password is too short. Minimum length is 11

Minimum letters password criteria is not met ​

-- Specify minimum password letters for all users at system level:
ALTER SYSTEM ALTER PASSWORD_POLICY WITH min_letters = 6;

-- Set the password with minimum length 11 and 3 letters
ALTER USER "testuser1@yellowbrick.com" WITH PASSWORD 'new_1234567';
txt
ERROR:  password does not contain enough letters. Minimum letters is 6

Maximum login attempts exceeded ​

-- Set the maximum login attempts for a user to 2
ALTER USER "testuser1@yellowbrick.com" ALTER PASSWORD_POLICY WITH max_attempts = 2;

The user enters an incorrect password two times. When they try to log in with the correct password for the third time, they receive the following message:

txt
ybsql: FATAL:  role "testuser1@yellowbrick.com" has been locked out due to too many failed login attempts

Allow a user to automatically unlock and log back in after 180 seconds. Please note that this interval starts from the time of a successful login.

ALTER USER "testuser1@yellowbrick.com" ALTER PASSWORD_POLICY WITH unlock_after_s = 180;

See user logging at sys.user. See the system view sys.password_policy. And enable_password_policy for enabling the feature.