23

I'm trying to connect to a remote psql database. Before I added the pg_hba.conf entry with the client's IP address, I was getting an error message :

xdev@xdevbox:~$ psql -U postgres testdb -h 10.1.1.47
psql: FATAL:  no pg_hba.conf entry for host "10.201.50.71", user "postgres", database "testdb", SSL off

I added the client's IP with trust settings. I also changed the listen address in postgres.conf on the server to listen to "*". Then I restarted the database server using /etc/init.d/postgresql restart command.

Now when I try to connect, I get the following error message: 

psql: could not connect to server: Connection refused
    Is the server running on host "10.1.1.47" and accepting
    TCP/IP connections on port 5432?

in postgresql.conf, the port is set to 5432. I'm not sure what else to check.

Thanks

Improve this question
4
  • Can you ping that address?
    – dezso
    May 4, 2015 at 19:44
  • 1
    I'd say the DB didn't restart correctly when you restarted it. Check the PostgreSQL log files for the reason why - probably a typo in pg_hba.conf.
    – Craig Ringer
    May 5, 2015 at 5:03
  • I had to change the listen address from * to the specific ip address.
    – dot
    May 5, 2015 at 13:29
  • I had trouble connecting to my postgresql db with pgAdmin4 and it turned out to be because I was stuck with an old version (Ubuntu). Had to add the postgres repo. I know you are connecting with psql but others might end up here try to troubleshoot this problem =)
    – grofte
    Jan 12, 2022 at 8:41

2 Answers 2

Reset to default
36

You have to configure the following two files

pg_hba.conf

host all all 0.0.0.0/0 md5

postgresql.conf

listen_addresses='*'

You have to check if the port 5432 is open: http://www.yougetsignal.com/tools/open-ports/

If it's not then add a rule to your iptables:

iptables -A INPUT -s 0/0 -p tcp --dport 5432 -j ACCEPT

0/0: If you want anybody to access it. You can change it to a specific IP address or range of IP addresses.

Improve this answer
4
  • 2
    Just an addition. You don't need to use an external tool. Just use telnet [yourServerIp] 5432 if you haven't telnet installed you can use PowerShell on windows for example.
    – Ionic
    Jul 4, 2015 at 15:41
  • @Ionic that's true
    – Danish Khakwani
    Jul 4, 2015 at 18:01
  • 3
    Well call me paranoid, but I don't trust too many services on the net. ;-) Just to be aware. :-)
    – Ionic
    Jul 4, 2015 at 18:10
  • port checking tool helped me here
    – Shobi
    Jul 18, 2019 at 22:55
2

I do not agree to use 

host all all 0.0.0.0/0 md5

if your database is exposed to the internet. In my project postrgres is in the cloud and it is used from the backend. I need to connect directly to the database only for maintenance. I connect to the database from a public IP, say 1.2.3.4. In this case the correct entry is:

host mybackend myuser 1.2.3.4/0 md5

Please note the "/0" after the IP address. Without "/0" you will receive the "Connection refused error".

It is always better to restrict the access to the database specifying the database name, the database user and the ip addresses in the white list. Never use "ALL" unless you are in a developing environment.

Bye!

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.