Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
Ask Question
I get this error:
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"
But it connects successfully when I use:
sudo -u postgres psql
Can someone please explain what is happening and how to diagnose/fix this problem? My pg_hba.conf contains the following:
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
You can edit your .conf files with privileges using an editor, for my case it is nano.
$sudo nano /etc/postgresql/14/main/pg_ident.conf
Map your user by adding this line
# MAPNAME SYSTEM-USERNAME PG-USERNAME
user1 <computer-username> postgres
Replace the <computer-username> with the System-Username, which can be found using the whoami command. Type in your terminal:
$whoami
Then go ahead to the pg_hba.conf with privileges
$sudo nano /etc/postgresql/14/main/pg_hba.conf
Add your postgres user, with method=peer, as shown below:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
This worked for me.
–
–
–
–
Peer authentication means that the connection is only allowed if the name of the database user is the same as the name of the operating system user.
So if you run psql -U postgres as operating system user root or jimmy, it won't work.
You can specify a mapping between operating system users and database users in pg_ident.conf.
I was trying to make the answer about using /etc/postgresql/14/main/pg_ident.conf work by adding a map like this:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
user1 <computer-username> postgres
Then adding the next code to /etc/postgresql/14/main/pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
But that didn't work for me.
Then I read this documentation and found the solution!
It's just a matter of adding a reference to the map to pg_hba.conf with map=user1:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer map=user1
–
vi /etc/postgresql/14/main# vi pg_hba.conf
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 0.0.0.0/0 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
step-3
sudo /etc/init.d/postgresql restart
step-4
After restart your changes
create your required database and database username
Depending on your use case and environment. For me, most of my environment is local. Just in case the above example fails to work. you can try this:
Edit the pg_indent.conf file
***$sudo nano /etc/postgresql/14/main/pg_ident.conf***
# MAPNAME SYSTEM-USERNAME PG-USERNAME
user1 <computer-username> <computer-username>
To find your computer user, you can use this command whoami, who, w ,who -q. any of those should give your a hint of the current user
> So you can replace your username against <computer-username>. So your
SYSTEM_USERNAME & PG-USERNAME should be = your username
*sudo nano pg_hba.conf*
$sudo nano /etc/postgresql/14/main/pg_hba.conf
Add the changes to satisfy your usecase
*# TYPE DATABASE USER ADDRESS METHOD
local all <Computer-username> peer map=user1
So this allows you to login without specifying our password
Make sure to restart postgresql
sudo /etc/init.d/postgresql restart
After restart your changes
create your required database and database username
If the above solutions do not work, try this one! It seems that it is a connection problem; probably, your issue would resolve by specifying all connection parameters. The general form of the command could be like this:
pg_dump --dbname=<DB-NAME> --username=<USER-NAME-OF-DB-OWNER> --port=<USUALLY: 5432> --host=<USUALLY: 127.0.0.1> --password > <DUMP-NAME>.sql
In addition, you can use different parameters for the dumping process.
If you are able to, just switch to the postgres user on your system first. I appreciate that this requires you to have permission to do this, but it is worth mentioning as it is much more simple than messing with config - and if you are allowed to mess with the postgres config then you can probably do this anyway.
If you are logged in as root
su - postgres
if you are logged in as a user with sudo privileges
sudo su - postgres
then your psql should just work with no parameters
If you have the similar problem where your database owner is not the default 'postgres', then the simple solution is to create a user whose name matches the database user e.g. 'mydatabase' and log in as that user (or switch to it as above)
adduser mydatabase
Optionally add that user to the privileged group if that's what you want (Ubuntu example below)
usermod -aG sudo mydatabase
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
