Username is case sensitive after client authentication record is created

Hi Rupendra,

Thanks for contributing to the community. I'm looking into this issue now and will get back to you when I have more information.

-Vicki

HP Vertica Information Development

Hi

I just tested this on 7.2 and it is working.

vsql -d v720 -h hostname -U Test

vsql -d v720 -h hostname -U TEST

vsql -d v720 -h hostname -U test

all work fine.

What authentication method is your user using? Please provide details.

Thanks

Gayatri

I am facing the same issue. Was there any solution for this?

There is no issue...

dbadmin=> CREATE USER Test_user IDENTIFIED BY 'pass';
CREATE USER

dbadmin=> CREATE AUTHENTICATION auth_pwd METHOD 'hash' LOCAL;
CREATE AUTHENTICATION

dbadmin=> GRANT AUTHENTICATION auth_pwd TO test_user;
GRANT AUTHENTICATION

dbadmin=> \q
[dbadmin@s18384357 ~]$ vsql -U test_user -w pass
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

test_user=> \q

[dbadmin@s18384357 ~]$ vsql -U Test_User -w pass
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

Test_User=>

Connection through VSQL works fine in 7.x version but fails in 8.x version. When tested with any other SQL Client like Squirrel it fails in both 7.x and 8.x versions

Hi,

I performed my test above on Vertica 9.1.

Here is a test with 8.1.1-16:

dbadmin=> SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v8.1.1-16
(1 row)

dbadmin=> CREATE USER Test_user IDENTIFIED BY 'pass';
CREATE USER

dbadmin=> CREATE AUTHENTICATION auth_pwd METHOD 'hash' LOCAL;
CREATE AUTHENTICATION

dbadmin=> GRANT AUTHENTICATION auth_pwd TO test_user;
GRANT AUTHENTICATION

dbadmin=> \q
[dbadmin@vertica8 ~]$ vsql -U test_user -w pass
vsql: FATAL 3781:  Invalid username or password

[dbadmin@vertica8 ~]$ vsql -U Test_user -w pass
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

Test_user=>

So there is an issue with 8.1.x.

I will open a ticket on this and keep this thread updated.

Thanks!

@udaymatampusala - Vertica strongly recommends that you use SHA-512 for hash authentication because it is more secure than MD5.

If I switch the “Hash Authentication” for the user I tested in the previous posts in this thread to “SHA512”, I can log in as a case-insensitive user:

Example:

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v9.1.0-1
(1 row)

dbadmin=> SELECT * FROM client_auth;
     auth_oid      | auth_name | is_auth_enabled | auth_host_type | auth_host_address | auth_method | auth_parameters | auth_priority
-------------------+-----------+-----------------+----------------+-------------------+-------------+-----------------+---------------
45035996273708748 | auth_pwd  | True            | LOCAL          |                   | HASH        |                 |             0
(1 row)

dbadmin=> SELECT * FROM user_client_auth;
     user_oid      | user_name |     auth_oid      | auth_name | granted_to
-------------------+-----------+-------------------+-----------+------------
45035996273708680 | Test_user | 45035996273708748 | auth_pwd  | Test_user
(1 row)

dbadmin=> ALTER USER test_user SECURITY_ALGORITHM 'SHA512' IDENTIFIED BY 'pass1';
ALTER USER

dbadmin=> \q

[dbadmin@vertica8 ~]$ vsql -U tEsT_UsEr -w pass1
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

tEsT_UsEr=>

See:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/Security/ClientAuth/ConfiguringHashAuthentication.htm