Unable to login to Vertica database without using the `-h` flag (HOSTNAME)

MinatV

I am unable to login to Vertica database without using the -h flag (HOSTNAME)

This worked perfectly fine in Vertica 11.1, but does not work in Vertica 23.4.

Obviously it works after setting the environment variable VSQL_HOST, but I am more interested in why it does not work, or what changed in the newer version.

h3. Failed auth when not using -h option:

dbadmin@5c99b9dd6671:/$ vsql -U dbadmin -w *****
vsql: FATAL 2248:  Authentication failed for username "dbadmin"

h3. Successful auth:

dbadmin@5c99b9dd6671:/$ vsql -U dbadmin -w ****** -h ids-database
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

SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, protocol: TLSv1.2)

insights_data=>

h3. Data from auth specific system tables user_client_auth, client_auth_params, client_auth

insights_data=> select auth_name from user_client_auth where user_name = 'dbadmin';
         auth_name
---------------------------
 pl_ident_local
 pl_hash_local
 pl_hash_remote
 pl_hash_remote_v6
 default_hash_network_ipv4
 default_hash_network_ipv6
 default_hash_local
(7 rows)


insights_data=> select auth_name, auth_parameter_name, auth_parameter_value from client_auth_params where auth_name = 'pl_ident_local';

   auth_name    | auth_parameter_name | auth_parameter_value
----------------+---------------------+----------------------
 pl_ident_local | system_users        | root:dbadmin
(1 row)

insights_data=> select * from client_auth;
     auth_oid      |         auth_name         | is_auth_enabled | auth_host_type | auth_host_address | auth_method |      auth_parameters      | auth_priority | method_priority | address_priority | is_fallthrough_enabled
-------------------+---------------------------+-----------------+----------------+-------------------+-------------+---------------------------+---------------+-----------------+------------------+------------------------
 45035996273705158 | default_hash_network_ipv4 | True            | HOST           | 0.0.0.0/0         | HASH        |                           |            -1 |               2 |               96 | False
 45035996273705162 | default_hash_network_ipv6 | True            | HOST           | ::/0              | HASH        |                           |            -1 |               2 |                0 | False
 45035996273705166 | default_hash_local        | True            | LOCAL          |                   | HASH        |                           |            -1 |               2 |                0 | False
 45035996273707756 | pl_ident_local            | True            | LOCAL          |                   | IDENT       | system_users=root:dbadmin |         10000 |               5 |                0 | False
 45035996273707758 | pl_hash_local             | True            | LOCAL          |                   | HASH        |                           |          2000 |               2 |                0 | False
 45035996273707760 | pl_hash_remote            | True            | HOST           | 0.0.0.0/0         | HASH        |                           |          1000 |               2 |               96 | False
 45035996273707762 | pl_hash_remote_v6         | True            | HOST           | ::/0              | HASH        |                           |             0 |               2 |                0 | False
(7 rows)

Any pointer would be helpful.

The side-effect of above issue it that, some dbadmin operations error out as well.

#{"description": "get nodes in database", "operations": [["vertica.engine.api.db_client.operations", "Select", {"name": "select", "sql": "select node_name from nodes order by node_name asc;"}]], "user": "dbadmin", "port": 5433, "database": "insights_data", "__rollback__": false, "__protocol__": "2.0"}

2023-12-18 09:39:14.955 admintools/1459:0x7f5c79a8b4c0 [adminExec._generateNodeDefinitions] <ERROR> DB client operation "get nodes in database" failed during `connect-secure`: ConnectionError: Severity: FATAL, Message: Authentication failed for username "dbadmin", Sqlstate: 28000, Routine: auth_failed, File: /data/jenkins/workspace/RE-ReleaseBuilds/RE-Laserbeam_2/server/vertica/Basics/ClientAuthentication.cpp, Line: 1275, Error Code: 2248
2023-12-18 09:39:14.956 admintools/1459:0x7f5c79a8b4c0 [commandLineCtrl.dbAddSubcluster] <INFO> Error: failed to generate node definitions.

Bryan_H

You have IDENT authentication set for LOCAL as the highest priority, with HASH as highest priority for remote. However, this would only affect system users root and dbadmin specified for IDENT. Can you connect as another user with username/password without using -h?
Using -h creates a remote connection and selects the remote HASH method. Does it break if you set "-h localhost"?
Possible workarounds would be to set authentication fallthrough to true so the HASH method will be tried if IDENT fails or remove IDENT entirely to always require a password. I will also check whether these changed between 11.x and 23.4
It would be helpful to see the vertica.log for the above tests. You might open a support case for this or contact me directly by email.

SruthiA

fallthrough authentication was introduced and it causes change in behavior. is dbadmin granted with more than one authentication method? if so please change it.. for more information, please go through below link

https://www.vertica.com/blog/using-fall-through-authentication/