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.
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;
1 - Profiles
You can set password policies for users by assigning them 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;
For passwords to be effective, they must be hard to guess.
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.
Password expiration has no effect on current sessions.
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.
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.
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.