Creating authentication records

You can manage client authentication records with vsql.

You can manage client authentication records with vsql. You must be connected to the database as a supseruser.

  1. Create an authentication records, specifying:

    • The name of the authentication record.

    • The authentication method, one of the following:

      • trust: Users can authenticate with a valid username (that is, without a password).

      • reject: Rejects the connection attempt.

      • hash: Users must provide a valid username and password. For details, see Hash authentication.

      • gss: Authorizes clients that connect to Vertica with an MIT Kerberos implementation. The Key Distribution Center (KDC) must support Kerberos 5 using the GSS-API. Non-MIT Kerberos implementations must use the GSS-API. For details, see Kerberos authentication.

      • ident: Authenticates the client against a username on an Ident server. For details, see Ident authentication.

      • ldap: Authenticates a client and their username and password with an LDAP or Active Directory server. For details, see LDAP authentication.

      • tls: Authenticates clients that provide a certificate with a Common Name (CN) that specifies a valid database username. Vertica must be configured for mutual mode TLS to use this method. For details, see TLS authentication

      • oauth: Authenticates a client with an access token. For details, see OAuth 2.0 authentication.

    • The access method, one of the following, which specify the allowed connection type:

      • LOCAL: Authenticates users or applications that attempt to connect from the same node that the database is running on.

      • HOST: Authentications users or applications that attempt to connect from a node that has a different IPv4 or IPv6 address than the database. You can use TLS or NO TLS to specify an encrypted or plaintext connection, respectively.

    • Whether to enable Fallthrough authentication (disabled by default).

  2. Grant the authentication record to a user or role.

Examples

The following examples show how to create authentication records.

Create authentication method localpwd to authenticate users who are trying to log in from a local host using a password:

=> CREATE AUTHENTICATION localpwd METHOD 'hash' LOCAL;

Create authentication method v_ldap that uses LDAP over TLS to authenticate users logging in from the host with the IPv4 address 10.0.0.0/23:

=> CREATE AUTHENTICATION v_ldap METHOD 'ldap' HOST TLS '10.0.0.0/23';

Create authentication method v_kerberos to authenticate users who are trying to connect from any host in the networks 2001:0db8:0001:12xx:

=> CREATE AUTHENTICATION v_kerberos METHOD 'gss' HOST '2001:db8:1::1200/56';

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

For example, to reject all plaintext client connections, specify the reject authentication method and the HOST NO TLS access method as follows:

=> CREATE AUTHENTICATION RejectNoSSL METHOD 'reject' HOST NO TLS '0.0.0.0/0';  --IPv4
=> CREATE AUTHENTICATION RejectNoSSL METHOD 'reject' HOST NO TLS '::/0';       --IPv6

See also