PostgreSQL is a famous relational database that has some advanced features compared to “MySQL”. It enables users to create, delete, and modify databases, tables, and roles according to their needs. In Linux, PostgreSQL is accessed using the “psql” shell from the terminal. While dealing with the “psql” shell, user authentication is required, which sometimes invokes the error “postgresql password authentication failed for user”. This post will enlist the possible reasons and the solutions to the above-stated error.
- Reason: Incorrect Authentication
- Solution 1: Set the Password for User
- Solution 2: Change the Encryption Method
Let’s start this guide.
Reason: Incorrect Authentication
During the installation of PostgreSQL, there is no step to set passwords for the default user. If the password is not set after installation and the “psql” shell is accessed, this error occurs, which is displayed in the following screenshot:
Solution 1: Set the Password for User
Users can resolve the failed authentication issue by setting a new password. For this, run the below command in the terminal to set a new password:
$ sudo passwd postgres
To switch the “postgres” user, type the password here and press the “ENTER” button. It navigates to the Postgres ubuntu terminal, on which write the “psql” to access the psql shell as seen below:
$ su - postgres
Now, you can perform any function in “postgres” terminal.
Solution 2: Change the Encryption Method
Sometimes, the encryption scheme md5 does not decrypt user passwords correctly. For instance, the encryption method can be changed, which is located in the “pg_hba.conf” configuration file. The step-by-step instructions are provided here to locate and modify the required file:
Step 1: Access the Configuration File
In the Linux system, the configuration file can be editable through the nano editor. To open the “pg_hba.conf” file in the nano editor, type “nano” by specifying the particular path as seen in the below script:
$ sudo nano /etc/postgresql/14/main/pg_hba.conf
After executing the above command in the terminal, it navigates to the “PostgreSQL Client Authentication Configuration File”. In this file, the basic information of “DATABASE”, “USER”, “ADDRESS”, and “METHOD” are provided below:
- DATABASE: The input field “all” represents that “host” and “hostssl” clients have permission to access any databases.
- USER: It specifies the particular user or all users can access the databases.
- ADDRESS: It represents the IP address through which clients can access the database, such as the current IP address “127.0.1.1” through which the “local” client can acquire the database.
- METHOD: It represents the authentication scheme that encrypts or decrypts the password in hexadecimal form.
Step 2: Change METHOD Value From md5 to trust
To perform modifications in the “pg_hba.conf” file, the arrow key of the keyboard is utilized to access the desired location and type the “trust” in the “METHOD” column as below:
Press “CTRL+S” to save changes and exit this file through the “CTRL+X”.
Step 3: Restart the PostgreSQL services
To integrate all the applied changes in the configuration file, execute the below script for restarting the Postgresql services:
$ sudo service postgresql restart
That’s all from this guide.
Conclusion
The “postgresql password authentication failed for user” error can be resolved by changing the encryption method from “md5” to “trust”. After that, set a new password by executing the “sudo passwd <username>”. This guide provides the step by step instructions to fix the “postgresql password authentication failed for user” error.
