PostgreSQL Peer Authentication Error: Connection Failed for User "postgres" (Ubuntu)

Problem Statement

When attempting to connect to PostgreSQL using psql -U postgres on Ubuntu, you may encounter the following error:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"

However, the connection succeeds when using sudo -u postgres psql. This behavior occurs due to PostgreSQL's authentication configuration, specifically how it handles local socket connections.

Understanding Peer Authentication

PostgreSQL's peer authentication method requires that the system username matches the database username. When using psql -U postgres, the system is verifying whether your current Linux user account matches the PostgreSQL username you're trying to connect with.

How Peer Authentication Works

• Local socket connections use peer authentication by default
• PostgreSQL checks if the system user matches the database user
• Mismatched usernames result in authentication failure

Solutions

Method 1: Switch to the postgres System User

The simplest solution is to switch to the PostgreSQL system user:

sudo -i -u postgres
psql

This approach works because:

• The system user matches the database user (both are "postgres")
• No configuration changes are required
• It maintains security by using the intended authentication method

Method 2: Modify Authentication Method in pg_hba.conf

For development environments, you can change the authentication method:

1. Edit the PostgreSQL host-based authentication file:

sudo nano /etc/postgresql/[version]/main/pg_hba.conf

2. Locate the line:

local   all             postgres                                peer

3. Change peer to md5 (password authentication) or scram-sha-256:

local   all             postgres                                md5

4. Restart PostgreSQL:

sudo service postgresql restart

Security Consideration

Using md5 is more secure than trust (which requires no password), but changing authentication methods in production should be done cautiously. The peer method is generally more secure for local connections when properly configured.

Method 3: Create Username Mapping

For a more secure and permanent solution, create a mapping between your system user and the postgres database user:

1. Edit the ident mapping file:

sudo nano /etc/postgresql/[version]/main/pg_ident.conf

2. Add a mapping (replace your-username with your actual system username):

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
user1           your-username           postgres

3. Modify the authentication method in pg_hba.conf:

sudo nano /etc/postgresql/[version]/main/pg_hba.conf

4. Update the postgres line to use the mapping:

local   all             postgres                                peer map=user1

5. Restart PostgreSQL:

sudo service postgresql restart

Method 4: Connect via TCP/IP Instead of Unix Socket

Force a TCP/IP connection instead of using the Unix domain socket:

psql -h 127.0.0.1 -U postgres -d postgres

This bypasses peer authentication because TCP/IP connections typically use password authentication methods configured in pg_hba.conf.

Diagnostic Steps

To understand your current configuration:

1. Check your PostgreSQL version and configuration path:

pg_lsclusters

2. Examine your current pg_hba.conf settings:

sudo cat /etc/postgresql/[version]/main/pg_hba.conf

3. Check your current system username:

whoami

Best Practices

• Development environments: Method 1 (switching users) is recommended for simplicity and security
• Production environments: Use username mapping (Method 3) for controlled access
• Avoid trust authentication: Never use trust method in production environments
• Regular audits: Periodically review your pg_hba.conf for appropriate authentication methods

Troubleshooting Checklist

Troubleshooting Steps

1. ✅ Verify PostgreSQL service is running: sudo service postgresql status
2. ✅ Check your current system username: whoami
3. ✅ Review pg_hba.conf authentication methods
4. ✅ Test connection with different methods
5. ✅ Restart PostgreSQL after configuration changes

Conclusion

The "peer authentication failed" error occurs when your system username doesn't match the PostgreSQL username you're trying to connect with. The most appropriate solution depends on your environment:

• For occasional access: Use sudo -u postgres psql
• For development: Modify authentication method to md5
• For production: Implement username mapping in pg_ident.conf

Always prioritize security when modifying PostgreSQL authentication methods, especially in production environments.