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, Okta, and OTDS but other providers should work if they support the RFC 7662 Token Introspection standard.
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.
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 = '-----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:
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 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:
Keycloak responds with a JSON string containing the access_token and refresh_token if you authenticated correctly:
Similarly, to retrieve an access token for oauth_user from Okta:
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. This token is retrieved by the client application and then set as a connection property in the driver.
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
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.
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.
Go to Client scopes > Client scope details > Mapper details, and toggle Full group path to Off. Vertica does not support subgroups.
(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:
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:
Similarly, to verify the refresh token:
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 (IdP).
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 exist in the database. The username must conform to conventions described in Identifiers.
If you configure automatic role assignment, Vertica identifies roles that exist in both the user or group identity and the database and grants each role to the Vertica database user as a default role.
Grants to the user the authentication record that is associated with the IdP if the user or role does not already have a grant on that authentication record.
You can view all JIT-provisioned users and their authentication record with the USERS system table. If the user was JIT-provisioned, then the managed_by_oauth2_auth_id column lists the JIT-enabled authentication record:
When Vertica authenticates a JIT-provisioned user, it loops through all existing OAuth authentication records for a record that can authenticate the OAuth token in the client request. If Vertica locates the authentication record, it assigns the record to the JIT-provisioned user.
After you create an OAuth authentication record, you must alter its authentication record to enable JIT. To enable JIT, set the oauth2_jit_enabled OAuth authentication parameter to yes:
To confirm that JIT authentication is enabled on the authentication record, query the CLIENT_AUTH_PARAMS table:
Vertica can automatically assign a role to a JIT-provisioned user if the OAuth token contains the following:
Vertica updates the role assignment each time a JIT-provisioned user authenticates with the OAuth token. For example, if Vertica automatically assigned a role using the group identity name and then that group identity is removed from the IdP, Vertica revokes that role the next time that JIT-provisioned user authenticates to Vertica. In addition, you can add a role to a blocklist that prevents Vertica from automatically granting that role during JIT provisioning. These features ensure that Vertica reflects up-to-date role assignments so that you can manage your database user identities from a single, secure location.
An IdP manages Vertica as a client identity. An IdP client can have zero or more roles, where each role has specific privileges in the client environment. IdPs also manage group identities, where each group identity is a collection of user identities that share attributes and roles.
During automatic role assignment, Vertica treats each IdP client role, group role, and group name the same. If the JIT-provisioned user has a client role, group role, or group name that maps to a Vertica role, Vertica automatically assigns that role to the JIT-provisioned user. A single OAuth token can contain both client and group identities.
To associate a database with an IdP client or group identity, set the corresponding security parameter:
OAuth2JITRolesClaimName identifies the Vertica client identity in an IdP that contains the role mappings:
OAuth2JITGroupsClaimName identifies a group identity that contains Vertica client role mappings:
Vertica automatically assigns a role only if the database security parameter setting exactly matches an IdP client or group claim name. If there is a match, then Vertica looks at the match in the IdP. If there is a client role, group name, or group role that exactly matches an existing Vertica database role, then Vertica grants the database role to the JIT-provisioned user. Vertica ignores any roles or groups that do not have exact matches in Vertica.
Vertica can assign any role to a JIT-provisioned user, including DBADMIN and PSEUDOSUPERUSER. Because granting unrestricted privileges might raise security concerns, you can restrict Vertica role assignments with the OAuth2JITForbiddenRoles security parameter:
When an IdP user has a role or group mapping that matches a role in OAuth2JITForbiddenRoles, Vertica does not grant the JIT-provisioned user that role.
To verify which roles were automatically assigned to JIT-provisioned users, you can query the vs_users table. The num_jit_roles column lists the number of roles that were assigned with JIT provisioning, and oauth2_jit_roles list the names of each role:
In this example scenario, an organization uses the Keycloak IdP to manage user identities across their organization. The Keycloak implementation has a client identity named vertica and a group identity named groups. The following table lists the client roles, IdP groups, and group roles:
vertica client roles |
groups name |
groups roles |
|---|---|---|
|
dbadmin orders_user view_realm |
realm_admin |
user_admin |
Vertica has a database named orders with the following security parameter settings that define the roles claim, group claim, and restricted roles:
Vertica has the following users:
Vertica has the following assigned roles:
A client authenticates to the orders database as a user named Alice, but the Alice user does not exist in Vertica. The authentication record associated with the orders database uses IDP validation and has oauth2_jit_enabled set to yes, so Vertica can JIT provision the Alice user and then assign roles that exist in both the IdP and Vertica.
When Alice authenticates to the orders database, Vertica performs the following steps:
Alice user.OAuth2JITRolesClaimName and OAuth2JITGroupsClaimName security parameter settings. In this case, Vertica requests roles for a client identity named vertica and a group identity named groups.orders_user role to the JIT-provisioned Alice user, and sets it as a default role.user_admin role to the JIT-provisioned Alice user, and sets it as a default role.dbadmin role to Alice because it is specified by the OAuth2JITForbiddenRoles security parameter.realm_admin group name and view_admin group role because there is no corresponding role in Vertica.After the automatic role assignment process completes, Alice has default roles that reflect the IdP configuration:
The automatic role assignment workflow occurs each time a client authenticates as the Alice user. This ensures that Vertica role assignments reflect the current role assignments in the IdP.
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:
=> 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 uses 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 following parameters configure just-in-time user provisioning (JIT):
groups_claim_nameOAuth2JITGroupsClaimName security parameter.oauth2_jit_enabled'yes': Vertica uses automatic role assignment.'no' or unset: You must manually create each user and grant the user an oauth authentication record before they can authenticate to Vertica with OAuth tokens.oauth2_jit_authorized_rolesThis parameter accepts one or more roles as a comma-separated list.
role_group_suffixAppends a value to the role or group name associated with the OAuth record. Vertica uses this value to identify users during authorization. Vertica recommends that you append your subscription ID in the following format: @subscriptionID.
roles_claim_nameOAuth2JITRolesClaimName security parameter.OAuth authentication records have two modes for validating OAuth tokens, each specified with the authentication parameter validate_type.
validate_type accepts 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 sections below.
The following parameters configure OAuth authentication records that use the IDP validation mode:
client_idclient_secretdiscovery_urlIDP validation if introspect_url is not specified.
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 discovery_url, you do not have to set any other endpoint parameters. If you specify discovery_url and other endpoints, the discovery_url takes precedence.
introspect_urldiscovery_url is not specified. 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.auth_urltoken_urlscopevalidate_hostnamesubjectAltName of the identity provider host when establishing a connection. If enabled, the IP address or hostname of the identity provider must be set as the subjectAltName in its certificate. This parameter only applies to connections made by JDBC clients.
Hostname verification is enabled by default.
The following parameters configure OAuth authentication records that use the JWT validation mode:
jwt_rsa_public_keyjwt_issuerjwt_user_mappingjwt_accepted_audience_listaud field. If set, tokens must include in aud one of the accepted audiences to authenticate.jwt_accepted_scope_listscope field. If set, tokens must include in scope at least one of the accepted scopes to authenticate.