The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Username is case sensitive after client authentication record is created

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

  • 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

  • Any more response on this?
  • 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?

  • Jim_KnicelyJim_Knicely 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
    [[email protected] ~]$ 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
    
    [[email protected] ~]$ 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

  • Jim_KnicelyJim_Knicely 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
    [[email protected] ~]$ vsql -U test_user -w pass
    vsql: FATAL 3781:  Invalid username or password
    
    [[email protected] ~]$ 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_KnicelyJim_Knicely 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
    
    [[email protected] ~]$ 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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.