Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails

Question

I'm getting the error:

FATAL: Peer authentication failed for user "postgres" 

when I try to make postgres work with Rails.

Here's my pg_hba.conf, my database.yml, and a dump of the full trace.

I changed authentication to md5 in pg_hba and tried different things, but none seem to work.

I also tried creating a new user and database as per Rails 3.2, FATAL: Peer authentication failed for user (PG::Error)

But they don't show up on pgadmin or even when I run sudo -u postgres psql -l.

Any idea where I'm going wrong?

Answer 1

After installing Postgresql I did the below steps.

1. Open the file pg_hba.conf. For Ubuntu, use for example /etc/postgresql/13/main$ sudo nano pg_hba.conf and change this line at the bottom of the file, it should be the first line of the settings:

   local   all             postgres                                peer

   to

   local   all             postgres                                trust

   Side note: If you want to be able to connect with other users as well, you also need to change:

   local   all             all                                peer

   to

   local   all             all                                md5

   If you used nano editor, exit with double Escape, x, y, Enter to save the config file.

2. Restart the server

    $ sudo service postgresql restart 

   Output: * Restarting PostgreSQL 13 database server

3. Login into psql and set your password

    $ psql -U postgres  db> ALTER USER postgres with password 'your-pass'; 

   Output: ALTER ROLE

   Side note: If you have other users, they will need a password as well:

    db> ALTER USER my_user with password 'your-pass'; 

   Then enter:

    exit 

4. Finally change the pg_hba.conf from

   local   all             postgres                                trust

   to

   local   all             postgres                                md5

5. Restart the server again

    $ sudo service postgresql restart 

   Output: * Restarting PostgreSQL 13 database server

6. Login at psql with postgres user

   After restarting the postgresql server, the postgres user accepts the password that you chose:

    psql -U postgres 

   Output:
   Password for user postgres:

   psql (13.4 (Ubuntu 13.4-1.pgdg20.04+1))

   Type "help" for help.

   And you are in psql:

   postgres=#

   Side note: Same now works for my_user if you added the user and password:

    psql -d YOUR_DB_NAME -U my_user 

   Which will ask you for the new password of my_user.

Authentication methods details:

trust - anyone who can connect to the server is authorized to access the database

peer - use client's operating system user name as database user name to access it.

md5 - password-base authentication

for further reference check here

Answer 2

The problem is still your pg_hba.conf file*.

This line:

local   all             postgres                                peer 

Should be:

local   all             postgres                                md5 

* The location of this file isn't very consistent. The command locate pg_hba.conf should help; here's some examples: /etc/postgresql/*/main/pg_hba.conf and /var/lib/pgsql/data/pg_hba.conf.

After altering this file, don't forget to restart your PostgreSQL server. If you're on Linux, that would be sudo service postgresql restart.

These are brief descriptions of both options according to the official PostgreSQL docs on authentication methods.

Peer authentication

The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

Password authentication

The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

If you are at all concerned about password "sniffing" attacks then md5 is preferred. Plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. If the connection is protected by SSL encryption then password can be used safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).

Sample location for pg_hba.conf:
/etc/postgresql/9.1/main/pg_hba.conf