Enabling OAuth Authentication in Vertica to Connect to Toad Data Point

Along with other methods of authentication, Vertica also supports OAuth authentication. This document provides an overview of the protocols and technologies used in connecting Toad Data Point to Vertica using OAuth authentication.

Open Authorization is an open standard protocol used for Authorization and Authentication. It allows you to grant access to the application without sharing user credentials.

Keycloak is an open-source identity and access management (IAM) solution that can act as an OAuth server. It provides the necessary infrastructure and functionality to implement OAuth-based authentication and authorization workflows. Keycloak handles the generation of access tokens, verifies user identities, and enforces access policies for protected resources. It acts as an OAuth server by managing client applications, user authentication, and authorization grants.

Toad Data Point is a data analysis and querying tool. It simplifies and enhances the process of accessing, querying, and analysing data from various sources.

Toad Data Point uses Vertica ODBC driver to connect to Vertica. For more details on connecting Toad Data Point with Vertica, see Vertica Integration with Toad Data Point: Connection Guide.

Test Environment

Vertica 23.3

Vertica ODBC 23.3

Toad Data Point 6.0.5

Keycloak 19.0.2

Connecting Toad Data Point to Vertica via OAuth Authentication

You can connect Toad Data Point to Vertica via OAuth Authentication. To do this, you can first authenticate to Vertica by generating an OAuth token using Keycloak. In this example, we use jwt as the validation type. After OAuth is enabled, you must create a DSN with additional steps that are specified in the following sections. You can use this DSN to connect to Toad Data Point.

Generating OAuth for Vertica in Keycloak

Following are steps to generate OAuth token in Keycloak:

  1. Login into Keycloak.

  2. Create a Realm.

  3. Create a user and follow these steps for the user:

    1. Enter details in all the required fields.

    2. Enable the user from the Enabled switch.

    3. Note down the password.

    4. Map user with pseudosuperuser role.

  4. Copy RS256 key from Realm Settings > Keys > Click Public Key > Copy RS256 key.

  5. Copy EndpointName: Open Realm Settings > General tab > OpenID Endpoint Configuration link.

Enabling OAuth in Vertica

The following steps enable OAuth in Vertica. Run the following queries in Vertica database:

  1. DROP AUTHENTICATION IF EXISTS oauthjwt CASCADE;

  2. CREATE AUTHENTICATION oauthjwt METHOD 'oauth' HOST '0.0.0.0/0';

  3. ALTER AUTHENTICATION oauthjwt SET validate_type = 'JWT';

  4. ALTER AUTHENTICATION oauthjwt SET jwt_issuer = 'http://<keycloak_IP>:8080/realms/<Realm_Name>';

  5. ALTER AUTHENTICATION oauthjwt SET jwt_rsa_public_key = '-----BEGIN PUBLIC KEY----- <PUBLIC_KEY Copied Above> -----END PUBLIC KEY-----';

  6. ALTER AUTHENTICATION oauthjwt SET jwt_user_mapping = 'preferred_username';

  7. ALTER AUTHENTICATION oauthjwt SET oauth2_jit_enabled = 'yes';

  8. ALTER DATABASE default set OAuth2JITClient = '<ClientName>';

  9. ALTER AUTHENTICATION oauthjwt SET client_id = '<ClientName>';

  10. ALTER AUTHENTICATION oauthjwt SET discovery_url = 'http://<Keycloak_Server>:8080/realms/<Realm_Name>/.well-known/openid-configuration';

  11. create user <Username>; (This user is same as created in Keycloak)

  12. GRANT AUTHENTICATION oauthjwt TO <username>;

Creating Vertica ODBC DSN:

  1. Install the Vertica ODBC driver.

  2. Open 64-bit ODBC DSN.

  3. Add Vertica DSN and provide the following details:

    1. DSN Name

    2. Database

    3. Server

    4. Port

    5. User Name

  4. Save the DSN.

  5. Open regedit and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<DSN_Name>

  6. Right-click in the right pane where all the details of the DSN are specified and select option New > String Value.

  7. Provide the name of the object as “OAuthJsonConfig” and its value should be “{"oauthtokenurl":"http://<Keycloak_IP>:8080/realms/<Realm_Name>/protocol/openid-connect/token","oauthauthurl":"http://<Keycloak_IP>:8080/realms/<Realm_Name>/protocol/openid-connect/auth","oauthclientid":"<Client_Name>", "oauthclientsecret": "<Client_Credentials>", "oauthscope": "offline_access openid", "oauthvalidatehostname": "false"}”

    1. Client Credentials are available in keycloak > Clients > Credentials tab.

  8. Now if you open Vertica DSN and save it, the registry entry made will be removed. You will need to add the key again.

Connecting OAuth Enabled Vertica to Toad Data Point

  1. Open Toad Data Point.

  2. Click Connect and select New Connection.

  3. Select Vertica from the Group drop-down.

  4. Select the recently created DSN.

  5. Click Connect.

  6. Keycloak page opens. Provide UserName and Password created in Keycloak.

    A success notification appears.

  7. Switch back to Toad Data Point. You should now be connected to Vertica.