SOLUTION: it turns out the ORDER of iptables matters. The firth INPUT rule is REJECT all ... which precludes the ACCEPT rule. So I had to use iptables -I INPUT 5 ... to add the rule in the fifth place, ahead of the REJECT rule. An important detail: remember to execute service iptables save before service iptables restart - if you forget the save, it won't remember the new order and you'll have to do it again.

I installed PostgreSQL on a CentOS 6 virtual machine. I can SSH to the machine from the VM host perfectly fine. However, I can't connect to my PostgreSQL installation.

Some useful output:

[root@localhost data]# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED 
ACCEPT     icmp --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     tcp  --  anywhere             anywhere            state NEW tcp dpt:ssh 
REJECT     all  --  anywhere             anywhere            reject-with icmp-host-prohibited 
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:postgres 

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         
REJECT     all  --  anywhere             anywhere            reject-with icmp-host-prohibited 

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         

[root@localhost data]# ps -u postgres
  PID TTY          TIME CMD
 1866 ?        00:00:00 postmaster
 1868 ?        00:00:00 postmaster
 1870 ?        00:00:00 postmaster
 1871 ?        00:00:00 postmaster
 1872 ?        00:00:00 postmaster
 1873 ?        00:00:00 postmaster
 1874 ?        00:00:00 postmaster

[root@localhost data]# netstat -nlp | grep 5432
tcp        0      0      *                   LISTEN      1866/postmaster     
tcp        0      0 :::5432                     :::*                        LISTEN      1866/postmaster     
unix  2      [ ACC ]     STREAM     LISTENING     13136  1866/postmaster     /tmp/.s.PGSQL.5432

[root@localhost data]# cat pg_hba.conf | grep "host all"
host all all trust

[root@localhost data]# cat postgresql.conf | grep listen_
listen_addresses = '*'      # what IP address(es) to listen on;

[root@localhost data]# cat postgresql.conf | grep port
port = 5432             # (change requires restart)
                    # supported by the operating system:
                    #   %r = remote host and port

So, here's what I see:

  • iptables is set to accept incoming connections to the port
  • postgres is running
  • it's listening on the tcp port
  • the database is set to accept connections
  • postgres is set to listen on all interfaces
  • the port is set correctly

But if I execute the command psql -h localhost -U pg_user (where pg_user is a user I created inside of postgresql) I get the message:

psql: FATAL:  no pg_hba.conf entry for host "::1", user "pg_user", database "postgres", SSL off

If, on my VM host machine I execute psql -h x.x.x.x -U pg_user (where x.x.x.x is the virtual machine's IP address) I get this:

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

Furthermore, I try this:

$ nc -zw 3 x.x.x.x 5432
$ nc -zw 3 x.x.x.x 22
Connection to x.x.x.x port 22 [tcp/ssh] succeeded!

So it's listening on 22 but not 5432.

Where should I go from here?


The command psql -h localhost -U pg_user gave the error. The problem was twofold - replacing it with psql -h -U pg_user -d postgres worked. First, I had to specify the postgres database (it was defaulting to one named the same as pg_user). Second, localhost fails and you have to specify the actual loopback IP address.

Second, here are all of the entries in pg_hba.conf:

# TYPE    DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local     all             all                                     peer

# IPv4 local connections:
# host    all             all               ident

# IPv6 local connections:
# host    all             all             ::1/128                 ident

# Allow replication connections from localhost, by a user with the
# replication privilege.
# local   replication     postgres                                peer
# host    replication     postgres            ident
# host    replication     postgres        ::1/128                 ident

host      all             all                  trust

You have a rule that REJECTs everything right before your postgres rule. Try:

iptables -I INPUT 1 -p tcp --dport 5432 -j ACCEPT

...and see if that fixes it?

  • ACCEPT all -- anywhere anywhere <=== (pasted from above)... this should cover it, but paste a "iptables -nvL" so we can see – nandoP Nov 21 '13 at 15:26
  • 1
    Good point. facepalm – nickgrim Nov 21 '13 at 15:29
  • hey its cool...i once posted you could iptables redirect tcp/80 to tcp/443 with -j REDIRECT ..... DOOOOOOH – nandoP Nov 21 '13 at 15:30
  • That was it... I didn't realize order matters. -nvL also reveals that the ACCEPT all -- anywhere anywhere is only on the localhost interface. Of course. Another detail: I deleted and re-added the rule at position 5 (because I felt like it) and restarted, thinking everything would be fine... nope. Then I checked again and they had been changed to the old order. Darned service iptables save. :D After that, then another service iptables restart, everything works. All I need to do now is add the proper security (I'm not using trust except for testing). – Justin Mrkva Nov 21 '13 at 15:38
  • Thanks. That worked for me. However, on restart, I needed to enter it again. Any help on how I can persist this? – frostymarvelous Jun 7 '16 at 10:06

it looks like you have all set up correctly at layer 3/4, as tcp/5432 is open to the world on this box.

tcp 0 0* LISTEN

most likely, it is going to be a postgres permissioning issue, or a possible pg misconfiguration:

psql: FATAL: no pg_hba.conf entry for host "::1", user "wiki", database "pg_user", SSL off

  • My bad. The message was for host "::1", user "pg_user", database "postgres", SSL off (when putting in placeholders I replaced postgres instead of the username). I did find the problem with that one - you need to use psql -h and NOT psql -h localhost - apparently it doesn't interpret localhost. So that works. I've clarified that as well as posted the entire main section of pg_hba.conf, not just the few lines I had before. – Justin Mrkva Nov 21 '13 at 15:24

Works good for me. I changed the line form nickgrim answer to add source IP. @frostymarvelous, and everybody else who nees help saving. You need to save your current IPTABLES you can see with -L command. Depending on your distro, you can use:

iptables-save > filename 

script to replace iptables file in your /etc folder.... Or you can edit the file and make changes. Make sure to check what your distro is using for location.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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