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 Data source name (DSN) connection properties.

The following procedure:

Configures Keycloak 18.0.
Creates an OAuth authentication record.
Retrieves an access token with a POST request.
Uses sample applications to authenticate to Vertica, passing the access token as an argument and, optionally, parameters for token refresh.

Configuring Keycloak

The following procedure configures a Keycloak 18.0.0 server on 203.0.113.1.

Using TLS (optional)

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

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

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

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

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

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

Set the SystemCABundlePath configuration parameter:

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

Starting Keycloak

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

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
Sign in as the admin.
(Optional) To make testing OAuth more convenient, navigate to Realm Settings > Tokens and increase Access Token Lifespan to a greater value (the default is 5 minutes).

Creating the Vertica client

Navigate to Clients and click on Create. The Add Client page appears.
In Client ID, enter vertica.
Click Save. The client configuration page appears.
In the Settings tab, use the Access Type dropdown to select confidential.
In the Credentials tab, make a note of the Secret. This is the client secret used to refresh the token when it expires.

Creating a Keycloak user

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

In the Users tab, click Add user. The Add user page appears.
In Username, enter oauth_user.
In the Credentials tab, enter a password.

Configuring Vertica

Creating the authentication record

Create an authentication record for OAuth.

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

client_id: The confidential client, vertica, registered in Keycloak.
client_secret: The client secret, generated by Keycloak.
discovery_url: Also known as the OpenID Provider Configuration Document, this is the endpoint that contains information about the identity provider's configuration and endpoints.

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

Creating a Vertica user

Vertica users map to Keycloak users with the same username.

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

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

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

Retrieving an access token

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

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

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

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

Running the sample applications

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

Refresh token
Client ID
Client secret
Token URL

ODBC

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-id OAuthClientID
    --client-secret OAuthClientSecret
    --token-url OAuthTokenURL

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

Troubleshooting

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

Importing CA certificates into the Java truststore

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

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

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