What does FATAL: Peer authentication failed for user “username” in a postgres database mean and how do you fix it?

by

The error message FATAL: Peer authentication failed for user "username" in PostgreSQL indicates that a connection attempt was rejected due to the peer authentication method failing for the specified user. Peer authentication is a method that relies on the client’s operating system user credentials to match the PostgreSQL role (user) they are trying to connect as.

Cause

This error typically occurs when you’re trying to connect to the PostgreSQL database using a Unix-domain socket (on Unix/Linux systems) and the pg_hba.conf file is configured to use peer authentication for the attempted connection. The peer authentication method checks if the operating system user’s name matches the PostgreSQL role name. If there is a mismatch, the authentication fails.

For example, if you’re logged into your Unix/Linux system as the user alice and try to connect to PostgreSQL as the user bob using peer authentication, it will fail unless alice is mapped to bob in the PostgreSQL role system.

Fix

To resolve this error, you have a few options:

  1. Connect as the Same User: Make sure that the Unix/Linux username matches the PostgreSQL username you want to connect with. If they match, peer authentication will succeed.
  2. Create or Modify a Role Mapping: You can create or modify a role mapping in PostgreSQL that maps the Unix/Linux username to the PostgreSQL username. This is done using the pg_ident.conf file.
  3. Change the Authentication Method: If you need to connect with a different username, or if peer authentication is not suitable for your use case, you can change the authentication method in the pg_hba.conf file. For example, you can change it to md5 to use password-based authentication:
   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   local   all             all                                     md5

After making changes to the pg_hba.conf file, you must reload the PostgreSQL configuration for the changes to take effect:

   SELECT pg_reload_conf();

Or if you have command-line access:

   pg_ctl reload
  1. Connect Using TCP/IP: If you prefer not to use Unix-domain sockets, you can connect to PostgreSQL using TCP/IP (e.g., psql -h localhost -U username). Make sure to configure the pg_hba.conf file to allow connections over TCP/IP for the desired user and method (e.g., md5 for password authentication).

After applying one of these fixes, try connecting to the PostgreSQL database again to verify that the issue has been resolved. Remember that any changes to the pg_hba.conf file should be made carefully, as incorrect configurations can affect the security and accessibility of your PostgreSQL server. Always back up the current pg_hba.conf file before making changes.

Leave a Comment