0

I have a remote machine and I am trying to log-in into a postgres database with the following command (run as Linux root user via ssh, i.e. locally on that machine):

# psql -d mydatabase -U postgres -W
Password for user postgres: <here goes my password>
psql: FATAL:  Peer authentication failed for user "postgres"

For some reason this fails (tried ~5 times).

But when I login to the same database using pgAdmin (i.e. remotely) with same role (postgres) and same password, it logs in successfully every time.

What's wrong with psql command I use? If it is correct, can you suggest any troubleshooting steps?

EDIT: So the problem is that in pg_hba.conf remote and local connections are treated differently. Remote ones are configured properly. The question is why locals don't work.

For locals, I have in pg_hba.conf:

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

Do I understand correctly that for localhost connections it first tries peer authentication, and if it fails it doesn't attempt md5 and just returns error?

Boris Burkov
  • 13,420
  • 17
  • 74
  • 109
  • 4
    PgAdmin will be using tcp/ip to localhost (127.0.0.1), `psql` will be using unix sockets. Check `pg_hba.conf` and the client authentication chapter of the manual. – Craig Ringer Sep 21 '15 at 12:25
  • 2
    You can force psql to use an inet socket by specifying a hostname: `psql -U postgres -h 127.0.0.1 mydb` – joop Sep 22 '15 at 09:33

1 Answers1

0

psql in default is trying to login via peer. This means, you have to be logged in as postgres on your local machine. You can force psql to use TCP/IP, you need tpo specify a host, which can be localhost, so the call could look like:

psql -U postgres -h 127.0.0.1

Another way would be to adjust your settings. I see two ways to change your current local settings:

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

1) Allow all users to login without password from localhost by setting line 

local   all             all                                     trust

This is very dangerous and shouldn't be done IMHO.

2) Another way could be to create a database user with same user name as your system login and grant all rights to this account

And of course, which is what I usually do in such cases: Run psql in context of postgres user e.g. by sudo -u postgres psql or with su -u postgres -c "psql ..."

frlan
  • 6,950
  • 3
  • 31
  • 72