Help Center/ GaussDB(DWS)/ Troubleshooting/ Account/Permission/Password/ Account Still Locked After Password Resetting
Updated on 2024-01-25 GMT+08:00

Account Still Locked After Password Resetting

Symptom

When a user connects to the cluster, the system displays a message indicating that the user is locked. After the user password is reset and the customer logs in again, the system still displays the message.

1
FATAL: The account has been locked.

Possible Causes

By default, a user will be locked if the user enters incorrect passwords for 10 consecutive times. The maximum number of incorrect password attempts is specified by the failed_login_attempts parameter. To modify the parameter, see Setting the Number of Times of Failed Login.

After the password is reset, the user is still locked. This may be caused by another user or application that has made 10 times of incorrect password attempts after the password is reset.

Handling Procedure

  1. Connect to the database as the system administrator dbadmin and run the following SQL statement to check the system time:

    1
    SELECT now();
    

    The command output shows that the default system time on GaussDB(DWS) is the UTC time, that is, Beijing time - 8 hours.

  2. Run the following SQL statement to query the client connection: In the preceding command:

    • username should be replaced with the name of the locked user.
    • The time period should be changed base actual requirements. For example, if you want to query the connection status from 09:00 to 10:00 (Beijing time), you need to convert the Beijing time to the UTC time, which is 01:00 to 02:00.
    1
    SELECT * FROM pgxc_query_audit('2022-10-27 01:00:00','2022-10-27 02:00:00') where username='username';
    

    The preceding command output shows that the client whose IP address is x.x.x.x has made many attempts for connection using incorrect passwords.

  3. Perform either of the following operations based on the actual service situation:

    • If the IP address obtained in step 2 belongs to a job, stop the job connection, connect to the database as the system administrator dbadmin, run the following SQL statement to unlock the user, then configure the job with the correct password.
      1
      ALTER USER username ACCOUNT UNLOCK;
      
    • If you are not sure which job the IP address belongs to, change the value of failed_login_attempts to 0 by referring to Setting the Number of Times of Failed Login, and then run the following SQL statement to reset a new password. In this way, incorrect password attempts will no longer cause the account to be locked.
      ALTER USER username IDENTIFIED BY '{Password}';

      Setting the value of failed_login_attempts to 0 is only a temporary solution. To ensure database security, you are advised not to set failed_login_attempts to 0. After locating the job and changing incorrect password, you are advised to set failed_login_attempts to 10.