Re: Remote / network connection fails - Mailing list pgsql-novice

Dear Andrej,
'iptables -L' returns

Chain INPUT (policy DROP)
target     prot opt source               destination
firewall   all  --  anywhere             anywhere

Chain FORWARD (policy DROP)
target     prot opt source               destination

Chain OUTPUT (policy DROP)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere
ACCEPT     tcp  --  anywhere             anywhere            tcp spt:www
ACCEPT     tcp  --  anywhere             anywhere            tcp spt:ssh
ACCEPT     tcp  --  anywhere             anywhere            tcp spt:postgresql

Chain firewall (1 references)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere
DROP       all  --  anywhere             anywhere            state INVALID
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:www
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:ssh
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:postgresql
REJECT     tcp  --  anywhere             anywhere            reject-with tcp-reset
REJECT     all  --  anywhere             anywhere            reject-with icmp-port-unreachable

The filtering rules set in iptables are as follows:

# PATH TO IPTABLES
IPTABLES=/sbin/iptables

# Flushing all old rules
$IPTABLES -F
$IPTABLES -X

# ESTABLISHING A NEW FIREWALL CHAIN NAMED 'firewall'.
$IPTABLES -N firewall

# BANNING ALL PACKAGES & CONNECTIONS THAT ARE NOT BEING OPENED EXPLCITELY
$IPTABLES -P INPUT DROP
$IPTABLES -P OUTPUT DROP
$IPTABLES -P FORWARD DROP

# ACCEPTING LOOPBACK-TRAFFIC FOR INTERNAL COMMUNICATION (e.g. Apache <=> PostgreSQL)
$IPTABLES -A firewall -i lo -j ACCEPT
$IPTABLES -A OUTPUT -o lo -j ACCEPT

# REJECTING INVALID PACKETS
$IPTABLES -A firewall -m state --state INVALID -j DROP

# ACCEPTING PACKETS AND CONNECTIONS, THAT RELATE TO ALREADY EXISTING CONNECTIONS
$IPTABLES -A firewall -m state --state RELATED,ESTABLISHED -j ACCEPT

# Aapache Web Server on port 80
$IPTABLES -A firewall -i eth0 -p tcp --dport 80 -j ACCEPT
$IPTABLES -A OUTPUT -o eth0 -p tcp --sport 80 -j ACCEPT

# SSH access on port 22
$IPTABLES -A firewall -i eth0 -p tcp --dport 22 -j ACCEPT
$IPTABLES -A OUTPUT -o eth0 -p tcp --sport 22 -j ACCEPT

# PostgreSQL access on port 5432
$IPTABLES -A firewall -i eth0 -p tcp --dport 5432 -j ACCEPT
$IPTABLES -A OUTPUT -o eth0 -p tcp --sport 5432 -j ACCEPT

# REJECT ALL TCP-PACKAGES, THAT HAVE NOT BEEN DEALT WITH UNTIL HERE VIA 'tcp-reset'
$IPTABLES -A firewall -p tcp -j REJECT --reject-with tcp-reset

# REJECT ALL OTHER PACKAGES, THAT HAVE NOT BEEN DEALT WITH UNTIL HERE
$IPTABLES -A firewall -j REJECT

# CHANNEL ALL PACKAGES OF THE CHAIN 'INPUT' INTO OUR CHAIN 'firewall'
$IPTABLES -A INPUT -j firewall

As posted in my first message, trying to connect to Postgres from a remote host (without
SSH tunneling) results in the following error:

psql: could not connect to server: Connection refused (0x0000274D/10061)
     Is the server running on host "pg_server_ip" and accepting
     TCP/IP connections on port 5432?

To me, the error tells me, that Since the connection works fine after establishing the
tunnel, I assume, that my pg_hba.conf is alright:

# Database administrative login
local    all        postgres        ident    sameuser

# TYPE    DATABASE    USER    CIDR-ADDRESS    METHOD    [OPTION]

# "local" is for Unix domain socket connections only
local    all        all            md5
local    all        jade            ident    sameuser

# IPv4 connections
host    all        all    ***.***.0.0/24    md5
host    all        all    127.0.0.1/32    md5

# IPv6 connections
host    all        all    ::1/128        md5

Andrej Ricnik-Bay schrieb:
> On 22/04/2008, Nils Zierath <nils.zierath@uni-bonn.de> wrote:
>> Dear Tom & Andrej,
> Hi Nils,
>
>>  it pretty much looks like a firewall problem. Although I am still not sure,
>> what's wrong with my firewall,
> If you're still interested in resolving this ... what do the IPtables
> rules around port 5432 look like?
>
>>  Thank you so much for helping,
>>  Nils
> Cheers,
> Andrej
>
>

--
Nils Zierath

ZEF 10 Years
1997-2007: 10 Years of Development Research
on Economic, Cultural, and Ecological Change
in the Developing World

Center for Development Research
Department of Ecology and Resource Management

Walter-Flex-Strasse 3
53113 Bonn (Germany)

Tel.:   +49 (0) 228 - 73-1793
FAX:    +49 (0) 228 - 73-1889

E-Mail: nils.zierath@uni-bonn.de
Web:    http://www.coffee.uni-bonn.de
         http://www.zef.de