Client authentication

• 1: Default authentication records
• 2: Configuring client authentication
• 3: Fallthrough authentication
• 4: Authentication filtering
• 5: Implicit authentication
• 6: Dbadmin authentication access
• 7: Creating authentication records
• 8: Modifying authentication records
• 9: Authentication record priority
• 10: Viewing information about client authentication records
• 11: Enabling and disabling authentication methods
• 12: Granting and revoking authentication methods
• 13: Hiding database usernames
• 14: Hash authentication
• 14.1: Password hashing algorithm
• 14.2: Configuring hash authentication
• 14.3: Passwords
• 14.3.1: Profiles
• 14.3.2: Password guidelines
• 14.3.3: Password expiration
• 14.3.4: Account locking
• 15: Ident authentication
• 15.1: Installing and setting up an ident server
• 15.2: Configuring ident authentication for database users
• 16: Kerberos authentication
• 16.1: Configure Vertica for Kerberos authentication
• 16.1.1: Create the Vertica principals and keytabs on Linux KDC
• 16.1.2: Specify KDC information and configure realms
• 16.1.3: Inform Vertica about the Kerberos principal
• 16.1.4: Configure the authentication method for all clients
• 16.1.5: Creating the principals and keytab on active directory
• 16.1.6: Get the Kerberos ticket and authenticate Vertica
• 16.2: Configure clients for Kerberos authentication
• 16.2.1: Configure ODBC and vsql clients on non-windows platforms
• 16.2.2: Configure ADO.NET, ODBC, and vsql clients on Windows
• 16.2.3: Configure JDBC clients on all platforms
• 16.3: Troubleshooting Kerberos authentication
• 17: LDAP authentication
• 17.1: LDAP prerequisites and definitions
• 17.2: LDAP authentication parameters
• 17.3: TLS for LDAP authentication
• 17.4: Authentication fallthrough for LDAP
• 17.5: LDAP bind methods
• 17.5.1: Workflow for configuring LDAP bind
• 17.5.2: Workflow for configuring LDAP search and bind
• 18: OAuth 2.0 authentication
• 18.1: Configuring OAuth authentication
• 18.1.1: Configure Keycloak
• 18.1.2: Configure Okta
• 18.2: Just-in-time user provisioning
• 18.3: OAuth authentication parameters
• 19: TLS authentication
• 19.1: Client authentication with TLS

1 - Default authentication records

Vertica automatically creates the following default authentication records and grants them to the public role. These have the lowest possible priority (-1), so user-created authentication records take priority over these default records:

=> SELECT auth_name,is_auth_enabled,auth_host_type,auth_method,auth_priority,is_fallthrough_enabled FROM client_auth;
         auth_name         | is_auth_enabled | auth_host_type | auth_method | auth_priority | is_fallthrough_enabled
---------------------------+-----------------+----------------+-------------+---------------+------------------------
 default_hash_network_ipv4 | True            | HOST           | PASSWORD    |            -1 | False
 default_hash_network_ipv6 | True            | HOST           | PASSWORD    |            -1 | False
 default_hash_local        | True            | LOCAL          | PASSWORD    |            -1 | False
(3 rows)

If no authentication records are defined (and the default authentication records are dropped), only the dbadmin and users without passwords can access the database. For details, see Implicit authentication.

2 - Configuring client authentication

You can restrict how database users can connect with authentication records. The Vertica database uses client authentication to establish the identity of the connecting client and determines whether that client is authorized to connect to the Vertica database using the supplied credentials and from their host address.

Basic authentication configuration workflow

In general, the workflow for configuring client authentication is as follows:

1. Create an authentication record. Authentication records are enabled automatically after creation. For details, see Creating authentication records.

2. Grant the authentication to a user or role.

To view existing authentication records, query the system table CLIENT_AUTH.

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.

• 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 can only be designated as local or host, 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.

3 - Fallthrough authentication

Normally, if a user fails to authenticate with the first chosen authentication record, the user is rejected. However, certain authentication methods can be specified to instead, upon failure, "fall through" to an authentication record with a lower priority. This can be useful, for example, for using multiple LDAP search attributes.

Authentication fallthrough is disabled by default for new records.

Another way to bypass method priority or implement fallthrough authentication is to implement it on the client instead. For details, see Authentication filtering.

Authentication method compatibility

The following table shows each authentication method's compatibility with fallthrough authentication. A value of "yes" indicates that the method in the column can fall through and validate the row method it falls through to. A value of "no" indicates that you cannot fall through to it and authentication ends.

A value of "-" (dash) indicates that while fallthrough works, it has no meaningful effect. For example, a hash authentication record can fall through to another hash authentication record, but authentication will always fail because you cannot fall through to a correct password:

For example, suppose the user Bob was granted the following authentication record with fallthrough enabled:

=> CREATE AUTHENTICATION v_tls_auth METHOD 'tls' HOST TLS '0.0.0.0/0' FALLTHROUGH;

In addition, Bob has the public role, and therefore has the default authentication records:

=> SELECT auth_name,is_auth_enabled,auth_host_type,auth_method,auth_priority,is_fallthrough_enabled FROM client_auth;
         auth_name         | is_auth_enabled | auth_host_type | auth_method | auth_priority | is_fallthrough_enabled
---------------------------+-----------------+----------------+-------------+---------------+------------------------
 default_hash_network_ipv4 | True            | HOST           | PASSWORD    |            -1 | False
 default_hash_network_ipv6 | True            | HOST           | PASSWORD    |            -1 | False
 default_hash_local        | True            | LOCAL          | PASSWORD    |            -1 | False
(3 rows)

If Bob, connecting from a remote address, fails to authenticate with v_tls_auth, Vertica attempts to authenticate him with the next (in order of priority) authentication record, default_hash_network_ipv4.

Logging authentication failures

Login failures are only logged if the user is rejected after all granted authentication records have failed. Only the final failure is logged.

For example, suppose a user is granted both tls and password authentication records, and the tls record falls through to the password record.

If the user fails to authenticate with both the tls record and the password record, then only the password failure is logged in LOGIN_FAILURES.

However, if the user fails to authenticate with the tls record, but succeeds with the password record, then no failure is logged in LOGIN_FAILURES because the user was not rejected at the end of the record chain.

Examples

To enable fallthrough on a new authentication record, use CREATE AUTHENTICATION:

=> CREATE AUTHENTICATION v_tls_auth METHOD 'tls' HOST TLS '0.0.0.0/0' FALLTHROUGH;

To toggle fallthrough on an existing authentication record, use ALTER AUTHENTICATION:

=> ALTER AUTHENTICATION v_tls_auth NO FALLTHROUGH; -- disable
=> ALTER AUTHENTICATION v_tls_auth FALLTHROUGH; -- enable

4 - Authentication filtering

You can filter for and authenticate with authentication records that use a particular method by specifying the credentials for that method. This process skips any higher-priority, non-trust authentication records.

If the selected record fails and fallthrough is enabled, Vertica uses to the record with the next highest priority.

If you provide the credentials for an authentication method for which you have not been granted, Vertica instead attempts to authenticate you with the highest priority authentication record.

The following table shows the credentials the client provides and the requested authentication method in descending filter priority. If you attempt to provide more than one type of credential, your client selects and sends the one with the higher priority:

For example, if a client provides a username and password for user Alice, and the Alice has oauth, ldap, and hash records, then Vertica attempts to authenticate the client with ldap or hash, whichever has the higher priority.

Similarly, if Alice instead provides an OAuthAccessToken, username, and password, then Vertica attempts to authenticate her with the oauth record because it has a higher filter priority.

However, if Alice provides a KerberosHostname, Vertica attempts to authenticate her with the highest priority authentication record between her oauth, ldap, and hash records.

Client-side fallthrough authentication

A workaround for the incompatibility of certain authentication methods with fallthrough authentication is to use authentication filtering to manually fall through to the proper authentication record by detecting the initial authentication failure, and then making a new connection attempt with a different set of credentials.

For example, oauth and hash cannot fall through to each other, so if a user is granted authentication records with these methods, they can only authenticate with the higher priority record. As a workaround, you can detect the authentication failure with oauth and then filter for hash in another connection attempt. Doing this programmatically depends on the client. In JDBC, you can catch SQLInvalidAuthorizationSpecException and then try to reconnect in the exception handler. In ODBC, you can check the return value of SQLDriverConnect():

// error checking omitted for brevity
    SQLHENV hdlEnv;
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);
    SQLHDBC hdlDbc;
    SQLSetEnvAttr(hdlEnv, SQL_ATTR_ODBC_VERSION,
                (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
    SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc);
    SQLRETURN ret = SQLDriverConnect(hdlDbc, NULL, (SQLCHAR *)("DSN=VerticaDSN;OAuthAccessToken=" + accessToken + ";OAuthRefreshToken=" + refreshToken + ";OAuthClientSecret=" + clientSecret).c_str(), SQL_NTS, NULL, 0, NULL, false);

    if (!SQL_SUCCEEDED(ret))
    {
        std::cout << "Could not connect to database with OAuth, retrying with hash authentication" << std::endl;
        reportError <SQLHDBC>(SQL_HANDLE_DBC, hdlDbc);
        SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc);
        const char* dsnName = "ExampleDB";
        const char* userID = "Alice";
        const char* passwd = "mypassword";
        SQLConnect(hdlDbc, (SQLCHAR*)dsnName,SQL_NTS,(SQLCHAR*)userID,SQL_NTS,(SQLCHAR*)passwd, SQL_NTS);
    }

Examples

In the following example, the user Bob is granted two authentication records: one that uses Kerberos authentication (gss) and another that uses password authentication (hash):

=> SELECT auth_name, auth_host_type, auth_method FROM client_auth;
       auth_name     | auth_host_type | auth_method
---------------------+----------------+-------------
 v_hash              | HOST           | HASH
 v_kerberos          | HOST           | GSS
(2 rows)

1. Bob attempts to authenticate with Kerberos, but fails (the exact error message varies):

   $ vsql -h vertica_db.example.com -K vcluster.example.com -U Bob
   vsql: GSSAPI continuation error: Unspecified GSS failure.  Minor code may provide more information
   GSSAPI continuation error: No Kerberos credentials available (default cache: KEYRING:persistent:0)
   

2. Bob notices the failure. The steps involved in detecting this programmatically depend on your client.

3. An authentication record that uses gss cannot fall through to one that uses hash, so the only way for Bob to authenticate to the database is to filter for the hash by providing the username and password:

   => vsql -h vertica_db.example.com -U Bob -w 'mypassword'
   

To implement client-side fallthrough authentication, check the initial connection/authentication attempt for errors and then create a new connection in the error handler. For example, the user Alice is granted oauth and hash authentication records:

=> SELECT auth_name, auth_host_type, auth_method FROM client_auth;
       auth_name     | auth_host_type | auth_method
---------------------+----------------+-------------
 v_oauth             | HOST           | OAUTH
 v_hash              | HOST           | HASH
(2 rows)

5 - Implicit authentication

Vertica has implicit (that is, not reflected in the CLIENT_AUTH system table) authentication records reserved for the dbadmin and users without passwords. These records cannot be dropped.

For the dbadmin user:

• The trust method is used if all of the following are true:

  • The dbadmin does not have a password.

  • The connection is local (that is, from a Vertica node).

• The hash method is used if all of the following are true:

  • The dbadmin has a password.

  • The connection is local (that is, from a Vertica node).

For a non-dbadmin user, the trust method is used if all of the following are true:

• The user does not have a password.

• No custom (that is, non-default) authentication methods are enabled.

6 - Dbadmin authentication access

The dbadmin user must have access to the database at all times. Vertica automatically ensures that a client can authenticate as the dbadmin from a LOCAL connection.

If you need to authenticate as the dbadmin from a remote connection, the dbadmin must have a password. You can use the following methods:

• Use fallthrough authentication.

• Create a custom, dbadmin-specific authentication method.

Authenticating from a local connection

You can always implicitly authenticate as the dbadmin from a local connection. These dbadmin-specific authentication records are implicit, so they are not listed in the CLIENT_AUTH system table, and cannot be dropped.

If the dbadmin user does not have a password, then Vertica authenticates them with the trust method. Otherwise, Vertica authenticates them with the password method.

In this example, the dbadmin did not have a password and connected to Vertica from a local connection:

=> SELECT authentication_method, client_authentication_name FROM vs_sessions;
 authentication_method | client_authentication_name
-----------------------+----------------------------
 ImpTrust              | default: Implicit Trust

Authenticating from a remote connection

Fallthrough authentication

Vertica automatically creates the following authentication records and grants them to the public role (for details, see Client authentication):

=> SELECT auth_name,is_auth_enabled,auth_host_type,auth_method,auth_priority,is_fallthrough_enabled FROM client_auth;
         auth_name         | is_auth_enabled | auth_host_type | auth_method | auth_priority | is_fallthrough_enabled
---------------------------+-----------------+----------------+-------------+---------------+------------------------
 default_hash_network_ipv4 | True            | HOST           | PASSWORD    |            -1 | False
 default_hash_network_ipv6 | True            | HOST           | PASSWORD    |            -1 | False
 default_hash_local        | True            | LOCAL          | PASSWORD    |            -1 | False
(3 rows)

These default authentication records ensure that all users with the public role (which includes dbadmin) have access to the database, provided that any custom authentication records are set to fall through (disabled by default) to the default records.

For example, the following ldap authentication enables fallthrough, so if the LDAP server is down, users can still authenticate with password authentication (as defined by the default records).

=> CREATE AUTHENTICATION ldap1 METHOD 'ldap' LOCAL FALLTHROUGH;
=> ALTER AUTHENTICATION ldap1 SET host='ldap://localhost:5389',
    binddn='cn=Manager,dc=example,dc=com',
    bind_password='password',
    basedn='ou=dev,dc=example,dc=com',
    search_attribute='cn';

Custom authentication records

A dbadmin-specific authentication record should:

• Use the hash authentication method (so authentication is not dependent on some external service).

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

The following example creates an authentication record v_dbadmin_hash and grants it to the dbadmin user. The hash method indicates that the dbadmin must provide a password when logging in. 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 an 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;

7 - Creating authentication records

You can manage client authentication records with vsql. You must be connected to the database as a supseruser.

1. Create an authentication records, specifying:

   • The name of the authentication record.

   • The authentication method, one of the following:

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

     • reject: Rejects the connection attempt.

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

   • The access method, one of the following, which specify the allowed connection type:

     • LOCAL: Authenticates users or applications that attempt to connect from the same node that the database is running on.

     • HOST: Authentications users or applications that attempt to connect from a node that has a different IPv4 or IPv6 address than the database. You can use TLS or NO TLS to specify an encrypted or plaintext connection, respectively.

   • Whether to enable Fallthrough authentication (disabled by default).

2. Grant the authentication record to a user or role.

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 by contacting the identity provider to validate the OAuth token (rather than a username and password) and uses the following parameters:

• validate_type: The method used to validate the OAuth token. This should be set to IDP (default) to validate the OAuth token for confidential clients.

• client_id: The client in the identity provider.

• client_secret: The client secret generated by the identity provider. This is required if validate_type is IDP.

• discovery_url: Also known as the OpenID Provider Configuration Document, Vertica uses this endpoint to retrieve information about the identity provider's configuration and other endpoints (Keycloak only).

• introspect_url: Used by Vertica to introspect (validate) access tokens. You must specify the introspect_url if you do not specify the discovery_url and are not using JWT validation.

If discovery_url and introspect_url are both set, discovery_url takes precedence. The following example sets both for demonstration purposes; in general, you should prefer to set the discovery_url:

=> CREATE AUTHENTICATION v_oauth METHOD 'oauth' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION v_oauth SET validate_type = 'IDP';
=> 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';

Alternatively, if your identity provider supports the OpenID Connect protocol and your client is public, Vertica can use JWT validation, where Vertica validates OAuth tokens by verifying that it was signed by the identity provider's private key.

Vertica does not contact the identity provider for JWT validation.

JWT validation requires the following parameters:

• validate_type: The method used to validate the OAuth token. This should be set to JWT to validate the OAuth token for public clients.

• jwt_rsa_public_key: In PEM format, the public key used to sign the client's OAuth token. Vertica uses this to validate the OAuth token. If your identity provider does not natively provide PEM-formatted public keys, you must convert them to PEM format. For example, keys retrieved from an Okta endpoint are in JWK format and must be converted.

• jwt_issuer: The issuer of the OAuth token. For Keycloak, this is the token endpoint.

• jwt_user_mapping: The name of the Vertica user.

You can also specify the following parameters to define a whitelist based on fields of the OAuth token:

• jwt_accepted_audience_list: Optional, a comma-delimited list of values to accept from the client JWT's aud field. If set, tokens must include in aud one of the accepted audiences to authenticate.

• jwt_accepted_scope_list: Optional, a comma-delimited list of values to accept from the client JWT's scope field. If set, tokens must include in scope at least one of the accepted scopes to authenticate.

The following authentication record v_oauth_jwt authenticates users from any IP address by verifying that the client's OAuth token was signed by the identity provider's private key. It also requires the user to provide the proper values in the token's aud and scope fields:

=> CREATE AUTHENTICATION v_oauth_jwt METHOD 'oauth' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION v_oauth_jwt SET validate_type = 'JWT';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_rsa_public_key = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAkjXjd6F8PyKkQtY5q2cJ9jNT9y+PeIJS134UvuUnuD9bQFhkBPstTBulpZV1QQivYaQ5k5bYVE2Q7n/XscrWzbRkK/qEwmztjVUH7dQAHSSKEjYcbH1fREx5nR5oNEelrUH2RrGM98Y7ln+Ch4oCl8yCS6gjI6hfaDxwqo2oImmGE+Qi06SIjoWGBCr5EIAhvlNuWe2rWD5uEe4ivaL6KoAR9sADqhGBoaYs/wIVUcv5DHtSjU+yZIz/sVspJehvmb/979eDsct2IddCHLrjUet3DiKz4imIyh+cv9VTEI6MWbk5WIiKW2fFzZ6Oei/ddzmTqVoNdn+d18tWwlf7hQIDAQAB';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_issuer = 'token_issuer';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_user_mapping = 'oauth_user';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_accepted_audience_list = 'vertica,local';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_accepted_scope_list = 'email,profile,user';

For example, to reject all plaintext client connections, specify the reject authentication method and the HOST NO TLS access method as follows:

=> 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

8 - 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

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

There are two ways to authenticate with a record other than that with the highest priority:

• Fallthrough authentication: If authentication fails, Vertica attempts to authenticate the client with the record with the next highest priority.

• Authentication filtering: Clients can send the credentials required for a particular authentication method to authenticate with a record that uses that method.

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

10 - 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

11 - 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

12 - 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;
  

13 - Hiding database usernames

If you want to keep certain database usernames a secret from connecting clients and your authentication records do not use Fallthrough authentication, then these two user groups must share the same single authentication method (not necessarily the same authentication record):

• Users whose usernames must be kept secret.

• Users with the PUBLIC role.

If your authentication records use fallthrough, then ensure that the first authentication method that prompts for a password in the authentication chain is the same for both the secret users and the PUBLIC role. The following methods prompt for a password:

• hash

• ldap

A simple way to satisfy this condition is by duplicating the fallthrough chain for both groups with the same methods. For example, a valid authentication chain would be tls > ldap for both the secret users and the PUBLIC role.

Another valid configuration would be tls > ldap > hash for secret users, and ldap for the PUBLIC role.

14 - Hash authentication

The hash authentication method authenticates users with passwords.

In general, you should prefer other authentication methods over hash.

14.1 - Password hashing algorithm

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

• A system-level configuration parameter, SecurityAlgorithm:

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

• A user-level parameter, SECURITY_ALGORITHM:

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

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

• SHA512 (default)

• MD5

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

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

• SHA512

• MD5

  Note

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

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

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

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

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

14.2 - Configuring hash authentication

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

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

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

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

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

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

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

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

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

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

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

15 - 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;

15.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 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 ubuntu/debian

After you install oidentd on your 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 by setting disable = no in the configuration file /etc/xinetd.d/auth. If this file does not exist, create it. The following is a sample configuration file:

   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
   

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

16 - 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

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

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

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

16.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 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'
   

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

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

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

16.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:

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

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

• ODBC DSN connection properties

• (vsql) Command-line options

16.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 @ VERT.LOCAL
Server: vertica/example.com @ VERT.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

16.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)

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

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

17.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:

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

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

4. Verify that the LDAPAuthConfigParameter parameter is using the TLS Configuration:

   => SHOW CURRENT LDAPAuthTLSConfig;
     level  |       name        | setting
   ---------+-------------------+----------
    DEFAULT | LDAPAuthTLSConfig | LDAPAuth
   (1 row)
   

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.

17.4 - Authentication fallthrough for LDAP

To use multiple search attributes for a single LDAP server or to configure multiple LDAP servers, create a separate authentication record for each search attribute or server and enable authentication fallthrough on each ldap record except the last (in order of priority).

Examples

The following example creates two authentication records, vldap1 and vldap2. Together, they specify that the LDAP server should 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 should then search for a DN with the OU attribute Marketing:

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

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

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

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

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

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

18.1 - Configuring OAuth authentication

For a list of ODBC OAuth connection properties, see ODBC DSN connection properties.

The following procedure performs the following actions:

1. Configures an identity provider for OAuth integration with Vertica (either Okta or Keycloak).

2. Creates an OAuth authentication record.

3. Retrieves an access token with a POST request.

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

Configure the identity provider

Vertica officially tests and supports OAuth integration with Keycloak and Okta. Other identity providers should also work as long as they implement the RFC 7662 Token Introspection standard. The following example configurations are provided for reference:

• Configure Keycloak
• Configure Okta

Create an authentication record

In Vertica, create an authentication record for OAuth. This uses the client ID, client secret, and either the discovery (Keycloak) or introspect (Okta) endpoint used by your identity provider.

The following authentication record v_oauth authenticates users from any IP address by contacting the identity provider to validate the OAuth token (rather than a username and password) and uses the following parameters:

• validate_type: The method used to validate the OAuth token. This should be set to IDP (default) to validate the OAuth token for confidential clients.

• client_id: The client in the identity provider.

• client_secret: The client secret generated by the identity provider. This is required if validate_type is IDP.

• discovery_url: Also known as the OpenID Provider Configuration Document, Vertica uses this endpoint to retrieve information about the identity provider's configuration and other endpoints (Keycloak only).

• introspect_url: Used by Vertica to introspect (validate) access tokens. You must specify the introspect_url if you do not specify the discovery_url and are not using JWT validation.

If discovery_url and introspect_url are both set, discovery_url takes precedence. The following example sets both for demonstration purposes; in general, you should prefer to set the discovery_url:

=> CREATE AUTHENTICATION v_oauth METHOD 'oauth' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION v_oauth SET validate_type = 'IDP';
=> 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';

Alternatively, if your identity provider supports the OpenID Connect protocol and your client is public, Vertica can use JWT validation, where Vertica validates OAuth tokens by verifying that it was signed by the identity provider's private key.

Vertica does not contact the identity provider for JWT validation.

JWT validation requires the following parameters:

• validate_type: The method used to validate the OAuth token. This should be set to JWT to validate the OAuth token for public clients.

• jwt_rsa_public_key: In PEM format, the public key used to sign the client's OAuth token. Vertica uses this to validate the OAuth token. If your identity provider does not natively provide PEM-formatted public keys, you must convert them to PEM format. For example, keys retrieved from an Okta endpoint are in JWK format and must be converted.

• jwt_issuer: The issuer of the OAuth token. For Keycloak, this is the token endpoint.

• jwt_user_mapping: The name of the Vertica user.

You can also specify the following parameters to define a whitelist based on fields of the OAuth token:

• jwt_accepted_audience_list: Optional, a comma-delimited list of values to accept from the client JWT's aud field. If set, tokens must include in aud one of the accepted audiences to authenticate.

• jwt_accepted_scope_list: Optional, a comma-delimited list of values to accept from the client JWT's scope field. If set, tokens must include in scope at least one of the accepted scopes to authenticate.

The following authentication record v_oauth_jwt authenticates users from any IP address by verifying that the client's OAuth token was signed by the identity provider's private key. It also requires the user to provide the proper values in the token's aud and scope fields:

=> CREATE AUTHENTICATION v_oauth_jwt METHOD 'oauth' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION v_oauth_jwt SET validate_type = 'JWT';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_rsa_public_key = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAkjXjd6F8PyKkQtY5q2cJ9jNT9y+PeIJS134UvuUnuD9bQFhkBPstTBulpZV1QQivYaQ5k5bYVE2Q7n/XscrWzbRkK/qEwmztjVUH7dQAHSSKEjYcbH1fREx5nR5oNEelrUH2RrGM98Y7ln+Ch4oCl8yCS6gjI6hfaDxwqo2oImmGE+Qi06SIjoWGBCr5EIAhvlNuWe2rWD5uEe4ivaL6KoAR9sADqhGBoaYs/wIVUcv5DHtSjU+yZIz/sVspJehvmb/979eDsct2IddCHLrjUet3DiKz4imIyh+cv9VTEI6MWbk5WIiKW2fFzZ6Oei/ddzmTqVoNdn+d18tWwlf7hQIDAQAB';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_issuer = 'token_issuer';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_user_mapping = 'oauth_user';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_accepted_audience_list = 'vertica,local';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_accepted_scope_list = 'email,profile,user';

Setting the required parameters automatically enables the authentication record. To manually enable the authentication record:

=> ALTER AUTHENTICATION v_oauth ENABLE;

For a full list of OAuth authentication parameters, see OAuth authentication parameters.

Create a Vertica user

Vertica users map to the identity provider's users with the same username. You can either create the user manually or enable just-in-time (JIT) user provisioning in the authentication record to automatically create users with valid tokens.

To manually create the user:

1. To map to the user oauth_user in the identity provider, 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;
   

To enable JIT user provisioning:

=> ALTER AUTHENTICATION v_oauth SET oauth2_jit_enabled = 'yes';

If the user already exists and JIT user provisioning is enabled and you use Keycloak as your IDP, Vertica automatically assigns the roles associated with the user as specified by the IDP if the roles also exist in Vertica. For details, see Just-in-time user provisioning.

Retrieve an access token

To authenticate to Vertica, you must retrieve an access token from the identity provider.

Programmatic method

A simple way to get an OAuth access token and refresh token is to send a POST request to the token endpoint, providing the credentials of the user. You can then use the returned access token, refresh token, and scope with the oauthaccesstoken and oauthrefreshtoken connection properties for your client. For details, see JDBC connection properties and ODBC DSN connection properties.

For example, to get an access token for oauth_user from Keycloak:

$ 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 string containing the access_token and refresh_token if you authenticated correctly.

{
   "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"
}

Similarly, to retrieve an access token for oauth_user from Okta:

$ curl --insecure -d "client_id=0oa5cgdga1fb812rW697" -d "client_secret=aq22wRl3Z3mmtuoB13omRo6Ql03Ltafet4xYi77p" \
-d "username=oauth_user" -d "password=oauth_user_password" \
-d "grant_type=password" -d "scope=offline_access%20openid" https://example.okta.com/oauth2/default/v1/introspect

{"token_type":"Bearer","expires_in":3600,"access_token":"access_token","id_token":"id_token"}

Single-sign on (SSO)

An alternative to manually retrieving the access token is using SSO through the ODBC client driver. With this method, the ODBC driver opens the default web browser to the IDP's authentication endpoint where the user can enter their credentials. If the user successfully authenticates to the IDP, the ODBC driver automatically retrieves the token and authenticates to Vertica.

To configure and use the ODBC driver for SSO:

1. For clients that use the confidential access type, set the oauthclientsecret.
2. In the authentication record, set the following parameters:
   • The discovery_url (Keycloak only) or the following:
     • auth_url
     • token_url
   • client_id
3. Connect to Vertica with the ODBC driver. The default web browser opens to your IDP's sign-in page.
4. Enter your credentials to authenticate to the IDP.

Run 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 provide a refresh token and a client secret.

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-secret OAuthClientSecret
     

For a list of all ODBC OAuth parameters, see ODBC DSN connection properties.

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-secret oauthclientsecret
     

Troubleshooting

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

Custom CA certificates

A truststore is a container for trusted certificate authority (CA) certificates. These CA certificates are used to verify the identities of other systems when establishing a TLS connection. When your JDBC client connects to the identity provider through an HTTPS endpoint, the JDBC client verifies the identity provider's certificate by making sure that it was issued by a CA in the 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 is not issued by a well-known CA, you must either specify a custom truststore or import the issuer's CA certificate into the system truststore with keytool.

To specify a custom truststore, set the JDBC connection properties oauthtruststorepath and oauthtruststorepassword:

connProps = new Properties(connProps);
connProps.setProperty("oauthtruststorepath", "/path/to/truststore/customoauth.truststore");
connProps.setProperty("oauthtruststorepassword", "password");

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

18.1.1 - Configure Keycloak

The following procedure configures a Keycloak 18.0.0 server on 203.0.113.1 for integration with Vertica. For details, see Configuring OAuth authentication.

The goals of this procedure are to configure Keycloak and obtain the following information:

• Client ID: The ID used to identify the Vertica database. This is configured by the user and set to vertica in the example procedure.
• Client secret: A Keycloak-generated string used to refresh the OAuth token when it expires.
• Discovery endpoint: The endpoint that serves information for all other endpoints as a JSON string. The endpoint for a Keycloak server on 203.0.113.1 is one of the following:
  • https://203.0.113.1:8443/realms/myrealm/.well-known/openid-configuration (if TLS is configured)
  • http://203.0.113.1:8443/realms/myrealm/.well-known/openid-configuration

Configure 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. The following example creates a certificate and key in Vertica:

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 server 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';
   

Start 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, go to Realm Settings > Tokens and increase Access Token Lifespan to a greater value (the default is 5 minutes).

Create the Vertica client

1. Go to Clients and select on Create. The Add Client page appears.

2. In Client ID, enter vertica.

3. Select Save. The client configuration page appears.

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

5. On the Credentials tab, copy the Secret. This is the client secret used to refresh the token when it expires.

Create a Keycloak user

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

1. On the Users tab, select Add user. The Add user page appears.

2. In Username, enter oauth_user.

3. On the Credentials tab, enter a password.

18.1.2 - Configure Okta

The following procedure configures Okta for integration with Vertica and requires administrator privileges. For details, see Configuring OAuth authentication.

The goals of this procedure are to configure Okta and obtain the following information:

• Client ID: The ID used to identify the Vertica database. This is generated by Okta.
• Client secret: An Okta-generated string used to refresh the OAuth token when it expires.
• Token endpoint: Used by the client to retrieve the OAuth token.
• Introspection endpoint: Used by Vertica to validate the OAuth token.

These values are used to create the oauth authentication record in Vertica and act as instructions for Vertica to communicate with Okta when a user attempts to authenticate.

Create an OIDC application

1. From the Okta dashboard, go to Applications > Applications and select Create App Integration. The Create a new app integration dialog box appears.
2. For the Sign-in method, select OIDC - OpenID Connect. The Application type section appears.
3. For the Application type, select Native Application.
4. Select Next. The New Native App Integration window opens.
5. In the App integration name, enter a name for your application. This example uses Demo_Vertica.
6. For the Grant Type, select Authorization Code, Refresh Token, and Resource Owner Password.
7. For Controlled access, select the option applicable to your organization. This example uses Allow everyone in your organization to access.
8. Select Save to save your application.

Retrieve the client ID and client secret

1. From the Okta dashboard, go to Applications > Applications and select the name of your OIDC application.
2. In the General tab in the Client Credentials section, select Edit.
3. For Client authentication, select Client secret and select Save. This generates a new client secret.
4. Copy the client ID and client secret.

Set the authentication policy

1. From the Okta dashboard, go to Applications > Applications and select the name of your OIDC application.
2. In the Sign On tab in the User authentication section, select Edit.
3. Select Password only.
4. Select Save to save the new policy.

Retrieve Okta endpoints

1. From the Okta dashboard, go to Security > API.
2. In the Authorization Servers tab, select the name of your authorization server. By default, the name of this server is default.
3. Select the Metadata URI to get a list of all endpoints for your authorization server as a json string.
4. Copy the values for the token_endpoint and introspection_endpoint.

Test the configuration

1. Verify that you can retrieve an OAuth token from the token endpoint. If successful, Okta respond with an access_token and refresh_token:

   $ curl --insecure -d "client_id=client_id" -d "client_secret=client_secret" -d "username=okta_username" -d "password=okta_password" -d "grant_type=password" -d "scope=offline_access%20openid" token_endpoint
   

2. Verify that the tokens are valid with the introspection endpoint. If successful, Okta responds with a json string containing "active":true:

   To verify the access token:

   $ curl --insecure -d "client_id=client_id" -d "client_secret=client_secret" -d "token=access_token" introspection_endpoint
   

   Similarly, to verify the refresh token:

   $ curl --insecure -d "client_id=client_id" -d "client_secret=client_secret" -d "token=refresh_token" introspection_endpoint
   

   The access_token and refresh_token can then be used for the oauthaccesstoken and oauthrefreshtoken parameters. For details, see JDBC connection properties and ODBC DSN connection properties.

18.2 - Just-in-time user provisioning

Just-in-time (JIT) user provisioning is the act of automatically configuring an authenticated user and their roles based on information provided by the identity provider.

When a client uses an OAuth authentication record that enables JIT user provisioning, Vertica automatically performs the following actions:

1. Creates the user if they do not already exist in the database. The length of the username in the identity provider cannot be greater than 128 characters.

2. (Keycloak only) Grants to the user and sets as default the roles associated with the user (as specified by the identity provider), provided the roles already exist in Vertica.

3. Grants to the user the authentication record used to authenticate them if neither their user nor role has a grant on that record.

For example, if a client presents an OAuth token to authenticate as user Alice with role director, and Alice does not exist in Vertica, Vertica automatically creates the user Alice, grants to her the authentication record, and grants to her the director role as a default role.

To view users created by JIT user provisioning and which authentication record they use, query the USERS system table:

=> SELECT user_name, managed_by_oauth2_auth_id FROM users;
 user_name |  managed_by_oauth2_auth_id
-----------+-----------------------------
 dbadmin   |
 Bob       | 45035996273853300
 Margie    |
 Alice     | 45035996273866484
(4 rows)

For details on using JIT user provisioning with OAuth authentication, see Configuring OAuth authentication.

Enabling just-in-time user provisioning

JIT user provisioning is enabled at the authentication record level by setting the oauth2_jit_enabled parameter:

=> CREATE AUTHENTICATION v_oauth METHOD 'oauth' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION v_oauth SET oauth2_jit_enabled = 'yes';

=> SELECT auth_name, is_oauth2_jit_enabled FROM client_auth WHERE auth_name='v_oauth';
  auth_name  |  is_oauth_2_jit_enabled
-------------+--------------------------
 v_oauth     | True
(1 row)

Automatic role assignment

The roles automatically assigned to JIT-provisioned active users are based on the information provided by the identity provider (either from the endpoints introspect_url or userinfo_url) and are identified by the client/application specified by the OAuth2JITClient configuration parameter ( vertica by default):

=> ALTER DATABASE DEFAULT SET OAuth2JITClient = "vertica";

With OAuth2JITClient set to vertica, roles that both exist in Vertica and are listed in resource_access.vertica.roles are automatically granted to and set as default roles for JIT-provisioned users.

The identity provider shares user roles through the introspect_url or userinfo_url endpoints. Vertica first sends a request to the introspect_url. If no roles are found in the response, Vertica sends a request to userinfo_url.

For example, if a client attempts to authenticate as the user bob and no user with the same name exists in the database, Vertica sends the following request to the identity provider's introspect_url to retrieve the roles given to bob by the identity provider (this example is truncated):

{
    ...
    "resource_access": {
        "vertica": {
            "roles": [
                "customer-facing",
                "order-management",
                "idp-exclusive-role"
            ]
        },
        "account": {
            "roles": [
                "manage-account",
                "manage-account-links",
                "view-profile"
            ]
        }
    },
    "scope": "email profile roles",
    "sid": "dcdd14b1-fe47-491e-b62b-10d1e05c6ffe",
    "client_id": "vertica",
    "username": "bob",
    "active": true
}

Because the active field is true and bob has no corresponding user in the Vertica server, Vertica automatically creates the user bob and grants to him the roles that exist in Vertica and are listed in resource_access.vertica.roles: customer-facing and order-management. The role idp-exclusive-role does not exist in Vertica, so it is ignored.

Automatic user pruning

You can enable automatic user pruning to periodically drop users created by JIT user provisioning if they do not log in after a certain period of time. This cleanup service is managed by the following database-level configuration parameters:

• EnableOAuthJITCleanup: Whether to enable cleanup (disabled by default).

  => ALTER DATABASE DEFAULT SET EnableOAuthJITCleanup = 1; --enables the pruning service
  => ALTER DATABASE DEFAULT SET EnableOAuthJITCleanup = 0; --disables the pruning service
  

• OAuth2UserExpiredInterval: The number of days a user must be inactive before it is dropped (14 by default). This is calculated based on the current date and the LAST_LOGIN_TIME in the USERS system table.

  Note

  The LAST_LOGIN_TIME as recorded by the USERS system table is not persistent; if the database is restarted, the LAST_LOGIN_TIME for users created by just-in-time user provisioning is set to the database start time (this appears as an empty value in LAST_LOGIN_TIME).

  You can view the database start time by querying the DATABASES system table:

  => SELECT database_name, start_time FROM databases;
   database_name |          start_time
  ---------------+-------------------------------
   VMart         | 2023-02-06 14:26:50.630054-05
  (1 row)
  

  => ALTER DATABASE DEFAULT SET OAuth2UserExpiredInterval = 20;
  

• GlobalHeirUsername: The user to reassign objects to if the owner is a JIT-provisioned (or LDAP) user that got dropped by the pruning service. If set to <auto>, objects are reassigned to the dbadmin.

  => ALTER DATABASE DEFAULT SET GlobalHeirUsername = <auto>
  

The cleanup service runs daily and there can be a delay of up to 24 hours for dropping an expired user.

18.3 - OAuth authentication parameters

Vertica OAuth authentication records use the following parameters to determine how to validate client OAuth tokens and how to contact the identity provider during the validation process. These parameters should be set with ALTER AUTHENTICATION.

Just-in-time provisioning parameters

The optional oauth2_jit_enabled parameter specifies whether to enable just-in-time user provisioning. If set to 'yes', when the user authenticates, Vertica automatically performs the following actions:

1. Creates the user if they do not already exist in the database. The length of the username in the identity provider cannot be greater than 128 characters.

2. (Keycloak only) Grants to the user and sets as default the roles associated with the user (as specified by the identity provider), provided the roles already exist in Vertica.

3. Grants to the user the authentication record used to authenticate them if neither their user nor role has a grant on that record.

If set to 'no' (default), users must be manually created and granted an oauth authentication record to authenticate to Vertica with OAuth tokens.

Validation modes

OAuth authentication records have two modes for validating OAuth tokens, each specified with the authentication parameter validate_type.

The validate_type parameter takes one of the following values:

• IDP (default): Validate OAuth tokens by contacting the identity provider. This validation type requires the client to specify their client secret. This should be used with confidential clients (set for each client by the identity provider).

• JWT: Validate OAuth tokens by verifying that it was signed by the identity provider's private key. This does not require Vertica to contact the identity provider for validation and should be used for public clients (set for each client by the identity provider).

Each validation mode uses a different set of parameters, which are detailed in the tables below.

IDP validation parameters

The following table lists the parameters used to configure OAuth authentication records that use the IDP validation mode:

JWT validation parameters

The following table lists the parameters used to configure OAuth authentication records that use the JWT validation mode:

19 - TLS authentication

The tls authentication method authenticates users that can establish a mutual mode client-server TLS connection using a certificate that specifies a valid database username in the Common Name (CN) field.

Before you create and use a tls authentication method, you must configure Vertica for client-server TLS in mutual mode (disabled by default).

19.1 - Client authentication with TLS

Database users or roles granted a tls authentication record can authenticate to Vertica with a TLS certificate.

Prerequisites

You must configure Vertica for mutual mode client-server TLS.

In mutual mode, the client and server must verify each other's identity before connecting. This mode allows Vertica to verify the identity of the client and allow them to authenticate the client through their certificate.

Configuring TLS authentication

The following sections generate a private key and certificate for the client. For simplicity, the example signs the client certificate with the following self-signed CA certificate (which has also, in the context of the example, signed the Vertica database's server certificate):

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

In a production environment, you should instead use a CA certificate from a trusted certificate authority.

Create client keys

The following steps generate a client key and certificate, and then make them available to the client:

1. Generate the client key:

         
      => CREATE KEY client_private_key TYPE 'RSA' LENGTH 2048;
      CREATE KEY
      
   

2. Generate the client certificate. Mutual TLS requires that the Common Name (CN) in the SUBJECT specifies a database username:

         
      => CREATE CERTIFICATE client_certificate
      SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=dbadmin/emailAddress=example@example.com'
      SIGNED BY ca_certificate
      EXTENSIONS 'nsComment' = 'Vertica client cert', 'extendedKeyUsage' = 'clientAuth'
      KEY client_private_key;
      CREATE CERTIFICATE
      
   

3. On the client machine, export the client key and client certificate to the client filesystem. The following commands use the vsql client:

   $ vsql -At -c "SELECT key FROM cryptographic_keys WHERE name = 'client_private_key';" -o client_private_key.key
   $ vsql -At -c "SELECT certificate_text FROM certificates WHERE name = 'client_certificate';" -o client_cert.pem
   

   In the preceding command:

   • -A: enables unaligned output.
   • -t: prevents the command from outputting metadata, such as column names.
   • -c: instructs the shell to run one command and then exit.
   • -o: writes the query output to the specified filename.

   For details about all vsql command line options, see Command-line options

4. Copy or move the client key and certificate to a location that your client recognizes.

   The following commands move the client key and certificate to the hidden directory ~/.client-creds, and then grants the file owner read and write permissions with chmod:

   $ mkdir ~/.client-creds
   $ mv client_private_key.key ~/.client-creds/client_key.key
   $ mv client_cert.pem ~/.client-creds/client_cert.pem
   $ chmod 600 ~/.client-creds/client_key.key ~/.client-creds/client_cert.pem
   

Create an authentication record

Next, you must create an authentication record in the database. An authentication record defines a set of authentication and the access methods for the database. You grant this record to a user or role to control how they authenticate to the database:

1. Create the authentication record. The tls method requires that clients authenticate with a certificate whose Common Name (CN) specifies a database username:

         
      => CREATE AUTHENTICATION auth_record METHOD 'tls' HOST TLS '0.0.0.0/0';
      CREATE AUTHENTICATION
      
   

2. Grant the authentication record to a user or to a role. The following example grants the authentication record to PUBLIC, the default role for all users:

         
      => GRANT AUTHENTICATION auth_record TO PUBLIC;
      GRANT AUTHENTICATION
      
   

Reject plaintext connections

You can create an authentication record that rejects remote connections from a specified IP range.

For example, to reject all plaintext client connections, specify the reject authentication method and the HOST NO TLS access method as follows:

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