Configuring OAuth authentication

For a list of ODBC OAuth connection properties, see ODBC OAuth Connection Properties.

The following procedure performs the following actions:

Configures an identity provider for OAuth integration with Vertica (either Okta or Keycloak).
Creates an OAuth authentication record.
Retrieves an access token from the identity provider with a POST request.
Passes the access token to a sample application, which then authenticates to Vertica.

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:

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 = '-----BEGIN PUBLIC KEY-----public-key-value-----END PUBLIC KEY-----';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_issuer = 'http://10.20.30.40:8080/realms/realm_name';
=> ALTER AUTHENTICATION v_oauth_jwt SET jwt_user_mapping = 'preferred_username';
=> 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:

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

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

For clients that use the confidential access type, set the oauthclientsecret.
In the authentication record, set the following parameters:
- The discovery_url (Keycloak only) or the following:
  - auth_url
  - token_url
- client_id
Connect to Vertica with the ODBC driver. The default web browser opens to your IDP's sign-in page.
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. This token is retrieved by the client application and then set as a connection property in the driver.

ODBC

Follow the instructions in the README.
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

Follow the instructions in the README.

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

ADO.NET

The ADO.NET driver uses a simplified configuration scheme with a single connection property: OAuthAccessToken. Other flows like token refresh should be handled externally by the driver. The sample application in the following example demonstrates how to pass an access token to the ADO.NET driver and how to handle token refresh:

Follow the instructions in the README.
Run the sample application.

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