CentOS 6 and PostgreSQL - Can't Connect to Port

Question

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 0.0.0.0:5432                0.0.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 0.0.0.0/0 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?

UPDATE 1

The command psql -h localhost -U pg_user gave the error. The problem was twofold - replacing it with psql -h 127.0.0.1 -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             127.0.0.1/32            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        127.0.0.1/32            ident
# host    replication     postgres        ::1/128                 ident

host      all             all             0.0.0.0/0               trust

Solution

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?

Comments

[jstm88]

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 127.0.0.1 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.

[nandoP]

ACCEPT all -- anywhere anywhere <=== (pasted from above)... this should cover it, but paste a "iptables -nvL" so we can see

[nickgrim]

Good point. facepalm

[nandoP]

hey its cool...i once posted you could iptables redirect tcp/80 to tcp/443 with -j REDIRECT ..... DOOOOOOH

[jstm88]

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).

[frostymarvelous]

Thanks. That worked for me. However, on restart, I needed to enter it again. Any help on how I can persist this?