dbadmin account Password Expiration

verbanverban Vertica Customer ✭
in General Discussion

Hi,
Last year i set PASSWORD_LIFE_TIME for dbadmin account,
Now yesterday i found I have an issue on my database service and i cannot login to database (vsql, admintools, odbc, ...)
I verified the logs and i found these logs:

2021-10-29 10:35:08.114 EEThread:0x7f89c0fff600-a0000000c036b7 [EE] Running ROS from sort buffer. Merge chunks = 1, merges per batch = 628
2021-10-29 10:35:08.365 EEThread:0x7f89c0fff600-a0000000c036b7 [EE] Finished writing one ROS at 0.24 sec. Write cost 0.18 sec, including compress pipe wait 0.00 sec
2021-10-29 10:35:08.365 EEThread:0x7f89c0fff600-a0000000c036b7 [EE] MergeHeap wait for child task 0 sec,other cost 0 sec.
2021-10-29 10:35:08.365 EEThread:0x7f89c0fff600-a0000000c036b7 [EE] Finalizing ROS container [0] with EE is [still running]
2021-10-29 10:35:08.386 EEThread:0x7f89c0fff600-a0000000c036b7 [EE] Finished writing ROSes from sort buffer.
2021-10-29 10:58:44.000 TM Moveout:0x7f89c0fff600-a0000000c03b1a [Txn] Begin Txn: a0000000c03b1a 'Moveout: Tuple Mover'
2021-10-29 10:58:44.000 TM Moveout:0x7f89c0fff600-a0000000c03b1a [Txn] Rollback Txn: a0000000c03b1a 'Moveout: Tuple Mover'
2021-10-29 10:58:44.000 TM Moveout:0x7f89c0fff600 [Util] Task 'TM Moveout' enabled
2021-10-29 10:58:48.544 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/2705: Connection received: host=10.xxx.xxx.xxx port=50776 (connCnt 6)
2021-10-29 10:58:48.544 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/4686: Authentication - sendAuthRequest: user=dbadmin database=appdb host=10.xxx.xxx.xxx authType=66048
2021-10-29 10:58:48.544 Init Session:0x7f89c0fff600-a0000000c03b1b [Txn] Begin Txn: a0000000c03b1b 'check_login_history'
2021-10-29 10:58:48.544 Init Session:0x7f89c0fff600-a0000000c03b1b [Txn] Rollback Txn: a0000000c03b1b 'check_login_history'
2021-10-29 10:58:48.549 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/4687: Authentication - sendAuthPswdChangeRequest: user=dbadmin database=appdb host=10.xxx.xxx.xxx authType=AUTH_REQ_CHANGE_PASSWORD
2021-10-29 10:58:48.550 Init Session:0x7f89c0fff600-a0000000c03b1c [Txn] Begin Txn: a0000000c03b1c 'update_user_salt'
2021-10-29 10:58:48.677 Init Session:0x7f89c0fff600-a0000000c03b1c [Txn] Starting Commit: Txn: a0000000c03b1c 'update_user_salt' 3369867
2021-10-29 10:58:48.692 Init Session:0x7f89c0fff600 [Txn] Commit Complete: Txn: a0000000c03b1c at epoch 0x3244d2 and new global catalog version 3369868
2021-10-29 10:59:03.603 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/2705: Connection received: host=10.xxx.xxx.xxx port=50781 (connCnt 6)
2021-10-29 10:59:03.604 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/4686: Authentication - sendAuthRequest: user=dbadmin database=appdb host=10.xxx.xxx.xxx authType=66048
2021-10-29 10:59:03.606 Init Session:0x7f89c0fff600-a0000000c03b29 [Txn] Begin Txn: a0000000c03b29 'check_login_history'
2021-10-29 10:59:03.606 Init Session:0x7f89c0fff600-a0000000c03b29 [Txn] Rollback Txn: a0000000c03b29 'check_login_history'
2021-10-29 10:59:03.607 Init Session:0x7f89c0fff600-a0000000c03b2a [Txn] Begin Txn: a0000000c03b2a 'update_login_history'
2021-10-29 10:59:03.608 Init Session:0x7f89c0fff600-a0000000c03b2a [Txn] Starting Commit: Txn: a0000000c03b2a 'update_login_history' 3369873
2021-10-29 10:59:03.608 Init Session:0x7f89c0fff600 [Txn] Commit Complete: Txn: a0000000c03b2a at epoch 0x3244d2 and new global catalog version 3369874
2021-10-29 10:59:03.608 Init Session:0x7f89c0fff600 @v_appdb_node0001: {SessionRun} 28000/3781: Invalid username or password
2021-10-29 10:59:08.903 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/2705: Connection received: host=10.xxx.xxx.xxx port=50785 (connCnt 6)
2021-10-29 10:59:08.903 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/4686: Authentication - sendAuthRequest: user=dbadmin database=appdb host=10.xxx.xxx.xxx authType=66048
2021-10-29 10:59:08.904 Init Session:0x7f89c0fff600-a0000000c03b33 [Txn] Begin Txn: a0000000c03b33 'check_login_history'
2021-10-29 10:59:08.904 Init Session:0x7f89c0fff600-a0000000c03b33 [Txn] Rollback Txn: a0000000c03b33 'check_login_history'
2021-10-29 10:59:08.905 Init Session:0x7f89c0fff600-a0000000c03b34 [Txn] Begin Txn: a0000000c03b34 'update_login_history'
2021-10-29 10:59:08.906 Init Session:0x7f89c0fff600-a0000000c03b34 [Txn] Starting Commit: Txn: a0000000c03b34 'update_login_history' 3369878
2021-10-29 10:59:08.906 Init Session:0x7f89c0fff600 [Txn] Commit Complete: Txn: a0000000c03b34 at epoch 0x3244d2 and new global catalog version 3369879
2021-10-29 10:59:08.907 Init Session:0x7f89c0fff600 @v_appdb_node0001: {SessionRun} 28000/3781: Invalid username or password
2021-10-29 10:59:15.909 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/2705: Connection received: host=10.xxx.xxx.xxx port=50790 (connCnt 6)
2021-10-29 10:59:15.909 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/4686: Authentication - sendAuthRequest: user=dbadmin database=appdb host=10.xxx.xxx.xxx authType=66048
2021-10-29 10:59:15.910 Init Session:0x7f89c0fff600-a0000000c03b39 [Txn] Begin Txn: a0000000c03b39 'check_login_history'
2021-10-29 10:59:15.910 Init Session:0x7f89c0fff600-a0000000c03b39 [Txn] Rollback Txn: a0000000c03b39 'check_login_history'
2021-10-29 10:59:15.911 Init Session:0x7f89c0fff600-a0000000c03b3a [Txn] Begin Txn: a0000000c03b3a 'update_login_history'
2021-10-29 10:59:15.931 Init Session:0x7f89c0fff600-a0000000c03b3a [Txn] Starting Commit: Txn: a0000000c03b3a 'update_login_history' 3369881
2021-10-29 10:59:15.931 Init Session:0x7f89c0fff600 [Txn] Commit Complete: Txn: a0000000c03b3a at epoch 0x3244d2 and new global catalog version 3369882
2021-10-29 10:59:15.931 Init Session:0x7f89c0fff600 @v_appdb_node0001: {SessionRun} 28000/3781: Invalid username or password
2021-10-29 11:00:02.310 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/2705: Connection received: host=10.xxx.xxx.xxx port=50805 (connCnt 6)
2021-10-29 11:00:02.310 Init Session:0x7f89c0fff600 @v_appdb_node0001: 00000/4686: Authentication - sendAuthRequest: user=dbadmin database=appdb host=10.xxx.xxx.xxx authType=66048
2021-10-29 11:00:02.311 Init Session:0x7f89c0fff600-a0000000c03b4d [Txn] Begin Txn: a0000000c03b4d 'check_login_history'
2021-10-29 11:00:02.311 Init Session:0x7f89c0fff600-a0000000c03b4d [Txn] Rollback Txn: a0000000c03b4d 'check_login_history'
2021-10-29 11:00:02.311 Init Session:0x7f89c0fff600-a0000000c03b4e [Txn] Begin Txn: a0000000c03b4e 'update_login_history'
2021-10-29 11:00:02.313 Init Session:0x7f89c0fff600-a0000000c03b4e [Txn] Starting Commit: Txn: a0000000c03b4e 'update_login_history' 3369891
2021-10-29 11:00:02.313 Init Session:0x7f89c0fff600 [Txn] Commit Complete: Txn: a0000000c03b4e at epoch 0x3244d2 and new global catalog version 3369892
2021-10-29 11:00:02.313 Init Session:0x7f89c0fff600 @v_appdb_node0001: {SessionRun} 28000/3781: Invalid username or password

I what to know that is the "AUTH_REQ_CHANGE_PASSWORD" message (on 10:58:48.549)?
What does mean "update_user_salt"?
And after this transaction, my application received "Invalid username or password" message continuously!

It seems the dbadmin password was changed without any user action (in a non interactive session), why it happend? and how can i recover the password?

(Datavase version: Vertica 9.3)

Tagged:

Best Answer

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited November 2021 Answer ✓

    @verban -

    AUTH_REQ_CHANGE_PASSWORD means that whoever was trying to login as the dbadmin user was asked to change the password.

    When you see "authType=66048" that means the authentication attempted was a HASH_SHA512

    The "salt" is the a hex string used to hash the password.

    Were you the one who changed the password? Do you have specific authentication records in place as described here: How to Configure Hash Authentication?

    Did you try vsql directly from one of the nodes?

    One thing you may have missed was making sure to set up DBADMIN Authentication Access.

    Anyway, if you can no longer log in to the DB, the nice folks in support can help you get back in! Sorry, but we can't help with those steps on this forum.

    If you are having problems logging a case, please email me your account info @ james.knicely@vertica.com and I will get it fixed for you.

Answers

  • moshegmosheg Vertica Employee Administrator

    Please open a support case to follow-up on this.

  • verbanverban Vertica Customer ✭

    Why most of recent questiopns respond with ticket submission and active support contract!

    additionally I'm using enterprise perpetual license and also requested to get entitlements access to open cases 6H ago.
    And the support center is checking if we have an active support contract or not!

    I just activated a normal password policy feature and after password expiration time i cannot login anymore!!!
    I think this is a bug !

  • verbanverban Vertica Customer ✭

    Dears,

    Unfotunatly another DB was broken and i do not have access to it anymore.
    Same as last DB, the dbadmin password was expired and the password was changed to unknown value automatically ...
    I'm pretty sure this is a bug and the DBs are useless now!

    ps:
    I'm agree with @Jim_Knicely to configure the 'LOCAL Trust' for dbadmin that it'll be helpfull in these cases but there is no choice now for me now :s
    I just configure PASSWORD_LIFE_TIME for dbadmin account ...

  • verbanverban Vertica Customer ✭

    Finally, I created a testbed and I found the bug...

    Normally after password expiration, vsql ask you to change the password (link)
    But If you set the 'authentication method' of an account to 'hash', after password expiration the password will be changed to an unknown value automatically without user interaction!

    And the tragedy is when that account was your only account (means dbadmin), and then your DB will be gone!! Because you cannot login to your database anymore.

    suggestion: configure the 'LOCAL Trust' for dbadmin (Link)

    It seems there is only one option to retrieve the DB that only support knows :|;)

  • moshegmosheg Vertica Employee Administrator

    You can also create another user with dbadmin credentials in advance.
    For example:
    CREATE USER user01;
    GRANT PSEUDOSUPERUSER TO user01; -- user01 can assign one or more roles to a user or to another role
    GRANT DBADMIN TO user01 WITH ADMIN OPTION;
    See: https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/DBADMINRole.htm

  • verbanverban Vertica Customer ✭

    .> @verban said:

    Finally, I created a testbed and I found the bug...

    Normally after password expiration, vsql ask you to change the password (link)
    But If you set the 'authentication method' of an account to 'hash', after password expiration the password will be changed to an unknown value automatically without user interaction!

    And the tragedy is when that account was your only account (means dbadmin), and then your DB will be gone!! Because you cannot login to your database anymore.

    suggestion: configure the 'LOCAL Trust' for dbadmin (Link)

    It seems there is only one option to retrieve the DB that only support knows :|;)

    Dear @Jim_Knicely,
    I checked this process on Vertica 10.0 and 11.0 and was same,
    Do you confirm this is a bug (maybe due to salt update)?

    Additionally it seems I do not have active support contact now, can you help me to recover the password ?!

  • verbanverban Vertica Customer ✭

    @mosheg said:
    You can also create another user with dbadmin credentials in advance.
    For example:
    CREATE USER user01;
    GRANT PSEUDOSUPERUSER TO user01; -- user01 can assign one or more roles to a user or to another role
    GRANT DBADMIN TO user01 WITH ADMIN OPTION;
    See: https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/DBADMINRole.htm

    Dear @mosheg,
    As you may know the password hashing (in Client Authentication) is a mechanism to prevent replay attacks and deal with the security hardening subject.
    It does not make scene to harden an admin account (with Client Authentication and Password Expiration configuration) And at the same time open a backdoor with creating a weak admin account (without security hardening features)!

  • moshegmosheg Vertica Employee Administrator

    @verban said:
    Dear @mosheg,
    As you may know the password hashing (in Client Authentication) is a mechanism to prevent replay attacks and deal with the security hardening subject.
    It does not make scene to harden an admin account (with Client Authentication and Password Expiration configuration) And at the same time open a backdoor with creating a weak admin account (without security hardening features)!

    The idea of two admin accounts with ALL the necessary security hardening, with different passwords, and different Password Expiration date can reduce the risk of one admin account which might be blocked because of a humane error.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    @verban - How did you test?

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

dbadmin=> ALTER USER dbadmin PASSWORD EXPIRE;
ALTER USER

dbadmin=> CREATE AUTHENTICATION v_hash METHOD 'hash' HOST '0.0.0.0/0';
CREATE AUTHENTICATION

dbadmin=> GRANT AUTHENTICATION v_hash TO dbadmin;
GRANT AUTHENTICATION

dbadmin=> \q

[dbadmin@vertica10 ~]$ vsql
The password has expired.

Changing password for dbadmin
New password:
Retype new password:
Password changed.

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

dbadmin=> SELECT user;
 current_user
--------------
 dbadmin
(1 row)
  • verbanverban Vertica Customer ✭
    edited November 2021

    @Jim_Knicely

    You are right and it seems Password_Expiration in your test working fine.
    I did some more tests and (i think) found the root cause,
    That's related to vertica client.
    If you use vsql as vertica client, there is no problem.
    But in my cases i have different result when using isql (odbc connection), python (odbc connection) and dbeaver (jdbc).
    After PASSWORD EXPIREcommand:
    1. isql: it asks for Changing the password (and you can change it). but if break it (with ctrl+c), the password will be update and you cannot login anymore.

    1. python: it does not asks for Changing the password (non interactive environment), the password will be update and you cannot login anymore.
    2. dbeaver: it does not asks for Changing the password. The password will be update and you cannot login anymore.

    (These scenario was not sequential and each one started from begining with non expired password)

    I hope these scenarios can help to understand the root cause.

  • verbanverban Vertica Customer ✭

    @Jim_Knicely any update?

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