Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails
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'soperating system user name from the kernel and using it as the alloweddatabase user name (with optional user name mapping). This method isonly supported on local connections.
Password authentication
The password-based authentication methods are md5 and password. Thesemethods operate similarly except for the way that the password is sentacross the connection, namely MD5-hashed and clear-text respectively.
If you are at all concerned about password "sniffing" attacks then md5is preferred. Plain password should always be avoided if possible.However, md5 cannot be used with the db_user_namespace feature. If theconnection is protected by SSL encryption then password can be usedsafely (though SSL certificate authentication might be a better choiceif one is depending on using SSL).
Sample location for pg_hba.conf
:/etc/postgresql/9.1/main/pg_hba.conf
After installing Postgresql I did the below steps.
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.Restart the server
$ sudo service postgresql restart
Output:
* Restarting PostgreSQL 13 database server
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
Finally change the
pg_hba.conf
fromlocal all postgres trust
to
local all postgres md5
Restart the server again
$ sudo service postgresql restart
Output:
* Restarting PostgreSQL 13 database server
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
If you connect over localhost (127.0.0.1) you shouldn't experience that particular issue. I wouldn't muck much with the pg_hba.conf but instead I would adjust your connection string:
psql -U someuser -h 127.0.0.1 database
where someuser is your user you're connecting as and database is the database your user has permission to connect to.
Here is what I do on Debian to setup postgres:
http://www.postgresql.org/download/linux/debian/ (Wheezy 7.x)as root … root@www0:~# echo "deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main" >> /etc/apt/sources.list root@www0:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - root@www0:~# apt-get update root@www0:~# apt-get install postgresql-9.4 root@www0:~# su - postgres postgres@www0:~$ createuser --interactive -P someuser Enter password for new role: Enter it again: Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) y Shall the new role be allowed to create more new roles? (y/n) n postgres@www0:~$ createdb -O someuser database postgres@www0:~$ psql -U someuser -h 127.0.0.1 database
Enjoy!