Options

Username is case sensitive after client authentication record is created

rupencrupenc
in General Discussion

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

Comments

  • Options
    [Deleted User][Deleted User]

    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

  • Options
    maria_2maria_2
    Any more response on this?
  • Options
    Gayatri_RGayatri_R

    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

     

     

     

  • Options
    udaymatampusalaudaymatampusala

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

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator

    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=>
  • Options
    udaymatampusalaudaymatampusala

    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

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator

    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!

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator

    @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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...
gdn.meta.AnalyticsTask=false; // Check if the user has given consent. let cookieConsent = getCookie('consent'); // If the user has given consent set the track to track them. if (cookieConsent) { gdn.meta.AnalyticsTask='tick'; }