I am running PostgreSQL 9.3 on ubuntu 14.04 LTS. It is the stock Postgres distribution available in apt. I found a bunch of threads in various forums that indicate that the correct way to allow remote psql connections to the database is to add the following line to /etc/postgresql/9.3/main/pg_hba.conf:
host all all 0.0.0.0/0 md5
And to set this in /etc/postgresql/9.3/main/postgresql.conf:
listen_addresses = '*'
then to restart the dbms ("sudo service postgresql restart")
I have done this and am still getting the following error when attempting to connect to this DB from another server:
psql -h <the db host> -p 5433 -d the_db the_user
$> Password for user the_user:
$> (I enter the pw)
$> psql: FATAL: password authentication failed for user "the_user"
$> FATAL: password authentication failed for user "the_user"
Please note that the password is unambiguously correct as the following, executed on the DB's host machine, gets me in successfully:
$> psql -h localhost -d the_db the_user
$> Password for user the_user:
$> (I enter the exact same pw as above)
$> psql (9.3.5, server 9.1.13)
$> Type "help" for help.
$>
<the psql shell session begins>
I am at a loss to see where I am going wrong here as the configuration seems to be correct, the password is correct, and the host machine does seem to be accepting the remote connection (for the prompt) and just failing at the point of authentication. Please let me know if you've ideas about how to get this working. Thanks.
pg_hba.conf
.