226

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.

Alexis Wilke
  • 19,179
  • 10
  • 84
  • 156
hsming
  • 2,468
  • 3
  • 14
  • 14
  • 2
    possible duplicate of [PG Peer authentication failed](http://stackoverflow.com/questions/15306770/pg-peer-authentication-failed) – Daniel Vérité Jul 03 '13 at 12:09
  • 1
    now, to be prompted for answers to the above questions you need to add `--interactive` to the command: `createuser --interactive joe` – user3791372 Mar 24 '17 at 23:08

12 Answers12

366

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 pg_hba.conf:

local   all             all                                     peer
Rajkaran Mishra
  • 4,532
  • 2
  • 36
  • 61
meyerson
  • 4,710
  • 1
  • 20
  • 20
  • 15
    I needed (ver 9.4): psql -U user-name -h 127.0.0.1 -d db-name – Gregor Dec 12 '14 at 00:05
  • 9
    what an ornery tool. the manual says `psql [option...] [dbname [username]]`, so you'd think `psql dbname username` would just work.. – djeikyb Aug 06 '15 at 21:04
  • 2
    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. – borisano Nov 24 '15 at 15:07
  • 1
    this worked for me, thanks a lot, though I am doing a research as to why it was the correct solution. nevertheless, in a configuration in another machine I would login as psql -U username -d database . so i guess that the accepted solution depends on the case. – Lazarus Rising Nov 24 '15 at 15:54
  • 2
    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. – look Dec 04 '16 at 02:26
  • 2
    Why is the `-h` required? I have Postgres databases that require it and ones that don't. What's the difference? – d512 Apr 13 '17 at 20:17
236

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.

flaviodesousa
  • 7,255
  • 4
  • 28
  • 34
  • 5
    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`! – Chloe Jul 06 '14 at 04:44
  • 3
    Dont worry, you can set peer authentication for specific users (for example, your username or postgres). It seems that specific rules override general rules – JavierIEH Nov 23 '14 at 03:29
  • 1
    On some distributions the file might also be found here: ``/var/lib/pgsql/9.4/data/pg_hba.conf`` – Jonas Eicher Apr 07 '15 at 17:01
  • 2
    can't you do this without restarting postgresql? can't you just specify the authentication method in the pg_user table? – fccoelho May 09 '15 at 12:49
  • 2
    You can reload instead of restart. That reloads the pg_hba table. Worked for me. – Joanis Jun 01 '15 at 14:08
30

Peer authentication means that postgres asks the operating system for your login name and uses this for authentication. To login as user "dev" using peer authentication on postgres, 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).

stefan.schwetschke
  • 8,862
  • 1
  • 26
  • 30
  • 1
    thanks for your answers.but it still don't work if i change the `dev` to system user `XXX` – hsming Jul 03 '13 at 09:29
  • 2
    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. – dsh Jul 03 '13 at 11:46
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
tymik
  • 606
  • 7
  • 17
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

sudo bangbang
  • 27,127
  • 11
  • 75
  • 77
mgoldwasser
  • 14,558
  • 15
  • 79
  • 103
13

This works for me when I run into it:

sudo -u username psql
alangrah
  • 139
  • 1
  • 2
  • 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`. – Kenneth Nov 30 '15 at 01:04
9

I simply had to add -h localhost

VPaul
  • 1,005
  • 11
  • 21
8

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;
Abel
  • 3,989
  • 32
  • 31
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
Ikrom
  • 4,785
  • 5
  • 52
  • 76
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

Tyler Curtis Jowers
  • 427
  • 5
  • 9
1

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

Dmitry Shilin
  • 15
  • 3
  • What question does this answer? Why might `-U postgres` be pivotal? What are all those *dash-letters*, anyway? – greybeard Apr 19 '20 at 06:45
0

Try in terminal:

>> psql -U role_name -d database -h hostname.<domain>.com -W
Antu
  • 2,197
  • 3
  • 25
  • 40
Srinu Mareti
  • 29
  • 8