remote connection via ssh tunnel

[tomfmason]

I am new to postgresql. I have mostly used mysql(much easier to setup and maintain, imho). I don't doubt that postgresql is better but there is something to be said about the ease of use with mysql and the initial learning curve involved with postgresql.

There are two reasons that I am wanting to use a remote database 1) it is bulky and requires NTFS on windows, 2) I travel a lot and would like to maintain access while on the road.

I use cygwin on a flash drive for connecting to my remote server and have setup a ssh tunnel to the remote machine:

ssh -L 5432:server.tld:5432 user@server.tld

The port is opened successfully locally but I am still unable to connect with pokertracker during the initial setup. All I see when I attempt to connect is "Unable to connect to this database. Please check configuration" This is a fresh install of postgresql 8.1 on debian etch and pokertracker3 on windows xp pro. Any suggestions would be great.

[_dave_]

This is a fresh install of postgresql 8.1 on debian

have you added your IP to pg_hba.conf, and set "listen_address="*" in postgresql.conf?

I'm not sure, but I suspect when connecting via SSH tunnel it the incoming connection looks like it is coming from your server's external IP rather than 127.0.0.1 (the only IP granted access by default) - so that is the IP you will want to allow.

[tomfmason]

thanks for your reply. I am still unable to connect though. I will post some relevant info and maybe something will stick out to you.

my pg_hba.conf looks like this:

Code: Select all

# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all         all         xx.xx.xx.xx        md5
# IPv6 local connections:
host    all         all         ::1/128               md5


I also have added listen_addresses = '*' to the postgresql.conf. Other than the edits noted here it is the default install from debian.

Code: Select all

dpkg -l |grep postgres
rc  postgresql                       7.5.22                               object-relational SQL database management sy
rc  postgresql-7.4                   7.4.19-0etch1                        object-relational SQL database, version 7.4
ii  postgresql-8.1                   8.1.11-0etch1                        object-relational SQL database, version 8.1
rc  postgresql-client                7.5.22                               front-end programs for PostgreSQL (transitio
ii  postgresql-client-8.1            8.1.11-0etch1                        front-end programs for PostgreSQL 8.1
ii  postgresql-client-common         71                                   manager for multiple PostgreSQL client versi
ii  postgresql-common                71                                   manager for PostgreSQL database clusters



Any other ideas?

[_dave_]

have you got /32 (or similar mask) on the end of your xx.xx.xx.xx IP address? Not entirely sure it is required, but it may well require a netmask of some variety. xx.xx.xx.xx is indeed the IP address of eth0 (or whereever SSH connects), yes?

Another thought, having just read the OP again - are you sure postgres is indeed running? bugsbunny will be better able to know what you have to do here, I'm not familiar with debian On my gentoo, I had to run a standalone initdb script, and add the postgresql startup script to default runlevel.

what do you get if you run "netstat -altn" on the debian box? something like this (well, the 5432 line)?

Code: Select all

newdb dave # netstat -altn
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN


[tomfmason]

after adding the external ip with the /32 mask it now works.

Thanks!