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