This is the multi-page printable view of this section.
Click here to print.
Return to the regular view of this page.
Client authentication
Authentication records and their associated methods define what credentials a user/client application must provide to access the database.
Authentication records and their associated methods define what credentials a user/client application must provide to access the database. For example, the hash
authentication method requires users to provide a password, while the oauth
authentication method requires users to provide an access token.
Client authentication overview
Vertica uses the following procedure to authenticate users:
-
If a client attempts to authenticate as the dbadmin from a local connection (that is, on the same node as the database):
-
If the dbadmin does not have a password, Vertica authenticates the client with the trust
method.
-
If the dbadmin has a password, Vertica authenticates the client with the hash
method.
-
If a client attempts to authenticate as a database user that does not have a password, and the only authentication records defined are the defaults, then Vertica authenticates the client with the trust
method. For details, see Implicit authentication.
-
If a client specifies the credentials for a particular authentication method, Vertica filters for granted authentication records that use that method, skipping higher priority authentication records (except TRUST, which is not skippable). However, if the client sends credentials that correspond with an authentication record that they do not have, Vertica uses the record with the highest priority.
-
If a client attempts to authenticate as a user that has a password and an authentication record, then Vertica attempts to authenticate the client with that record. If more than one authentication record exists for the user or role, Vertica chooses the one with the highest priority.
Note
Typically, if no custom authentication records were defined with
CREATE AUTHENTICATION, the default authentication records take effect (unless they were deliberately dropped by the dbadmin), and clients are authenticated with the
password
method.
-
If the client fails to authenticate with the chosen authentication method and authentication fallthrough is enabled, Vertica attempts to authenticate the client with the authentication with the next highest priority. Otherwise, the client is rejected.
-
Otherwise, no authentication records exist and the default authentication records have been dropped; no users (other than the dbadmin from a local connection) can access the database.
Authentication management
Users with the DBADMIN role can perform the following authentication tasks:
-
Create authentication records.
-
Drop an authentication record from the database.
-
Define parameters required by the following authentication methods:
-
Grant (assign) or revoke an authentication record to a user.
-
Use ALTER AUTHENTICATION to:
-
Enable/disable authentication methods.
-
Define a default authentication method to be used if a user has not been assigned a specific authentication method. To assign this as a default authentication method, use GRANT (authentication) to grant it to the PUBLIC role.
-
Change authentication record priority.
-
Enable fallthrough authentication.
1 - Default authentication records
Vertica automatically creates the following default authentication records and grants them to the public role.
Vertica automatically creates the following default authentication records and grants them to the public
role. These have the lowest possible priority (-1
), so user-created authentication records take priority over these default records:
=> SELECT auth_name,is_auth_enabled,auth_host_type,auth_method,auth_priority,is_fallthrough_enabled FROM client_auth;
auth_name | is_auth_enabled | auth_host_type | auth_method | auth_priority | is_fallthrough_enabled
---------------------------+-----------------+----------------+-------------+---------------+------------------------
default_hash_network_ipv4 | True | HOST | PASSWORD | -1 | False
default_hash_network_ipv6 | True | HOST | PASSWORD | -1 | False
default_hash_local | True | LOCAL | PASSWORD | -1 | False
(3 rows)
Note
Do not use the password
method for your custom authentication records. If you want to use password-based authentication for your custom authentication records, use hash
instead.
If no authentication records are defined (and the default authentication records are dropped), only the dbadmin and users without passwords can access the database. For details, see Implicit authentication.
2 - Configuring client authentication
You can restrict how database users can connect with authentication records.
You can restrict how database users can connect with authentication records. The Vertica database uses client authentication to establish the identity of the connecting client and determines whether that client is authorized to connect to the Vertica database using the supplied credentials and from their host address.
Basic authentication configuration workflow
In general, the workflow for configuring client authentication is as follows:
-
Create an authentication record. Authentication records are enabled automatically after creation. For details, see Creating authentication records.
-
Grant the authentication to a user or role.
To view existing authentication records, query the system table CLIENT_AUTH.
IPv4 and IPv6 for client authentication
Vertica supports clients using either the IPv4 or the IPv6 protocol to connect to the database server. Internal communication between database servers must consistently use one address family (IPv4 or IPv6). The client, however, can connect to the database from either type of IP address.
If the client will be connecting from either IPv4 or IPv6, you must create two authentication methods, one for each address. Any authentication method that uses HOST authentication requires an IP address.
For example, the first statement allows users to connect from any IPv4 address. The second statement allows users to connect from any IPv6 address:
=> CREATE AUTHENTICATION <name> METHOD 'gss' HOST '0.0.0.0/0'; --IPv4
=> CREATE AUTHENTICATION <name> METHOD 'gss' HOST '::/0'; --IPv6
If you are using a literal IPv6 address in a URL, you must enclose the IPv6 address in square brackets as shown in the following examples:
=> ALTER AUTHENTICATION Ldap SET host='ldap://[1dfa:2bfa:3:45:5:6:7:877]';
=> ALTER AUTHENTICATION Ldap SET host='ldap://[fdfb:dbfa:0:65::177]';
=> ALTER AUTHENTICATION Ldap SET host='ldap://[fdfb::177]';
=> ALTER AUTHENTICATION Ldap SET host='ldap://[::1]';
=> ALTER AUTHENTICATION Ldap SET host='ldap://[1dfa:2bfa:3:45:5:6:7:877]:5678';
If you are working with a multi-node cluster, any IP/netmask settings in (HOST, HOST TLS, HOST NO TLS) must match all nodes in the cluster. This setup allows the database owner to authenticate with and administer every node in the cluster. For example, specifying 10.10.0.8/30 allows a CIDR address range of 10.10.0.8-10.10.0.11.
For detailed information about IPv6 addresses, see RFC 1924 and RFC 2732.
Supported client authentication methods
Vertica supports the following client authentication methods:
-
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.
Local and host authentication
You can define a client authentication method as:
-
Local: Local connection to the database.
-
Host: Remote connection to the database from different hosts, each with their own IPv4 or IPv6 address and host parameters. For more information see IPv4 and IPv6 for Client Authentication above.
Some authentication methods can only be designated as local or host, as listed in this table:
Authentication Method |
Local? |
Host? |
gss (Kerberos) |
No |
Yes |
ident |
Yes |
No |
ldap |
Yes |
Yes |
hash |
Yes |
Yes |
reject |
Yes |
Yes |
trust |
Yes |
Yes |
tls |
No |
Yes |
oauth |
Yes |
Yes |
Authentication for chained users and roles
Vertica supports creating chained users and roles, where you can grant ROLE2 privileges to ROLE1. All users in ROLE1 use the same authentication assigned to ROLE2. For example:
=> CREATE USER user1;
=> CREATE ROLE role1;
=> CREATE ROLE role2;
=> CREATE AUTHENTICATION h1 METHOD 'hash' LOCAL;
=> GRANT AUTHENTICATION h1 to role2;
=> GRANT role2 to role1;
=> GRANT role1 to user1;
The user and role chain in the example above can be illustrated as follows:
auth1 -> role2 -> role1 -> user1
In this example, since role2 privileges are granted to role1 you only need to grant authentication to role2 to also enable it for role1.
3 - Fallthrough authentication
Normally, if a user fails to authenticate with the first chosen authentication record, the user is rejected.
Normally, if a user fails to authenticate with the first chosen authentication record, the user is rejected. However, certain authentication methods can be specified to instead, upon failure, "fall through" to an authentication record with a lower priority. This can be useful, for example, for using multiple LDAP search attributes.
Authentication fallthrough is disabled by default for new records.
Another way to bypass method priority or implement fallthrough authentication is to implement it on the client instead. For details, see Authentication filtering.
Authentication method compatibility
The following table shows each authentication method's compatibility with fallthrough authentication. A value of "yes" indicates that the method in the column can fall through and validate the row method it falls through to. A value of "no" indicates that you cannot fall through to it and authentication ends.
A value of "-" (dash) indicates that while fallthrough works, it has no meaningful effect. For example, a hash
authentication record can fall through to another hash
authentication record, but authentication will always fail because you cannot fall through to a correct password:
Column falls through to row |
ldap |
hash |
tls |
ident |
oauth |
gss |
trust |
reject |
ldap |
yes |
yes |
yes |
yes |
no |
no |
no |
no |
hash |
yes |
- |
yes |
yes |
no |
no |
no |
no |
tls |
yes |
yes |
- |
yes |
no |
no |
no |
no |
ident |
yes |
yes |
yes |
yes |
no |
no |
no |
no |
oauth |
no |
no |
no |
no |
no |
no |
no |
no |
gss |
no |
no |
no |
no |
no |
no |
no |
no |
trust |
yes |
yes |
yes |
yes |
no |
no |
no |
no |
reject |
yes |
yes |
yes |
yes |
no |
no |
no |
no |
For example, suppose the user Bob
was granted the following authentication record with fallthrough enabled:
=> CREATE AUTHENTICATION v_tls_auth METHOD 'tls' HOST TLS '0.0.0.0/0' FALLTHROUGH;
In addition, Bob
has the public
role, and therefore has the default authentication records:
=> SELECT auth_name,is_auth_enabled,auth_host_type,auth_method,auth_priority,is_fallthrough_enabled FROM client_auth;
auth_name | is_auth_enabled | auth_host_type | auth_method | auth_priority | is_fallthrough_enabled
---------------------------+-----------------+----------------+-------------+---------------+------------------------
default_hash_network_ipv4 | True | HOST | PASSWORD | -1 | False
default_hash_network_ipv6 | True | HOST | PASSWORD | -1 | False
default_hash_local | True | LOCAL | PASSWORD | -1 | False
(3 rows)
If Bob
, connecting from a remote address, fails to authenticate with v_tls_auth
, Vertica attempts to authenticate him with the next (in order of priority) authentication record, default_hash_network_ipv4
.
Logging authentication failures
Login failures are only logged if the user is rejected after all granted authentication records have failed. Only the final failure is logged.
For example, suppose a user is granted both tls
and password
authentication records, and the tls
record falls through to the password
record.
If the user fails to authenticate with both the tls
record and the password
record, then only the password
failure is logged in LOGIN_FAILURES.
However, if the user fails to authenticate with the tls
record, but succeeds with the password
record, then no failure is logged in LOGIN_FAILURES because the user was not rejected at the end of the record chain.
Examples
To enable fallthrough on a new authentication record, use CREATE AUTHENTICATION:
=> CREATE AUTHENTICATION v_tls_auth METHOD 'tls' HOST TLS '0.0.0.0/0' FALLTHROUGH;
To toggle fallthrough on an existing authentication record, use ALTER AUTHENTICATION:
=> ALTER AUTHENTICATION v_tls_auth NO FALLTHROUGH; -- disable
=> ALTER AUTHENTICATION v_tls_auth FALLTHROUGH; -- enable
4 - Authentication filtering
You can filter for and authenticate with authentication records that use a particular method by specifying the credentials for that method.
You can filter for and authenticate with authentication records that use a particular method by specifying the credentials for that method. This process skips any higher-priority, non-trust
authentication records.
If the selected record fails and fallthrough is enabled, Vertica uses to the record with the next highest priority.
If you provide the credentials for an authentication method for which you have not been granted, Vertica instead attempts to authenticate you with the highest priority authentication record.
The following table shows the credentials the client provides and the requested authentication method in descending filter priority. If you attempt to provide more than one type of credential, your client selects and sends the one with the higher priority:
Credentials sent by client |
Requested authentication method |
OAuthRefreshToken or OAuthAccessToken |
oauth |
Non-default KerberosServiceName or KerberosHostname |
gss |
Username, Password |
ldap , hash , ident , trust , tls |
For example, if a client provides a username and password for user Alice, and the Alice has oauth
, ldap
, and hash
records, then Vertica attempts to authenticate the client with ldap
or hash
, whichever has the higher priority.
Similarly, if Alice instead provides an OAuthAccessToken, username, and password, then Vertica attempts to authenticate her with the oauth
record because it has a higher filter priority.
However, if Alice provides a KerberosHostname, Vertica attempts to authenticate her with the highest priority authentication record between her oauth
, ldap
, and hash
records.
Client-side fallthrough authentication
A workaround for the incompatibility of certain authentication methods with fallthrough authentication is to use authentication filtering to manually fall through to the proper authentication record by detecting the initial authentication failure, and then making a new connection attempt with a different set of credentials.
For example, oauth
and hash
cannot fall through to each other, so if a user is granted authentication records with these methods, they can only authenticate with the higher priority record. As a workaround, you can detect the authentication failure with oauth
and then filter for hash
in another connection attempt. Doing this programmatically depends on the client. In JDBC, you can catch SQLInvalidAuthorizationSpecException
and then try to reconnect in the exception handler. In ODBC, you can check the return value of SQLDriverConnect()
:
// error checking omitted for brevity
SQLHENV hdlEnv;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);
SQLHDBC hdlDbc;
SQLSetEnvAttr(hdlEnv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc);
SQLRETURN ret = SQLDriverConnect(hdlDbc, NULL, (SQLCHAR *)("DSN=VerticaDSN;OAuthAccessToken=" + accessToken + ";OAuthRefreshToken=" + refreshToken + ";OAuthClientSecret=" + clientSecret).c_str(), SQL_NTS, NULL, 0, NULL, false);
if (!SQL_SUCCEEDED(ret))
{
std::cout << "Could not connect to database with OAuth, retrying with hash authentication" << std::endl;
reportError <SQLHDBC>(SQL_HANDLE_DBC, hdlDbc);
SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc);
const char* dsnName = "ExampleDB";
const char* userID = "Alice";
const char* passwd = "mypassword";
SQLConnect(hdlDbc, (SQLCHAR*)dsnName,SQL_NTS,(SQLCHAR*)userID,SQL_NTS,(SQLCHAR*)passwd, SQL_NTS);
}
Examples
In the following example, the user Bob is granted two authentication records: one that uses Kerberos authentication (gss
) and another that uses password authentication (hash
):
=> SELECT auth_name, auth_host_type, auth_method FROM client_auth;
auth_name | auth_host_type | auth_method
---------------------+----------------+-------------
v_hash | HOST | HASH
v_kerberos | HOST | GSS
(2 rows)
-
Bob attempts to authenticate with Kerberos, but fails (the exact error message varies):
$ vsql -h vertica_db.example.com -K vcluster.example.com -U Bob
vsql: GSSAPI continuation error: Unspecified GSS failure. Minor code may provide more information
GSSAPI continuation error: No Kerberos credentials available (default cache: KEYRING:persistent:0)
-
Bob notices the failure. The steps involved in detecting this programmatically depend on your client.
-
An authentication record that uses gss
cannot fall through to one that uses hash
, so the only way for Bob to authenticate to the database is to filter for the hash
by providing the username and password:
=> vsql -h vertica_db.example.com -U Bob -w 'mypassword'
To implement client-side fallthrough authentication, check the initial connection/authentication attempt for errors and then create a new connection in the error handler. For example, the user Alice is granted oauth
and hash
authentication records:
=> SELECT auth_name, auth_host_type, auth_method FROM client_auth;
auth_name | auth_host_type | auth_method
---------------------+----------------+-------------
v_oauth | HOST | OAUTH
v_hash | HOST | HASH
(2 rows)
5 - Implicit authentication
Vertica has implicit (that is, not reflected in the CLIENT_AUTH system table) authentication records reserved for the dbadmin and users without passwords.
Vertica has implicit (that is, not reflected in the CLIENT_AUTH system table) authentication records reserved for the dbadmin and users without passwords. These records cannot be dropped.
For the dbadmin user:
For a non-dbadmin user, the trust
method is used if all of the following are true:
-
The user does not have a password.
-
No custom (that is, non-default) authentication methods are enabled.
6 - Dbadmin authentication access
The dbadmin user must have access to the database at all times.
The dbadmin user must have access to the database at all times. Vertica automatically ensures that a client can authenticate as the dbadmin from a LOCAL connection.
If you need to authenticate as the dbadmin from a remote connection, the dbadmin must have a password. You can use the following methods:
Authenticating from a local connection
You can always implicitly authenticate as the dbadmin from a local connection. These dbadmin-specific authentication records are implicit, so they are not listed in the CLIENT_AUTH system table, and cannot be dropped.
If the dbadmin user does not have a password, then Vertica authenticates them with the trust
method. Otherwise, Vertica authenticates them with the password
method.
In this example, the dbadmin did not have a password and connected to Vertica from a local connection:
=> SELECT authentication_method, client_authentication_name FROM vs_sessions;
authentication_method | client_authentication_name
-----------------------+----------------------------
ImpTrust | default: Implicit Trust
Authenticating from a remote connection
Fallthrough authentication
Vertica automatically creates the following authentication records and grants them to the public
role (for details, see Client authentication):
=> SELECT auth_name,is_auth_enabled,auth_host_type,auth_method,auth_priority,is_fallthrough_enabled FROM client_auth;
auth_name | is_auth_enabled | auth_host_type | auth_method | auth_priority | is_fallthrough_enabled
---------------------------+-----------------+----------------+-------------+---------------+------------------------
default_hash_network_ipv4 | True | HOST | PASSWORD | -1 | False
default_hash_network_ipv6 | True | HOST | PASSWORD | -1 | False
default_hash_local | True | LOCAL | PASSWORD | -1 | False
(3 rows)
These default authentication records ensure that all users with the public
role (which includes dbadmin) have access to the database, provided that any custom authentication records are set to fall through (disabled by default) to the default records.
For example, the following ldap
authentication enables fallthrough, so if the LDAP server is down, users can still authenticate with password
authentication (as defined by the default records).
=> CREATE AUTHENTICATION ldap1 METHOD 'ldap' LOCAL FALLTHROUGH;
=> ALTER AUTHENTICATION ldap1 SET host='ldap://localhost:5389',
binddn='cn=Manager,dc=example,dc=com',
bind_password='password',
basedn='ou=dev,dc=example,dc=com',
search_attribute='cn';
Custom authentication records
A dbadmin-specific authentication record should:
-
Use the hash
authentication method (so authentication is not dependent on some external service).
-
Have a high priority (e.g. 10,000) so it supersedes all other authentication records.
The following example creates an authentication record v_dbadmin_hash
and grants it to the dbadmin user. The hash
method indicates that the dbadmin must provide a password when logging in. The HOST '0.0.0.0/0'
access method indicates that the dbadmin can connect remotely from any IPv4 address:
=> CREATE AUTHENTICATION v_dbadmin_hash METHOD 'hash' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION v_dbadmin_hash PRIORITY 10000;
=> GRANT AUTHENTICATION v_dbadmin_hash TO dbadmin;
If you want to authenticate as the dbadmin from a local connection, but want to use an authentication record with the HOST
access method, specify the --host option with the hostname or IP address of the database:
$ vsql database_name user --host hostname_or_ip;
7 - 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.
Important
You cannot modify client authentication records using the Administration Tools. The Administration Tools interface allows you to modify the contents of the vertica.conf
file. However, Vertica ignores any client authentication information stored in that file.
-
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).
-
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
8 - Modifying authentication records
To modify existing authentication records, you must first be connected to your database.
To modify existing authentication records, you must first be connected to your database. The following examples show how to make changes to your authentication records. For more information see ALTER AUTHENTICATION.
Rename an authentication method
Rename the v_kerberos
authentication method to K5
, and enable it. All users who have been associated with the v_kerberos
authentication method are now associated with the K5
method granted instead.
=> ALTER AUTHENTICATION v_kerberos RENAME TO K5 ENABLE;
Specify a priority for an authentication method
Specify a priority of 10 for K5
authentication:
=> ALTER AUTHENTICATION K5 PRIORITY 10;
For more information see Authentication record priority.
Change a parameter
Set the system_users
parameter for ident1
authentication to root
:
=> CREATE AUTHENTICATION ident1 METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION ident1 SET system_users='root';
Change the IP address and specify the parameters for an LDAP authentication method named Ldap1
.
In this example, you specify the bind parameters for the LDAP server. Vertica connects to the LDAP server, which authenticates the Vertica client. If the authentication succeeds, Vertica authenticates any users who have been granted the Ldap1
authentication method on the designated LDAP server:
=> CREATE AUTHENTICATION Ldap1 METHOD 'ldap' HOST '172.16.65.196';
=> ALTER AUTHENTICATION Ldap1 SET host='ldap://172.16.65.177',
binddn_prefix='cn=', binddn_suffix=',dc=qa_domain,dc=com';
Change the IP address, and specify the parameters for an LDAP authentication method named Ldap1
. Assume that Vertica does not have enough information to create the distinguished name (DN) for a user attempting to authenticate. Therefore, in this case, you must specify to use LDAP search and bind:
=> CREATE AUTHENTICATION LDAP1 METHOD 'ldap' HOST '172.16.65.196';
=> ALTER AUTHENTICATION Ldap1 SET host='ldap://172.16.65.177',
basedn='dc=qa_domain,dc=com',binddn='cn=Manager,dc=qa_domain,
dc=com',search_attribute='cn',bind_password='secret';
Change the associated method
Change the localpwd
authentication from trust to hash:
=> CREATE AUTHENTICATION localpwd METHOD 'trust' LOCAL;
=> ALTER AUTHENTICATION localpwd METHOD 'hash';
ALTER AUTHENTICATION validates the parameters you enter. If there are errors, it disables the authentication method that you are trying to modify.
The advantages of using the Administration Tools are:
-
You do not have to connect to the database
-
The editor verifies that records are correctly formed
-
The editor maintains records so they are available to you to edit later
Note
You must restart the database to implement your changes.
For information about using the Administration Tools to create and edit authentication records, see Creating authentication records.
Deleting authentication records
To delete client authentication record, use DROP AUTHENTICATION. To use this approach, you have to be connected to your database.
To delete an authentication record for md5_auth use the following command:
=> DROP AUTHENTICATION md5_auth;
To delete an authentication record for a method that has been granted to a user, use the CASCADE keyword:
=> CREATE AUTHENTICATION localpwd METHOD 'password' LOCAL;
=> GRANT AUTHENTICATION localpwd TO jsmith;
=> DROP AUTHENTICATION localpwd CASCADE;
See also
9 - Authentication record priority
Each authentication record has a priority.
Each authentication record has a priority. If a user is granted more than one authentication record, Vertica attempts to authenticate the user with the authentication record with the highest priority and rejects the user if authentication fails.
There are two ways to authenticate with a record other than that with the highest priority:
-
Fallthrough authentication: If authentication fails, Vertica attempts to authenticate the client with the record with the next highest priority.
-
Authentication filtering: Clients can send the credentials required for a particular authentication method to authenticate with a record that uses that method.
Determining authentication priority
The following factors contribute to an authentication record's priority, as reflected in the CLIENT_AUTH system table:
=> SELECT auth_name, auth_method, auth_priority, method_priority, address_priority FROM client_auth;
auth_name | auth_method | auth_priority | method_priority | address_priority
---------------+-------------+---------------+-----------------+------------------
ldap_auth | LDAP | 5 | 5 | 96
hash_auth | HASH | 5 | 2 | 126
tls_auth | TLS | 0 | 5 | 96
oauth_auth | OAUTH | 0 | 5 | 96
gss_auth | GSS | 0 | 5 | 96
trust_auth | TRUST | 0 | 0 | 96
reject_auth | REJECT | 0 | 10 | 96
(7 rows)
Note
Greater values indicate higher priorities. For example:
Priorities are divided into tiers and listed in order of importance; in the event of a tie at one priority tier, Vertica checks the next priority tier. For example, if a user had both ldap
and hash
authentication records with an auth_priority
of 5, Vertica would attempt to use the ldap
authentication record because it has a greater method_priority
value:
-
auth_priority
: The priority explicitly set with ALTER AUTHENTICATION (default: 0).
-
method_priority
: The priority specific to the authentication method. These priorities are as follows:
-
trust
: 0
-
hash
: 2
-
ldap
: 5
-
tls
: 5
-
oauth
: 5
-
gss
: 5
-
reject
: 10
-
address_priority
: The priority for IP address specified in HOST [ TLS | NO TLS ] '
host-ip-address
'
. This priority is determined by the size of the netmask of the address; fewer zeros indicate greater specificity, and therefore higher priority. LOCAL
has the lowest priority: 0.
Setting authentication priority
To set authentication priority:
=> ALTER AUTHENTICATION authentication_name PRIORITY value;
See also
10 - Viewing information about client authentication records
For information about client authentication records that you have configured for your database, query the following system tables in the V_CATALOG schema:.
For information about client authentication records that you have configured for your database, query the following system tables in the V_CATALOG schema:
To determine the details behind the client authentication used for a particular user session, query the following tables in the V_MONITOR schema:
11 - Enabling and disabling authentication methods
When you create an authentication method, Vertica stores it in the catalog and enables it automatically.
When you create an authentication method, Vertica stores it in the catalog and enables it automatically. To enable or disable an authentication method, use the ALTER AUTHENTICATION statement. To use this approach, you must be connected to your database.
If an authentication method has not been enabled, Vertica cannot use it to authenticate users and clients trying to connect to the database.
To enable an authentication method:
ALTER AUTHENTICATION v_kerberos ENABLE;
To disable this authentication method:
ALTER AUTHENTICATION v_kerberos DISABLE;
See also
12 - Granting and revoking authentication methods
Before Vertica can validate a user or client through an authentication method, you must first associate that authentication method with the user or role that requires it, with GRANT (Authentication).
Before Vertica can validate a user or client through an authentication method, you must first associate that authentication method with the user or role that requires it, with GRANT (authentication). When that user or role no longer needs to connect to Vertica using that method, you can disassociate that authentication from that user with REVOKE AUTHENTICATION.
Grant authentication methods
You can grant an authentication method to a specific user or role. You can also specify the default authentication method by granting an authentication method to PUBLIC
, as in the following examples.
-
Associate v_ldap
authentication with user jsmith
:
=> GRANT AUTHENTICATION v_ldap TO jsmith;
-
Associate v_gss
authentication to the role DBprogrammer
:
=> CREATE ROLE DBprogrammer;
=> GRANT AUTHENTICATION v_gss TO DBprogrammer;
-
Associate client authentication method v_localpwd
with role PUBLIC
, which is assigned by default to all users:
=> GRANT AUTHENTICATION v_localpwd TO PUBLIC;
Revoke authentication methods
If you no longer want to authenticate a user or client with a given authentication method, use the REVOKE (authentication) statement as in the following examples.
-
Revoke v_ldap
authentication from user jsmith
:
=> REVOKE AUTHENTICATION v_ldap FROM jsmith;
-
Revoke v_gss
authentication from the role DBprogrammer
:
=> REVOKE AUTHENTICATION v_gss FROM DBprogrammer;
-
Revoke localpwd
as the default client authentication method:
=> REVOKE AUTHENTICATION localpwd FROM PUBLIC;
13 - Hiding database usernames
If you want to keep certain database usernames a secret from connecting clients and your authentication records do not use Fallthrough Authentication, then these two user groups must share the same single authentication method (not necessarily the same authentication record):.
If you want to keep certain database usernames a secret from connecting clients and your authentication records do not use Fallthrough authentication, then these two user groups must share the same single authentication method (not necessarily the same authentication record):
If your authentication records use fallthrough, then ensure that the first authentication method that prompts for a password in the authentication chain is the same for both the secret users and the PUBLIC role. The following methods prompt for a password:
A simple way to satisfy this condition is by duplicating the fallthrough chain for both groups with the same methods. For example, a valid authentication chain would be tls
> ldap
for both the secret users and the PUBLIC role.
Another valid configuration would be tls
> ldap
> hash
for secret users, and ldap
for the PUBLIC role.
14 - Hash authentication
The hash authentication method authenticates users with passwords.
The hash
authentication method authenticates users with passwords.
In general, you should prefer other authentication methods over hash
.
14.1 - Password hashing algorithm
Vertica strongly recommends that you use SHA-512 for hash authentication.
Note
Vertica strongly recommends that you use SHA-512 for hash
authentication.
Vertica does not store user passwords for the hash
authentication method. Rather, Vertica stores a hash of the password. The hashing algorithm is determined by two parameters:
-
A system-level configuration parameter, SecurityAlgorithm
:
=> ALTER DATABASE DEFAULT SET PARAMETER SecurityAlgorithm = 'hashing_algorithm';
-
A user-level parameter, SECURITY_ALGORITHM
:
=> ALTER USER username SECURITY_ALGORITHM 'hashing_algorithm' IDENTIFIED BY 'new_password';
The system-level parameter, SecurityAlgorithm
, can have the following values:
The user-level parameter, SECURITY_ALGORITHM
, can have the following values. Values other than NONE
will take priority over the system-level parameter:
A user's EFFECTIVE_SECURITY_ALGORITHM
is determined by a combination of the system-level and user-level parameters. If the user-level parameter is set to NONE
, the effective security algorithm will be that of the system-level parameter. You can override the system-level parameter for a particular user by setting the user-level parameter to a non-NONE
value.
You can view these parameters and their effects on each user by querying the system table PASSWORD_AUDITOR.
The following table shows the various combinations of the system-level and user-level parameters and the effective security algorithm for each.
FIPS mode forces the effective security algorithm to be SHA-512.
Parameter value |
Effective Security Algorithm |
System level: SecurityAlgorithm |
User-level: SECURITY_ALGORITHM |
Algorithm Used |
Algorithm Used (FIPS mode) |
MD5 |
NONE |
MD5 |
SHA-512 |
SHA512 |
NONE |
SHA-512 |
SHA-512 |
MD5 |
MD5 |
MD5 |
SHA-512 |
SHA512 |
MD5 |
MD5 |
SHA-512 |
MD5 |
SHA512 |
SHA-512 |
SHA-512 |
SHA512 |
SHA512 |
SHA-512 |
SHA-512 |
14.2 - Configuring hash authentication
The hash authentication method allows users to authenticate with a password.
The hash
authentication method allows users to authenticate with a password.
Vertica stores hashes (SHA-512 by default) of passwords and not the passwords themselves. For details, see Password hashing algorithm.
-
Create an authentication record with the hash
method. Authentication records are automatically enabled after creation. For example, to create the authentication record v_hash
for users that log in from the IP address 192.0.2.0/24:
=> CREATE AUTHENTICATION v_hash METHOD 'hash' HOST '192.0.2.0/24';
-
Associate the v_hash
authentication method with the desired users or roles, using a GRANT statement:
=> GRANT AUTHENTICATION v_hash to user1, user2, ...;
14.3 - Passwords
Assign a password to a user to allow that user to connect to the database using password authentication.
Assign a password to a user to allow that user to connect to the database using password authentication. When the user supplies the correct password a connection to the database occurs.
Vertica hashes passwords according to each user's EFFECTIVE_SECURITY_ALGORITHM. However, the transmission of the hashed password from the client to Vertica is in plaintext. Thus, it is possible for a "man-in-the-middle" attack to intercept the plaintext password from the client.
Configuring Hash authentication ensures secure login using passwords.
About password creation and modification
You must be a superuser to create passwords for user accounts using the CREATE USER statement. A superuser can set any user account's password.
-
To add a password, use the ALTER USER statement.
-
To change a password, use ALTER USER or the vsql meta-command \password
.
Users can also change their own passwords.
To make password authentication more effective, Vertica recommends that you enforce password policies that control how often users are forced to change passwords and the required content of a password. You set these policies using Profiles.
Default password authentication
When you have not specified any authentication methods, Vertica defaults to using password authentication for user accounts that have passwords.
If you create authentication methods, even for remote hosts, password authentication is disabled. In such cases, you must explicitly enable password authentication. The following commands create the local_pwd authentication method and make it the default for all users. When you create an authentication method, Vertica enables it automatically:
=> CREATE AUTHENTICATION local_pwd METHOD hash' LOCAL;
=> GRANT AUTHENTICATION local_pwd To Public;
14.3.1 - Profiles
You can set password policies for users by assigning them profiles.
You can set password policies for users by assigning them profiles. You can create multiple profiles to manage the password policies for several categories of users. For example, you could create one profile for interactive users that requires frequent password changes and another profile for user accounts that never requires password changes.
Defining profiles
You create profiles with CREATE PROFILE and alter existing profiles with ALTER PROFILE. Both statements let you set one or more profile parameters which can control, among other things, the minimum lifetime of a password, password complexity, and password-reset rules.
Each profile can specify one or more of the following policies.
-
How often users must change their passwords
-
How long a password must be set before it can be reset
-
How many times users must change their passwords before they can reuse an old password
-
How many times a user can fail to log in before the account is locked
-
The required length and content of the password:
-
Maximum and minimum number of characters
-
Minimum number of capital letters, lowercase letters, digits, and symbols required in a password
-
How different a new password must be from the old password
Assigning profiles
After you define a profile, you can assign it to new and existing users with CREATE USER and ALTER USER, respectively.
Changes to profile policies for password content—for example, PASSWORD_MAX_LENGTH
and PASSWORD_MIN_SYMBOLS
—affect users only when they change their passwords. Vertica does not test existing passwords to verify that they comply with new password requirements. To enforce immediate compliance with new profile requirements, use ALTER USER...PASSWORD EXPIRE
to immediately expire the current user's password. The next time the user logs in, Vertica prompts them to supply a new password, which must comply with the current policy.
Default profile
Each database contains a DEFAULT
profile. Vertica assigns the default profile to users who are not explicitly assigned a profile. The default profile also sets parameters of non-default profiles in two cases:
All parameters in the default profile are initially set to unlimited
. You can use ALTER PROFILE
to change these settings. For example, the following statement modifies the default profile parameter PASSWORD_MIN_SYMBOLS
. The change requires passwords to contain at least one symbol, such as $, #, @. This change affects all profiles where PASSWORD_MIN_SYMBOLS
is set to default
:
ALTER PROFILE DEFAULT LIMIT PASSWORD_MIN_SYMBOLS 1;
Profile settings and client authentication
The following profile settings affect client authentication methods, such as LDAP or GSS:
-
FAILED_LOGIN_ATTEMPTS
-
PASSWORD_LOCK_TIME
All other profile settings are used only by Vertica to manage its passwords.
See also
14.3.2 - Password guidelines
For passwords to be effective, they must be hard to guess.
For passwords to be effective, they must be hard to guess. You need to protect passwords from:
-
Dictionary-style, brute-force attacks
-
Users who have knowledge of the password holder (family names, birth dates , etc.)
Use Profiles to enforce good password practices (password length and required content). Make sure database users know the password guidelines, and encourage them not to use personal information in their passwords.
For guidelines on creating strong passwords go to Microsoft Tips for Creating a Strong Password.
See also
14.3.3 - Password expiration
The following PROFILE parameters control the conditions for password expiration, new passwords, and minimum lifetime:.
The following PROFILE parameters control the conditions for password expiration, new passwords, and minimum lifetime:
-
PASSWORD_LIFE_TIME
- The number of days a password remains valid
-
PASSWORD_MIN_LIFE_TIME
- The number of days a password must be set before it can be changed
-
PASSWORD_GRACE_TIME
- The number of days a password can be used after it expires
-
PASSWORD_REUSE_MAX
- The number of times you must change your password before you can reuse an earlier password
-
PASSWORD_REUSE_TIME
- The number of days that must pass after a password is set before you can reuse it
-
PASSWORD_MIN_CHAR_CHANGE
- Minimum number of characters that must be different from the previous password
For more details on these and other parameters, see CREATE PROFILE and ALTER PROFILE.
Important
Password expiration has no effect on current sessions.
Password expiration and grace period behavior
The profile parameter PASSWORD_LIFE_TIME
controls the life time of a password in days. By default, the DEFAULT profile sets PASSWORD_LIFE_TIME to UNLIMITED
, which disables password expiration. You can change this for the DEFAULT and custom profiles with ALTER PROFILE.
Normally, when a password expires, Vertica forces users to change their passwords the next time they log in. However, you can set a PASSWORD_GRACE_TIME to allow users to log in after their password expires. If a user logs in during their grace period, Vertica warns the user that their password has expired. Once this grace period ends, Vertica will issue the standard prompt to change the user's password.
Expire a password
You can expire a user's password immediately using the ALTER USER statement's PASSWORD EXPIRE parameter. By expiring a password, you can:
14.3.4 - Account locking
In a profile, you can set a password policy for how many consecutive failed login attempts a user account is allowed before locking.
In a profile, you can set a password policy for how many consecutive failed login attempts a user account is allowed before locking. This locking mechanism helps prevent dictionary-style brute-force attempts to guess users' passwords.
Set account locking
Set this value using the FAILED_LOGIN_ATTEMPTS
parameter using the CREATE PROFILE or ALTER PROFILE statement.
Vertica locks any user account that has more consecutive failed login attempts than the value to which you set FAILED_LOGIN_ATTEMPTS
. The user cannot log in to a locked account, even by supplying the correct password.
Unlock a locked account
You can unlock accounts in one of two ways, depending on your privileges.
-
Manually: If you are a superuser , you can manually unlock the account using the ALTER USER command.
Note
A superuser account cannot be locked, because it is the only user that can unlock accounts. For this reason, choose a very secure password for a superuser account. See
Password guidelines for suggestions.
-
Password Lock Time Setting: PASSWORD_LOCK_TIME
specifies the number of days (units configurable with PasswordLockTimeUnit) an account is locked after a specified number of failed login attempts (configurable with FAILED_LOGIN_ATTEMPTS
). Vertica automatically unlocks the account after the specified number of days has passed.
If you set this parameter to UNLIMITED
, the user's account is never automatically unlocked and a superuser must manually unlock it.
15 - Ident authentication
The Ident protocol, defined in RFC 1413, authenticates a database user with a system user name.To see if that system user can log in without specifying a password, you configure Vertica client authentication to query an Ident server.
The Ident protocol, defined in RFC 1413, authenticates a database user with a system user name.To see if that system user can log in without specifying a password, you configure Vertica client authentication to query an Ident server. With this feature, the DBADMIN user can run automated scripts to execute tasks on the Vertica server.
Caution
Ident responses can be easily spoofed by untrusted servers. Use Ident authentication only on local connections, where the Ident server is installed on the same computer as the Vertica database server.
Following the instructions in these topics to install, set up, and configure Ident authentication for your database:
Examples
The following examples show several ways to configure Ident authentication.
Allow system_user1
to connect to the database as Vertica vuser1
:
=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION v_ident SET system_users='system_user1';
=> GRANT AUTHENTICATION v_ident to vuser1;
=> ALTER AUTHENTICATION v_ident ENABLE;
Allow system_user1
, system_user2
, and system_user3
to connect to the database as vuser1
. Use colons (:) to separate the user names:
=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION v_ident SET system_users='system_user1:system_user2:system_user3';
=> GRANT AUTHENTICATION v_ident TO vuser1;
=> ALTER AUTHENTICATION v_ident ENABLE;
Associate the authentication with Public
using a GRANT AUTHENTICATION statement. The users, system_user1
, system_user2
, and system_user3
can now connect to the database as any database user:
=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION v_ident SET system_users='system_user1:system_user2:system_user3';
=> GRANT AUTHENTICATION v_ident to Public;
=> ALTER AUTHENTICATION v_ident ENABLE;
Set the system_users
parameter to *
to allow any system user to connect to the database as vuser1
:
=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION v_ident SET system_users='*';
=> GRANT AUTHENTICATION v_ident TO vuser1;
=> ALTER AUTHENTICATION v_ident ENABLE;
Using a GRANT statement, associate the v_ident
authentication with Public
to allow system_user1
to log into the database as any database user:
=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
=> ALTER AUTHENTICATION v_ident SET system_users='system_user1';
=> GRANT AUTHENTICATION v_ident to Public;
=> ALTER AUTHENTICATION v_ident ENABLE;
15.1 - Installing and setting up an ident server
To use Ident authentication, you must install one or more packages, depending on your operating system, and enable the Ident server on your Vertica server.
To use Ident authentication, you must install one or more packages, depending on your operating system, and enable the Ident server on your Vertica server. oidentd
is an Ident daemon that is compatible with Vertica and compliant with RFC 1413.
Note
You can find the source code and installation instructions for oidentd at the
oidentd website.
To install and configure Ident authentication for use with your Vertica database, follow the appropriate steps for your operating system.
Red hat 7.x/CentOS 7.x
Install an Ident server on Red Hat 7.x or CentOS 7.x by installing the authd
and xinetd
packages:
$ yum install authd
$ yum install xinetd
Ubuntu/debian
Install oidentd
on Ubuntu or Debian by running this command:
$ sudo apt-get install oidentd
SUSE Linux enterprise server
Install the pidentd
and xinetd
RPMs from the following locations:
Post-installation steps for ubuntu/debian
After you install oidentd
on your Ubuntu/Debian system, continue with the following steps:
-
Verify that the Ident server accepts IPv6 connections to prevent authentication failure. To do so, you must enable this capability. In the script /etc/init.d/oidentd
, change the line from:
exec="/usr/sbin/oidentd"
to
exec="/usr/sbin/oidentd -a ::"
Then, at the Linux prompt, start oidentd
with -a ::
.
-
Restart the server with the following command:
$ /etc/init.d/oidentd restart
Post-installation steps for red hat 7.x/CentOS 7.x and SUSE Linux enterprise server
After you install the required packages on your Red Hat 7.x/CentOS 7.x or SUSE Linux Enterprise Server system, continue with the following steps:
-
Enable the auth
service by setting disable = no
in the configuration file /etc/xinetd.d/auth
. If this file does not exist, create it. The following is a sample configuration file:
service auth
{
disable = no
socket_type = stream
wait = no
user = ident
cps = 4096 10
instances = UNLIMITED
server = /usr/sbin/in.authd
server_args = -t60 --xerror --os
}
-
Restart the xinetd
service with the following command:
$ service xinetd restart
15.2 - Configuring ident authentication for database users
To configure Ident authentication, take the following steps:.
To configure Ident authentication, take the following steps:
-
Create an authentication method that uses Ident.
The Ident server must be installed on the same computer as your database, so specify the keyword LOCAL. Vertica requires that the Ident server and database always be on the same computer as the database.
=> CREATE AUTHENTICATION v_ident METHOD 'ident' LOCAL;
-
Set the Ident authentication parameters, specifying the system users who should be allowed to connect to your database.
=> ALTER AUTHENTICATION v_ident SET system_users='user1:user2:user3';
-
Associate the authentication method with the Vertica user. Use a GRANT statement that allows the system user user1
to log in using Ident authentication:
=> GRANT AUTHENTICATION v_ident TO user1;
16 - Kerberos authentication
Kerberos authentication uses the following components to perform user authentication.
Kerberos authentication uses the following components to perform user authentication.
Client package
The Kerberos 5 client package communicates with the KDC server. This package is not included as part of the Vertica Analytics Platform installation. Kerberos software is built into Microsoft Windows. If you are using another operating system, you must obtain and install the client package.
If you do not already have the Kerberos 5 client package on your system, download it from the MIT Kerberos Distribution page. Install the package on each Vertica server and client used in Kerberos authentication, except the KDC itself.
Refer to the Kerberos documentation for installation instructions.
Service principals
A service principal consists of a host name, a service name, and a realm to which a set of credentials gets assigned (service/hostname@REALM). These credentials connect to the service, which is a host that you connect to over your network and authenticate using the KDC.
See Specify KDC information and configure realms to create the realm name. The host name must match the value supplied by the operating system. Typically this is the fully qualified host name. If the host name part of your principal does not match the value supplied by the operating system, Kerberos authentication fails.
Some systems use a hosts file (/etc/hosts or /etc/hostnames) to define host names. A hosts file can define more than one name for a host. The operating system supplies the first entry, so use that in your principal. For example, if your hosts file contains:
192.168.1.101 v_vmart_node0001.example.com v_vmart_node0001
then use v_vmart_node0001.example.com as the hostname value.
Note
Depending on your configuration it may be safer to use the fully qualified domain name rather than the hostname.
Configure the following as Kerberos principals:
See the following topics for more information:
Keytab files
Principals are stored in encrypted keytab files. The keytab file contains the credentials for the Vertica principal. The keytab allows the Vertica server to authenticate itself to the KDC. You need the keytab so that Vertica Analytic Database does not have to prompt for a password.
Create one service principal for each node in your cluster. You can then either create individual keytab files (one for each node containing only that node's principal) or create one keytab file containing all the principals.
-
Create one keytab file with all principals to simplify setup: all nodes have the same file, making initial setup easier. If you add nodes later you either update (and redistribute) the global keytab file or make separate keytabs for the new nodes. If a principal is compromised it is compromised on all nodes where it is present in a keytab file.
-
Create separate keytab files on each node to simplify maintenance. Initial setup is more involved as you must create a different file on each node, but no principals are shared across nodes. If you add nodes later you create keytabs on the new nodes. Each node's ke ytab contains only one principal, the one to use for that node.
Ticket-granting ticket
The Ticket-Granting Ticket (TGT) retrieves service tickets that authenticates users to servers in the domain. Future login requests use the cached HTTP Service Ticket for authentication, unless it has expired as set in the ticket_lifetime parameter in krb5.conf.
Multi-realm support
Note
When assigning multiple realms to an authentication record, keep in mind that Vertica cannot distinguish between users from one realm and users from the Vertica realm. This allows the same user to log in to Vertica from multiple realms at the same time.
Vertica provides multi-realm support for Kerberos authentication using the SET param=value parameter in ALTER AUTHENTICATION with REALM as the parameter:
=> ALTER AUTHENTICATION krb_auth_users set REALM='USERS.COM';
=> ALTER AUTHENTICATION krb_auth_realmad set REALM='REALM_AD.COM';
This allows you to assign a different realm so that users from another realm can authenticate to Vertica.
Mutli-realm support applies to GSS authentication types only. You can have one realm per authentication method. If you have multiple authentication methods, each can have its own realm:
=> SELECT * FROM client_auth;
auth_oid | auth_name | is_auth_enabled | auth_host_type | auth_host_address | auth_method | auth_parameters | auth_priority
---------+-----------+-----------------+----------------+-------------------+-------------+-----------------+-----------------
45035996 | krb001 | True | HOST | 0.0.0.0/0 | GSS | realm=USERS.COM | 0
45035997 | user_auth | True | LOCAL | | TRUST | | 1000
45035737 | krb002 | True | HOST | 0.0.0.0/0 | GSS | realm=REALM_AD.COM | 1
16.1 - Configure Vertica for Kerberos authentication
Kerberos provides a strong cryptographic authentication against the devices which lets the client & servers to communicate in a more secured manner.
Kerberos provides a strong cryptographic authentication against the devices which lets the client & servers to communicate in a more secured manner. It addresses network security problems.
Your system must have one or more Kerberos Key Distribution Centers (KDC) installed and configured. The KDCs must be accessible from every node in your Vertica Analytic Database cluster.
The KDC must support Kerberos 5 using GSS-API. For details, see the MIT Kerberos Distribution Page.
In this section
16.1.1 - Create the Vertica principals and keytabs on Linux KDC
Vertica uses service principals for system-level operations.
Vertica uses service principals for system-level operations. These principals identify the Vertica service and are used as follows:
-
Kerberized Vertica clients request access to this service when they authenticate to the database.
-
System processes like the Tuple Mover use this identity when they authenticate to external services such as Hadoop.
Create principals and keys as follows:
-
Start the Kerberos 5 database administration utility (kadmin
or kadmin.local
) to create Vertica principals on a Linux KDC.
-
Use kadmin
if you are accessing the KDC on a remote server. If you have access to the Kerberos administrator password, you can use kadmin
on any machine where the Kerberos 5 client package is installed. When you start kadmin
, the utility prompts you for the Kerberos administrator's password. You might need root privileges on the client to run kadmin
.
-
Use kadmin.local
if:
kadmin.local
does not require the administrators login credentials.
For more information about the kadmin
and kadmin.local
commands, see the kadmin documentation.
-
Create one service principal for Vertica on each node. The host name must match the value supplied by the operating system. The following example creates the service principal vertica
for the node named v_vmart_node0001.example.com
:
$ sudo /usr/kerberos/sbin/kadmin.local
kadmin.local add_principal vertica/v_vmart_node0001.example.com
Repeat the ktadd
command once per principal. You can create separate keytabs for each principal user or add them all to a single keytab file (such as krb5.keytab). If you are using a single file, see the documentation for the -glob option in the MIT Kerberos documentation.
You must have a user principal for each Vertica Analytic Database user that uses Kerberos Authentication. For example:
$ sudo /usr/kerberos/sbin/kadmin.local
kadmin.local add_principal [options] VerticaUser1
-
Copy each keytab file to the /etc
folder on the corresponding cluster node. Use the same path and file name on all nodes.
-
On each node, make the keytab file readable by the file owner who is running the database process (typically, the Linux dbadmin user). For example, you can change ownership of the files to dbadmin as follows:
$ sudo chown dbadmin *.keytab
Important
In a production environment, you must control who can access the keytab file to prevent unauthorized users from delegating your server. For more information about delegation (also known as impersonation), see
Technet.Microsoft.com.
After you create a keytab file, you can use the klist
command to view keys stored in the file:
$ sudo /usr/kerberos/bin/klist -ke -t
Keytab name: FILE:/etc/krb5.keytab
KVNO Timestamp Principal
---- ------------------- --------------------------------------------------------------------------
4 08/15/2017 7:35:41 vertica/v_vmart_node0001.example.com@EXAMPLE.COM (aes256-cts-hmac-sha1-96)
4 08/15/2017 7:35:41 vertica/v_vmart_node0001.example.com@EXAMPLE.COM (aes128-cts-hmac-sha1-96)
-
On Vertica run the following to ensure the Kerberos parameters are set correctly:
=> select parameter_name, current_value from configuration_parameters where parameter_name like 'Ker%';
parameter_name | current_value
-----------------------+---------------------------------------------------------------------
KerberosHostname | v_vmart_node0001.example.com
KerberosKeytabFile | /etc/krb5.keytab
KerberosRealm | EXAMPLE.COM
KerberosTicketDuration | 0
KerberosServiceName | vertica
(5 rows)
-
Ensure that all clients use the gss authentication method.
From Vertica:
=> CREATE USER bob;
CREATE USER
=> CREATE AUTHENTICATION v_kerberos method 'gss' host '0.0.0.0/0';
CREATE AUTHENTICATION
=> ALTER AUTHENTICATION v_kerberos enable;
ALTER AUTHENTICATION
=> GRANT AUTHENTICATION v_kerberos to bob;
GRANT AUTHENTICATION
From the operating system command line:
$ kinit bob
$ vsql -U bob -k vertica -K v_vmart_node0001.example.com -h v_vmart_node0001 -c "select client_authentication_name,
authentication_method from sessions;"
client_authentication_name | authentication_method--
----------------------------+-----------------------
v_kerberos | GSS-Kerberos
(1 row)
-
On Vertica, run KERBEROS_CONFIG_CHECK to verify the Kerberos configuration. KERBEROS_CONFIG_CHECK verifies the following:
-
The existence of the kinit and kb5.conf files.
-
Whether the keytab file exists and is set
-
The Kerberos configuration parameters set in the database:
-
KerberosServiceName
-
KerberosHostname
-
KerberosRealm
-
Vertica Principal
-
That Kerberos can read the Vertica keys
-
That Kerberos can get the tickets for the Vertica principal
-
That Vertica can initialize the keys with kinit
16.1.2 - Specify KDC information and configure realms
Each client and Vertica Analytic Database server in the Kerberos realm must have a valid, identically configured Kerberos configuration (krb5.conf) file.
Each client and Vertica Analytic Database server in the Kerberos realm must have a valid, identically configured Kerberos configuration (krb5.conf
) file. Without this file, the client does not know how to reach the KDC.
If you use Microsoft Active Directory, you do not need to perform this step. Refer to the Kerberos documentation for your platform for more information about the Kerberos configuration file on Active Directory.
At a minimum, you must configure the following sections in the krb5.conf
file.
- [
libdefaults
]—Settings used by the Kerberos 5 library
- [
realms
]—Realm-specific contact information and settings
- [
domain_realm
]—Maps server hostnames to Kerberos realms
See the Kerberos documentation for information about other sections in this configuration file.
You must update the /etc/krb5.conf
file to reflect your site's Kerberos configuration. The simplest way to enforce consistency among all clients and servers in the Kerberos realm is to copy the /etc/krb5.conf
file from the KDC. Then, place this file in the /etc
directory on each Vertica cluster node.
16.1.3 - Inform Vertica about the Kerberos principal
Follow these steps to inform Vertica about the principal name and keytab location.
Follow these steps to inform Vertica about the principal name and keytab location.
For information about the parameters that you are setting in this procedure, see Kerberos parameters.
-
Log in to the database as an administrator (typically dbadmin).
-
Set the KerberosKeyTabFile
configuration parameter to point to the location of the keytab file:
=> ALTER DATABASE DEFAULT SET PARAMETER KerberosKeytabFile = '/etc/krb5.keytab';
The keytab file must be in the same location (/etc/krb5.keytab
in this example) on all nodes.
-
Set the service name for the Vertica principal; for example, vertica
:
=> ALTER DATABASE DEFAULT SET PARAMETER KerberosServiceName = 'vertica';
-
Provide the realm portion of the principal, for example, EXAMPLE.COM
:
=> ALTER DATABASE DEFAULT SET PARAMETER KerberosRealm = 'EXAMPLE.COM'
16.1.4 - Configure the authentication method for all clients
To make sure that all clients use the gss authentication method, run the following statements:.
To make sure that all clients use the gss authentication method, run the following statements:
=> CREATE AUTHENTICATION <method_name> METHOD 'gss' HOST '0.0.0.0/0';
=> GRANT AUTHENTICATION <method_name> TO Public;
For more information, see Implementing Client Authentication.
16.1.5 - Creating the principals and keytab on active directory
Active Directory stores information about members of the Windows domain, including users and hosts.
Active Directory stores information about members of the Windows domain, including users and hosts.
Vertica uses the Kerberos protocol to access this information in order to authenticate Windows users to the Vertica database. The Kerberos protocol uses principals to identify users and keytab files to store their cryptographic information. You need to install the keytab files into Vertica to enable the Vertica database to cryptographically authenticate windows users.
This procedure describes:
-
Creating a Vertica service principal.
-
Exporting the keytab files for these principals
-
Installing the keytab files in the Vertica database. This allows Vertica to authenticate Windows users and grant them access to the Vertica database.
-
Create a Windows account (principal) for the Vertica service and one Vertica host for each node/host in the cluster. This procedure creates Windows accounts for host verticanode01
and service vertica
running on this node.
When you create these accounts, select the following:
Note
You can deselect Password never expires. However, if you change these user passwords, you must recreate the keytab files and reinstall them into Vertica. This includes repeating the entire procedure.
-
If you are using external tables on HDFS that are secured by Kerberos authentication, you must enable Delegation. To do so, access the Active Directory Users and Computers dialog, right-click the Windows account (principal) for the Vertica
service, and select Delegation. Trust this user for delegation to any service.
-
Run the following command to create the keytab for the host verticanode01.dc.com
node/host:
$ ktpass -out ./host.verticanode01.dc.com.keytab -princ host/verticanode01.dc.com@DC.COM -mapuser verticanode01
-mapop set -pass secret -ptype KRB5_NT_SRV_HST
-
Run the following command to create the keytab for the vertica
service:
$ ktpass -out ./vertica.verticanode01dc.com.keytab -princ vertica/verticanode01.dc.com@DC.COM -mapuser vertica
-mapop set -pass secret -ptype KRB5_NT_PRINCIPAL
For more information about keytab files, see Technet.Microsoft.com.
-
Run the following commands to verify that the service principal name is mapped correctly. You must run these commands for each node in your cluster:
$ setspn -L vertica
Registered ServicePrincipalNamefor CN=vertica,CN=Users,DC=dc,DC=com
vertica/verticanode01.dc.com
$ setspn -L verticanode01
Registered ServicePrincipalNamefor CN=verticanode01,CN=Users,DC=dc,DC=com
host/verticanode01.dc.com
-
Copy the keytabs you created above, vertica.verticanode01.dc.com.keytab
and host.verticanode01.dc.com.keytab
, to the Linux host verticanode01.dc.com
.
-
Combine the keytab files into a single keytab:
[release@vertica krbTest]$ /usr/kerberos/sbin/ktutil
ktutil: rkt host.verticanode01.dc.com.keytab
ktutil: rkt vertica.verticanode01.dc.com.keytab
ktutil: list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
1 3 host/verticanode01.dc.com@DC.COM
2 16 vertica/verticanode01.dc.com@DC.COM
ktutil: wkt verticanode01.dc.com.keytab
ktutil: exit
This creates a single keytab file that contains the server principal for authentication.
-
Copy the new keytab file to the catalog directory. For example:
$ cp verticanode01.dc.com.keytab /home/dbadmin/VMart/v_vmart_nodennnn_catalog
-
Test the keytab file's ability to retrieve a ticket to ensure it works from the Vertica node:
$ kinit vertica/verticanode01.dc.com -k -t verticanode01.dc.com.keytab
$ klist
Ticket cache: KFILE:/tmp/krb_ccache_1003
Default principal: vertica/verticanode01.dc.com@DC.COM
Valid starting Expires Service principal
04/08/2017 13:35:25 04/08/2017 23:35:25 krbtgt/DC.COM@DC.COM
renew until 04/15/2017 14:35:25
When the ticket expires or not automatically retrieved you need to manually run the kinit command. See Get the Kerberos ticket and authenticate Vertica.
-
Set the right permissions and ownership on the keytab files:
$ chmod 600 verticanode01.dc.com.keytab
$ chown dbadmin:verticadba verticanode01.dc.com.keytab
-
Set the following Kerberos parameters using ALTER DATABASE to inform Vertica about the Kerberos principal:
KerberosKeytabFile=<CATALOGDIR>/verticanode01.dc.com.keytab
KerberosRealm=DC.COM
KerberosServiceName=vertica
KerberosTicketDuration = 0
KerberosHostname=verticanode01.dc.com
-
Restart the Vertica server.
-
Test your Kerberos setup as follows to ensure that all clients use the gss authentication method.
From Vertica:
=> CREATE USER windowsuser1;
CREATE USER
=> CREATE AUTHENTICATION v_kerberos method 'gss' host '0.0.0.0/0';
CREATE AUTHENTICATION
=> ALTER AUTHENTICATION v_kerberos enable;
ALTER AUTHENTICATION
=> GRANT AUTHENTICATION v_kerberos to windowsuser1;
GRANT AUTHENTICATION
From the operating system command line:
$ kinit windowsuser1
$ vsql -U windowsuser1 -k vertica -K verticanode01.dc.com -h verticanode01.dc.com -c "select client_authentication_name,
authentication_method from sessions;"
client_authentication_name | authentication_method--
----------------------------+-----------------------
v_kerberos | GSS-Kerberos
(1 row)
-
Run KERBEROS_CONFIG_CHECK to verify the Kerberos configuration. KERBEROS_CONFIG_CHECK verifies the following:
-
The existence of the kinit and kb5.conf files.
-
Whether the keytab file exists and is set
-
The Kerberos configuration parameters set in the database:
-
KerberosServiceName
-
KerberosHostname
-
KerberosRealm
-
Vertica Principal
-
That Kerberos can read the Vertica keys
-
That Kerberos can get the tickets for the Vertica principal
-
That Vertica can initialize the keys with kinit
16.1.6 - Get the Kerberos ticket and authenticate Vertica
If your organization uses Kerberos as part of the login process, Kerberos tickets are automatically retrieved upon login.
If your organization uses Kerberos as part of the login process, Kerberos tickets are automatically retrieved upon login. Otherwise, you need to run kinit
to retrieve the Kerberos ticket.
The following example shows how to retrieve the ticket and authenticate Vertica Analytic Database with the KDC using the kinit
command. EXAMPLE.COM is the realm name. You must use the realm name with your username to retrieve a Kerberos ticket. See Specify KDC information and configure realms.
$ kinit
Password for principal_user@EXAMPLE.COM: kpasswd
You are prompted for the password of the principal user name created when you created the principals and keytabs (see Create the Vertica principals and keytabs on Linux KDC).
The Kerberos ticket gets cached for a pre-determined length of time. See Ticket Management in the Kerberos documentation for more information on setting expiration parameters.
Upon expiration, you need to run the kinit
command again to retrieve another Kerberos ticket.
16.2 - Configure clients for Kerberos authentication
Each supported platform has a different security framework.
Each supported platform has a different security framework. Thus, the steps required to configure and authenticate against Kerberos differ among clients.
On the server side, you construct the Vertica Kerberos service name principal using this format:
Kerberos_Service_Name/Kerberos_Host_Name@Kerberos_Realm
For each client, the GSS libraries require the following format for the Vertica service principal:
Kerberos_Service_Name@Kerberos_Host_Name
You can omit the realm portion of the principal because GSS libraries use the realm name of the configured default (Kerberos_Realm
) realm.
For information about client connection strings, see the following topics:
In this section
16.2.1 - Configure ODBC and vsql clients on non-windows platforms
To configure an ODBC or vsql client on Linux or MAC OSX, you must first install the Kerberos 5 client package.
To configure an ODBC or vsql client on Linux or MAC OSX, you must first install the Kerberos 5 client package. See Kerberos authentication.
After you install the Kerberos 5 client package, you must provide clients with a valid Kerberos configuration file (krb5.conf). To communicate with the KDC, each client participating in Kerberos authentication must have a valid, identically configured krb5.conf file. The default location for the Kerberos configuration file is /etc/krb5.conf.
Tip
To enforce consistency among clients, Vertica Analytic Database, and the KDC, copy the /etc/krb5.conf file from the KDC to the client's/etc directory.
The Kerberos configuration (krb5.conf
) file contains Kerberos-specific information, including:
-
How to reach the KDC
-
Default realm name
-
Domain
-
Path to log files
-
DNS lookup
-
Encryption types to use
-
Ticket lifetime
The default location for the Kerberos configuration file is /etc/krb5.conf
.
When configured properly, the client can authenticate with Kerberos and retrieve a ticket through the kinit
utility (see Acquire an ODBC Authentication Request and Connection below). Likewise, the server can then use ktutil to store its credentials in a keytab file
ODBC and vsql use the client's ticket established by kinit
to perform Kerberos authentication. These clients rely on the security library's default mechanisms to find the ticket file and the and Kerberos configuration file.
To authenticate against Kerberos, call the kinit
utility to obtain a ticket from the Kerberos KDC server. The following two examples show how to send the ticket request using ODBC and vsql clients.
Acquire an ODBC authentication request and connection
-
On an ODBC client, acquire a ticket for the kuser
user by calling the kinit
utility.
$ kinit kuser@EXAMPLE.COM
Password for kuser@EXAMPLE.COM:
-
Connect to Vertica, and provide the principals in the connection string:
char outStr[100];
SQLLEN len;
SQLDriverConnect(handle, NULL, "Database=VMart;User=kuser;
Server=myserver.example.com;Port=5433;KerberosHostname=vcluster.example.com",
SQL_NTS, outStr, &len);
Acquire a vsql authentication request connection
If the vsql client is on the same machine you are connecting to, vsql connects through a UNIX domain socket. This connection bypasses Kerberos authentication. When you authenticate with Kerberos, especially if the client authentication method is configured as 'local', you must include the -h hostname option. See Command Line Options.
-
On the vsql client, call the kinit
utility:
$ kinit kuser@EXAMPLE.COM
Password for kuser@EXAMPLE.COM:
-
Connect to Vertica, and provide the host and user principals in the connection string:
$ ./vsql -K vcluster.example.com -h myserver.example.com -U kuser
Welcome to vsql, the Vertica Analytic Database
interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
In the future, when you log in to vsql as kuser
, vsql uses your cached ticket without prompting you for a password.
Verify the authentication method
You can verify the authentication method by querying the SESSIONS system table:
=> SELECT authentication_method FROM sessions;
authentication_method
-----------------------
GSS-Kerberos
(1 row)
See also
16.2.2 - Configure ADO.NET, ODBC, and vsql clients on Windows
The Vertica client drivers support the Windows SSPI library for Kerberos authentication.
The Vertica client drivers support the Windows SSPI library for Kerberos authentication. Windows Kerberos configuration is stored in the registry.
You can choose between two different setup scenarios for Kerberos authentication on ODBC and vsql clients on Windows and ADO.NET:
Note
The procedures on this page are only relevant for
ADO.NET drivers 12.0.4 and below. Later versions of the ADO.NET driver do not currently support Kerberos authentication.
Windows KDC on active directory with Windows built-in Kerberos client and Vertica
Kerberos authentication on Windows is commonly used with Active Directory, Microsoft's enterprise directory service/Kerberos implementation.Typically your organization's network or IT administrator performs the setup.
Windows clients have Kerberos authentication built into the authentication process. You do not need any additional software.
Your login credentials authenticate you to the Kerberos server (KDC) when you:
To use Kerberos authentication on Windows clients, log in as REALM\user.
Important
When you use the ADO.NET driver to connect to Vertica, you can optionally specify
IntegratedSecurity=true
in the connection string. This informs the driver to authenticate the calling user against the user's Windows credentials. As a result, you do not need to include a user name or password in the connection string. Any
user=<
username
>
entry to the connection string is ignored.
Linux KDC with Windows built-in Kerberos client and Vertica
A simple, but less common scenario is to configure Windows to authenticate against a non-Windows KDC. In this implementation, you use the ksetup
utility to point the Windows operating system native Kerberos capabilities at a non-Active Directory KDC. By logging in to Windows, you obtain a ticket-granting ticket, similar to the Active Directory implementation. However, in this case, Windows is internally communicating with a Linux KDC. See the Microsoft Windows Server Ksetup page for more information.
When a database/windows user logs into their Windows machine (or after performing a kinit on Windows) the Kerberos ticket MUST have ok_as_delegate and forwardable flag set to be able to access webhdfs based external tables as follows:
$ CMD \> klist
#2> Client: release @ VERT.LOCAL
Server: vertica/example.com @ VERT.LOCAL
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
Ticket Flags 0x40a50000 forwardable renewable pre_authent ok_as_delegate name_canonicalize
Start Time: 9/27/2017 13:24:43 (local)
End Time: 9/27/2017 20:34:45 (local)
Renew Time: 10/3/2017 15:04:45 (local)
Session Key Type: RSADSI RC4-HMAC(NT)
Cache Flags: 0
Kdc Called: ADKDC01
Note
The Ticket Flags setting above must contain
ok_as_delegate
and
forwardable
entries. For information on these parameters see
Kerberos documentation.
Depending on which implementation you want to configure, refer to one of the following pages on the Microsoft Server website:
Authenticate and connect clients
The KDC can authenticate both an ADO.NET and a vsql client.
Note
Use the fully-qualified domain name as the server in your connection string; for example, use host.example.com
instead of just host
. That way, if the server moves location, you do not have to change your connection string.
Verify an ADO.NET authentication request and connection
This example shows how to use the IntegratedSecurity=true
, setting to specify that the ADO.NET driver authenticate the calling user's Windows credentials:
VerticaConnection conn = new
VerticaConnection("Database=VMart;Server=host.example.com;
Port=5433;IntegratedSecurity=true;
KerberosServiceName=vertica;KerberosHostname=vcluster.example.com");
conn.open();
Verify a vsql authentication request and connection
-
Log in to your Windows client, for example, as EXAMPLE\kuser
.
-
Run the vsql client and supply the connection string to Vertica:
C:\Users\kuser\Desktop>vsql.exe -h host.example.com -K vcluster -U kuser
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
See also
16.2.3 - Configure JDBC clients on all platforms
Kerberos authentication on JDBC clients uses Java Authentication and Authorization Service (JAAS) to acquire the initial Kerberos credentials.
Kerberos authentication on JDBC clients uses Java Authentication and Authorization Service (JAAS) to acquire the initial Kerberos credentials. JAAS is an API framework that hides platform-specific authentication details and provides a consistent interface for other applications.
You specify the client login process through the JAAS Login Configuration File. This file contains options that specify the authentication method and other settings to use for Kerberos. A class called the LoginModule
defines valid options in the configuration file.
The JDBC client principal is crafted as jdbc-username@server-from-connection-string
.
Implement the LoginModule
Vertica recommends that you use the JAAS public class com.sun.security.auth.module.Krb5LoginModul
provided in the Java Runtime Environment (JRE).
The Krb5LoginModule
authenticates users using Kerberos protocols and is implemented differently on non-Windows and Windows platforms:
- On non-Windows platforms: The
Krb5LoginModule
defers to a native Kerberos client implementation. Thus, you can use the same /etc/krb5.conf
setup as you use to configure ODBC and vsql clients on Linux and MAC OSX platforms.
- On Windows platforms: The
Krb5LoginModule
uses a custom Kerberos client implementation bundled with the Java Runtime Environment (JRE). Windows settings are stored in a %WINDIR%\krb5.ini
file, which has similar syntax and conventions to the non-Windows krb5.conf
file. You can copy a krb5.conf
from a non-Windows client to %WINDIR%\krb5.ini
.
You can find documentation for the LoginModules
in the com.sun.security.auth
package, and on the Krb5LoginModule web page.
Create the JAAS login configuration
The JAASConfigName connection property identifies a specific configuration within a JAAS configuration that contains the Krb5LoginModule
and its settings. The JAASConfigName
setting lets multiple JDBC applications with different Kerberos settings coexist on a single host. The default configuration name is verticajdbc
.
Important
Carefully construct the JAAS login configuration file. If syntax is incorrect, authentication fails.
You can configure JAAS-related settings in the java.security
master security properties file. This file resides in the lib/security
directory of the JRE. For more information, see Appendix A in the JavaTM Authentication and Authorization Service (JAAS) Reference Guide.
Create a JDBC login context
The following example shows how to create a login context for Kerberos authentication on a JDBC client. The client uses the default JAASConfigName
of verticajdbc
and specifies that:
-
The ticket-granting ticket will be obtained from the ticket cache
-
The user will not be prompted for a password if credentials cannot be obtained from the cache, keytab file, or through a shared state.
verticajdbc {
com.sun.security.auth.module.Krb5LoginModule
required
useTicketCache=true
doNotPrompt=true;
};
JDBC authentication request and connection
You can configure the Krb5LoginModule
to use a cached ticket or keytab. The driver can also acquire a ticket or keytab automatically if the calling user provides a password.
In the preceding example, the login process uses a cached ticket and does not prompt for a password because both useTicketCache
and doNotPrompt
are set to true
. If doNotPrompt=false
and you provide a user name and password during the login process, the driver provides that information to the LoginModule. The driver then calls the kinit
utility on your behalf.
-
On a JDBC client, call the kinit
utility to acquire a ticket:
$ kinit kuser@EXAMPLE.COM
If you prefer to use a password instead of calling the kinit
utility, see the next section.
-
Connect to Vertica:
Properties props = new Properties();
props.setProperty("user", "kuser");
props.setProperty("KerberosServiceName", "vertica");
props.setProperty("KerberosHostName", "vcluster.example.com");
props.setProperty("JAASConfigName", "verticajdbc");
Connection conn = DriverManager.getConnection
"jdbc:vertica://myserver.example.com:5433/VMart", props);
Have the driver acquire a ticket
Sometimes, you may want to bypass calling the kinit
utility yourself but still use encrypted, mutual authentication. In such cases, you can optionally pass the driver a clear text password to acquire the ticket from the KDC. The password is encrypted when sent across the network. For example, useTicketCache
and doNotPrompt
are both false in the following example. Thus, the calling user's credentials are not obtained through the ticket cache or keytab.
$ verticajdbc {
com.sun.security.auth.module.Krb5LoginModule
required
useTicketCache=false
doNotPrompt=false;
};
The preceding example demonstrates the flexibility of JAAS. The driver no longer looks for a cached ticket, and you do not have to call kinit
. Instead, the driver takes the password and user name and calls kinit
on your behalf.
See also
16.3 - Troubleshooting Kerberos authentication
These tips can help you avoid issues related to Kerberos authentication with Vertica and to troubleshoot any problems that occur.
These tips can help you avoid issues related to Kerberos authentication with Vertica and to troubleshoot any problems that occur.
JDBC client authentication fails
If Kerberos authentication fails on a JDBC client, check the JAAS login configuration file for syntax issues. If syntax is incorrect, authentication fails.
Working domain name service (DNS) not configured
Verify that the DNS entries and the system host file (/etc/hosts or /etc/hostnames) on the network are all properly configured for your environment. If you are using a fully qualified domain name, ensure that is properly configured as well. Refer to the Kerberos documentation for your platform for details.
System clocks out of sync
System clocks in your network must remain in sync for Kerberos authentication to work properly. If you access data in HDFS, then Vertica nodes must also be in sync with Hadoop.
All systems except red hat 7/CentOS 7
To keep system clocks in sync:
-
Install NTP on the Kerberos server (KDC).
-
Install NTP on each server in your network.
-
Synchronize system clocks on all machines that participate in the Kerberos realm within a few minutes of the KDC and each other
Clock skew can be a problem on Linux virtual machines that need to sync with the Windows Time Service. Use the following stepsto keep time in sync:
-
Using any text editor, open /etc/ntp.conf
.
-
Under the Undisciplined Local Clock
section, add the IP address for the Vertica server. Then, remove existing server entries.
-
Log in to the server as root, and set up a cron job to sync time with the added IP address every half hour, or as often as needed. For example:
# 0 */2 * * * /etc/init.d/ntpd restart
-
Alternatively, run the following command to force clock sync immediately:
$ sudo /etc/init.d/ntpd restart
For more information, see Enabling network time protocol (NTP) and the Network Time Protocol website.
Red hat 7/CentOS 7 systems
In Red Hat 7/CentOS 7, ntpd
is deprecated in favor of chrony
. To keep system clocks in your network in sync for Kerberos authentication to work properly, do the following:
-
Install chrony
on the Kerberos server (KDC).
-
Install chrony
on each server in your network.
-
Synchronize system clocks on all machines that participate in the Kerberos realm within a few minutes of the KDC and each other.
Clock skew on Linux virtual machines
Clock skew can be problematic on Linux virtual machines that need to sync with the Windows Time Service. Try the following to keep time in sync:
-
Using any text editor, open /etc/chrony.conf
.
-
Under the Undisciplined Local Clock
section, add the IP address for the Vertica server. Then, remove existing server entries.
-
Log in to the server as root, and set up a cron job to sync time with the added IP address every half hour, or as often as needed. For example:
# 0 */2 * * * systemctl start chronyd
-
Alternatively, run the following command to force clock sync immediately:
$ sudo systemctl start chronyd
For more information, see the Red Hat chrony guide.
Kerberos ticket is valid, but Hadoop access fails
Vertica uses Kerberos tickets to obtain Hadoop tokens. It then uses the Hadoop tokens to access the Hadoop data. Hadoop tokens expire after a period of time, so Vertica periodically refreshes them. However, if your Hadoop cluster is set to expire tokens frequently, it is possible that tokens might not be refreshed in time. If the token expires, you cannot access data.
Setting the HadoopFSTokenRefreshFrequency configuration parameter allows you to specify how often Vertica should refresh the token. Specify this value, in seconds, to be smaller than the expiration period set for Hadoop. For example:
=> ALTER DATABASE exampledb SET HadoopFSTokenRefreshFrequency = '86400';
For another cause of Hadoop access failure, see System Clocks Out of Sync.
Encryption algorithm choices
Kerberos is based on symmetric encryption. Be sure that all Kerberos parties used in the Kerberos realm agree on the encryption algorithm to use. If they do not agree, authentication fails. You can review the exceptions in the vertica.log
.
On a Windows client, be sure the encryption types match the types set on Active Directory. See Configure Vertica for Kerberos authentication.
Be aware that Kerberos is used only for securing the login process. After the login process completes, by default, information travels between client and server without encryption. If you want to encrypt traffic, use SSL. For details, see Implementing SSL.
Kerberos passwords not recognized
If you change your Kerberos password, you must re-create all of your keytab files.
Using the ODBC data source configuration utility
On Windows vsql clients, you may choose to use the ODBC Data Source Configuration utility and supply a client Data Source. If so, be sure you enter a Kerberos host name in the Client Settings tab to avoid client connection failures with the Vertica Analytic Database server.
This problem can arise in configurations where each Vertica node uses its own Kerberos principal. (This configuration is recommended.) When using vbr or admintools you might see an error such as the following:
$ vsql: GSSAPI continuation error: Miscellaenous failure
GSSAPI continuation error: Server not found in Kerberos database
Backup/restore and the admin tools use the value of KerberosHostname, if it is set, in the Kerberos principal used to authenticate. The same value is used on all nodes. If you have defined one Kerberos principal per node, as recommended, this value does not match. To correct this, unset the KerberosHostname parameter:
=> ALTER DATABASE DEFAULT CLEAR KerberosHostname;
Server's principal name does not match host name
This problem can arise in configurations where a single Kerberos principal is used for all nodes. Vertica recommends against using a single Kerberos principal for all nodes. Instead, use one principal per node and do not set the KerberosHostname parameter.
In some cases during client connection, the Vertica server's principal name might not match the host name in the connection string. (See also Using the ODBC Data Source Configuration Utility in this topic.)
On Windows vsql clients, you may choose to use the ODBC Data Source Configuration utility and supply a client Data Source. If so, be sure you enter a Kerberos host name in the Client Settings tab to avoid client connection failures with the Vertica server.
On ODBC, JDBC, and ADO.NET clients, set the host name portion of the server's principal using the KerberosHostName
connection string.
Tip
On vsql clients, you set the host name portion of the server's principal name using the -K KRB HOST
command-line option. The default value is specified by the -h
switch, which is the host name of the machine on which the Vertica server is running. -K
is equivalent to the drivers' KerberosHostName
connection string value.
For details, see Command Line Options.
Principal/host mismatch issues and resolutions
The following issues can occur if the principal and host are mismatched.
The KerberosHostName
configuration parameter has been overridden
For example, consider the following connection string:
jdbc:vertica://v_vmart_node0001.example.com/vmart?user=kuser
Because the this connection string includes no explicit KerberosHostName
parameter, the driver defaults to the host in the URL (v_vmart_node0001.example.com
). If you overwrite the server-side KerberosHostName
parameter as “abc
”, the client generates an incorrect principal.
To resolve this issue, explicitly set the client’s KerberosHostName
to the connection string, as in this example:
jdbc:vertica://v_vmart_node0001.example.com/vmart?user=kuser&kerberoshostname=abc
Connection load balancing is enabled... but the node against which the client authenticates might not be the node in the connection string.
In this situation, consider changing all nodes to use the same KerberosHostName
setting. When you use the default to the host that was originally specified in the connection string, load balancing cannot interfere with Kerberos authentication.
A DNS name does not match the Kerberos host name
For example, imagine a cluster of six servers, where you want hr-servers
and finance-servers
to connect to different nodes on the Vertica cluster. Kerberos authentication, however, occurs on a single (the same) KDC. In the following example, the Kerberos service host name of the servers is server.example.com
.
Suppose you have the following list of example servers:
server1.example.com 192.16.10.11
server2.example.com 192.16.10.12
server3.example.com 192.16.10.13
server4.example.com 192.16.10.14
server5.example.com 192.16.10.15
server6.example.com 192.16.10.16
Now, assume you have the following DNS entries:
finance-servers.example.com 192.168.10.11, 192.168.10.12, 192.168.10.13
hr-servers.example.com 192.168.10.14, 192.168.10.15, 192.168.10.16
When you connect to finance-servers.example.com
, specify:
For example:
$ vsql -h finance-servers.example.com -K server.example.com
No DNS is set up on the client machine... so you must connect by IP only
To resolve this issue, specify:
For example:
$ vsql -h 192.168.1.12 -K server.example.com
There is a load balancer involved (Virtual IP)... but there is no DNS name for the VIP
Specify:
For example:
$ vsql -h <virtual IP> -K server.example.com
You connect to Vertica using an IP address... but there is no host name to construct the Kerberos principal name.
Provide the instance or host name for the Vertica as described in Inform Vertica about the Kerberos principal
The server-side KerberosHostName
configuration parameter is set to a name other than the Vertica node's host name... but the client cannot determine the host name based on the host name in the connection string alone.
Reset KerberosHostName to match the name of the Vertica node's host name. For more information, see the following topics:
17 - LDAP authentication
Lightweight Directory Access Protocol (LDAP) is an authentication method that works like password authentication.
Lightweight Directory Access Protocol (LDAP) is an authentication method that works like password authentication. The main difference is that the LDAP method authenticates clients trying to access your Vertica database against an LDAP or Active Directory server. Use LDAP authentication when your database needs to authenticate a user with an LDAP or Active Directory server.
17.1 - LDAP prerequisites and definitions
Before you configure LDAP authentication for your Vertica database you must have:.
Prerequisites
Before you configure LDAP authentication for your Vertica database you must have:
-
IP address and host name for the LDAP server. Vertica supports IPv4 and IPv6 addresses.
-
Your organization's Active Directory information.
-
A service account for search and bind.
-
Administrative access to your Vertica database.
-
open-ldap-tools
package installed on at least one node. This package includes ldapsearch
.
Definitions
The following definitions are important to remember for LDAP authentication:
Parameter name |
Description |
Host |
IP address or host name of the LDAP server. Vertica supports IPv4 and IPv6 addresses. For more information, see IPv4 and IPv6 for Client Authentication. |
Common name (CN) |
Depending on your LDAP environment, this value can be either the username or the first and last name of the user. |
Domain component (DC) |
Comma-separated list that contains your organization's domain component broken up into separate values, for example:
dc=vertica, dc=com
|
Distinguished name (DN) |
domain.com. A DN consists of two DC components, as in "DC=example, DC= com". |
Organizational unit (OU) |
Unit in the organization with which the user is associated, for example, Vertica Users. |
sAMAccountName |
An Active Directory user account field. This value is usually the attribute to be searched when you use bind and search against the Microsoft Active Directory server. |
UID |
A commonly used LDAP account attribute used to store a username. |
Bind |
LDAP authentication method that allows basic binding using the DN. |
Search and bind |
LDAP authentication method that must log in to the LDAP server to search on the specified attribute. |
Service account |
An LDAP user account that can be used to log in to the LDAP server during bind and search. This account's password is usually shared. |
Anonymous binding |
Allows a client to connect and search the directory (search and bind) without needing to log in. |
ldapsearch |
A command-line utility to search the LDAP directory. It returns information that you use to configure LDAP search and bind. |
basedn |
Distinguished name where the directory search should begin. |
binddn |
Domain name to find in the directory search. |
search_attribute |
Text to search for to locate the user record. The default is UID. |
17.2 - LDAP authentication parameters
There are several parameters that you need to configure for LDAP authentication.
There are several parameters that you need to configure for LDAP authentication.
General LDAP parameters
Use the following parameters to configure for either LDAP bind or LDAP bind and search:
Parameter name |
Description |
host |
LDAP server URL in the following format:
schema ://host: optional_port
Where schema is one of the following:
-
ldap : The connection between Vertica and the LDAP server uses plaintext if TLSMODE of LDAPAuth is DISABLE . Set TLSMODE to ENABLE or higher for StartTLS (LDAP over TLS).
-
ldaps : If the TLSMODE of LDAPAuth is ENABLE or higher, the connection between Vertica and the LDAP server uses LDAPS.
|
ldap_continue |
When set to yes, this parameter allows a connection retry when a user not found error occurs during the previous connection attempt.
For any other failure error, the system automatically retries the connection.
|
starttls |
Whether to request the connection between Vertica and the LDAP server during user authentication to be upgraded to TLS. You must configure the LDAPAuth TLS Configuration before using this parameter.
starttls can be set to one of the following:
-
soft : If the server does not support TLS, use a plaintext connection. This value is equivalent to the -Z option in ldapsearch . If you use soft , Vertica ignores the certificate verification policies of the TLSMODE in the LDAPAuth TLS configuration.
-
hard : If the LDAP server does not support TLS, reject the connection. This value is equivalent to the -ZZ in ldapsearch .
Using ldaps is equivalent to starttls='hard' . However, if you use them together in the same connection string, authentication fails and the following error appears:
FATAL 2248: Authentication failed for username "<user_name>"
If starttls is not set, whether TLS is requested and required depends on the value of the TLSMODE of the LDAPAuth TLS Configuration.
|
LDAP bind parameters
The following parameters create a bind name string, which specifies and uniquely identifies a user to the LDAP server. For details, see Workflow for configuring LDAP bind.
To create a bind name string, you must set one (and only one) of the following:
For example, if you set binddn_prefix
and binddn_suffix
, you cannot also set email_suffix
. Conversely, if you set email_suffix
, you cannot set binddn_prefix
and binddn_suffix
.
If you do not set a bind parameter, Vertica performs bind and search operations instead of a bind operation.
The following examples use the authentication record v_ldap
:
=> CREATE AUTHENTICATION v_ldap METHOD 'ldap' HOST '10.0.0.0/23';
Parameter name |
Description |
binddn_prefix |
First half of the bind string. If you set this parameter, you must also set binddn_suffix .
For example, to construct the bind name cn= exampleusername,cn=Users,dc=ExampleDomain,dc=com :
=> ALTER AUTHENTICATION v_ldap SET
binddn_prefix='cn=', binddn_suffix=',cn=Users,dc=ExampleDomain,dc=com';
|
binddn_suffix |
Second half of bind string.
If you set this parameter, you must also set binddn_prefix .
For example, to construct the bind name cn=exampleusername, ou=ExampleUsers,dc=example,dc=com :
=> ALTER AUTHENTICATION v_ldap SET
binddn_prefix='cn=', binddn_suffix=',ou=OrgUsers,dc=example,dc=com';
|
domain_prefix |
The domain that contains the user.
For example, to construct the bind name Example \exampleusername :
=> ALTER AUTHENTICATION v_ldap SET domain_prefix='Example';
|
email_suffix |
The email domain.
For example, to construct the bind name exampleusername@ example.com
=> ALTER AUTHENTICATION v_ldap SET email_suffix='example.com';
|
LDAP search and bind parameters
Use the following parameters when authenticating with LDAP search and bind. For more information see Workflow for configuring LDAP search and bind.
Parameter name |
Description |
basedn |
Base DN for search. |
binddn |
Bind DN. Domain name to find in the directory search. |
bind_password |
Bind password. Required if you specify a binddn. |
search_attribute |
Optional attribute to search for on the LDAP server. |
The following example shows how to set these three attributes. In this example, it sets
=> ALTER AUTHENTICATION auth_method_name SET host='ldap://example13',
basedn='dc=example,dc=com',binddn='cn=Manager,dc=example,dc=com',
bind_password='secret',search_attribute='cn';
The binddn
and bind_password
parameters are optional. If you omit them, Vertica performs an anonymous search.
17.3 - TLS for LDAP authentication
This page covers the LDAPAuth context.
Vertica establishes a connection to an LDAP server in two contexts, and each context has a corresponding TLS Configuration that controls if each connection should use TLS:
-
LDAPLink: using the LDAPLink service or its dry run functions to synchronize users and groups between Vertica and the LDAP server.
-
LDAPAuth: when a user with an ldap
authentication method attempts to log into Vertica, Vertica attempts to bind the user to a matching user in the LDAP server. If the bind succeeds, Vertica allows the user to log in.
Query TLS_CONFIGURATIONS to view existing TLS Configurations:
=> SELECT * FROM tls_configurations WHERE name IN ('LDAPLink', 'LDAPAuth');
name | owner | certificate | ca_certificate | cipher_suites | mode
----------+---------+-------------+----------------+---------------+----------
LDAPLink | dbadmin | client_cert | ldap_ca | | VERIFY_CA
LDAPAuth | dbadmin | client_cert | ldap_ca | | DISABLE
(2 rows)
This page covers the LDAPAuth context. For details on the LDAPLink context, see TLS for LDAP link.
Keep in mind that configuring TLS for LDAP authentication does not encrypt the connection between Vertica and the client with TLS. To configure client-server TLS, see Configuring client-server TLS.
Configuring LDAP authentication
After a client successfully establishes a connection with Vertica, they must authenticate as a user before they can interact with the database. If the user has the ldap
authentication method, Vertica connects to the LDAP server to authenticate the user. To configure TLS for this context, use the following procedure.
Setting the LDAPAuth TLS configuration
The LDAPAuth TLS Configuration takes a client certificate and CA certificate created or imported with CREATE CERTIFICATE. Vertica presents the client certificate to the LDAP server for verification by its CA. Vertica uses the CA certificate to verify the LDAP server's certificate.
For details on key and certificate generation, see Generating TLS certificates and keys.
-
If you want Vertica to verify the LDAP server's certificate before establishing the connection, generate or import a CA certificate and add it to the LDAPAuth TLS CONFIGURATION.
For example, to import the existing CA certificate LDAP_CA.crt
:
=> \set ldap_ca '\''`cat ldap_ca.crt`'\''
=> CREATE CA CERTIFICATE ldap_ca AS :ldap_ca;
CREATE CERTIFICATE
Then, to add the ldap_ca
CA certificate to LDAPAuth:
ALTER TLS CONFIGURATION LDAPAuth ADD CA CERTIFICATES ldap_ca;
-
If your LDAP server verifies client certificates, you must generate or import a client certificate and its key and add it to the LDAPAuth TLS Configuration. Vertica presents this certificate to the LDAP server for verification by its CA.
For example, to import the existing certificate client.crt
(signed by the imported CA) and key client.key
:
=> \set client_key '\''`cat client.key`'\''
=> CREATE KEY client_key TYPE 'RSA' AS :client_key;
CREATE KEY
=> \set client_cert '\''`cat client.crt`'\''
=> CREATE CERTIFICATE client_cert AS :client_cert SIGNED BY ldap_ca KEY client_key;
CREATE CERTIFICATE
Then, to add client_cert
to LDAPAuth:
=> ALTER TLS CONFIGURATION LDAPAuth CERTIFICATE client_cert;
-
Enable TLS or LDAPS (the exact protocol used depends on the value of host
in the AUTHENTICATION object) by setting the TLSMODE to one of the following. TRY_VERIFY
or higher requires a CA certificate:
-
ENABLE
: Enables TLS. Vertica does not check the LDAP server's certificate.
-
TRY_VERIFY
: Establishes a TLS connection if one of the following is true:
If the LDAP server presents an invalid certificate, a plaintext connection is used.
-
VERIFY_CA
: Connection succeeds if Vertica verifies that the LDAP server's certificate is from a trusted CA. Using this TLSMODE forces all connections without a certificate to use plaintext.
-
VERIFY_FULL
: Connection succeeds if Vertica verifies that the LDAP server's certificate is from a trusted CA and the cn
(Common Name) or subjectAltName
attribute matches the hostname or IP address of the LDAP server.
The cn
is used for the username, so subjectAltName
must match the hostname or IP address of the LDAP server.
Note
The value of TLSMODE only applies to
authentication records where the
starttls
LDAP authentication parameter is set to
hard
or not set at all. If
starttls
is set to
soft
, Vertica establishes a TLS connection without verifying the LDAP server's certificate and falls back to a plaintext connection if the LDAP server does not support TLS. For details, see the next section.
For example:
=> ALTER TLS CONFIGURATION LDAPAuth TLSMODE 'verify_ca';
ALTER TLS CONFIGURATION
-
Verify that the LDAPAuthConfigParameter parameter is using the TLS Configuration:
=> SHOW CURRENT LDAPAuthTLSConfig;
level | name | setting
---------+-------------------+----------
DEFAULT | LDAPAuthTLSConfig | LDAPAuth
(1 row)
Creating an LDAP authentication record
After a client successfully establishes a connection with Vertica, they must authenticate as a user before they can interact with the database. If the user has the ldap
authentication method, Vertica connects to the LDAP server and attempts a bind to authenticate the user.
To view existing authentication records, query CLIENT_AUTH.
For details on the parameters referenced in this procedure, see LDAP authentication parameters.
-
CREATE an authentication record with an LDAP method.
Syntax for creating an LDAP authentication record:
=> CREATE AUTHENTICATION auth_record_name method 'ldap' HOST 'user_connection_source';
For example, to create an LDAP authentication record that applies to users that connect from any host:
=> CREATE AUTHENTICATION ldap_auth METHOD 'ldap' HOST '0.0.0.0/0';
-
ALTER the authentication record to to set the host and port (optional) of the LDAP server and the domain name (basedn
) and bind distinguished name (binddn
).
-
To use a plaintext connection between Vertica and the LDAP server (disable TLS):
-
To use StartTLS and reject plaintext connections:
-
Begin the host
URL with ldap://
.
-
Set the TLSMODE of LDAPAuth
to ENABLE
or higher. Vertica only verifies the LDAP server's certificate if TLSMODE is set to TRY_VERIFY
or higher.
-
Verify that starttls
is set to hard
or not set.
-
To use StartTLS, but still accept a plaintext connection if the LDAP server cannot be upgrade the connection to TLS:
-
Begin the host
URL with ldap://
.
-
Set starttls
to soft
and the TLSMODE of LDAPAuth
to ENABLE
or higher. Vertica does not verify the server's certificate before establishing the connection and ignores the certificate verification policy of the LDAPAuth TLSMODE.
-
To use LDAPS:
This example authentication record searches for users in the active directory orgunit.example.com
on an LDAP server with an IP address of 192.0.2.0 on port 5389 and requires a TLS connection to the LDAP server:
=> ALTER AUTHENTICATION ldap_auth SET
host='ldap://192.0.2.0:5389',
basedn='ou=orgunit,dc=example,dc=com',
binddn_prefix='cn=',
binddn_suffix=',ou=orgunit,dc=example,dc=com',
starttls='hard';
The binddn_prefix
and binddn_suffix
combine to create the full DN. That is, for some Vertica user asmith, 'cn=
asmith
,ou=orgunit,dc=example,dc=com
' is the full DN when Vertica attempts the bind.
To modify the ldap_auth
authentication record to request StartTLS, but still accept plaintext connections, set the starttls
parameter to soft
:
=> ALTER AUTHENTICATION ldap_auth SET starttls='soft';
-
Enable the authentication record:
=> ALTER AUTHENTICATION ldap_auth ENABLE;
-
GRANT the authentication record to a user or role.
For example:
=> GRANT AUTHENTICATION ldap_auth TO asmith;
In this case, when the user asmith attempts to log in, Vertica constructs the distinguished name 'cn=asmith,ou=orgunit,dc=example,dc=com' from the search base specified in the ldap_auth, connects to the LDAP server, and attempts to bind it to the Vertica user. If the bind succeeds, Vertica allows asmith to log in.
17.4 - Authentication fallthrough for LDAP
To use multiple search attributes for a single LDAP server or to configure multiple LDAP servers, create a separate authentication record for each search attribute or server and enable authentication fallthrough on each ldap record except the last (in order of priority).
To use multiple search attributes for a single LDAP server or to configure multiple LDAP servers, create a separate authentication record for each search attribute or server and enable authentication fallthrough on each ldap
record except the last (in order of priority).
Examples
The following example creates two authentication records, vldap1
and vldap2
. Together, they specify that the LDAP server should first search the entire directory (basedn=dc=example,dc=com
) for a DN with an OU attribute Sales
. If the first search returns no results or otherwise fails, the LDAP server should then search for a DN with the OU attribute Marketing
:
=> CREATE AUTHENTICATION vldap1 method 'ldap' HOST '10.0.0.0/8' FALLTHROUGH;
=> ALTER AUTHENTICATION vldap1 PRIORITY 1;
=> ALTER AUTHENTICATION vldap1
SET host='ldap://ldap.example.com/search',
basedn='dc=example,dc=com',
search_attribute='Sales';
=> GRANT AUTHENTICATION vldap1 to public;
=> CREATE AUTHENTICATION vldap2 method 'ldap' HOST '10.0.0.0/8';
=> ALTER AUTHENTICATION vldap2 PRIORITY 0;
=> ALTER AUTHENTICATION vldap2 SET
host='ldap://ldap.example.com/search',
basedn='dc=example,dc=com',
search_attribute='Marketing';
=> GRANT AUTHENTICATION vldap2 to public;
17.5 - LDAP bind methods
There are two LDAP methods that you use to authenticate your Vertica database against an LDAP server.
There are two LDAP methods that you use to authenticate your Vertica database against an LDAP server.
-
Bind—Use LDAP bind when Vertica connects to the LDAP server and binds using the CN and password. (These values are the username and password of the user logging into the database). Use the bind method when your LDAP account's CN field matches that of the username defined in your database. For more information see Workflow for configuring LDAP bind.
-
Search and Bind —Use LDAP search and bind when your LDAP account's CN field is a user's full name or does not match the username defined in your database. For search and bind, the username is usually in another field such as UID or sAMAccountName in a standard Active Directory environment. Search and bind requires your organization's Active Directory information. This information allows Vertica to log into the LDAP server and search for the specified field. For more information see Workflow for configuring LDAP search and bind.
If you are using search and bind, having a service account simplifies your server side configuration. In addition, you do not need to store your Active Directory password.
LDAP anonymous binding
Anonymous binding is an LDAP server function. Anonymous binding allows a client to connect and search the directory (bind and search) without logging in because binddn and bindpasswd are not needed.
You also do not need to log in when you configure LDAP authentication using Management Console.
17.5.1 - Workflow for configuring LDAP bind
To configure your Vertica database to authenticate clients using LDAP bind, follow these steps:.
To configure your Vertica database to authenticate clients using LDAP bind, follow these steps:
-
Obtain a service account. For information see the LDAP product documentation.You cannot use the service account in the connection parameters for LDAP bind.
-
Compare the user's LDAP account name to their Vertica username. For example, if John Smith's Active Directory (AD) sAMAccountName = jsmith, his Vertica username must also be jsmith.
However, the LDAP account does not have to match the database user name, as shown in the following example:
=> CREATE USER r1 IDENTIFIED BY 'password';
=> CREATE AUTHENTICATION ldap1 METHOD 'ldap' HOST '172.16.65.177';
=> ALTER AUTHENTICATION ldap1 SET HOST=
'ldap://172.16.65.10',basedn='dc=dc,dc=com',binddn_suffix=',ou=unit2,dc=dc,dc=com',binddn_prefix='cn=use';
=> GRANT AUTHENTICATION ldap1 TO r1;
\! ${TARGET}/bin/vsql -p $PGPORT -U r1 -w $LDAP_USER_PASSWD -h ${HOSTNAME} -c
"select user_name, client_authentication_name from sessions;"
user_name | client_authentication_name
-----------+----------------------------
r1 | ldap
(1 row)
-
Run ldapsearch
from a Vertica node against your LDAP or AD server. Verify the connection to the server and identify the values of relevant fields. Running ldapsearch
helps you build the client authentication string needed to configure LDAP authentication.
In the following example, ldapsearch
returns the CN, DN, and sAMAccountName fields (if they exist) for any user whose CN contains the username jsmith. This search succeeds only for LDAP servers that allow anonymous binding:
$ ldapsearch -x -h 10.10.10.10 -b "ou=Vertica Users,dc=CompanyCorp,dc=com"
'(cn=jsmith*)' cn dn uid sAMAccountName
ldapsearch
returns the following results. The relevant information for LDAP bind is in bold:
# extended LDIF
#
# LDAPv3
# base <ou=Vertica Users,dc=CompanyCorp,dc=com> with scope subtree
# filter: (cn=jsmith*)
# requesting: cn dn uid sAMAccountName
#
# jsmith, Users, CompanyCorp.com
dn:cn=jsmith,ou=Vertica Users,dc=CompanyCorp,dc=com
cn: jsmith
uid: jsmith
# search result
search: 2
result: 0 Success
# numResponses: 2
# numEntries: 1
-
Create a new authentication record based on the information from ldapsearch
. In the ldapsearch
entry, the CN is username jsmith, so you do not need to set it. Vertica automatically sets the CN to the username of the user who is trying to connect. Vertica uses that CN to bind against the LDAP server.
=> CREATE AUTHENTICATION v_ldap_bind METHOD 'ldap' HOST '0.0.0.0/0';
=> GRANT AUTHENTICATION v_ldap_bind TO public;
=> ALTER AUTHENTICATION v_ldap_bind SET
host='ldap://10.10.10.10/',
basedn='DC=CompanyCorp,DC=com',
binddn_prefix='cn=',
binddn_suffix=',OU=Vertica Users,DC=CompanyCorp,DC=com';
For more information see LDAP Bind Parameters.
17.5.2 - Workflow for configuring LDAP search and bind
To configure your Vertica database to authenticate clients using LDAP search and bind, follow these steps:.
To configure your Vertica database to authenticate clients using LDAP search and bind, follow these steps:
-
Obtain a service account. For information see the LDAP product documentation.
-
From a Vertica node, run ldapsearch
against your LDAP or AD server. Verify the connection to the server, and identify the values of relevant fields. Running ldapsearch
helps you build the client authentication string needed to configure LDAP authentication.
In the following example, ldapsearch
returns the CN, DN, and sAMAccountName fields (if they exist) for any user whose CN contains the username, John. This search succeeds only for LDAP servers that allow anonymous binding:
$ ldapsearch -x -h 10.10.10.10 -b 'OU=Vertica Users,DC=CompanyCorp,DC=com' -s sub -D
'CompanyCorp\jsmith' -W '(cn=John*)' cn dn uid sAMAccountName
-
Review the results that ldapsearch
returns.The relevant information for search and bind is in bold:
# extended LDIF
#
# LDAPv3
# base <OU=Vertica Users,DC=CompanyCorp,DC=com> with scope subtree
# filter: (cn=John*)
# requesting: cn dn sAMAccountName
#
# John Smith, Vertica Users, CompanyCorp.com
dn: CN=jsmith,OU=Vertica Users,DC=CompanyCorp,DC=com
cn: Jsmith
sAMAccountName: jsmith
# search result
search: 2
result: 0 Success
# numResponses: 2
# numEntries: 1
-
Create the client authentication record. The cn attribute contains the username you want—jsmith. Set your search attribute to the CN field so that the search finds the appropriate account.
=> CREATE AUTHENTICATION v_ldap_bind_search METHOD 'ldap' HOST '10.10.10.10';
=> GRANT AUTHENTICATION v_ldap_bind_search TO public;
=> ALTER AUTHENTICATION v_ldap_bind_search SET
host='ldap://10.10.10.10',
basedn='OU=Vertica,DC=CompanyCorp,DC=com',
binddn='CN=jsmith,OU=Vertica Users,DC=CompanyCorp,DC=com',
bind_password='password',
search_attribute='CN';
For more information see LDAP Bind and Search Parameters
18 - OAuth 2.0 authentication
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.
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.
18.1 - 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 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:
Create an authentication record
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.
Create a Vertica user
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.
Retrieve an access token
To authenticate to Vertica, you must retrieve an access token from the identity provider.
Programmatic method
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"}
Single-sign on (SSO)
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:
- For clients that use the
confidential
access type, set the oauthclientsecret
.
- In the authentication record, set the following parameters:
- The
discovery_url
(Keycloak only) or the following:
client_id
- Connect to Vertica with the ODBC driver. The default web browser opens to your IDP's sign-in page.
- Enter your credentials to authenticate to the IDP.
Run 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 provide a refresh token and a client secret.
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-secret OAuthClientSecret
For a list of all ODBC OAuth parameters, see ODBC DSN connection properties.
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-secret oauthclientsecret
Troubleshooting
To get debugging information for TLS, use the -Djavax.net.debug=ssl
flag.
Custom CA certificates
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
18.1.1 - Configure Keycloak
The following procedure configures a Keycloak 18.0.0 server on 203.0.113.1.
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:
- Client ID: The ID used to identify the Vertica database. This is configured by the user and set to
vertica
in the example procedure.
- Client secret: A Keycloak-generated string used to refresh the OAuth token when it expires.
- Discovery endpoint: The endpoint that serves information for all other endpoints as a JSON string. The endpoint for a Keycloak server on 203.0.113.1 is one of the following:
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-configuration
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. 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';
Start 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):
-
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).
Create the Vertica client
-
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.
Create a Keycloak user
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.
18.1.2 - Configure Okta
The following procedure configures Okta for integration with Vertica.
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:
- Client ID: The ID used to identify the Vertica database. This is generated by Okta.
- Client secret: An Okta-generated string used to refresh the OAuth token when it expires.
- Token endpoint: Used by the client to retrieve the OAuth token.
- Introspection endpoint: Used by Vertica to validate the OAuth token.
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.
Create an OIDC application
- From the Okta dashboard, go to Applications > Applications and select Create App Integration. The Create a new app integration dialog box appears.
- For the Sign-in method, select OIDC - OpenID Connect. The Application type section appears.
- For the Application type, select Native Application.
- Select Next. The New Native App Integration window opens.
- In the App integration name, enter a name for your application. This example uses Demo_Vertica.
- For the Grant Type, select Authorization Code, Refresh Token, and Resource Owner Password.
- For Controlled access, select the option applicable to your organization. This example uses Allow everyone in your organization to access.
- Select Save to save your application.
Retrieve the client ID and client secret
- From the Okta dashboard, go to Applications > Applications and select the name of your OIDC application.
- In the General tab in the Client Credentials section, select Edit.
- For Client authentication, select Client secret and select Save. This generates a new client secret.
- Copy the client ID and client secret.
Set the authentication policy
- From the Okta dashboard, go to Applications > Applications and select the name of your OIDC application.
- In the Sign On tab in the User authentication section, select Edit.
- Select Password only.
- Select Save to save the new policy.
Retrieve Okta endpoints
- From the Okta dashboard, go to Security > API.
- In the Authorization Servers tab, select the name of your authorization server. By default, the name of this server is default.
- Select the Metadata URI to get a list of all endpoints for your authorization server as a
json
string.
- Copy the values for the token_endpoint and introspection_endpoint.
Test the configuration
-
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.
18.2 - Just-in-time user provisioning
To use this feature, you must use Keycloak as your identity provider.
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.
Enabling just-in-time user provisioning
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)
Automatic role assignment
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.
Automatic user pruning
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.
Note
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.
18.3 - OAuth authentication parameters
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.
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.
Just-in-time provisioning parameters
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.
Validation modes
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.
IDP validation parameters
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 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.
|
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 subjectAltName 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.
|
Optional |
JWT validation parameters
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 |
19 - TLS authentication
The tls authentication method authenticates users that can establish a mutual mode client-server TLS connection using a certificate that specifies a valid database username in the Common Name (CN) field.
The tls
authentication method authenticates users that can establish a mutual mode client-server TLS connection using a certificate that specifies a valid database username in the Common Name (CN) field.
Before you create and use a tls
authentication method, you must configure Vertica for client-server TLS in mutual mode (disabled by default).
19.1 - Client authentication with TLS
Database users or roles granted a tls authentication record can authenticate to Vertica with a TLS certificate.
Database users or roles granted a tls
authentication record can authenticate to Vertica with a TLS certificate.
Prerequisites
You must configure Vertica for mutual mode client-server TLS.
In mutual mode, the client and server must verify each other's identity before connecting. This mode allows Vertica to verify the identity of the client and allow them to authenticate the client through their certificate.
Configuring TLS authentication
The following sections generate a private key and certificate for the client. For simplicity, the example signs the client certificate with the following self-signed CA certificate (which has also, in the context of the example, signed the Vertica database's server certificate):
=> CREATE KEY SSCA_key TYPE 'RSA' LENGTH 2048;
=> CREATE CA CERTIFICATE ca_certificate
SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=Vertica Root CA'
VALID FOR 3650
EXTENSIONS 'nsComment' = 'Self-signed root CA cert'
KEY SSCA_key;
In a production environment, you should instead use a CA certificate from a trusted certificate authority.
Create client keys
The following steps generate a client key and certificate, and then make them available to the client:
-
Generate the client key:
=> CREATE KEY client_private_key TYPE 'RSA' LENGTH 2048;
CREATE KEY
-
Generate the client certificate. Mutual TLS requires that the Common Name (CN
) in the SUBJECT
specifies a database username:
=> CREATE CERTIFICATE client_certificate
SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=dbadmin/emailAddress=example@example.com'
SIGNED BY ca_certificate
EXTENSIONS 'nsComment' = 'Vertica client cert', 'extendedKeyUsage' = 'clientAuth'
KEY client_private_key;
CREATE CERTIFICATE
-
On the client machine, export the client key and client certificate to the client filesystem. The following commands use the vsql client:
$ vsql -At -c "SELECT key FROM cryptographic_keys WHERE name = 'client_private_key';" -o client_private_key.key
$ vsql -At -c "SELECT certificate_text FROM certificates WHERE name = 'client_certificate';" -o client_cert.pem
In the preceding command:
-A
: enables unaligned output.
-t
: prevents the command from outputting metadata, such as column names.
-c
: instructs the shell to run one command and then exit.
-o
: writes the query output to the specified filename.
For details about all vsql command line options, see Command-line options
-
Copy or move the client key and certificate to a location that your client recognizes.
The following commands move the client key and certificate to the hidden directory ~/.client-creds
, and then grants the file owner read and write permissions with chmod
:
$ mkdir ~/.client-creds
$ mv client_private_key.key ~/.client-creds/client_key.key
$ mv client_cert.pem ~/.client-creds/client_cert.pem
$ chmod 600 ~/.client-creds/client_key.key ~/.client-creds/client_cert.pem
Create an authentication record
Next, you must create an authentication record in the database. An authentication record defines a set of authentication and the access methods for the database. You grant this record to a user or role to control how they authenticate to the database:
- Create the authentication record. The
tls
method requires that clients authenticate with a certificate whose Common Name (CN) specifies a database username:
=> CREATE AUTHENTICATION auth_record METHOD 'tls' HOST TLS '0.0.0.0/0';
CREATE AUTHENTICATION
- Grant the authentication record to a user or to a role. The following example grants the authentication record to PUBLIC, the default role for all users:
=> GRANT AUTHENTICATION auth_record TO PUBLIC;
GRANT AUTHENTICATION
Reject plaintext connections
You can create an authentication record that rejects remote connections from a specified IP range.
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