Postgres, FATAL: Peer authentication failed for user

The error FATAL: Peer authentication failed for user "db_user" indicates that PostgreSQL is trying to authenticate the user db_user using "peer" authentication, but it is failing.

What is Peer Authentication?

Peer authentication is a method used by PostgreSQL to authenticate users based on the Unix/Linux user they are currently logged in as. This means PostgreSQL expects the database username to match the system username.

Solutions to Resolve the Error

1. Switch to Password Authentication

You can change the authentication method to md5 (password-based) or password by modifying the pg_hba.conf file, which is the PostgreSQL configuration file for client authentication.

1. Locate the pg_hba.conf file:
   • The location of the pg_hba.conf file can vary, but it is typically found in /etc/postgresql/<version>/main/ on Debian-based systems or /var/lib/pgsql/<version>/data/ on RedHat-based systems.

You can locate it using the find command:

   sudo find / -name "pg_hba.conf"

1. Edit the pg_hba.conf file:
   • Open the file in a text editor:

   sudo nano /etc/postgresql/<version>/main/pg_hba.conf

1. Change the Authentication Method:
   • Look for the line that reads something like this:

   local   all             all                                     peer

• Change peer to md5 or password so it looks like this:

   local   all             all                                     md5

• Save the file and exit the editor.

1. Restart PostgreSQL:
   • After making changes to the pg_hba.conf file, restart the PostgreSQL service:

   sudo systemctl restart postgresql

1. Try Connecting Again:
   • Now, try connecting again, and it should prompt you for a password:

   psql -U db_user -d db_name

2. Use the Correct Unix/Linux User

If you prefer to use peer authentication, ensure that you are logged in as the Unix/Linux user db_user. You can switch to the db_user user using the following command:

su - db_user

After switching to the db_user user, try connecting to the database again:

psql -d road_sim

3. Specify a Different Authentication Method Just for Your User

You can also configure the pg_hba.conf file to specify md5 authentication just for the db_user user:

1. Edit the pg_hba.conf file:
   • Add a line above the existing entries in the pg_hba.conf file:

   local   db_name        db_user                                   md5

• Save the file and restart PostgreSQL.

1. Set a Password for the User:
   • If you haven't set a password for the db_user user, you can do so with:

   ALTER USER db_user WITH PASSWORD 'your_password';

Therefore ...

• Switch to password-based authentication by modifying the pg_hba.conf file and setting it to md5 or password.
• Use the correct Unix/Linux user that matches the PostgreSQL user to maintain peer authentication.
• Configure user-specific authentication in pg_hba.conf.

Try these solutions, and you should be able to connect successfully.

@eddiegulay