SEO

Dear Andrey, dear list,

I tried 'netstat -anp' while being logged into my server via ssh:
...
unix 2 [ ACC ] STREAM LISTENING 317212 2992/postgres /var/run/postgresql/.s.PGSQL.5432
...

So that looks alright?

What else could I try?
Cheers,
nilson

Andrej Ricnik-Bay schrieb:
> On 17/04/2008, Nils Zierath <nils.zierath@uni-bonn.de> wrote:
>> Dear list,
> Hi,
>
>
>> 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?
> When you run netstat -an ... do you see postmaster listing on that port?
>
>
> 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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

On 18/04/2008, Nils Zierath <nils.zierath@uni-bonn.de> wrote:

> I tried 'netstat -anp' while being logged into my server via ssh:
> ..
> unix 2 [ ACC ] STREAM LISTENING 317212 2992/postgres
> /var/run/postgresql/.s.PGSQL.5432
> ..

> So that looks alright?

No, it doesn't. That's the local socket, it's not listening on
any network interface. You'd expect something more like this:

netstat -anp|grep postgr
tcp 0 0 0.0.0.0:5432 0.0.0.0:*
LISTEN 5233/postgres
tcp6 0 0 :::5432 :::*
LISTEN 5233/postgres
udp 0 0 127.0.0.1:32769 127.0.0.1:32769
ESTABLISHED5233/postgres
unix 2 [ ACC ] STREAM LISTENING 18729
5233/postgres /var/run/postgresql/.s.PGSQL.5432


> What else could I try?

Not quite sure what's going wrong there. Have you got
more than one version of postgres installed?

> Cheers,
> nilson
Cheers,
Andrej


--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

hmm strange,
Andrej, I tried again and got the same output as you posted:

my_host:/etc# netstat -anp|grep postgres
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 3298/postgres
tcp6 0 0 :::5432 :::* LISTEN 3298/postgres
udp 0 0 127.0.0.1:33092 127.0.0.1:33092 ESTABLISHED 3298/postgres
unix 2 [ ACC ] STREAM LISTENING 383161 3298/postgres
/var/run/postgresql/.s.PGSQL.5432


As far as I know, I am running only one instance of Postgres version 8.3 running:

my_host:/etc# ps -efl
....
0 S postgres 3298 1 0 78 0 - 10546 - 11:57 pts/0 00:00:00
/usr/lib/postgresql/8.3/bin/postgres
1 S postgres 3300 3298 0 75 0 - 10546 - 11:57 ? 00:00:00 postgres:
writer process
1 S postgres 3301 3298 0 75 0 - 10546 - 11:57 ? 00:00:00 postgres: wal
writer process
1 S postgres 3302 3298 0 75 0 - 10613 - 11:57 ? 00:00:00 postgres:
autovacuum launcher process
1 S postgres 3303 3298 0 75 0 - 3410 - 11:57 ? 00:00:00 postgres: stats
collector process


my_host:/etc# ls -al /var/run/postgresql/
total 16
drwxrwsr-x 2 postgres postgres 4096 2008-04-21 11:57 .
drwxr-xr-x 19 root root 4096 2008-04-21 11:13 ..
-rw------- 1 postgres postgres 5 2008-04-21 11:57 8.3-main.pid
srwxrwxrwx 1 postgres postgres 0 2008-04-21 11:57 .s.PGSQL.5432
-rw------- 1 postgres postgres 33 2008-04-21 11:57 .s.PGSQL.5432.lock

Is that giving you some more information on where to look?

Thanks,
Nils

Andrej Ricnik-Bay schrieb:
> On 18/04/2008, Nils Zierath <nils.zierath@uni-bonn.de> wrote:
>
>> I tried 'netstat -anp' while being logged into my server via ssh:
>> ..
>> unix 2 [ ACC ] STREAM LISTENING 317212 2992/postgres
>> /var/run/postgresql/.s.PGSQL.5432
>> ..
>
>> So that looks alright?
>
> No, it doesn't. That's the local socket, it's not listening on
> any network interface. You'd expect something more like this:
>
> netstat -anp|grep postgr
> tcp 0 0 0.0.0.0:5432 0.0.0.0:*
> LISTEN 5233/postgres
> tcp6 0 0 :::5432 :::*
> LISTEN 5233/postgres
> udp 0 0 127.0.0.1:32769 127.0.0.1:32769
> ESTABLISHED5233/postgres
> unix 2 [ ACC ] STREAM LISTENING 18729
> 5233/postgres /var/run/postgresql/.s.PGSQL.5432
>
>
>> What else could I try?
>
> Not quite sure what's going wrong there. Have you got
> more than one version of postgres installed?
>
>> Cheers,
>> nilson
> 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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Nils Zierath <nils.zierath@uni-bonn.de> writes:
> hmm strange,
> Andrej, I tried again and got the same output as you posted:

> my_host:/etc# netstat -anp|grep postgres
> tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 3298/postgres
> tcp6 0 0 :::5432 :::* LISTEN 3298/postgres

In that case it's probably a firewall problem. Double-check what you
did with iptables.

regards, tom lane

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Dear Tom & Andrej,
it pretty much looks like a firewall problem. Although I am still not sure, what's wrong
with my firewall, I solved the connection problem via SSH-tunneling
(http://www.postgresonline.com/journa...L-Server.html).

Thank you so much for helping,
Nils

Tom Lane schrieb:
> Nils Zierath <nils.zierath@uni-bonn.de> writes:
>> hmm strange,
>> Andrej, I tried again and got the same output as you posted:
>
>> my_host:/etc# netstat -anp|grep postgres
>> tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 3298/postgres
>> tcp6 0 0 :::5432 :::* LISTEN 3298/postgres
>
> In that case it's probably a firewall problem. Double-check what you
> did with iptables.
>
> regards, tom lane

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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

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


--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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 sptostgresql

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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice