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?
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