Hash authentication

• 1: Password hashing algorithm
• 2: Configuring hash authentication
• 3: Passwords
• 3.1: Profiles
• 3.2: Password guidelines
• 3.3: Password expiration
• 3.4: Account locking

1 - Password hashing algorithm

Vertica does not store user passwords for the hash authentication method. Rather, Vertica stores a hash of the password. The hashing algorithm is determined by two parameters:

• A system-level configuration parameter, SecurityAlgorithm:

  => ALTER DATABASE DEFAULT SET PARAMETER SecurityAlgorithm = 'hashing_algorithm';
  

• A user-level parameter, SECURITY_ALGORITHM:

  => ALTER USER username SECURITY_ALGORITHM 'hashing_algorithm' IDENTIFIED BY 'new_password';
  

The system-level parameter, SecurityAlgorithm, can have the following values:

• SHA512 (default)

• MD5

The user-level parameter, SECURITY_ALGORITHM, can have the following values. Values other than NONE will take priority over the system-level parameter:

• NONE (default, uses algorithm specified by the system-level parameter SecurityAlgorithm)

• SHA512

• MD5

  Note

  If user's password is hashed with MD5, you cannot change their username with ALTER USER.

A user's EFFECTIVE_SECURITY_ALGORITHM is determined by a combination of the system-level and user-level parameters. If the user-level parameter is set to NONE, the effective security algorithm will be that of the system-level parameter. You can override the system-level parameter for a particular user by setting the user-level parameter to a non-NONE value.

You can view these parameters and their effects on each user by querying the system table PASSWORD_AUDITOR.

The following table shows the various combinations of the system-level and user-level parameters and the effective security algorithm for each.

FIPS mode forces the effective security algorithm to be SHA-512.

2 - Configuring hash authentication

The hash authentication method allows users to authenticate with a password.

Vertica stores hashes (SHA-512 by default) of passwords and not the passwords themselves. For details, see Password hashing algorithm.

1. Create an authentication record with the hash method. Authentication records are automatically enabled after creation. For example, to create the authentication record v_hash for users that log in from the IP address 192.0.2.0/24:

   => CREATE AUTHENTICATION v_hash METHOD 'hash' HOST '192.0.2.0/24';
   

2. Associate the v_hash authentication method with the desired users or roles, using a GRANT statement:

   => GRANT AUTHENTICATION v_hash to user1, user2, ...;
   

3 - Passwords

Assign a password to a user to allow that user to connect to the database using password authentication. When the user supplies the correct password a connection to the database occurs.

Vertica hashes passwords according to each user's EFFECTIVE_SECURITY_ALGORITHM. However, the transmission of the hashed password from the client to Vertica is in plaintext. Thus, it is possible for a "man-in-the-middle" attack to intercept the plaintext password from the client.

Configuring Hash authentication ensures secure login using passwords.

About password creation and modification

You must be a superuser to create passwords for user accounts using the CREATE USER statement. A superuser can set any user account's password.

• To add a password, use the ALTER USER statement.

• To change a password, use ALTER USER or the vsql meta-command \password.

Users can also change their own passwords.

To make password authentication more effective, Vertica recommends that you enforce password policies that control how often users are forced to change passwords and the required content of a password. You set these policies using Profiles.

Default password authentication

When you have not specified any authentication methods, Vertica defaults to using password authentication for user accounts that have passwords.

If you create authentication methods, even for remote hosts, password authentication is disabled. In such cases, you must explicitly enable password authentication. The following commands create the local_pwd authentication method and make it the default for all users. When you create an authentication method, Vertica enables it automatically:

=> CREATE AUTHENTICATION local_pwd METHOD hash' LOCAL;
=> GRANT AUTHENTICATION local_pwd To Public;

3.1 - Profiles

You can set password policies for users by assigning them profiles. You can create multiple profiles to manage the password policies for several categories of users. For example, you could create one profile for interactive users that requires frequent password changes and another profile for user accounts that never requires password changes.

Defining profiles

You create profiles with CREATE PROFILE and alter existing profiles with ALTER PROFILE. Both statements let you set one or more profile parameters which can control, among other things, the minimum lifetime of a password, password complexity, and password-reset rules.

Each profile can specify one or more of the following policies.

• How often users must change their passwords

• How long a password must be set before it can be reset

• How many times users must change their passwords before they can reuse an old password

• How many times a user can fail to log in before the account is locked

• The required length and content of the password:

  • Maximum and minimum number of characters

  • Minimum number of capital letters, lowercase letters, digits, and symbols required in a password

  • How different a new password must be from the old password

Assigning profiles

After you define a profile, you can assign it to new and existing users with CREATE USER and ALTER USER, respectively.

Changes to profile policies for password content—for example, PASSWORD_MAX_LENGTH and PASSWORD_MIN_SYMBOLS—affect users only when they change their passwords. Vertica does not test existing passwords to verify that they comply with new password requirements. To enforce immediate compliance with new profile requirements, use ALTER USER...PASSWORD EXPIRE to immediately expire the current user's password. The next time the user logs in, Vertica prompts them to supply a new password, which must comply with the current policy.

Default profile

Each database contains a DEFAULT profile. Vertica assigns the default profile to users who are not explicitly assigned a profile. The default profile also sets parameters of non-default profiles in two cases:

• Profile parameters that are not explicitly set by CREATE PROFILE

• Parameters that ALTER PROFILE sets to DEFAULT

All parameters in the default profile are initially set to unlimited. You can use ALTER PROFILE to change these settings. For example, the following statement modifies the default profile parameter PASSWORD_MIN_SYMBOLS. The change requires passwords to contain at least one symbol, such as $, #, @. This change affects all profiles where PASSWORD_MIN_SYMBOLS is set to default:

ALTER PROFILE DEFAULT LIMIT PASSWORD_MIN_SYMBOLS 1;

Profile settings and client authentication

The following profile settings affect client authentication methods, such as LDAP or GSS:

• FAILED_LOGIN_ATTEMPTS

• PASSWORD_LOCK_TIME

All other profile settings are used only by Vertica to manage its passwords.

See also

• PROFILES
• Creating a database name and password

3.2 - Password guidelines

For passwords to be effective, they must be hard to guess. You need to protect passwords from:

• Dictionary-style, brute-force attacks

• Users who have knowledge of the password holder (family names, birth dates , etc.)

Use Profiles to enforce good password practices (password length and required content). Make sure database users know the password guidelines, and encourage them not to use personal information in their passwords.

For guidelines on creating strong passwords go to Microsoft Tips for Creating a Strong Password.

See also

• Creating a database name and password

3.3 - Password expiration

The following PROFILE parameters control the conditions for password expiration, new passwords, and minimum lifetime:

• PASSWORD_LIFE_TIME - The number of days a password remains valid

• PASSWORD_MIN_LIFE_TIME - The number of days a password must be set before it can be changed

• PASSWORD_GRACE_TIME - The number of days a password can be used after it expires

• PASSWORD_REUSE_MAX - The number of times you must change your password before you can reuse an earlier password

• PASSWORD_REUSE_TIME - The number of days that must pass after a password is set before you can reuse it

• PASSWORD_MIN_CHAR_CHANGE - Minimum number of characters that must be different from the previous password

For more details on these and other parameters, see CREATE PROFILE and ALTER PROFILE.

Password expiration and grace period behavior

The profile parameter PASSWORD_LIFE_TIME controls the life time of a password in days. By default, the DEFAULT profile sets PASSWORD_LIFE_TIME to UNLIMITED, which disables password expiration. You can change this for the DEFAULT and custom profiles with ALTER PROFILE.

Normally, when a password expires, Vertica forces users to change their passwords the next time they log in. However, you can set a PASSWORD_GRACE_TIME to allow users to log in after their password expires. If a user logs in during their grace period, Vertica warns the user that their password has expired. Once this grace period ends, Vertica will issue the standard prompt to change the user's password.

Expire a password

You can expire a user's password immediately using the ALTER USER statement's PASSWORD EXPIRE parameter. By expiring a password, you can:

• Force users to comply with a change to password policy.

• Set a new password when a user forgets the old password.

3.4 - Account locking

In a profile, you can set a password policy for how many consecutive failed login attempts a user account is allowed before locking. This locking mechanism helps prevent dictionary-style brute-force attempts to guess users' passwords.

Set account locking

Set this value using the FAILED_LOGIN_ATTEMPTS parameter using the CREATE PROFILE or ALTER PROFILE statement.

Vertica locks any user account that has more consecutive failed login attempts than the value to which you set FAILED_LOGIN_ATTEMPTS. The user cannot log in to a locked account, even by supplying the correct password.

Unlock a locked account

You can unlock accounts in one of two ways, depending on your privileges.

• Manually: If you are a superuser , you can manually unlock the account using the ALTER USER command.

  Note

  A superuser account cannot be locked, because it is the only user that can unlock accounts. For this reason, choose a very secure password for a superuser account. See Password guidelines for suggestions.

• Password Lock Time Setting: PASSWORD_LOCK_TIME specifies the number of days (units configurable with PasswordLockTimeUnit) an account is locked after a specified number of failed login attempts (configurable with FAILED_LOGIN_ATTEMPTS). Vertica automatically unlocks the account after the specified number of days has passed.
  
  If you set this parameter to UNLIMITED, the user's account is never automatically unlocked and a superuser must manually unlock it.