Skip site navigation (1) Skip section navigation (2)

Re: Remote / network connection fails

From: Nils Zierath <nils(dot)zierath(at)uni-bonn(dot)de>
To: Andrej Ricnik-Bay <andrej(dot)groups(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Remote / network connection fails
Date: 2008-04-22 12:46:03
Message-ID: 480DDE0B.30300@uni-bonn.de (view raw or flat)
Thread:
Lists: 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(dot)zierath(at)uni-bonn(dot)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(dot)zierath(at)uni-bonn(dot)de
Web:    http://www.coffee.uni-bonn.de
         http://www.zef.de

In response to

pgsql-novice by date

Next:From: Didier Gasser-MorlayDate: 2008-04-22 17:18:00
Subject: Client connection lost; still in the task list
Previous:From: Szentpali JanosDate: 2008-04-22 10:03:30
Subject: Re: convert text file to database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group