Client authentication

• 1: Implementing client authentication
• 2: Dbadmin authentication access
• 3: Creating authentication records
• 4: Modifying authentication records
• 5: Authentication record priority
• 6: Viewing information about client authentication records
• 7: Enabling and disabling authentication methods
• 8: Granting and revoking authentication methods
• 9: Hash authentication
• 9.1: Hash authentication parameters
• 9.2: Configuring hash authentication
• 9.3: Passwords
• 9.3.1: Profiles
• 9.3.2: Password guidelines
• 9.3.3: Password expiration
• 9.3.4: Account locking
• 10: Ident authentication
• 10.1: Installing and setting up an ident server
• 10.2: Configuring ident authentication for database users
• 11: Kerberos authentication
• 11.1: Configure Vertica for Kerberos authentication
• 11.1.1: Create the Vertica principals and keytabs on Linux KDC
• 11.1.2: Specify KDC information and configure realms
• 11.1.3: Inform Vertica about the Kerberos principal
• 11.1.4: Configure the authentication method for all clients
• 11.1.5: Creating the principals and keytab on active directory
• 11.1.6: Get the Kerberos ticket and authenticate Vertica
• 11.2: Configure clients for Kerberos authentication
• 11.2.1: Configure ODBC and vsql clients on non-windows platforms
• 11.2.2: Configure ADO.NET, ODBC, and vsql clients on Windows
• 11.2.3: Configure JDBC clients on all platforms
• 11.3: Troubleshooting Kerberos authentication
• 11.4: Configuring Kerberos authentication
• 12: LDAP authentication
• 12.1: LDAP prerequisites and definitions
• 12.2: LDAP authentication parameters
• 12.3: TLS for LDAP authentication
• 12.4: Configuring multiple LDAP servers
• 12.5: LDAP bind methods
• 12.5.1: Workflow for configuring LDAP bind
• 12.5.2: Workflow for configuring LDAP search and bind
• 13: OAuth 2.0 authentication
• 13.1: Configuring OAuth authentication
• 14: TLS authentication
• 14.1: Implementing client self-authentication

1 - Implementing client authentication

Vertica restricts which database users can connect through client authentication. The database server uses client authentication to establish the identity of the requesting client and determines whether that client is authorized to connect to the Vertica server using the supplied credentials.

When a user or client application attempts to connect to Vertica, they must supply a user name and password.

Vertica offers several client authentication methods. You can configure Vertica to require just a user name for connections, you likely require more secure means of authentication, such as a password at a minimum.

How client authentication works

When connecting to a Vertica database, a user or client application must supply the name of a valid user account. In addition, the application usually includes a means of authentication, such as a password or security certificate.

There are two types of client authentication:

• LOCAL: Authenticating users or applications that are trying to connect from the same node that the database is running on.

• HOST: Authenticating users or applications that are trying to connect from a node that has a different IPv4 or IPv6 address than the database.

The DBADMIN user manages the client authentication information that the database uses to authenticate users.

Vertica takes the following steps to authenticate users:

1. When a user or application attempts to connect to a Vertica database, the system checks to see if the user is a DBADMIN user. If so, authentication occurs using the assigned authentication method, local trust or local hash authentication.

2. For non-DBADMIN users, the database checks to see if the user is associated with an authentication method through a GRANT statement. If so, the database allows the user to log in if they match the parameters required for that authentication method.

   Note

   For detailed information on how authentication priorities work, see Priorities for Client Authentication Methods.

   The DBADMIN user can grant an authentication method to users or user roles. The DBADMIN user can also create a default authentication method that Vertica uses when no authentication has been associated with a user or role.

3. If the user has not been granted an authentication method, the database checks to see if the DBADMIN has established a default authentication method.

4. If the DBADMIN has specified a default authentication method, the database authenticates the user using that default method.

5. If you have not specified a default authentication method, the database checks to see if the DBADMIN user has defined any authentication methods. If not, no authentication information exists in the database. However, if a password exists, the DBADMIN user can log in.

6. If authentication information exists, Vertica rejects the user request to connect to the database. The DBADMIN has not granted an authentication method for that user nor has the DBADMIN defined a default authentication method for all users ('public').

7. If no authentication records exist in the database, Vertica uses implicit trust/implicit password to authenticate the user.

The following image illustrates the steps involved in client authentication:

IPv4 and IPv6 for client authentication

Vertica supports clients using either the IPv4 or the IPv6 protocol to connect to the database server. Internal communication between database servers must consistently use one address family (IPv4 or IPv6). The client, however, can connect to the database from either type of IP address.

If the client will be connecting from either IPv4 or IPv6, you must create two authentication methods, one for each address. Any authentication method that uses HOST authentication requires an IP address.

For example, the first statement allows users to connect from any IPv4 address. The second statement allows users to connect from any IPv6 address:

=> CREATE AUTHENTICATION <name> METHOD 'gss' HOST '0.0.0.0/0'; --IPv4
=> CREATE AUTHENTICATION <name> METHOD 'gss' HOST '::/0'; --IPv6

If you are using a literal IPv6 address in a URL, you must enclose the IPv6 address in square brackets as shown in the following examples:

=> ALTER AUTHENTICATION Ldap SET host='ldap://[1dfa:2bfa:3:45:5:6:7:877]';
=> ALTER AUTHENTICATION Ldap SET host='ldap://[fdfb:dbfa:0:65::177]';
=> ALTER AUTHENTICATION Ldap SET host='ldap://[fdfb::177]';
=> ALTER AUTHENTICATION Ldap SET host='ldap://[::1]';
=> ALTER AUTHENTICATION Ldap SET host='ldap://[1dfa:2bfa:3:45:5:6:7:877]:5678';

If you are working with a multi-node cluster, any IP/netmask settings in (HOST, HOST TLS, HOST NO TLS) must match all nodes in the cluster. This setup allows the database owner to authenticate with and administer every node in the cluster. For example, specifying 10.10.0.8/30 allows a CIDR address range of 10.10.0.8–10.10.0.11.

For detailed information about IPv6 addresses, see RFC 1924 and RFC 2732.

Supported client authentication methods

Vertica supports the following client authentication methods:

• trust: Users can authenticate with a valid username (that is, without a password).

• reject: Rejects the connection attempt.

• password or hash: Users must provide a valid username and password. For details, see Hash authentication.

• gss: Authorizes clients that connect to Vertica with an MIT Kerberos implementation. The Key Distribution Center (KDC) must support Kerberos 5 using the GSS-API. Non-MIT Kerberos implementations must use the GSS-API. For details, see Kerberos authentication.

• ident: Authenticates the client against a username on an Ident server. For details, see Ident authentication.

• ldap: Authenticates a client and their username and password with an LDAP or Active Directory server. For details, see LDAP authentication.

• tls: Authenticates clients that provide a certificate with a Common Name (CN) that specifies a valid database username. Vertica must be configured for mutual mode TLS to use this method. For details, see TLS authentication

• oauth: Authenticates a client with an access token. For details, see OAuth 2.0 authentication.

Local and host authentication

You can define a client authentication method as:

• Local: Local connection to the database.

• Host: Remote connection to the database from different hosts, each with their own IPv4 or IPv6 address and host parameters. For more information see IPv4 and IPv6 for Client Authentication above.

Some authentication methods cannot be designated as local, as listed in this table:

Authentication for chained users and roles

Vertica supports creating chained users and roles, where you can grant ROLE2 privileges to ROLE1. All users in ROLE1 use the same authentication assigned to ROLE2. For example:

=> CREATE USER user1;
=> CREATE ROLE role1;
=> CREATE ROLE role2;
=> CREATE AUTHENTICATION h1 method 'hash' local;
=> GRANT AUTHENTICATION h1 to role2;
=> GRANT role2 to role1;
=> GRANT role1 to user1;

The user and role chain in the example above can be illustrated as follows:

auth1 -> role2 -> role1 -> user1

In this example, since role2 privileges are granted to role1 you only need to grant authentication to role2 to also enable it for role1.

2 - Dbadmin authentication access

The dbadmin user must have access to the database at all times, and its authentication record should:

• Use one of the following authentication methods:

  • TRUST with a LOCAL access method

  • HASH

• Have a high priority (e.g. 10,000) so it supersedes other authentication records like PUBLIC.

LOCAL TRUST

The following example creates an authentication record v_dbadmin_trust with a high priority and grants it to the dbadmin user. The combination of the TRUST method and LOCAL access method allow the dbadmin to authenticate to the database without a password if the connection is local:

=> CREATE AUTHENTICATION v_dbadmin_trust METHOD 'trust' LOCAL;
=> ALTER AUTHENTICATION v_dbadmin_trust PRIORITY 10000;
=> GRANT AUTHENTICATION v_dbadmin_trust TO dbadmin;

HASH

The following example creates an authentication record v_dbadmin_hash and grants it to the dbadmin user. The HASH method indicates that the dbadmin's password is hashed with the database's SECURITY ALGORITHM. The HOST '0.0.0.0/0' access method indicates that the dbadmin can connect remotely from any IPv4 address:

=> CREATE AUTHENTICATION v_dbadmin_hash METHOD 'hash' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION v_dbadmin_hash PRIORITY 10000;
=> GRANT AUTHENTICATION v_dbadmin_hash TO dbadmin;

If you want to authenticate as the dbadmin from a local connection, but want to use the authentication record with the HOST access method, specify the --host option with the hostname or IP address of the database:

$ vsql database_name user --host hostname_or_ip;

3 - Creating authentication records

You can manage client authentication records using vsql commands. To use these statements, you must be connected to the database.

You create authentication records with CREATE AUTHENTICATION, which Vertica stores in the catalog and automatically enables.

Examples

The following examples show how to create authentication records.

Create authentication method localpwd to authenticate users who are trying to log in from a local host using a password:

=> CREATE AUTHENTICATION localpwd METHOD 'hash' LOCAL;

Create authentication method v_ldap that uses LDAP over TLS to authenticate users logging in from the host with the IPv4 address 10.0.0.0/23:

=> CREATE AUTHENTICATION v_ldap METHOD 'ldap' HOST TLS '10.0.0.0/23';

Create authentication method v_kerberos to authenticate users who are trying to connect from any host in the networks 2001:0db8:0001:12xx:

=> CREATE AUTHENTICATION v_kerberos METHOD 'gss' HOST '2001:db8:1::1200/56';

The following authentication record v_oauth authenticates users from any IP address with an OAuth token (rather than a username and password) and uses the following parameters. The identity provider is Keycloak 18.0.0:

• client_id: The confidential client, vertica, registered in Keycloak.

• client_secret: The client secret, generated by Keycloak.

• discovery_url: Also known as the OpenID Provider Configuration Document, this is the endpoint that contains information about the identity provider's configuration and endpoints.

=> CREATE AUTHENTICATION v_oauth METHOD 'oauth' HOST '0.0.0.0/0'
=> ALTER AUTHENTICATION v_oauth SET client_id = 'vertica';
=> ALTER AUTHENTICATION v_oauth SET client_secret = 'client_secret';
=> ALTER AUTHENTICATION v_oauth SET discovery_url = 'https://203.0.113.1:8443/realms/myrealm/.well-known/openid-configuration';
=> ALTER AUTHENTICATION v_oauth SET introspect_url = 'https://203.0.113.1:8443/realms/myrealm/protocol/openid-connect/token/introspect';

Create authentication method RejectNoSSL that rejects users from any IP address that are trying to authenticate without TLS:

=> CREATE AUTHENTICATION RejectNoSSL METHOD 'reject' HOST NO TLS '0.0.0.0/0';  --IPv4
=> CREATE AUTHENTICATION RejectNoSSL METHOD 'reject' HOST NO TLS '::/0';       --IPv6

See also

• Deleting Authentication Records

• Enabling and disabling authentication methods
• Granting and revoking authentication methods
• Modifying authentication records

4 - Modifying authentication records

To modify existing authentication records, you must first be connected to your database. The following examples show how to make changes to your authentication records. For more information see ALTER AUTHENTICATION.

Rename an authentication method

Rename the v_kerberos authentication method to K5, and enable it. All users who have been associated with the v_kerberos authentication method are now associated with the K5 method granted instead.

=> ALTER AUTHENTICATION v_kerberos RENAME TO K5 ENABLE;

Specify a priority for an authentication method

Specify a priority of 10 for K5 authentication:

=> ALTER AUTHENTICATION K5 PRIORITY 10;

For more information see Authentication record priority.

Change a parameter

Set the system_users parameter for ident1 authentication to root:

=> CREATE AUTHENTICATION ident1 METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION ident1 SET system_users='root';

Change the IP address and specify the parameters for an LDAP authentication method named Ldap1.

In this example, you specify the bind parameters for the LDAP server. Vertica connects to the LDAP server, which authenticates the Vertica client. If the authentication succeeds, Vertica authenticates any users who have been granted the Ldap1 authentication method on the designated LDAP server:

=> CREATE AUTHENTICATION Ldap1 METHOD 'ldap' HOST '172.16.65.196';
=> ALTER AUTHENTICATION Ldap1 SET host='ldap://172.16.65.177',
   binddn_prefix='cn=', binddn_suffix=',dc=qa_domain,dc=com';

Change the IP address, and specify the parameters for an LDAP authentication method named Ldap1. Assume that Vertica does not have enough information to create the distinguished name (DN) for a user attempting to authenticate. Therefore, in this case, you must specify to use LDAP search and bind:

=> CREATE AUTHENTICATION LDAP1 METHOD 'ldap' HOST '172.16.65.196';
=> ALTER AUTHENTICATION Ldap1 SET host='ldap://172.16.65.177',
basedn='dc=qa_domain,dc=com',binddn='cn=Manager,dc=qa_domain,
dc=com',search_attribute='cn',bind_password='secret';

Change the associated method

Change the localpwd authentication from trust to hash:

=> CREATE AUTHENTICATION localpwd METHOD 'trust' LOCAL;
=> ALTER AUTHENTICATION localpwd METHOD 'hash';

ALTER AUTHENTICATION validates the parameters you enter. If there are errors, it disables the authentication method that you are trying to modify.

Using the administration tools

The advantages of using the Administration Tools are:

• You do not have to connect to the database

• The editor verifies that records are correctly formed

• The editor maintains records so they are available to you to edit later

For information about using the Administration Tools to create and edit authentication records, see Creating authentication records.

Deleting authentication records

To delete client authentication record, use DROP AUTHENTICATION. To use this approach, you have to be connected to your database.

To delete an authentication record for md5_auth use the following command:

=> DROP AUTHENTICATION md5_auth;

To delete an authentication record for a method that has been granted to a user, use the CASCADE keyword:

=> CREATE AUTHENTICATION localpwd METHOD 'password' LOCAL;
=> GRANT AUTHENTICATION localpwd TO jsmith;
=> DROP AUTHENTICATION localpwd CASCADE;

See also

• Creating authentication records
• Granting and revoking authentication methods

5 - Authentication record priority

Each authentication record has a priority. If a user is granted more than one authentication record, Vertica attempts to authenticate the user with the authentication record with the highest priority and rejects the user if authentication fails.

Determining authentication priority

The following factors contribute to an authentication record's priority, as reflected in the CLIENT_AUTH system table:

=> SELECT auth_name, auth_method, auth_priority, method_priority, address_priority FROM client_auth;
   auth_name   | auth_method | auth_priority | method_priority | address_priority
---------------+-------------+---------------+-----------------+------------------
 ldap_auth     | LDAP        |             5 |               5 |               96
 hash_auth     | HASH        |             5 |               2 |              126
 tls_auth      | TLS         |             0 |               5 |               96
 oauth_auth    | OAUTH       |             0 |               5 |               96
 gss_auth      | GSS         |             0 |               5 |               96
 trust_auth    | TRUST       |             0 |               0 |               96
 reject_auth   | REJECT      |             0 |              10 |               96
(7 rows)

Priorities are divided into tiers and listed in order of importance; in the event of a tie at one priority tier, Vertica checks the next priority tier. For example, if a user had both ldap and hash authentication records with an auth_priority of 5, Vertica would attempt to use the ldap authentication record because it has a greater method_priority value:

1. auth_priority: The priority explicitly set with ALTER AUTHENTICATION (default: 0).

2. method_priority: The priority specific to the authentication method. These priorities are as follows:

   • trust: 0

   • hash: 2

   • ldap: 5

   • tls: 5

   • oauth: 5

   • gss: 5

   • reject: 10

3. address_priority: The priority for IP address specified in HOST [ TLS | NO TLS ] 'host-ip-address'. This priority is determined by the size of the netmask of the address; fewer zeros indicate greater specificity, and therefore higher priority. LOCAL has the lowest priority: 0.

Setting authentication priority

To set authentication priority:

=> ALTER AUTHENTICATION authentication_name PRIORITY value;

See also

• CLIENT_AUTH
• CLIENT_AUTH_PARAMS
• Client authentication

6 - Viewing information about client authentication records

For information about client authentication records that you have configured for your database, query the following system tables in the V_CATALOG schema:

• CLIENT_AUTH
• CLIENT_AUTH_PARAMS
• PASSWORD_AUDITOR
• USER_CLIENT_AUTH

To determine the details behind the client authentication used for a particular user session, query the following tables in the V_MONITOR schema:

• SESSIONS

• USER_SESSIONS

7 - Enabling and disabling authentication methods

When you create an authentication method, Vertica stores it in the catalog and enables it automatically. To enable or disable an authentication method, use the ALTER AUTHENTICATION statement. To use this approach, you must be connected to your database.

If an authentication method has not been enabled, Vertica cannot use it to authenticate users and clients trying to connect to the database.

To enable an authentication method:

ALTER AUTHENTICATION v_kerberos ENABLE;

To disable this authentication method:

ALTER AUTHENTICATION v_kerberos DISABLE;

See also

• Creating authentication records

• Deleting Authentication Records

• Granting and revoking authentication methods
• Modifying authentication records

8 - Granting and revoking authentication methods

Before Vertica can validate a user or client through an authentication method, you must first associate that authentication method with the user or role that requires it, with GRANT (authentication). When that user or role no longer needs to connect to Vertica using that method, you can disassociate that authentication from that user with REVOKE AUTHENTICATION.

Grant authentication methods

You can grant an authentication method to a specific user or role. You can also specify the default authentication method by granting an authentication method to PUBLIC, as in the following examples.

• Associate v_ldap authentication with user jsmith:

  => GRANT AUTHENTICATION v_ldap TO jsmith;
  

• Associate v_gss authentication to the role DBprogrammer:

  
  => CREATE ROLE DBprogrammer;
  => GRANT AUTHENTICATION v_gss TO DBprogrammer;
  

• Associate client authentication method v_localpwd with role PUBLIC, which is assigned by default to all users:

  => GRANT AUTHENTICATION v_localpwd TO PUBLIC;
  

Revoke authentication methods

If you no longer want to authenticate a user or client with a given authentication method, use the REVOKE (authentication) statement as in the following examples.

• Revoke v_ldap authentication from user jsmith:

  => REVOKE AUTHENTICATION v_ldap FROM jsmith;
  

• Revoke v_gss authentication from the role DBprogrammer:

  => REVOKE AUTHENTICATION v_gss FROM DBprogrammer;
  

• Revoke localpwd as the default client authentication method:

  => REVOKE AUTHENTICATION localpwd FROM PUBLIC;
  

9 - Hash authentication

This section covers making passwords, hashing them, and configuring hash authentication.

9.1 - Hash authentication parameters

Two parameters control which hashing algorithm is used for passwords:

• 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)

• SHA512

• MD5

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. Notice that FIPS mode forces the effective security algorithm to be SHA-512.

9.2 - Configuring hash authentication

Follow these steps to configure hash authentication:

1. Create an authentication method that is based on hash encryption. When you create an authentication method, it is automatically enabled for use.

   The following example shows how to create an authentication method v_hash for users logging in from the IP address 10.0.0.0/0.

   => CREATE AUTHENTICATION v_hash METHOD 'hash' HOST '10.0.0.0/0';
   

   If users are trying to connect from an IPv6 address, the statement might look like this example:

   => CREATE AUTHENTICATION v_hash METHOD 'hash' HOST '2001:db8:ab::123/128';
   

2. Specify the following parameters to set the effective security algorithms:

   • At the system level, set the SecurityAlgorithm configuration parameter. This setting applies to all users with NONE (default) for their user-level parameter, SECURITY_ALGORITHM:

     => ALTER DATABASE DEFAULT SET SecurityAlgorithm = 'SHA512';
     

     If you want users to immediately inherit changes to the system-level parameter, set their passwords to expire the next time they log in. Once they reset their passwords, Vertica hashes them using the current system-level security algorithm.

   • To override the system-level parameter for a particular user, use ALTER USER to set the SECURITY_ALGORITHM parameter to a non-NONE value:

     => ALTER USER username SECURITY_ALGORITHM 'SHA512' IDENTIFIED BY 'newpassword';
     

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

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

For more information on these parameters, see Hash authentication parameters.

9.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;

9.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

9.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

9.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.

9.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.

10 - Ident authentication

The Ident protocol, defined in RFC 1413, authenticates a database user with a system user name.To see if that system user can log in without specifying a password, you configure Vertica client authentication to query an Ident server. With this feature, the DBADMIN user can run automated scripts to execute tasks on the Vertica server.

Following the instructions in these topics to install, set up, and configure Ident authentication for your database:

• Installing and setting up an ident server
• Configuring ident authentication for database users

Examples

The following examples show several ways to configure Ident authentication.

Allow system_user1 to connect to the database as Vertica vuser1:

=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION v_ident SET system_users='system_user1';
=> GRANT AUTHENTICATION v_ident to vuser1;
=> ALTER AUTHENTICATION v_ident ENABLE;

Allow system_user1, system_user2, and system_user3 to connect to the database as vuser1. Use colons (:) to separate the user names:

=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION v_ident SET system_users='system_user1:system_user2:system_user3';
=> GRANT AUTHENTICATION v_ident TO vuser1;
=> ALTER AUTHENTICATION v_ident ENABLE;

Associate the authentication with Public using a GRANT AUTHENTICATION statement. The users, system_user1, system_user2, and system_user3 can now connect to the database as any database user:

=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION v_ident SET system_users='system_user1:system_user2:system_user3';
=> GRANT AUTHENTICATION v_ident to Public;
=> ALTER AUTHENTICATION v_ident ENABLE;

Set the system_users parameter to * to allow any system user to connect to the database as vuser1:

=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION v_ident SET system_users='*';
=> GRANT AUTHENTICATION v_ident TO vuser1;
=> ALTER AUTHENTICATION v_ident ENABLE;

Using a GRANT statement, associate the v_ident authentication with Public to allow system_user1 to log into the database as any database user:

=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION v_ident SET system_users='system_user1';
=> GRANT AUTHENTICATION v_ident to Public;
=> ALTER AUTHENTICATION v_ident ENABLE;

10.1 - Installing and setting up an ident server

To use Ident authentication, you must install one or more packages, depending on your operating system, and enable the Ident server on your Vertica server. oidentd is an Ident daemon that is compatible with Vertica and compliant with RFC 1413.

To install and configure Ident authentication for use with your Vertica database, follow the appropriate steps for your operating system:

Red hat 6.x/CentOS 6.x

Install oidentd on Red Hat 6.x or CentOS 6.x by running this command:

$ yum install oidentd

Depending on your configuration, you might receive the following error message:

No package oidentd available.

In this case, you must install the Red Hat/CentOS Extras Repository. Download and install the Extras Repository from the following location: https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm

Red hat 7.x/CentOS 7.x

Install an Ident server on Red Hat 7.x or CentOS 7.x by installing the authd and xinetd packages:

$ yum install authd
$ yum install xinetd

Ubuntu/debian

Install oidentd on Ubuntu or Debian by running this command:

$ sudo apt-get install oidentd

SUSE Linux enterprise server

Install the pidentd and xinetd RPMs from the following locations:

• https://www.suse.com/LinuxPackages/packageRouter.jsp?product=server&version=11&service_pack=&architecture=i386&package_name=pidentd

• https://www.suse.com/LinuxPackages/packageRouter.jsp?product=server&version=11&service_pack=&architecture=i386&package_name=xinetd

Post-installation steps for red hat 6.x/CentOS 6.x and ubuntu/debian

After you install oidentd on your Red Hat 6.x/CentOS 6.x or Ubuntu/Debian system, continue with the following steps:

1. Verify that the Ident server accepts IPv6 connections to prevent authentication failure. To do so, you must enable this capability. In the script /etc/init.d/oidentd, change the line from:

   exec="/usr/sbin/oidentd"
   

   to

   exec="/usr/sbin/oidentd -a ::"
   

   Then, at the Linux prompt, start oidentd with -a ::.

2. Restart the server with the following command:

   $ /etc/init.d/oidentd restart
   

Post-installation steps for red hat 7.x/CentOS 7.x and SUSE Linux enterprise server

After you install the required packages on your Red Hat 7.x/CentOS 7.x or SUSE Linux Enterprise Server system, continue with the following steps:

1. Enable the auth service in the configuration file located at the following location: /etc/xinet.d/auth.

   Enter no for the disable option, as this sample configuration file shows.

   service auth
   {
           disable = no
           socket_type = stream
           wait = no
           user = ident
           cps = 4096 10
           instances = UNLIMITED
           server = /usr/sbin/in.authd
           server_args = -t60 --xerror --os
   }
   

2. Restart the xinetd service with the following command:

   $ service xinetd restart
   

10.2 - Configuring ident authentication for database users

To configure Ident authentication, take the following steps:

1. Create an authentication method that uses Ident.

   The Ident server must be installed on the same computer as your database, so specify the keyword LOCAL. Vertica requires that the Ident server and database always be on the same computer as the database.

   => CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
   

2. Set the Ident authentication parameters, specifying the system users who should be allowed to connect to your database.

   => ALTER AUTHENTICATION v_ident SET system_users='user1:user2:user3';
   

3. Associate the authentication method with the Vertica user. Use a GRANT statement that allows the system user user1 to log in using Ident authentication:

   => GRANT AUTHENTICATION v_ident TO user1;
   

11 - Kerberos authentication

Kerberos authentication uses the following components to perform user authentication.

Client package

The Kerberos 5 client package communicates with the KDC server. This package is not included as part of the Vertica Analytics Platform installation. Kerberos software is built into Microsoft Windows. If you are using another operating system, you must obtain and install the client package.

If you do not already have the Kerberos 5 client package on your system, download it from the MIT Kerberos Distribution page. Install the package on each Vertica server and client used in Kerberos authentication, except the KDC itself.

Refer to the Kerberos documentation for installation instructions.

Service principals

A service principal consists of a host name, a service name, and a realm to which a set of credentials gets assigned (service/hostname@REALM). These credentials connect to the service, which is a host that you connect to over your network and authenticate using the KDC.

See Specify KDC information and configure realms to create the realm name. The host name must match the value supplied by the operating system. Typically this is the fully qualified host name. If the host name part of your principal does not match the value supplied by the operating system, Kerberos authentication fails.

Some systems use a hosts file (/etc/hosts or /etc/hostnames) to define host names. A hosts file can define more than one name for a host. The operating system supplies the first entry, so use that in your principal. For example, if your hosts file contains:

192.168.1.101 v_vmart_node0001.example.com v_vmart_node0001

then use v_vmart_node0001.example.com as the hostname value.

Configure the following as Kerberos principals:

• Each client (users or applications that connects to Vertica)

• The Vertica server

See the following topics for more information:

• Configure Vertica for Kerberos authentication

• Configure Clients for Kerberos Authentication

Keytab files

Principals are stored in encrypted keytab files. The keytab file contains the credentials for the Vertica principal. The keytab allows the Vertica server to authenticate itself to the KDC. You need the keytab so that Vertica Analytic Database does not have to prompt for a password.

Create one service principal for each node in your cluster. You can then either create individual keytab files (one for each node containing only that node's principal) or create one keytab file containing all the principals.

• Create one keytab file with all principals to simplify setup: all nodes have the same file, making initial setup easier. If you add nodes later you either update (and redistribute) the global keytab file or make separate keytabs for the new nodes. If a principal is compromised it is compromised on all nodes where it is present in a keytab file.

• Create separate keytab files on each node to simplify maintenance. Initial setup is more involved as you must create a different file on each node, but no principals are shared across nodes. If you add nodes later you create keytabs on the new nodes. Each node's ke ytab contains only one principal, the one to use for that node.

Ticket-granting ticket

The Ticket-Granting Ticket (TGT) retrieves service tickets that authenticates users to servers in the domain. Future login requests use the cached HTTP Service Ticket for authentication, unless it has expired as set in the ticket_lifetime parameter in krb5.conf.

Multi-realm support

Vertica provides multi-realm support for Kerberos authentication using the SET param=value parameter in ALTER AUTHENTICATION with REALM as the parameter:

=> ALTER AUTHENTICATION krb_auth_users set REALM='USERS.COM';
=> ALTER AUTHENTICATION krb_auth_realmad set REALM='REALM_AD.COM';

This allows you to assign a different realm so that users from another realm can authenticate to Vertica.

Mutli-realm support applies to GSS authentication types only. You can have one realm per authentication method. If you have multiple authentication methods, each can have its own realm:

=> SELECT * FROM client_auth;
auth_oid | auth_name | is_auth_enabled | auth_host_type | auth_host_address | auth_method | auth_parameters     | auth_priority
---------+-----------+-----------------+----------------+-------------------+-------------+-----------------+-----------------
45035996 | krb001    |   True          |   HOST         |  0.0.0.0/0        |  GSS        | realm=USERS.COM     |  0
45035997 | user_auth |   True          |   LOCAL        |                   |  TRUST      |                     |  1000
45035737 | krb002    |   True          |   HOST         |  0.0.0.0/0        |  GSS        | realm=REALM_AD.COM  |  1

11.1 - Configure Vertica for Kerberos authentication

Kerberos provides a strong cryptographic authentication against the devices which lets the client & servers to communicate in a more secured manner. It addresses network security problems.

Your system must have one or more Kerberos Key Distribution Centers (KDC) installed and configured. The KDCs must be accessible from every node in your Vertica Analytic Database cluster.

The KDC must support Kerberos 5 using GSS-API. For details, see the MIT Kerberos Distribution Page.

In this section

11.1.1 - Create the Vertica principals and keytabs on Linux KDC

Vertica uses service principals for system-level operations. These principals identify the Vertica service and are used as follows:

• Kerberized Vertica clients request access to this service when they authenticate to the database.

• System processes like the Tuple Mover use this identity when they authenticate to external services such as Hadoop.

Create principals and keys as follows:

1. Start the Kerberos 5 database administration utility (kadmin or kadmin.local) to create Vertica principals on a Linux KDC.

   • Use kadmin if you are accessing the KDC on a remote server. If you have access to the Kerberos administrator password, you can use kadmin on any machine where the Kerberos 5 client package is installed. When you start kadmin, the utility prompts you for the Kerberos administrator's password. You might need root privileges on the client to run kadmin.

   • Use kadmin.local if:

     • The KDC is on the machine that you are logging in to.

     • You have root privileges on that server.

   kadmin.local does not require the administrators login credentials.

   For more information about the kadmin and kadmin.local commands, see the kadmin documentation.

2. Create one service principal for Vertica on each node. The host name must match the value supplied by the operating system. The following example creates the service principal vertica for the node named v_vmart_node0001.example.com:

   $ sudo /usr/kerberos/sbin/kadmin.local
   kadmin.local add_principal vertica/v_vmart_node0001.example.com
   

   Repeat the ktadd command once per principal. You can create separate keytabs for each principal user or add them all to a single keytab file (such as krb5.keytab). If you are using a single file, see the documentation for the -glob option in the MIT Kerberos documentation.

   You must have a user principal for each Vertica Analytic Database user that uses Kerberos Authentication. For example:

   $ sudo /usr/kerberos/sbin/kadmin.local
   kadmin.local add_principal [options] VerticaUser1
   

3. Copy each keytab file to the /etc folder on the corresponding cluster node. Use the same path and file name on all nodes.

4. On each node, make the keytab file readable by the file owner who is running the database process (typically, the Linux dbadmin user). For example, you can change ownership of the files to dbadmin as follows:

   $ sudo chown dbadmin *.keytab
   

   Important

   In a production environment, you must control who can access the keytab file to prevent unauthorized users from delegating your server. For more information about delegation (also known as impersonation), see Technet.Microsoft.com.

   After you create a keytab file, you can use the klist command to view keys stored in the file:

   $ sudo /usr/kerberos/bin/klist -ke -t
   Keytab name: FILE:/etc/krb5.keytab
   KVNO    Timestamp        Principal
   ---- ------------------- --------------------------------------------------------------------------
   4     08/15/2017 7:35:41 vertica/v_vmart_node0001.example.com@EXAMPLE.COM (aes256-cts-hmac-sha1-96)
   4     08/15/2017 7:35:41 vertica/v_vmart_node0001.example.com@EXAMPLE.COM (aes128-cts-hmac-sha1-96)
   

5. On Vertica run the following to ensure the Kerberos parameters are set correctly:

   => select parameter_name, current_value from configuration_parameters where parameter_name like 'Ker%';
   parameter_name         |                      current_value
   -----------------------+---------------------------------------------------------------------
   KerberosHostname       | v_vmart_node0001.example.com
   KerberosKeytabFile     | /etc/krb5.keytab
   KerberosRealm          | EXAMPLE.COM
   KerberosTicketDuration | 0
   KerberosServiceName    | vertica
   (5 rows)
   

6. Ensure that all clients use the gss authentication method.

   From Vertica:

   => CREATE USER bob;
   CREATE USER
   
   => CREATE AUTHENTICATION v_kerberos method 'gss' host '0.0.0.0/0';
   CREATE AUTHENTICATION
   
   => ALTER AUTHENTICATION v_kerberos enable;
   ALTER AUTHENTICATION
   
   => GRANT AUTHENTICATION v_kerberos to bob;
   GRANT AUTHENTICATION
   

   From the operating system command line:

   $ kinit bob
   
   $ vsql -U bob -k vertica -K v_vmart_node0001.example.com -h v_vmart_node0001 -c "select client_authentication_name,
   authentication_method from sessions;"
    client_authentication_name | authentication_method--
   ----------------------------+-----------------------
    v_kerberos                 |    GSS-Kerberos
   
   (1 row)
   

7. On Vertica, run KERBEROS_CONFIG_CHECK to verify the Kerberos configuration. KERBEROS_CONFIG_CHECK verifies the following:

   • The existence of the kinit and kb5.conf files.

   • Whether the keytab file exists and is set

   • The Kerberos configuration parameters set in the database:

     • KerberosServiceName

     • KerberosHostname

     • KerberosRealm

     • Vertica Principal

   • That Kerberos can read the Vertica keys

   • That Kerberos can get the tickets for the Vertica principal

   • That Vertica can initialize the keys with kinit

11.1.2 - Specify KDC information and configure realms

Each client and Vertica Analytic Database server in the Kerberos realm must have a valid, identically configured Kerberos configuration (krb5.conf) file. Without this file, the client does not know how to reach the KDC.

If you use Microsoft Active Directory, you do not need to perform this step. Refer to the Kerberos documentation for your platform for more information about the Kerberos configuration file on Active Directory.

At a minimum, you must configure the following sections in the krb5.conf file.

• [libdefaults]—Settings used by the Kerberos 5 library
• [realms]—Realm-specific contact information and settings
• [domain_realm]—Maps server hostnames to Kerberos realms

See the Kerberos documentation for information about other sections in this configuration file.

You must update the /etc/krb5.conf file to reflect your site's Kerberos configuration. The simplest way to enforce consistency among all clients and servers in the Kerberos realm is to copy the /etc/krb5.conf file from the KDC. Then, place this file in the /etc directory on each Vertica cluster node.

11.1.3 - Inform Vertica about the Kerberos principal

Follow these steps to inform Vertica about the principal name and keytab location.

For information about the parameters that you are setting in this procedure, see Kerberos configuration parameters.

1. Log in to the database as an administrator (typically dbadmin).

2. Set the KerberosKeyTabFile configuration parameter to point to the location of the keytab file:

   => ALTER DATABASE DEFAULT SET PARAMETER KerberosKeytabFile = '/etc/krb5.keytab';
   

   The keytab file must be in the same location (/etc/krb5.keytab in this example) on all nodes.

3. Set the service name for the Vertica principal; for example, vertica:

   => ALTER DATABASE DEFAULT SET PARAMETER KerberosServiceName = 'vertica';
   

4. Provide the realm portion of the principal, for example, EXAMPLE.COM:

   => ALTER DATABASE DEFAULT SET PARAMETER KerberosRealm = 'EXAMPLE.COM'
   

11.1.4 - Configure the authentication method for all clients

To make sure that all clients use the gss authentication method, run the following statements:

=> CREATE AUTHENTICATION <method_name> METHOD 'gss' HOST '0.0.0.0/0';
=> GRANT AUTHENTICATION <method_name> TO Public;

For more information, see Implementing Client Authentication.

11.1.5 - Creating the principals and keytab on active directory

Active Directory stores information about members of the Windows domain, including users and hosts.

Vertica uses the Kerberos protocol to access this information in order to authenticate Windows users to the Vertica database. The Kerberos protocol uses principals to identify users and keytab files to store their cryptographic information. You need to install the keytab files into Vertica to enable the Vertica database to cryptographically authenticate windows users.

This procedure describes:

• Creating a Vertica service principal.

• Exporting the keytab files for these principals

• Installing the keytab files in the Vertica database. This allows Vertica to authenticate Windows users and grant them access to the Vertica database.

1. Create a Windows account (principal) for the Vertica service and one Vertica host for each node/host in the cluster. This procedure creates Windows accounts for host verticanode01 and service vertica running on this node.
   

   When you create these accounts, select the following:
   

   • User cannot change password

   • Password never expires

   Note

   You can deselect Password never expires. However, if you change these user passwords, you must recreate the keytab files and reinstall them into Vertica. This includes repeating the entire procedure.

2. If you are using external tables on HDFS that are secured by Kerberos authentication, you must enable Delegation. To do so, access the Active Directory Users and Computers dialog, right-click the Windows account (principal) for the Vertica service, and select Delegation. Trust this user for delegation to any service.
   

3. Run the following command to create the keytab for the host verticanode01.dc.com node/host:

   $ ktpass -out ./host.verticanode01.dc.com.keytab -princ host/verticanode01.dc.com@DC.COM -mapuser verticanode01
    -mapop set -pass secret  -ptype KRB5_NT_SRV_HST
   

4. Run the following command to create the keytab for the vertica service:

   $ ktpass -out ./vertica.verticanode01dc.com.keytab -princ vertica/verticanode01.dc.com@DC.COM -mapuser vertica
    -mapop set -pass secret  -ptype KRB5_NT_PRINCIPAL
   

   For more information about keytab files, see Technet.Microsoft.com.

5. Run the following commands to verify that the service principal name is mapped correctly. You must run these commands for each node in your cluster:

   $ setspn -L vertica
       Registered ServicePrincipalNamefor CN=vertica,CN=Users,DC=dc,DC=com
         vertica/verticanode01.dc.com
   
   $ setspn -L verticanode01
       Registered ServicePrincipalNamefor CN=verticanode01,CN=Users,DC=dc,DC=com
         host/verticanode01.dc.com
   

6. Copy the keytabs you created above, vertica.verticanode01.dc.com.keytab and host.verticanode01.dc.com.keytab, to the Linux host verticanode01.dc.com.

7. Combine the keytab files into a single keytab:

   
   [release@vertica krbTest]$ /usr/kerberos/sbin/ktutil
   ktutil:  rkt host.verticanode01.dc.com.keytab
   ktutil:  rkt vertica.verticanode01.dc.com.keytab
   ktutil:  list
   slot KVNO Principal
   ---- ---- ---------------------------------------------------------------------
     1    3  host/verticanode01.dc.com@DC.COM
     2   16  vertica/verticanode01.dc.com@DC.COM
   ktutil:  wkt verticanode01.dc.com.keytab
   ktutil:  exit
   

   This creates a single keytab file that contains the server principal for authentication.

8. Copy the new keytab file to the catalog directory. For example:

   $ cp verticanode01.dc.com.keytab /home/dbadmin/VMart/v_vmart_nodennnn_catalog
   

9. Test the keytab file's ability to retrieve a ticket to ensure it works from the Vertica node:

   
   $ kinit vertica/verticanode01.dc.com -k -t verticanode01.dc.com.keytab
   $ klist
   
   Ticket cache: KFILE:/tmp/krb_ccache_1003
   Default principal: vertica/verticanode01.dc.com@DC.COM
   
   Valid starting Expires Service principal
   04/08/2017 13:35:25 04/08/2017 23:35:25 krbtgt/DC.COM@DC.COM
                   renew until 04/15/2017 14:35:25
   

   When the ticket expires or not automatically retrieved you need to manually run the kinit command. See Get the Kerberos ticket and authenticate Vertica.

10. Set the right permissions and ownership on the keytab files:

    $ chmod 600 verticanode01.dc.com.keytab
    $ chown dbadmin:verticadba verticanode01.dc.com.keytab
    

11. Set the following Kerberos configuration parameters using ALTER DATABASE to inform Vertica about the Kerberos principal:
    

    KerberosKeytabFile=<CATALOGDIR>/verticanode01.dc.com.keytab
    KerberosRealm=DC.COM
    KerberosServiceName=vertica
    KerberosTicketDuration = 0
    KerberosHostname=verticanode01.dc.com
    

12. Restart the Vertica server.

13. Test your Kerberos setup as follows to ensure that all clients use the gss authentication method.
    
    From Vertica:

    => CREATE USER windowsuser1;
    CREATE USER
    
    => CREATE AUTHENTICATION v_kerberos method 'gss' host '0.0.0.0/0';
    CREATE AUTHENTICATION
    
    => ALTER AUTHENTICATION v_kerberos enable;
    ALTER AUTHENTICATION
    
    => GRANT AUTHENTICATION v_kerberos to windowsuser1;
    GRANT AUTHENTICATION
    

    From the operating system command line:

    $ kinit windowsuser1
    
    $ vsql -U windowsuser1 -k vertica -K verticanode01.dc.com -h verticanode01.dc.com -c "select client_authentication_name,
    authentication_method from sessions;"
     client_authentication_name | authentication_method--
    ----------------------------+-----------------------
     v_kerberos                 |    GSS-Kerberos
    
    (1 row)
    

14. Run KERBEROS_CONFIG_CHECK to verify the Kerberos configuration. KERBEROS_CONFIG_CHECK verifies the following:

    • The existence of the kinit and kb5.conf files.

    • Whether the keytab file exists and is set

    • The Kerberos configuration parameters set in the database:

      • KerberosServiceName

      • KerberosHostname

      • KerberosRealm

      • Vertica Principal

    • That Kerberos can read the Vertica keys

    • That Kerberos can get the tickets for the Vertica principal

    • That Vertica can initialize the keys with kinit
      

11.1.6 - Get the Kerberos ticket and authenticate Vertica

If your organization uses Kerberos as part of the login process, Kerberos tickets are automatically retrieved upon login. Otherwise, you need to run kinit to retrieve the Kerberos ticket.

The following example shows how to retrieve the ticket and authenticate Vertica Analytic Database with the KDC using the kinit command. EXAMPLE.COM is the realm name. You must use the realm name with your username to retrieve a Kerberos ticket. See Specify KDC information and configure realms.

$ kinit
Password for principal_user@EXAMPLE.COM: kpasswd

You are prompted for the password of the principal user name created when you created the principals and keytabs (see Create the Vertica principals and keytabs on Linux KDC).

The Kerberos ticket gets cached for a pre-determined length of time. See Ticket Management in the Kerberos documentation for more information on setting expiration parameters.

Upon expiration, you need to run the kinit command again to retrieve another Kerberos ticket.

11.2 - Configure clients for Kerberos authentication

Each supported platform has a different security framework. Thus, the steps required to configure and authenticate against Kerberos differ among clients.

On the server side, you construct the Vertica Kerberos service name principal using this format:

Kerberos_Service_Name/Kerberos_Host_Name@Kerberos_Realm

For each client, the GSS libraries require the following format for the Vertica service principal:

Kerberos_Service_Name@Kerberos_Host_Name

You can omit the realm portion of the principal because GSS libraries use the realm name of the configured default (Kerberos_Realm) realm.

For information about client connection strings, see the following topics:

• Data source name (DSN) connection properties
• JDBC connection properties
• ADO.NET connection properties

• (vsql) Command-line options

In this section

• Configure ODBC and vsql clients on non-windows platforms

• Configure ODBC and vsql Clients on Windows and ADO.NET

• Configure JDBC Clients on all Platforms

11.2.1 - Configure ODBC and vsql clients on non-windows platforms

To configure an ODBC or vsql client on Linux or MAC OSX, you must first install the Kerberos 5 client package. See Configuring Kerberos authentication.

After you install the Kerberos 5 client package, you must provide clients with a valid Kerberos configuration file (krb5.conf). To communicate with the KDC, each client participating in Kerberos authentication must have a valid, identically configured krb5.conf file. The default location for the Kerberos configuration file is /etc/krb5.conf.

The Kerberos configuration (krb5.conf) file contains Kerberos-specific information, including:

• How to reach the KDC

• Default realm name

• Domain

• Path to log files

• DNS lookup

• Encryption types to use

• Ticket lifetime

The default location for the Kerberos configuration file is /etc/krb5.conf.

When configured properly, the client can authenticate with Kerberos and retrieve a ticket through the kinit utility (see Acquire an ODBC Authentication Request and Connection below). Likewise, the server can then use ktutil to store its credentials in a keytab file

Authenticating ODBC and vsql clients requests and connections on non-windows platforms

ODBC and vsql use the client's ticket established by kinit to perform Kerberos authentication. These clients rely on the security library's default mechanisms to find the ticket file and the and Kerberos configuration file.

To authenticate against Kerberos, call the kinit utility to obtain a ticket from the Kerberos KDC server. The following two examples show how to send the ticket request using ODBC and vsql clients.

Acquire an ODBC authentication request and connection

1. On an ODBC client, acquire a ticket for the kuser user by calling the kinit utility.

   $ kinit kuser@EXAMPLE.COM
   Password for kuser@EXAMPLE.COM:
   

2. Connect to Vertica, and provide the principals in the connection string:

   char outStr[100];
   SQLLEN len;
   SQLDriverConnect(handle, NULL, "Database=VMart;User=kuser;
   Server=myserver.example.com;Port=5433;KerberosHostname=vcluster.example.com",
   SQL_NTS, outStr, &len);
   

Acquire a vsql authentication request connection

If the vsql client is on the same machine you are connecting to, vsql connects through a UNIX domain socket. This connection bypasses Kerberos authentication. When you authenticate with Kerberos, especially if the client authentication method is configured as 'local', you must include the -h hostname option. See Command Line Options.

1. On the vsql client, call the kinit utility:

   $ kinit kuser@EXAMPLE.COM
   Password for kuser@EXAMPLE.COM:
   

2. Connect to Vertica, and provide the host and user principals in the connection string:

   $ ./vsql -K vcluster.example.com -h myserver.example.com -U kuser
   Welcome to vsql, the Vertica Analytic Database
   interactive terminal.
   
   Type:  \h or \? for help with vsql commands
   \g or terminate with semicolon to execute query
   \q to quit
   

In the future, when you log in to vsql as kuser, vsql uses your cached ticket without prompting you for a password.

Verify the authentication method

You can verify the authentication method by querying the SESSIONS system table:

=> SELECT authentication_method FROM sessions;
 authentication_method
-----------------------
GSS-Kerberos
(1 row)

See also

• Data source name (DSN) connection properties

• (vsql) Command-line options

11.2.2 - Configure ADO.NET, ODBC, and vsql clients on Windows

The Vertica client drivers support the Windows SSPI library for Kerberos authentication. Windows Kerberos configuration is stored in the registry.

You can choose between two different setup scenarios for Kerberos authentication on ODBC and vsql clients on Windows and ADO.NET:

• Windows KDC on Active Directory with Windows Built-in Kerberos Client and

• Linux KDC with Windows Built-in Kerberos Client and

Windows KDC on active directory with windows built-in Kerberos client and Vertica

Kerberos authentication on Windows is commonly used with Active Directory, Microsoft's enterprise directory service/Kerberos implementation.Typically your organization's network or IT administrator performs the setup.

Windows clients have Kerberos authentication built into the authentication process. You do not need any additional software.

Your login credentials authenticate you to the Kerberos server (KDC) when you:

• Log in to Windows from a client machine

• Use a Windows instance that has been configured to use Kerberos through Active Directory

To use Kerberos authentication on Windows clients, log in as REALM\user.

Linux KDC with windows built-in Kerberos client and Vertica

A simple, but less common scenario is to configure Windows to authenticate against a non-Windows KDC. In this implementation, you use the ksetup utility to point the Windows operating system native Kerberos capabilities at a non-Active Directory KDC. By logging in to Windows, you obtain a ticket-granting ticket, similar to the Active Directory implementation. However, in this case, Windows is internally communicating with a Linux KDC. See the Microsoft Windows Server Ksetup page for more information.

When a database/windows user logs into their Windows machine (or after performing a kinit on Windows) the Kerberos ticket MUST have ok_as_delegate and forwardable flag set to be able to access webhdfs based external tables as follows:

$ CMD \> klist
#2>     Client: release @ VERTQA.LOCAL
Server: vertica/vqatest108.verticacorp.com @ VERTQA.LOCAL
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
Ticket Flags 0x40a50000 forwardable renewable pre_authent ok_as_delegate name_canonicalize
Start Time: 9/27/2017 13:24:43 (local)
End Time:   9/27/2017 20:34:45 (local)
Renew Time: 10/3/2017 15:04:45 (local)
Session Key Type: RSADSI RC4-HMAC(NT)
Cache Flags: 0
Kdc Called: ADKDC01

Configure windows clients for Kerberos authentication

Depending on which implementation you want to configure, refer to one of the following pages on the Microsoft Server website:

• To set up Windows clients with Active Directory, refer to Step-by-Step Guide to Kerberos 5 (krb5 1.0) Interoperability.

• To set up Windows clients with the ksetup utility, refer to the Ksetup page.

Authenticate and connect clients

The KDC can authenticate both an ADO.NET and a vsql client.

Verify an ADO.NET authentication request and connection

This example shows how to use the IntegratedSecurity=true, setting to specify that the ADO.NET driver authenticate the calling user's Windows credentials:

VerticaConnection conn = new
VerticaConnection("Database=VMart;Server=host.example.com;
Port=5433;IntegratedSecurity=true;
KerberosServiceName=vertica;KerberosHostname=vcluster.example.com");
conn.open();

Verify a vsql authentication request and connection

1. Log in to your Windows client, for example, as EXAMPLE\kuser.

2. Run the vsql client and supply the connection string to Vertica:

   C:\Users\kuser\Desktop>vsql.exe -h host.example.com -K vcluster -U kuser
   
   Welcome to vsql, the Vertica Analytic Database interactive terminal.
   Type:  \h or \? for help with vsql commands
   \g or terminate with semicolon to execute query
   \q to quit
   

See also

• Kerberos authentication
• Command-line options
• ADO.NET connection properties

11.2.3 - Configure JDBC clients on all platforms

Kerberos authentication on JDBC clients uses Java Authentication and Authorization Service (JAAS) to acquire the initial Kerberos credentials. JAAS is an API framework that hides platform-specific authentication details and provides a consistent interface for other applications.

You specify the client login process through the JAAS Login Configuration File. This file contains options that specify the authentication method and other settings to use for Kerberos. A class called the LoginModule defines valid options in the configuration file.

The JDBC client principal is crafted as jdbc-username@server-from-connection-string.

Implement the LoginModule

Vertica recommends that you use the JAAS public class com.sun.security.auth.module.Krb5LoginModul provided in the Java Runtime Environment (JRE).

The Krb5LoginModule authenticates users using Kerberos protocols and is implemented differently on non-Windows and Windows platforms:

• On non-Windows platforms: The Krb5LoginModule defers to a native Kerberos client implementation. Thus, you can use the same /etc/krb5.conf setup as you use to configure ODBC and vsql clients on Linux and MAC OSX platforms.
• On Windows platforms: The Krb5LoginModule uses a custom Kerberos client implementation bundled with the Java Runtime Environment (JRE). Windows settings are stored in a %WINDIR%\krb5.ini file, which has similar syntax and conventions to the non-Windows krb5.conf file. You can copy a krb5.conf from a non-Windows client to %WINDIR%\krb5.ini.

You can find documentation for the LoginModules in the com.sun.security.auth package, and on the Krb5LoginModule web page.

Create the JAAS login configuration

The JAASConfigName connection property identifies a specific configuration within a JAAS configuration that contains the Krb5LoginModule and its settings. The JAASConfigName setting lets multiple JDBC applications with different Kerberos settings coexist on a single host. The default configuration name is verticajdbc.

You can configure JAAS-related settings in the java.security master security properties file. This file resides in the lib/security directory of the JRE. For more information, see Appendix A in the Java^TM Authentication and Authorization Service (JAAS) Reference Guide.

Create a JDBC login context

The following example shows how to create a login context for Kerberos authentication on a JDBC client. The client uses the default JAASConfigName of verticajdbc and specifies that:

• The ticket-granting ticket will be obtained from the ticket cache

• The user will not be prompted for a password if credentials cannot be obtained from the cache, keytab file, or through a shared state.

verticajdbc {
  com.sun.security.auth.module.Krb5LoginModule
  required
  useTicketCache=true
  doNotPrompt=true;
};

JDBC authentication request and connection

You can configure the Krb5LoginModule to use a cached ticket or keytab. The driver can also acquire a ticket or keytab automatically if the calling user provides a password.

In the preceding example, the login process uses a cached ticket and does not prompt for a password because both useTicketCache and doNotPrompt are set to true. If doNotPrompt=false and you provide a user name and password during the login process, the driver provides that information to the LoginModule. The driver then calls the kinit utility on your behalf.

1. On a JDBC client, call the kinit utility to acquire a ticket:

   $ kinit kuser@EXAMPLE.COM
   

   If you prefer to use a password instead of calling the kinit utility, see the next section.

2. Connect to Vertica:

   Properties props = new Properties();
   props.setProperty("user", "kuser");
   props.setProperty("KerberosServiceName", "vertica");
   props.setProperty("KerberosHostName", "vcluster.example.com");
   props.setProperty("JAASConfigName", "verticajdbc");
   Connection conn = DriverManager.getConnection
   "jdbc:vertica://myserver.example.com:5433/VMart", props);
   

Have the driver acquire a ticket

Sometimes, you may want to bypass calling the kinit utility yourself but still use encrypted, mutual authentication. In such cases, you can optionally pass the driver a clear text password to acquire the ticket from the KDC. The password is encrypted when sent across the network. For example, useTicketCache and doNotPrompt are both false in the following example. Thus, the calling user's credentials are not obtained through the ticket cache or keytab.

$ verticajdbc  {
  com.sun.security.auth.module.Krb5LoginModule
  required
  useTicketCache=false
  doNotPrompt=false;
};

The preceding example demonstrates the flexibility of JAAS. The driver no longer looks for a cached ticket, and you do not have to call kinit. Instead, the driver takes the password and user name and calls kinit on your behalf.

See also

• Kerberos Client/Server Requirements

• JDBC connection properties

• Java^TM Authentication and Authorization Service (JAAS) Reference Guide (external website)

11.3 - Troubleshooting Kerberos authentication

These tips can help you avoid issues related to Kerberos authentication with Vertica and to troubleshoot any problems that occur.

JDBC client authentication fails

If Kerberos authentication fails on a JDBC client, check the JAAS login configuration file for syntax issues. If syntax is incorrect, authentication fails.

Working domain name service (DNS) not configured

Verify that the DNS entries and the system host file (/etc/hosts or /etc/hostnames) on the network are all properly configured for your environment. If you are using a fully qualified domain name, ensure that is properly configured as well. Refer to the Kerberos documentation for your platform for details.

System clocks out of sync

System clocks in your network must remain in sync for Kerberos authentication to work properly. If you access data in HDFS, then Vertica nodes must also be in sync with Hadoop.

All systems except red hat 7/CentOS 7

To keep system clocks in sync:

1. Install NTP on the Kerberos server (KDC).

2. Install NTP on each server in your network.

3. Synchronize system clocks on all machines that participate in the Kerberos realm within a few minutes of the KDC and each other

Clock skew can be a problem on Linux virtual machines that need to sync with the Windows Time Service. Use the following stepsto keep time in sync:

1. Using any text editor, open /etc/ntp.conf.

2. Under the Undisciplined Local Clock section, add the IP address for the Vertica server. Then, remove existing server entries.

3. Log in to the server as root, and set up a cron job to sync time with the added IP address every half hour, or as often as needed. For example:

   # 0 */2 * * * /etc/init.d/ntpd restart
   

4. Alternatively, run the following command to force clock sync immediately:

   $ sudo /etc/init.d/ntpd restart
   

For more information, see Enabling network time protocol (NTP) and the Network Time Protocol website.

Red hat 7/CentOS 7 systems

In Red Hat 7/CentOS 7, ntpd is deprecated in favor of chrony. To keep system clocks in your network in sync for Kerberos authentication to work properly, do the following:

1. Install chrony on the Kerberos server (KDC).

2. Install chrony on each server in your network.

3. Synchronize system clocks on all machines that participate in the Kerberos realm within a few minutes of the KDC and each other.

Clock skew on Linux virtual machines

Clock skew can be problematic on Linux virtual machines that need to sync with the Windows Time Service. Try the following to keep time in sync:

1. Using any text editor, open /etc/chrony.conf.

2. Under the Undisciplined Local Clock section, add the IP address for the Vertica server. Then, remove existing server entries.

3. Log in to the server as root, and set up a cron job to sync time with the added IP address every half hour, or as often as needed. For example:

   # 0 */2 * * * systemctl start chronyd
   

4. Alternatively, run the following command to force clock sync immediately:

   $ sudo systemctl start chronyd
   

For more information, see the Red Hat chrony guide.

Kerberos ticket is valid, but Hadoop access fails

Vertica uses Kerberos tickets to obtain Hadoop tokens. It then uses the Hadoop tokens to access the Hadoop data. Hadoop tokens expire after a period of time, so Vertica periodically refreshes them. However, if your Hadoop cluster is set to expire tokens frequently, it is possible that tokens might not be refreshed in time. If the token expires, you cannot access data.

Setting the HadoopFSTokenRefreshFrequency configuration parameter allows you to specify how often Vertica should refresh the token. Specify this value, in seconds, to be smaller than the expiration period set for Hadoop. For example:

=> ALTER DATABASE exampledb SET HadoopFSTokenRefreshFrequency = '86400';

For another cause of Hadoop access failure, see System Clocks Out of Sync.

Encryption algorithm choices

Kerberos is based on symmetric encryption. Be sure that all Kerberos parties used in the Kerberos realm agree on the encryption algorithm to use. If they do not agree, authentication fails. You can review the exceptions in the vertica.log.

On a Windows client, be sure the encryption types match the types set on Active Directory. See Configure Vertica for Kerberos authentication.

Be aware that Kerberos is used only for securing the login process. After the login process completes, by default, information travels between client and server without encryption. If you want to encrypt traffic, use SSL. For details, see Implementing SSL.

Kerberos passwords not recognized

If you change your Kerberos password, you must re-create all of your keytab files.

Using the ODBC data source configuration utility

On Windows vsql clients, you may choose to use the ODBC Data Source Configuration utility and supply a client Data Source. If so, be sure you enter a Kerberos host name in the Client Settings tab to avoid client connection failures with the Vertica Analytic Database server.

Authentication failure in backup, restore, or admin tools

This problem can arise in configurations where each Vertica node uses its own Kerberos principal. (This configuration is recommended.) When using vbr or admintools you might see an error such as the following:

$ vsql: GSSAPI continuation error: Miscellaenous failure
GSSAPI continuation error: Server not found in Kerberos database

Backup/restore and the admin tools use the value of KerberosHostname, if it is set, in the Kerberos principal used to authenticate. The same value is used on all nodes. If you have defined one Kerberos principal per node, as recommended, this value does not match. To correct this, unset the KerberosHostname parameter:

=> ALTER DATABASE DEFAULT CLEAR KerberosHostname;

Server's principal name does not match host name

This problem can arise in configurations where a single Kerberos principal is used for all nodes. Vertica recommends against using a single Kerberos principal for all nodes. Instead, use one principal per node and do not set the KerberosHostname parameter.

In some cases during client connection, the Vertica server's principal name might not match the host name in the connection string. (See also Using the ODBC Data Source Configuration Utility in this topic.)

On Windows vsql clients, you may choose to use the ODBC Data Source Configuration utility and supply a client Data Source. If so, be sure you enter a Kerberos host name in the Client Settings tab to avoid client connection failures with the Vertica server.

On ODBC, JDBC, and ADO.NET clients, set the host name portion of the server's principal using the KerberosHostName connection string.

Principal/host mismatch issues and resolutions

The following issues can occur if the principal and host are mismatched.

The KerberosHostName configuration parameter has been overridden

For example, consider the following connection string:

jdbc:vertica://v_vmart_node0001.example.com/vmart?user=kuser

Because the this connection string includes no explicit KerberosHostName parameter, the driver defaults to the host in the URL (v_vmart_node0001.example.com). If you overwrite the server-side KerberosHostName parameter as “abc”, the client generates an incorrect principal.

To resolve this issue, explicitly set the client’s KerberosHostName to the connection string, as in this example:

jdbc:vertica://v_vmart_node0001.example.com/vmart?user=kuser&kerberoshostname=abc

Connection load balancing is enabled... but the node against which the client authenticates might not be the node in the connection string.

In this situation, consider changing all nodes to use the same KerberosHostName setting. When you use the default to the host that was originally specified in the connection string, load balancing cannot interfere with Kerberos authentication.

A DNS name does not match the Kerberos host name

For example, imagine a cluster of six servers, where you want hr-servers and finance-servers to connect to different nodes on the Vertica cluster. Kerberos authentication, however, occurs on a single (the same) KDC. In the following example, the Kerberos service host name of the servers is server.example.com.

Suppose you have the following list of example servers:

server1.example.com 192.16.10.11
server2.example.com 192.16.10.12
server3.example.com 192.16.10.13
server4.example.com 192.16.10.14
server5.example.com 192.16.10.15
server6.example.com 192.16.10.16

Now, assume you have the following DNS entries:

finance-servers.example.com 192.168.10.11, 192.168.10.12, 192.168.10.13
hr-servers.example.com 192.168.10.14, 192.168.10.15, 192.168.10.16

When you connect to finance-servers.example.com, specify:

• Kerberos -h host name option as server.example.com

• -K host option for hr-servers.example.com

For example:

$ vsql -h finance-servers.example.com -K server.example.com

No DNS is set up on the client machine... so you must connect by IP only

To resolve this issue, specify:

• Kerberos -h host name option for the IP address

• -K host option for server.example.com

For example:

$ vsql -h 192.168.1.12 -K server.example.com

There is a load balancer involved (Virtual IP)... but there is no DNS name for the VIP

Specify:

• Kerberos -h host name option for the Virtual IP address

• -K host option for server.example.com

For example:

$ vsql -h <virtual IP> -K server.example.com

You connect to Vertica using an IP address... but there is no host name to construct the Kerberos principal name.

Provide the instance or host name for the Vertica as described in Inform Vertica about the Kerberos principal

The server-side KerberosHostName configuration parameter is set to a name other than the Vertica node's host name... but the client cannot determine the host name based on the host name in the connection string alone.

Reset KerberosHostName to match the name of the Vertica node's host name. For more information, see the following topics:

• ODBC DSN Parameters

• JDBC Connection Properties

• ADO.NET Connection Properties

11.4 - Configuring Kerberos authentication

Kerberos authentication differs from user name/password authentication. Instead of authenticating each user to each network service, Kerberos uses symmetric encryption through a trusted third party, called the Key Distribution Center (KDC). In this environment, clients and servers validate their authenticity by obtaining a shared secret (ticket) from the KDC, after which clients and servers can talk to each other directly.

Vertica uses the GSS-API (Generic Security Services Application Programming Interface) to communicate with the Kerberos client. When you create an authentication method, specify that Vertica use the 'gss' method to authenticate with Kerberos, as in the following syntax:

=> CREATE AUTHENTICATION <method_name> METHOD 'gss' HOST <ip_address>;

Topics in this section describe how to configure the Vertica server and clients for Kerberos authentication. This section does not describe how to install, configure, or administer a Key Distribution Center.

To install the Kerberos 5 GSS-API distribution for your operating system, see the MIT Kerberos Distribution page.

You must meet the following minimum requirements to use Kerberos authentication with the Vertica server and client drivers.

Kerberos server

Your network administrator should have already installed and configured one or more Kerberos Key Distribution Centers (KDC), and the KDC must be accessible from every node in your Vertica cluster.

The KDC must support Kerberos 5 via GSSAPI. For details, see the MIT Kerberos Distribution page.

Client package

The Kerberos 5 client package contains software that communicates with the KDC server. This package is not included as part of the Vertica installation. If the Kerberos 5 client package is not present on your system, you must download and install it on all clients and servers involved in Kerberos authentication (for example, each Vertica and each Vertica client), with the exception of the KDC itself.

Kerberos software is built into Microsoft Windows. If you are using another operating system, you must obtain and install the client package.

Refer to the Kerberos documentation for installation instructions, such as on the MIT website, including the MIT Kerberos Distribution page.

Client/Server identity

Each client (users or applications that will connect to Vertica) and the Vertica server must be configured as Kerberos principals. These principals authenticate using the KDC.

Each client platform has a different security framework, so the steps required to configure and authenticate against Kerberos differ among clients. See the following topics for more information:

• Configure Vertica for Kerberos authentication
• Configure clients for Kerberos authentication

    <ul>
        <li><a href="ConfigureVerticaForKerberosAuthentication.htm"><MadCap:xref href="ConfigureVerticaForKerberosAuthentication.htm">Configure  for Kerberos Authentication</MadCap:xref></a>
        </li>
        <li><a href="ConfigureClientsForKerberosAuthentication.htm"><MadCap:xref href="ConfigureClientsForKerberosAuthentication.htm">Configure Clients for Kerberos Authentication</MadCap:xref></a>
        </li>
    </ul>

12 - LDAP authentication

Lightweight Directory Access Protocol (LDAP) is an authentication method that works like password authentication. The main difference is that the LDAP method authenticates clients trying to access your Vertica database against an LDAP or Active Directory server. Use LDAP authentication when your database needs to authenticate a user with an LDAP or Active Directory server.

12.1 - LDAP prerequisites and definitions

Prerequisites

Before you configure LDAP authentication for your Vertica database you must have:

• IP address and host name for the LDAP server. Vertica supports IPv4 and IPv6 addresses.

• Your organization's Active Directory information.

• A service account for search and bind.

• Administrative access to your Vertica database.

• open-ldap-tools package installed on at least one node. This package includes ldapsearch.

Definitions

The following definitions are important to remember for LDAP authentication:

12.2 - LDAP authentication parameters

There are several parameters that you need to configure for LDAP authentication.

General LDAP parameters

Use the following parameters to configure for either LDAP bind or LDAP bind and search:

LDAP bind parameters

The following parameters create a bind name string, which specifies and uniquely identifies a user to the LDAP server. For details, see Workflow for configuring LDAP bind.

To create a bind name string, you must set one (and only one) of the following:

• Both binddn_prefix and binddn_suffix (must be set together)

• domain_prefix

• email_suffix

For example, if you set binddn_prefix and binddn_suffix, you cannot also set email_suffix. Conversely, if you set email_suffix, you cannot set binddn_prefix and binddn_suffix.

If you do not set a bind parameter, Vertica performs bind and search operations instead of a bind operation.

The following examples use the authentication record v_ldap:

=> CREATE AUTHENTICATION v_ldap METHOD 'ldap' HOST '10.0.0.0/23';

=> ALTER AUTHENTICATION v_ldap SET
    binddn_prefix='cn=', binddn_suffix=',cn=Users,dc=ExampleDomain,dc=com';

=> ALTER AUTHENTICATION v_ldap SET
    binddn_prefix='cn=', binddn_suffix=',ou=OrgUsers,dc=example,dc=com';

LDAP search and bind parameters

Use the following parameters when authenticating with LDAP search and bind. For more information see Workflow for configuring LDAP search and bind.

The following example shows how to set these three attributes. In this example, it sets

• binddn to cn=Manager,dc=example,dc=com

• bind_password to secret

• search_attribute to cn

=> ALTER AUTHENTICATION auth_method_name SET host='ldap://example13',
basedn='dc=example,dc=com',binddn='cn=Manager,dc=example,dc=com',
bind_password='secret',search_attribute='cn';

The binddn and bind_password parameters are optional. If you omit them, Vertica performs an anonymous search.

12.3 - TLS for LDAP authentication

Vertica establishes a connection to an LDAP server in two contexts, and each context has a corresponding TLS CONFIGURATION that controls if each connection should use TLS:

1. LDAPLink: using the LDAPLink service or its dry run functions to synchronize users and groups between Vertica and the LDAP server.

2. LDAPAuth: when a user with an ldap authentication method attempts to log into Vertica, Vertica attempts to bind the user to a matching user in the LDAP server. If the bind succeeds, Vertica allows the user to log in.

Query TLS_CONFIGURATIONS to view existing TLS CONFIGURATIONs:

=> SELECT * FROM tls_configurations WHERE name IN ('LDAPLink', 'LDAPAuth');
   name   |  owner  | certificate | ca_certificate | cipher_suites |  mode
----------+---------+-------------+----------------+---------------+----------
 LDAPLink | dbadmin | client_cert | ldap_ca        |               | VERIFY_CA
 LDAPAuth | dbadmin | client_cert | ldap_ca        |               | DISABLE
(2 rows)

This page covers the LDAPAuth context. For details on the LDAPLink context, see TLS for LDAP link.

Keep in mind that configuring TLS for LDAP authentication does not encrypt the connection between Vertica and the client with TLS. To configure client-server TLS, see Configuring client-server TLS.

Configuring LDAP authentication

After a client successfully establishes a connection with Vertica, they must authenticate as a user before they can interact with the database. If the user has the ldap authentication method, Vertica connects to the LDAP server to authenticate the user. To configure TLS for this context, use the following procedure.

Setting the LDAPAuth TLS CONFIGURATION

The LDAPAuth TLS CONFIGURATION takes a client certificate and CA certificate created or imported with CREATE CERTIFICATE. Vertica presents the client certificate to the LDAP server for verification by its CA. Vertica uses the CA certificate to verify the LDAP server's certificate.

For details on key and certificate generation, see Generating TLS certificates and keys.

1. If you want Vertica to verify the LDAP server's certificate before establishing the connection, generate or import a CA certificate and add it to the LDAPAuth TLS CONFIGURATION.

   For example, to import the existing CA certificate LDAP_CA.crt:

   => \set ldap_ca '\''`cat ldap_ca.crt`'\''
   => CREATE CA CERTIFICATE ldap_ca AS :ldap_ca;
   CREATE CERTIFICATE
   

   Then, to add the ldap_ca CA certificate to LDAPAuth:

   ALTER TLS CONFIGURATION LDAPAuth ADD CA CERTIFICATES ldap_ca;
   

2. If your LDAP server verifies client certificates, you must generate or import a client certificate and its key and add it to the LDAPAuth TLS CONFIGURATION. Vertica presents this certificate to the LDAP server for verification by its CA.

   For example, to import the existing certificate client.crt (signed by the imported CA) and key client.key:

   => \set client_key '\''`cat client.key`'\''
   => CREATE KEY client_key TYPE 'RSA' AS :client_key;
   CREATE KEY
   
   => \set client_cert '\''`cat client.crt`'\''
   => CREATE CERTIFICATE client_cert AS :client_cert SIGNED BY ldap_ca KEY client_key;
   CREATE CERTIFICATE
   

   Then, to add client_cert to LDAPAuth:

   => ALTER TLS CONFIGURATION LDAPAuth CERTIFICATE client_cert;
   

3. Enable TLS or LDAPS (the exact protocol used depends on the value of host in the AUTHENTICATION object) by setting the TLSMODE to one of the following. TRY_VERIFY or higher requires a CA certificate:

   • ENABLE: Enables TLS. Vertica does not check the LDAP server's certificate.

   • TRY_VERIFY: Establishes a TLS connection if one of the following is true:

     • The LDAP server presents a valid certificate.

     • The LDAP server doesn't present a certificate.

     If the LDAP server presents an invalid certificate, a plaintext connection is used.

   • VERIFY_CA: Connection succeeds if Vertica verifies that the LDAP server's certificate is from a trusted CA. Using this TLSMODE forces all connections without a certificate to use plaintext.

   • VERIFY_FULL: Connection succeeds if Vertica verifies that the LDAP server's certificate is from a trusted CA and the cn (Common Name) or subjectAltName attribute matches the hostname or IP address of the LDAP server.

     The cn is used for the username, so subjectAltName must match the hostname or IP address of the LDAP server.

   Note

   The value of TLSMODE only applies to authentication records where the starttls LDAP authentication parameter is set to hard or not set at all. If starttls is set to soft, Vertica establishes a TLS connection without verifying the LDAP server's certificate and falls back to a plaintext connection if the LDAP server does not support TLS. For details, see the next section.

   For example:

   => ALTER TLS CONFIGURATION LDAPAuth TLSMODE 'verify_ca';
   ALTER TLS CONFIGURATION
   

Creating an LDAP authentication record

After a client successfully establishes a connection with Vertica, they must authenticate as a user before they can interact with the database. If the user has the ldap authentication method, Vertica connects to the LDAP server and attempts a bind to authenticate the user.

To view existing authentication records, query CLIENT_AUTH.

For details on the parameters referenced in this procedure, see LDAP authentication parameters.

1. CREATE an authentication record with an LDAP method.

   Syntax for creating an LDAP authentication record:

   => CREATE AUTHENTICATION auth_record_name method 'ldap' HOST 'user_connection_source';
   

   For example, to create an LDAP authentication record that applies to users that connect from any host:

   => CREATE AUTHENTICATION ldap_auth METHOD 'ldap' HOST '0.0.0.0/0';
   

2. ALTER the authentication record to to set the host and port (optional) of the LDAP server and the domain name (basedn) and bind distinguished name (binddn).

   • To use a plaintext connection between Vertica and the LDAP server (disable TLS):

     • Begin the host URL with ldap://.

     • Set the TLSMODE of LDAPAuth to DISABLE and verify that starttls is not set.

   • To use StartTLS and reject plaintext connections:

     • Begin the host URL with ldap://.

     • Set the TLSMODE of LDAPAuth to ENABLE or higher. Vertica only verifies the LDAP server's certificate if TLSMODE is set to TRY_VERIFY or higher.

     • Verify that starttls is set to hard or not set.

   • To use StartTLS, but still accept a plaintext connection if the LDAP server cannot be upgrade the connection to TLS:

     • Begin the host URL with ldap://.

     • Set starttls to soft and the TLSMODE of LDAPAuth to ENABLE or higher. Vertica does not verify the server's certificate before establishing the connection and ignores the certificate verification policy of the LDAPAuth TLSMODE.

   • To use LDAPS:

     • Begin the host URL with ldaps://

     • TLSMODE of LDAPAuth to ENABLE or higher.

   This example authentication record searches for users in the active directory orgunit.example.com on an LDAP server with an IP address of 192.0.2.0 on port 5389 and requires a TLS connection to the LDAP server:

   => ALTER AUTHENTICATION ldap_auth SET
       host='ldap://192.0.2.0:5389',
       basedn='ou=orgunit,dc=example,dc=com',
       binddn_prefix='cn=',
       binddn_suffix=',ou=orgunit,dc=example,dc=com',
       starttls='hard';
   

   The binddn_prefix and binddn_suffix combine to create the full DN. That is, for some Vertica user asmith, 'cn=asmith,ou=orgunit,dc=example,dc=com' is the full DN when Vertica attempts the bind.

   To modify the ldap_auth authentication record to request StartTLS, but still accept plaintext connections, set the starttls parameter to soft:

   => ALTER AUTHENTICATION ldap_auth SET starttls='soft';
   

3. Enable the authentication record:

   => ALTER AUTHENTICATION ldap_auth ENABLE;
   

4. GRANT the authentication record to a user or role.

   For example:

   => GRANT AUTHENTICATION ldap_auth TO asmith;
   

   In this case, when the user asmith attempts to log in, Vertica constructs the distinguished name 'cn=asmith,ou=orgunit,dc=example,dc=com' from the search base specified in the ldap_auth, connects to the LDAP server, and attempts to bind it to the Vertica user. If the bind succeeds, Vertica allows asmith to log in.

12.4 - Configuring multiple LDAP servers

If you need to configure multiple LDAP servers that have different URLs, create a separate authentication record for each server. Use the PRIORITY keyword to indicate which search the LDAP server performs first.

The following statements create two authentication methods, vldap1 and vldap2. They specify that the LDAP server first search the entire directory (basedn=dc=example,dc=com) for a DN with an OU attribute Sales. If the first search returns no results, or otherwise fails, the LDAP server next searches for a DN with the OU attribute Marketing:

=> CREATE AUTHENTICATION vldap1 method "ldap" HOST 10.0.0.0/8;
=> ALTER AUTHENTICATION vldap1 SET
      host='ldap://ldap.example.com/search',
      basedn='dc=example,dc=com',
      search_attribute='Sales'
      PRIORITY 1;
=> GRANT AUTHENTICATION vldap1 to public;
=> CREATE AUTHENTICATION vldap2 method "ldap" HOST 10.0.0.0/8;
=> ALTER AUTHENTICATION vldap2 SET
      host='ldap://ldap.example.com/search',
      basedn='dc=example,dc=com',
      search_attribute='Marketing'
      PRIORITY 0;
=> GRANT AUTHENTICATION vldap2 to public;

12.5 - LDAP bind methods

There are two LDAP methods that you use to authenticate your Vertica database against an LDAP server.

• Bind—Use LDAP bind when Vertica connects to the LDAP server and binds using the CN and password. (These values are the username and password of the user logging into the database). Use the bind method when your LDAP account's CN field matches that of the username defined in your database. For more information see Workflow for configuring LDAP bind.

• Search and Bind —Use LDAP search and bind when your LDAP account's CN field is a user's full name or does not match the username defined in your database. For search and bind, the username is usually in another field such as UID or sAMAccountName in a standard Active Directory environment. Search and bind requires your organization's Active Directory information. This information allows Vertica to log into the LDAP server and search for the specified field. For more information see Workflow for configuring LDAP search and bind.

  If you are using search and bind, having a service account simplifies your server side configuration. In addition, you do not need to store your Active Directory password.

LDAP anonymous binding

Anonymous binding is an LDAP server function. Anonymous binding allows a client to connect and search the directory (bind and search) without logging in because binddn and bindpasswd are not needed.

You also do not need to log in when you configure LDAP authentication using Management Console.

12.5.1 - Workflow for configuring LDAP bind

To configure your Vertica database to authenticate clients using LDAP bind, follow these steps:

1. Obtain a service account. For information see the LDAP product documentation.You cannot use the service account in the connection parameters for LDAP bind.

2. Compare the user's LDAP account name to their Vertica username. For example, if John Smith's Active Directory (AD) sAMAccountName = jsmith, his Vertica username must also be jsmith.

   However, the LDAP account does not have to match the database user name, as shown in the following example:

   => CREATE USER r1 IDENTIFIED BY 'password';
   => CREATE AUTHENTICATION ldap1  METHOD 'ldap' HOST '172.16.65.177';
   => ALTER AUTHENTICATION ldap1 SET HOST=
      'ldap://172.16.65.10',basedn='dc=dc,dc=com',binddn_suffix=',ou=unit2,dc=dc,dc=com',binddn_prefix='cn=use';
   => GRANT AUTHENTICATION ldap1 TO r1;
   \! ${TARGET}/bin/vsql -p $PGPORT -U r1 -w $LDAP_USER_PASSWD -h ${HOSTNAME} -c
      "select user_name, client_authentication_name from sessions;"
    user_name | client_authentication_name
   -----------+----------------------------
    r1        | ldap
   (1 row)
   

3. Run ldapsearch from a Vertica node against your LDAP or AD server. Verify the connection to the server and identify the values of relevant fields. Running ldapsearch helps you build the client authentication string needed to configure LDAP authentication.

   In the following example, ldapsearch returns the CN, DN, and sAMAccountName fields (if they exist) for any user whose CN contains the username jsmith. This search succeeds only for LDAP servers that allow anonymous binding:

   $ ldapsearch -x -h 10.10.10.10 -b "ou=Vertica Users,dc=CompanyCorp,dc=com"
   '(cn=jsmith*)' cn dn uid sAMAccountName
   

   ldapsearch returns the following results. The relevant information for LDAP bind is in bold:

   # extended LDIF
   #
   # LDAPv3
   # base <ou=Vertica Users,dc=CompanyCorp,dc=com> with scope subtree
   # filter: (cn=jsmith*)
   # requesting: cn dn uid sAMAccountName
   #
   # jsmith, Users, CompanyCorp.com
   dn:cn=jsmith,ou=Vertica Users,dc=CompanyCorp,dc=com
   cn: jsmith
   uid: jsmith
   # search result
   search: 2
   result: 0 Success
   # numResponses: 2
   # numEntries: 1
   

4. Create a new authentication record based on the information from ldapsearch. In the ldapsearch entry, the CN is username jsmith, so you do not need to set it. Vertica automatically sets the CN to the username of the user who is trying to connect. Vertica uses that CN to bind against the LDAP server.

   => CREATE AUTHENTICATION v_ldap_bind METHOD 'ldap' HOST '0.0.0.0/0';
   => GRANT AUTHENTICATION v_ldap_bind TO public;
   => ALTER AUTHENTICATION v_ldap_bind SET
   host='ldap://10.10.10.10/',
   basedn='DC=CompanyCorp,DC=com',
   binddn_prefix='cn=',
   binddn_suffix=',OU=Vertica Users,DC=CompanyCorp,DC=com';
   

For more information see LDAP Bind Parameters.

12.5.2 - Workflow for configuring LDAP search and bind

To configure your Vertica database to authenticate clients using LDAP search and bind, follow these steps:

1. Obtain a service account. For information see the LDAP product documentation.

2. From a Vertica node, run ldapsearch against your LDAP or AD server. Verify the connection to the server, and identify the values of relevant fields. Running ldapsearch helps you build the client authentication string needed to configure LDAP authentication.

   In the following example, ldapsearch returns the CN, DN, and sAMAccountName fields (if they exist) for any user whose CN contains the username, John. This search succeeds only for LDAP servers that allow anonymous binding:

   $ ldapsearch -x -h 10.10.10.10 -b 'OU=Vertica Users,DC=CompanyCorp,DC=com' -s sub -D
   'CompanyCorp\jsmith' -W '(cn=John*)' cn dn uid sAMAccountName
   

3. Review the results that ldapsearch returns.The relevant information for search and bind is in bold:

   # extended LDIF
   #
   # LDAPv3
   # base <OU=Vertica Users,DC=CompanyCorp,DC=com> with scope subtree
   # filter: (cn=John*)
   # requesting: cn dn sAMAccountName
   #
   # John Smith, Vertica Users, CompanyCorp.com
   dn: CN=jsmith,OU=Vertica Users,DC=CompanyCorp,DC=com
   cn: Jsmith
   sAMAccountName: jsmith
   # search result
   search: 2
   result: 0 Success
   # numResponses: 2
   # numEntries: 1
   

4. Create the client authentication record. The cn attribute contains the username you want—jsmith. Set your search attribute to the CN field so that the search finds the appropriate account.

   => CREATE AUTHENTICATION v_ldap_bind_search METHOD 'ldap' HOST '10.10.10.10';
   => GRANT AUTHENTICATION v_ldap_bind_search TO public;
   => ALTER AUTHENTICATION v_ldap_bind_search SET
   host='ldap://10.10.10.10',
   basedn='OU=Vertica,DC=CompanyCorp,DC=com',
   binddn='CN=jsmith,OU=Vertica Users,DC=CompanyCorp,DC=com',
   bind_password='password',
   search_attribute='CN';
   

For more information see LDAP Bind and Search Parameters

13 - OAuth 2.0 authentication

Rather than with a username and password, users can authenticate to Vertica by first verifying their identity with an identity provider, receiving an OAuth token, and then passing the token to Vertica.

OAuth in Vertica is tested with Keycloak and Okta, but other providers should work if they support the RFC 7662 Token Introspection standard.

13.1 - Configuring OAuth authentication

For a list of ODBC OAuth connection properties, see Data source name (DSN) connection properties.

The following procedure:

1. Configures Keycloak 18.0.

2. Creates an OAuth authentication record.

3. Retrieves an access token with a POST request.

4. Uses sample applications to authenticate to Vertica, passing the access token as an argument and, optionally, parameters for token refresh.

Configuring Keycloak

The following procedure configures a Keycloak 18.0.0 server on 203.0.113.1.

Using TLS (optional)

If you want to use TLS, you must obtain a certificate and key for Keycloak signed by a trusted CA. This example uses a self-signed CA for convenience.

1. Generate the CA certificate:

   => CREATE KEY SSCA_key TYPE 'RSA' LENGTH 2048;
   CREATE KEY
   
   => CREATE CA CERTIFICATE SSCA_cert
   SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=Micro Focus/OU=Vertica/C N=Vertica Root CA'
   VALID FOR 3650
   EXTENSIONS 'nsComment' = 'Self-signed root CA cert'
   KEY SSCA_key;
   CREATE CERTIFICATE
   

2. Generate a server key and certificate, signed by your CA, setting the subjectAltName of the certificate to the DNS and/or IP address of your Keycloak server:

   => CREATE KEY keycloak_key TYPE 'RSA' LENGTH 2048;
   CREATE KEY
   
   => CREATE CERTIFICATE keycloak_cert
   SUBJECT '/C=US/ST=Massachussets/L=Cambridge/O=Micro Focus/OU=Vertica/CN=Vertica Server'
   SIGNED BY SSCA_cert
   EXTENSIONS 'nsComment' = 'Keycloak CA', 'extendedKeyUsage' = 'serverAuth', 'subjectAltName' = 'DNS.1:dnsserver,IP:203.0.113.1'
   KEY keycloak_key;
   CREATE CERTIFICATE
   

3. Create the file keycloak_directory/conf/keyfile.pem with the content from the key column for the generated key:

   => SELECT key FROM cryptographic_keys WHERE name = 'keycloak_key';
   

4. Create the file keycloak_directory/conf/certfile.pem with the content from the certificate_text column for the generated certificate:

   => SELECT certificate_text FROM certificates WHERE name = 'keycloak_cert';
   

5. Append to your system's CA bundle the content from the certificate_text column for the generated CA certificate. The default CA bundle path and format varies between distributions; for details, see SystemCABundlePath:

   => SELECT certificate_text FROM certificates WHERE name = 'SSCA_cert';
   

6. Set the SystemCABundlePath configuration parameter:

   => ALTER DATABASE DEFAULT SET SystemCABundlePath = 'path/to/ca_bundle';
   

Starting Keycloak

1. Enter the following commands for a minimal configuration to create the Keycloak admin and to start Keycloak in start-dev mode:

   $ KEYCLOAK_ADMIN=kcadmin
   $ export KEYCLOAK_ADMIN
   $ KEYCLOAK_ADMIN_PASSWORD=password
   $ export KEYCLOAK_ADMIN_PASSWORD
   $ cd keycloak_directory/bin/
   $ ./kc.sh start-dev --hostname 203.0.113.1 --https-certificate-file ../conf/certfile.pem --https-certificate-key-file=../conf/keyfile.pem
   

2. Open the Keycloak console with your browser (these examples use the default ports):

   • For HTTP: http://203.0.113.1:8080

   • For HTTPS: http://203.0.113.1:8443

3. Sign in as the admin.

4. (Optional) To make testing OAuth more convenient, navigate to Realm Settings > Tokens and increase Access Token Lifespan to a greater value (the default is 5 minutes).

Creating the Vertica client

1. Navigate to Clients and click on Create. The Add Client page appears.

2. In Client ID, enter vertica.

3. Click Save. The client configuration page appears.

4. In the Settings tab, use the Access Type dropdown to select confidential.

5. In the Credentials tab, make a note of the Secret. This is the client secret used to refresh the token when it expires.

Creating a Keycloak user

Keycloak users map to Vertica users with the same name. This example creates a the Keycloak user oauth_user.

1. In the Users tab, click Add user. The Add user page appears.

2. In Username, enter oauth_user.

3. In the Credentials tab, enter a password.

Configuring Vertica

Creating the authentication record

Create an authentication record for OAuth.

The following authentication record v_oauth authenticates users from any IP address with an OAuth token (rather than a username and password) and uses the following parameters. The identity provider is Keycloak 18.0.0:

• client_id: The confidential client, vertica, registered in Keycloak.

• client_secret: The client secret, generated by Keycloak.

• discovery_url: Also known as the OpenID Provider Configuration Document, this is the endpoint that contains information about the identity provider's configuration and endpoints.

=> CREATE AUTHENTICATION v_oauth METHOD 'oauth' HOST '0.0.0.0/0'
=> ALTER AUTHENTICATION v_oauth SET client_id = 'vertica';
=> ALTER AUTHENTICATION v_oauth SET client_secret = 'client_secret';
=> ALTER AUTHENTICATION v_oauth SET discovery_url = 'https://203.0.113.1:8443/realms/myrealm/.well-known/openid-configuration';
=> ALTER AUTHENTICATION v_oauth SET introspect_url = 'https://203.0.113.1:8443/realms/myrealm/protocol/openid-connect/token/introspect';

Creating a Vertica user

Vertica users map to Keycloak users with the same username.

1. To map to the Keycloak user oauth_user, create a Vertica user with the same name. You do not need to specify a password because authentication is performed by the identity provider:

   => CREATE USER oauth_user;
   

2. Grant the OAuth authentication record to the user (or their role):

   => GRANT AUTHENTICATION v_oauth TO oauth_user;
   => GRANT ALL ON SCHEMA PUBLIC TO oauth_user;
   

Retrieving an access token

The simple way to get an OAuth access token is to send a POST request to the token endpoint, providing the credentials of the Keycloak user. For example, oauth_user:

$ curl --location --request POST 'http://203.0.113.1:8080/realms/master/protocol/openid-connect/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'username=oauth_user' \
--data-urlencode 'password=oauth_user_password' \
--data-urlencode 'client_id=vertica' \
--data-urlencode 'client_secret=client_secret' \
--data-urlencode 'grant_type=password'

Keycloak responds with a JSON if you authenticated correctly. You can then use the returned access token, refresh token, and scope with the corresponding connection properties.

{
   "access_token":"access_token",
   "expires_in":60,
   "refresh_expires_in":1800,
   "refresh_token":"refresh_token",
   "token_type":"Bearer",
   "not-before-policy":0,
   "session_state":"6745892a-aa74-452f-b6b9-c45637193859",
   "scope":"profile email"
}

Running the sample applications

The OAuth sample applications, at a minimum, take an access token as an argument to authenticate to the database until the token expires. If you want the sample application to refresh the token after it expires, you must specify the following:

• Refresh token

• Client ID

• Client secret

• Token URL

ODBC

1. Follow the instructions in the README.

2. Run the sample application, passing the OAuth parameters as arguments:

   • To authenticate until the token expires:

     $ ./a.out --access-token OAuthAccessToken
     

   • To authenticate and silently refresh the access token when it expires:

     $ ./a.out --access-token OAuthAccessToken
         --refresh-token OAuthRefreshToken
         --client-id OAuthClientID
         --client-secret OAuthClientSecret
         --token-url OAuthTokenURL
     

JDBC

1. Follow the instructions in the README.

2. Run the sample application, passing the OAuth parameters as arguments:

   • To authenticate until the token expires:

     $ mvn compile exec:java -Dexec.mainClass=OAuthSampleApp -Dexec.args="vertica_host database_name --access-token oauthaccesstoken"
     

   • To authenticate and silently refresh the access token when it expires:

     $ mvn compile exec:java -Dexec.mainClass=OAuthSampleApp -Dexec.args="vertica_host database_name --access-token oauthaccesstoken
         --refresh_token oauthrefreshtoken
         --client-id oauthclientid
         --client-secret oauthclientsecret
         --token-url oauthtokenurl"
     

Troubleshooting

To get debugging information for TLS, use the -Djavax.net.debug=ssl flag.

Importing CA certificates into the Java truststore

If you configure your identity provider with TLS (that is, if you use HTTPS endpoints for your token or refresh URLs) and its certificate isn't issued by a well-known CA, you must import the issuer's CA certificate with keytool.

For example, to add the certificate keycloak/cert.crt to the Java truststore:

$ keytool -trustcacerts -keystore /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.261-2.6.22.2.el7_8.x86_64/jre/lib/security/cacerts -storepass changeit -importcert -alias keycloak -file /keycloak/cert.crt

14 - TLS authentication

This section contains information about the tls authentication method, which is one of several authentication methods used to manage client connections.

Before creating a tls authentication method, you must configure your server to use TLS (TLS is disabled by default). The supported modes are:

• Server Mode: In server mode, the client must confirm the server's identity before connecting. The client verifies that the server's certificate and public key are valid and were issued by a certificate authority (CA) listed in the client's list of trusted CAs. This helps prevent man-in-the-middle attacks.

• Mutual Mode: In mutual mode, the client and server must verify each other's identity before connecting. Client authentication is optional because Vertica can authenticate the client at the application protocol level with the client's username and password.

You can use the tls authentication method with either Server Mode or Mutual Mode. However, to use client self-authentication, your server must use Mutual Mode.

Before you create a tls authentication method, perform the pre-requisite tasks necessary for your environment (for example, certificate creation). Refer to TLS protocol and all subsections applicable to your environment.

To create a tls authentication method, see Creating authentication records.

14.1 - Implementing client self-authentication

To use a client self-authentication method, your server must be in SSL Mutual Mode.

To create an authentication method for client self-authentication, use the CREATE AUTHENTICATION statement. Specify the auth_type 'tls' and with HOST TLS.

Create an authentication method with client self-authentication method

This section provides sample chronological steps for setting up a client for self-authentication, creating an authentication method, and associating the method with a user through a grant statement.

1. Follow all applicable procedures for implementing SSL and distributing certificates and keys. Refer to TLS protocol as it applies to your environment.
   
   When you create a client key, make sure to include a Common Name (CN) that is the database user name you want to use with the target database.
   

   $ Common Name <server hostname> []:<database username>
   

2. Create the authentication method with CREATE AUTHENTICATION. Authentication methods are automatically enabled when you create them.

   => CREATE AUTHENTICATION myssltest METHOD 'tls' HOST TLS '10.0.0.0/23;
   

3. Associate the method with the user through a grant statement.

   => GRANT AUTHENTICATION myssltest TO mydatabaseusername;
   

Your client can now log on and be recognized.

Specify TLS for client connections

You can require clients to use TLS when connecting to Vertica. To do so, create a client authentication method for them that uses the HOST TLS syntax with CREATE AUTHENTICATION.

Specific clients might connect through a network connection known to be insecure. In such cases, you can choose to limit specific users to connecting through TLS. You can also require all clients to use TLS.

Create authentication method RejectNoSSL that rejects users from any IP address that are trying to authenticate without TLS:

=> CREATE AUTHENTICATION RejectNoSSL METHOD 'reject' HOST NO TLS '0.0.0.0/0';  --IPv4
=> CREATE AUTHENTICATION RejectNoSSL METHOD 'reject' HOST NO TLS '::/0';       --IPv6

See Creating authentication records for more information about creating client authentication methods.