Configuring OAuth authentication

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

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:

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 by contacting the identity provider.

  • client_id: The client in the identity provider.

  • client_secret: The client secret generated by the identity provider.

  • 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 JSON Web Token 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 validation method, IDP by default. Setting this to JWT enables JWT validation.

  • 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. This value is set by the identify provider.

  • 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. Set the OAuthJsonConfig JSON string with, minimally, the following parameters. For details on each, see ODBC DSN connection properties:
    • oauthtokenurl
    • oauthauthurl
    • oauthclientid
    • oauthclientsecret (if your client uses the confidential access type)
  2. Connect to Vertica with the ODBC driver. The default web browser opens to your IDP's sign-in page.
  3. 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 specify the following. The sample applications put these into a JSON string, OAuthJsonConfig or (ODBC) oauthjsonconfig (JDBC).

  • Refresh token

  • Client ID

  • Client secret

  • Token URL

ODBC

  1. Follow the instructions in the README.

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

    • To authenticate until the token expires:

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

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

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

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-id oauthclientid
    --client-secret oauthclientsecret
    --token-url oauthtokenurl"

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

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

In this section