This is the multi-page printable view of this section. Click here to print.
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.
For a list of ODBC OAuth connection properties, see ODBC DSN 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 with a POST request.
Uses a sample application to authenticate to Vertica, passing the access token as an argument and, optionally, parameters for token refresh.
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:
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.
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.
To authenticate to Vertica, you must retrieve an access token from the identity provider.
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"}
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:
confidential access type, set the oauthclientsecret.discovery_url (Keycloak only) or the following:
auth_urltoken_urlclient_idThe 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.
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.
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
To get debugging information for TLS, use the -Djavax.net.debug=ssl flag.
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
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:
vertica in the example procedure.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-configurationIf 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:
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 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
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';
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, go to Realm Settings > Tokens and increase Access Token Lifespan to a greater value (the default is 5 minutes).
Go to Clients and select on Create. The Add Client page appears.
In Client ID, enter
vertica.
Select Save. The client configuration page appears.
On the Settings tab, use the Access Type dropdown to select confidential.
On the Credentials tab, copy the Secret. This is the client secret used to refresh the token when it expires.
Keycloak users map to Vertica users with the same name. This example creates a the Keycloak user oauth_user.
On the Users tab, select Add user. The Add user page appears.
In Username, enter oauth_user.
On the Credentials tab, enter a password.
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:
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.
json string.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
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.
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:
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.
(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.
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.
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)
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.
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.
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.
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.
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:
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.
(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.
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.
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.
The following table lists the parameters used to configure OAuth authentication records that use the IDP validation mode:
| Parameter name | Description | Required/Optional |
|---|---|---|
client_id |
The ID of the confidential client application registered in the identity provider. Vertica uses this ID to call the introspection API to retrieve user grants. | Required |
client_secret |
The secret of the confidential client application registered in the identity provider. This value is not shared with other clients. | Required |
discovery_url (Keycloak only) |
Also known as the OpenID Provider Configuration Document or the well-known configuration endpoint, this endpoint contains information about the configuration and endpoints of the identity provider. If you set the |
Required for IDP validation if introspect_url is not specified. |
introspect_url |
Used by Vertica to introspect (validate) access tokens. You must specify this parameter if you do not specify the discovery_url. For examples, see the Keycloak and Okta documentation. |
Required if discovery_url is not specified. |
auth_url |
The authorization endpoint for your identity provider. | Required for single-sign on (SSO) |
token_url |
The token endpoint for your identity provider. | Required for single-sign on (SSO) |
scope |
The requested OAuth scopes, delimited with spaces. These scopes define the extent of access to the resource server (in this case, Vertica) granted to the client by the access token. For details, see the OAuth documentation. | Optional |
validate_hostname |
Boolean, whether Vertica verifies the Hostname verification is enabled by default. |
Optional |
The following table lists the parameters used to configure OAuth authentication records that use the JWT validation mode:
| Parameter name | Description | Required/Optional |
|---|---|---|
jwt_rsa_public_key |
In PEM format, the public key that corresponds to the private 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. | Required |
jwt_issuer |
The issuer of the OAuth token. This value is set by the identify provider. | Required |
jwt_user_mapping |
The name of the Vertica user. | Required |
jwt_accepted_audience_list |
A comma-delimited list of values to accept from the client OAuth token's aud field. If set, tokens must include in aud one of the accepted audiences to authenticate. |
Optional |
jwt_accepted_scope_list |
A comma-delimited list of values to accept from the client OAuth token's scope field. If set, tokens must include in scope at least one of the accepted scopes to authenticate. |
Optional |