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