Username is case sensitive after client authentication record is created

rupenc · April 2015

After creating a client authentication record, user cannot log in if the user provides correct username but with different case.

CREATE USER Test_user IDENTIFIED BY 'pass';

Without client authentrication record, the user can login usnig case-insensitive username.

However, after creating auth record, the user can login only if the username is provided as "Test_user" but not as "test_user" or so on.

I had created auth record as below-

CREATE AUTHENTICATION auth_pwd METHOD 'hash' LOCAL;
GRANT auth_pwd TO test_user;

Is there any way to make username case insensitivie when auth record is active?

Thanks,

Rupendra

[Deleted User] · April 2015

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

maria_2 · March 2016

Any more response on this?

Gayatri_R · March 2016

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

udaymatampusala · May 2018

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

Jim_Knicely · May 2018

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=>

udaymatampusala · May 2018

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

Jim_Knicely · May 2018

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!

Jim_Knicely · August 2018

@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

Username is case sensitive after client authentication record is created

Comments

Leave a Comment