92

When I try to open psql with this command:

psql -U postgres

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
Improve this question

11 Answers 11

Reset to default
60

You can edit your .conf files with privileges using an editor, for my case it is nano. First edit the pg_ident.conf file:

$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 edit the pg_hba.conf file 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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Changing $sudo nano /etc/postgresql/14/main/pg_hba.conf the line local all postgres peer from peer to trust then systemctl restart [email protected] it works for me.
I don't know what is the implication of the above solution but it worked for me thanks.
@RaghavendraAcharya editing the pg_hba.conf allows the user (postgres to authenticate or connect* with the database backend), the computer username is mapped with the allowed database user which is why you added it as a peer in the pg_ident.conf file. The connection parameter is localhost, which is handled locally in your machine.
local all postgres md5 will make the password really work. trust mode will never ask for password.
@DanielAdo I think you forgot to specify the option map=user1 in your pg_hba.conf. Could that be? Doesn't work without the option for me. Using postgresql 14.
|
57

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.

Improve this answer

3 Comments

Thanks for explaining peer authentication so clearly. Is there any reason (besides the convenience of being able to log in as your system user) not to change the authentication method to md5 or trust instead of using peer + identity mapping?
@John md5 should no longer be used. scram-sha-256 requires you to enter a password - safer, but more cumbersome. trust would allow any user in, which is worse than peer if you cannot absolutely trust all operating system users on the machine.
Thanks! The docs have 13 pages on authentication methods (postgresql.org/docs/current/auth-methods.html) so I appreciate your two-sentence summary of the main methods.
16

Try to add param host,

for example like this -h 127.0.0.1

psql -h 127.0.0.1 -U user -d database
pg_dump -h 127.0.0.1 -U user -d database -f dump.sql
Improve this answer

2 Comments

psql -h localhost -U postgres mydbname < /home/dbfolfer/sample_db.dmp
It worked perfectly for me.
13

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
Improve this answer

2 Comments

Thanks! After a lot of tries found your ans working, but there is a problem, After implementing your solution sudo -u postgres is not working any more it ask for ubuntu user password after giving password showing an error
Oh, this didn't work for me at first. However, after making sure to reload postgres configurations (systemctl reload postgresql) there was no issue. Thanks!
4 

step-1 
vi /etc/postgresql/14/main# vi postgresql.conf

listen_addresses = '*'

step-2  

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

Improve this answer

Comments

4

I don't think in production you wana bother yourself changing any files.
So, What you can do is temporarily make the username same to the username of your postgres which is usually postgres by passing command sudo -i -u postgres and then psql, now you successfully connected to postgres.

Improve this answer

Comments

2

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***

Add

# 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

Next

*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

Improve this answer

Comments

2 
$ sudo nano /etc/postgresql/14/main/pg_hba.conf`

# Change the line 
local   all    postgres        peer

from peer to trust

then restart:

service postgres restart

Warning: In this configuration, any user can connect to the PostgreSQL database as the postgres user without any authentication. This is a very permissive setting and is generally only recommended for development environments where security is not a concern. In production, a more secure authentication method, such as md5 (password-based), is typically used.

Improve this answer

Comments

1

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.

Improve this answer

Comments

1

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
Improve this answer

Comments

0

local all postgres peer

Just change above to below line

local all postgres md5

and restart the postgresql service

sudo service postgresql restart

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.