Postgresql – Postgres 9.4 won’t accept remote connections

postgresql

I'm running postgres 9.4 on Ubuntu server 14 which is running on virtualbox. After a reboot no remote connections would work to postgres.

I have other services on server that still take remote connections (Rstudio server, Shiny server, SSH) and that machine can still connect to the internet, and to samba shares.

Here's my pg_hba.conf

    local   all             postgres                                peer
    host    all           scraper         localhost             trust
    local   all             all                                     peer
    host    all             all             127.0.0.1/32            md5
    host    all             all             ::1/128                 md5
    hostssl all             all             0.0.0.0/0               md5

In trying to connect with pgadmin from another machine, I get this message

An error has occurred:

03:23:06 PM: Error: Error connecting to the server.
FATAL: password authentication failed for user "postgres"

It fails if I use R, psql, or windows odbc connections so it doesn't appear to have anything to do with pgadmin

If I ssh in then I can run psql locally…

If I run \du I find that all users show Password valid until infinity

EDIT

I forgot to say that I am indeed using the correct password. I can be sure I'm using the right password because I when I was in psql I reset the password of the users in question using ALTER USER user WITH PASSWORD 'MYPASSWORD';

Best Answer

With your edit, you are probably running something like this, which will default to the user postgres:

psql -h 123.123.123 -d mydb

when you should be using the user flag:

psql -h 123.123.123 -d mydb -U whoever

you will be prompted for the password for user whoever.

Related Question