psql: FATAL: Peer authentication failed for user "dev"

Question

when i create a new user, but it cannot login the database.
I do that like this:

postgres@Aspire:/home/XXX$ createuser dev
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) y

then create a database:

postgres@Aspire:/home/XXX$ createdb -O dev test_development

after that, I try psql -U dev -W test_development to login, but get the error:

psql: FATAL:  Peer authentication failed for user "dev"

I tried to solve the problem but failed.

now, to be prompted for answers to the above questions you need to add --interactive to the command: createuser --interactive joe — user3791372
– user3791372, Commented Mar 24, 2017 at 23:08

Manuel Jordan · Accepted Answer · 2025-11-22 22:21:30Z

381

Try:

psql -U user_name  -h 127.0.0.1 -d db_name

where

-U is the database user name
-h is the hostname/IP of the local server thus avoiding Unix domain sockets
-d is the database name to connect to

This is then evaluated as a network connection by PostgreSQL rather than a Unix domain socket connection; thus not evaluated as a "local" connect as you might see in the pg_hba.conf file:

local   all             all                                     peer

edited Nov 22 at 22:21

Manuel Jordan

16.5k26 gold badges113 silver badges187 bronze badges

answered Oct 3, 2014 at 17:39

meyerson

4,8601 gold badge22 silver badges20 bronze badges

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

6 Comments

Gregor Over a year ago

I needed (ver 9.4): psql -U user-name -h 127.0.0.1 -d db-name

djeikyb Over a year ago

what an ornery tool. the manual says psql [option...] [dbname [username]], so you'd think psql dbname username would just work..

borisano Over a year ago

Worked for me as well. I also think this is a much more preferable way than changing config files, especially when you don't have a clue about what you are doing and just following SO answer to solve your problem.

look Over a year ago

Good answer. I was getting from inside the database after having logged in as postgres \c glossary john FATAL: Peer authentication failed for user "john" then with \c glossary john localhost Password for user john: SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) You are now connected to database "glossary" as user "john" on host "localhost" at port "5432". it worked.

d512 Over a year ago

Why is the -h required? I have Postgres databases that require it and ones that don't. What's the difference?

|

flaviodesousa · Accepted Answer · 2017-04-29 23:29:36Z

239

Your connection failed because by default psql connects over UNIX sockets using peer authentication, that requires the current UNIX user to have the same user name as psql. So you will have to create the UNIX user dev and then login as dev or use sudo -u dev psql test_development for accessing the database (and psql should not ask for a password).

If you cannot or do not want to create the UNIX user, like if you just want to connect to your database for ad hoc queries, forcing a socket connection using psql --host=localhost --dbname=test_development --username=dev (as pointed out by @meyerson answer) will solve your immediate problem.

But if you intend to force password authentication over Unix sockets instead of the peer method, try changing the following pg_hba.conf* line:

from

# TYPE DATABASE USER ADDRESS METHOD
local  all      all          peer

to

# TYPE DATABASE USER ADDRESS METHOD
local  all      all          md5

peer means it will trust the identity (authenticity) of UNIX user. So not asking for a password.
md5 means it will always ask for a password, and validate it after hashing with MD5.

You can, of course, also create more specific rules for a specific database or user, with some users having peer and others requiring passwords.

After changing pg_hba.conf if PostgreSQL is running you'll need to make it re-read the configuration by reloading (pg_ctl reload) or restarting (sudo service postgresql restart).

^{* The file pg_hba.conf will most likely be at /etc/postgresql/9.x/main/pg_hba.conf}

Edited: Remarks from @Chloe, @JavierEH, @Jonas Eicher, @fccoelho, @Joanis, @Uphill_What comments incorporated into answer.

edited Apr 29, 2017 at 23:29

answered Feb 19, 2014 at 18:56

flaviodesousa

7,9954 gold badges30 silver badges35 bronze badges

5 Comments

Chloe Over a year ago

How do you allow peer AND md5? When I set md5, then I can no longer login with postgres user! I tried adding multiple lines, and separating the method with commas, but they didn't work. Ok I found mgoldwasser's answer and that worked. I just added another line for user postgres with method peer!

JavierIEH Over a year ago

Dont worry, you can set peer authentication for specific users (for example, your username or postgres). It seems that specific rules override general rules

Jonas Eicher Over a year ago

On some distributions the file might also be found here: /var/lib/pgsql/9.4/data/pg_hba.conf

fccoelho Over a year ago

can't you do this without restarting postgresql? can't you just specify the authentication method in the pg_user table?

Joanis Over a year ago

You can reload instead of restart. That reloads the pg_hba table. Worked for me.

Manuel Jordan · Accepted Answer · 2025-11-22 22:42:34Z

31

Peer authentication means that PostgreSQL asks the operating system for your login name and uses this for authentication. To login as user "dev" using peer authentication on PostgreSQL, you must also be the user "dev" on the operating system.

You can find details to the authentication methods in the Postgresql documentation

Hint: If no authentication method works anymore, disconnect the server from the network and use method "trust" for "localhost" (and double check that your server is not reachable through the network while method "trust" is enabled).

edited Nov 22 at 22:42

Manuel Jordan

16.5k26 gold badges113 silver badges187 bronze badges

answered Jul 3, 2013 at 9:10

stefan.schwetschke

8,9521 gold badge28 silver badges30 bronze badges

2 Comments

hsming Over a year ago

thanks for your answers.but it still don't work if i change the dev to system user XXX

dsh Over a year ago

stefan's answer is correct. I just added a link, which will be visible when my edit is reviewed, to the documentation where each of the authentication methods are explained.

tymik · Accepted Answer · 2015-05-05 12:39:44Z

25

When you specify:

psql -U user

it connects via UNIX Socket, which by default uses peer authentication, unless specified in pg_hba.conf otherwise.

You can specify:

host    database             user             127.0.0.1/32       md5
host    database             user             ::1/128            md5

to get TCP/IP connection on loopback interface (both IPv4 and IPv6) for specified database and user.

After changes you have to restart postgres or reload it's configuration. Restart that should work in modern RHEL/Debian based distros:

service postgresql restart

Reload should work in following way:

pg_ctl reload

but the command may differ depending of PATH configuration - you may have to specify absolute path, which may be different, depending on way the postgres was installed.

Then you can use:

psql -h localhost -U user -d database

to login with that user to specified database over TCP/IP. md5 stands for encrypted password, while you can also specify password for plain text passwords during authorisation. These 2 options shouldn't be of a great matter as long as database server is only locally accessible, with no network access.

Important note: Definition order in pg_hba.conf matters - rules are read from top to bottom, like iptables, so you probably want to add proposed rules above the rule:

host    all             all             127.0.0.1/32            ident

edited May 5, 2015 at 12:39

answered May 5, 2015 at 12:26

tymik

8588 silver badges21 bronze badges

1 Comment

hawkeye Over a year ago

re the Important note: - order is important +1

sudo bangbang · Accepted Answer · 2016-09-09 09:23:01Z

24

While @flaviodesousa's answer would work, it also makes it mandatory for all users (everyone else) to enter a password.

Sometime it makes sense to keep peer authentication for everyone else, but make an exception for a service user. In that case you would want to add a line to the pg_hba.conf that looks like:

local   all             some_batch_user                         md5

I would recommend that you add this line right below the commented header line:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             some_batch_user                         md5

You will need to restart PostgreSQL using

sudo service postgresql restart

If you're using 9.3, your pg_hba.conf would most likely be:

/etc/postgresql/9.3/main/pg_hba.conf

edited Sep 9, 2016 at 9:23

sudo bangbang

28.4k11 gold badges79 silver badges77 bronze badges

answered May 21, 2014 at 15:03

mgoldwasser

15.6k16 gold badges86 silver badges107 bronze badges

Comments

alangrah · Accepted Answer · 2015-01-23 17:52:08Z

14

This works for me when I run into it:

sudo -u username psql

answered Jan 23, 2015 at 17:52

alangrah

1491 silver badge2 bronze badges

1 Comment

Kenneth Over a year ago

only works if you create a whole new user on the system AND in postgres -- hsming just wants to connect to postgres with the newly created postgres user, dev.

Abel O'Ryan · Accepted Answer · 2016-06-09 06:41:38Z

10

The easiest solution:

CREATE USER dev WITH PASSWORD 'dev';
CREATE DATABASE test_development;
GRANT ALL PRIVILEGES ON DATABASE test_development to dev;
ALTER ROLE dev CREATEROLE CREATEDB;

answered Jun 9, 2016 at 6:41

Abel O'Ryan

4,27236 silver badges34 bronze badges

Comments

VPaul · Accepted Answer · 2019-06-13 19:17:24Z

10

I simply had to add -h localhost

answered Jun 13, 2019 at 19:17

VPaul

1,02311 silver badges24 bronze badges

1 Comment

tidydee Over a year ago

My issue was using postgres on a Raspberry. ^^^ worked for me!! Thank you!

Ikrom · Accepted Answer · 2017-02-21 06:20:30Z

2

In my case I was using different port. Default is 5432. I was using 5433. This worked for me:

$ psql -f update_table.sql -d db_name -U db_user_name -h 127.0.0.1 -p 5433

answered Feb 21, 2017 at 6:20

Ikrom

5,1395 gold badges55 silver badges79 bronze badges

1 Comment

Galigator Over a year ago

This can occure automatically when you have more than one installation of postgres.

Tyler Curtis Jowers · Accepted Answer · 2019-03-15 01:59:38Z

1

For people in the future seeing this, postgres is in the /usr/lib/postgresql/10/bin on my Ubuntu server.

I added it to the PATH in my .bashrc file, and add this line at the end

PATH=$PATH:/usr/lib/postgresql/10/bin

then on the command line

$> source ./.bashrc

I refreshed my bash environment. Now I can use postgres -D /wherever from any directory

answered Mar 15, 2019 at 1:59

Tyler Curtis Jowers

4576 silver badges9 bronze badges

Comments

Dmitry Shilin · Accepted Answer · 2020-04-19 06:07:20Z

1

pg_dump -h localhost -U postgres -F c -b -v -f mydb.backup mydb

answered Apr 19, 2020 at 6:07

Dmitry Shilin

133 bronze badges

1 Comment

greybeard Over a year ago

What question does this answer? Why might -U postgres be pivotal? What are all those dash-letters, anyway?

Antu · Accepted Answer · 2021-08-26 02:42:13Z

0

Try in terminal:

>> psql -U role_name -d database -h hostname.<domain>.com -W

edited Aug 26, 2021 at 2:42

Antu

2,3534 gold badges27 silver badges43 bronze badges

answered Feb 6, 2020 at 23:41

Srinu Mareti

278 bronze badges

1 Comment

Thoth Over a year ago

Please add a little more context to you answer.

ThomasV · Accepted Answer · 2024-03-28 15:52:17Z

If you encounter the error: psql: error: FATAL: Peer authentication failed for user "dev”, you will need to allow the new SQL user to be authenticated.

Add Client Authentication to new user for PostgreSQL:

To add the Client Authentication for a new user in SQL and allow it to authenticate, the steps are the following:

Edit the Client authentication file named pg_hba.conf this can generally be done with the command (in this case, I use PostgreSQL version 12): sudo nano /etc/postgresql/12/main/pg_hba.conf
Add a line toward the bottom of the file, following the previous lines spacing with the following content:

local all dev md5
Save the file. Then restart PostgreSQL with: sudo service postgresql restart

You should now be able to connect to your new user with psql -U dev. If you want help with the whole process of creating a PostgreSQL user and setting it up (as well as a new database) I have written a whole tutorial on it at: https://medium.com/p/44a38362bad9

李嘉奇 · Accepted Answer · 2024-07-26 16:41:17Z

0

You can first enter psql -l in the terminal. This will list all databases. Then psql -d db_name. Please note that db_name is the database you found with psql -l.

answered Jul 26, 2024 at 16:41

李嘉奇

1

1 Comment

李嘉奇 Over a year ago

For me, I just typed psql -d postgres

Collectives™ on Stack Overflow

psql: FATAL: Peer authentication failed for user "dev"

14 Answers 14

6 Comments

5 Comments

2 Comments

1 Comment

Comments

1 Comment

Comments

1 Comment

1 Comment

Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

14 Answers 14

6 Comments

5 Comments

2 Comments

1 Comment

Comments

1 Comment

Comments

1 Comment

1 Comment

Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related