Dbadmin authentication access

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:

• Use fallthrough authentication.

• Create a custom, dbadmin-specific authentication method.

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;