Обсуждение: Remote / network connection fails
Dear list,
I am trying to connect to my database over the network. When trying to connect to the
server from a Windows client via psql with
#-> psql -h pg_server_ip -d postgres -U postgres OR
#-> psql -h pg_server_ip -d cms -U cmsuser
I get the following error (same with pgAdmin3)
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?
****
- I am using PostgreSQL 8.3 on Debian Etch.
- The server is up and running on host "pg_server_ip" on Port 5432.
- The Postgres server process is running as UNIX user 'postgres'.
- I can connect to the postgres-server locally via PSQL.
- A CMS is running fine with a Postgres data base 'cms' as backend on the same machine.
- I changed 'listen_address' to '*' in postgresql.conf
- iptables allows connections on ports 22, 80 and 5432.
- pg_hba.conf looks as follows:
# LOCAL CONNECTIONS
local all postgres ident sameuser
local all cmsuser md5
local all all ident sameuser
# IPv4
# TYPE DBASE USER CIDR-ADDRESS METHOD [OPTION]
host all all 131.220.0.0/16 md5
host all all 127.0.0.1/32 md5
- I restarted the Postgres server process / reloaded the config files after modification.
Since the CMS with 'cmsuser' is running fine, I suspect it is connecting locally. What do
I have to change at the IPv4 connections?
Thanks in advance,
Nils
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 -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm
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
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
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
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
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/journal/index.php?/archives/38-PuTTY-for-SSH-Tunneling-to-PostgreSQL-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
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
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