postgresql (remote) connection refused

Question

My error:

# psql -U postgres -h 10.230.5.51
psql: could not connect to server: Connection refused
        Is the server running on host "10.230.5.51" and accepting
        TCP/IP connections on port 5432?

in postgresql.conf:

listen_addresses = '*'
port = 5432                             # (change requires restart)

I have added the client server in pg_hba.conf (username/database replaced by X)

host    X          X          10.230.5.21             md5
host    X          X          10.230.5.22             md5

Before I added those in the pg_hba.conf, it gave me this error:

# psql -U postgres -h 10.230.5.51
psql: FATAL:  no pg_hba.conf entry for host "10.230.5.22", user "X", database "X", SSL on
FATAL:  no pg_hba.conf entry for host "10.230.5.22", user "X", database "X", SSL off

So I assume the first steps I have taken are correct?

The last step I feel like I may be missing, could be iptables. they look like this:

-A INPUT -i lo -j ACCEPT
-A INPUT -d 127.0.0.0/8 ! -i lo -j REJECT --reject-with icmp-port-unreachable
-A INPUT -s 10.230.4.0/22 -j LOCAL
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 80 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 443 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 8010 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 10443 -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -p gre -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-port-unreachable
-A LOCAL -p udp -m state --state NEW -m udp --dport 53 -j ACCEPT
-A LOCAL -p tcp -m state --state NEW -m tcp --dport 53 -j ACCEPT
-A LOCAL -p udp -m state --state NEW -m udp --dport 123 -j ACCEPT
-A LOCAL -p tcp -m state --state NEW -m tcp --dport 2181 -j ACCEPT
-A LOCAL -p tcp -m state --state NEW -m tcp --dport 2888 -j ACCEPT
-A LOCAL -p tcp -m state --state NEW -m tcp --dport 3888 -j ACCEPT
-A LOCAL -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT
-A LOCAL -p tcp -m state --state NEW -m tcp --dport 7000 -j ACCEPT
-A LOCAL -p tcp -m state --state NEW -m tcp --dport 7001 -j ACCEPT
-A LOCAL -p tcp -m state --state NEW -m tcp --dport 9042 -j ACCEPT
-A LOCAL -p tcp -m state --state NEW -m tcp --dport 9160 -j ACCEPT

Is the iptables ok? I can see that port 5432 is on the list. Is the connection being refused by another rule before that? iptable commands are weird.

General information:

Both computers are running linux. I did not originally setup the database. The database is running fine, but only locally, even though this happened when running the command on the server with postgres database:

# psql -U postgres -h localhost
psql: could not connect to server: Connection refused
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5432?
could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

Do you mind posting the full contents of your pg_hba.conf from the instance that is refusing the connections? To be honest it does look like an IPTABLES issue from here, but localhost connections should be allowed, which is not explained by IPTABLES. — d1ll1nger
– d1ll1nger, Commented Jun 14, 2016 at 13:35
1) The database is running fine, but only locally, ... what is locally, and how do you know the DBMS is running fine? 2) What is in the postgres logfile? If there were any connection attempts, they will have been logged. — joop
– joop, Commented Jun 14, 2016 at 14:44

Anurag · Accepted Answer · 2019-05-07 12:17:54Z

3

I faced the same problem in PostgreSQL 9.6 and I resolved in the following way.

sudo nano /etc/postgresql/9.6/main/postgresql.conf
put '*' instead of localhost on the following line and removed comment sign(#)
```
listen_addresses = '*'
```
Add the following line in pg_hba.conf

#TYPE DATABASE USER CIDR-ADDRESS METHOD
```
host  all  all 0.0.0.0/0 md5
```
Restart the service:
(Ubuntu) sudo service postgresql restart

edited May 7, 2019 at 12:17

Anurag

91613 silver badges16 bronze badges

answered Dec 17, 2018 at 8:27

Shree Prakash

2,3443 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anurag Over a year ago

This answer is correct. It worked for me on Postgres version 11.

Monish Sen Over a year ago

This is NOT a recommended configuration for production servers. * binds to all network interfaces. 0.0.0.0/0 allows access from everywhere

Shree Prakash Over a year ago

@MonishSen I think ppl know very well how to run DB in production with the help of public-private subnets along with inbound/outbound rules.

Riddhi Gohil · Accepted Answer · 2016-07-06 10:12:38Z

2

Do following

Update : /var/lib/pgsql/<version>/data/postgresql.conf

change : #listen_addresses = 'localhost' to listen_addresses = '*'

restart service

answered Jul 6, 2016 at 10:12

Riddhi Gohil

1,80819 silver badges18 bronze badges

Comments

Misha · Accepted Answer · 2019-08-08 00:29:30Z

0

Check if you did not switch DATABASE with USERNAME.

This also happened to me and this fixed it. The correct syntax is:

host DATABASE USERNAME ADDRESS METHOD

answered Aug 8, 2019 at 0:29

Misha

1271 gold badge2 silver badges9 bronze badges

Collectives™ on Stack Overflow

postgresql (remote) connection refused

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related