5

I am trying to connect via pgAdmin3 GUI to a Postgresql database on a remote server myHost on port 5432.

Server side :

  • I have a Unix myUser that match a postgresql role.
  • pg_hba.conf is :
    local all all trust
    host all all 127.0.0.1/32 trust

Client side :

  • I open an ssh tunnel : ssh -L 3333:myHost:5432 myUser@myHost
  • I connect to the server via pgAdmin3 ( or via psql -h localhost -p 3333 ).

I get the following error message :
server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

I have tried to access a specific database with the superuser role using psql -h localhost -p 3333 --dbname=myDB --user=mySuperUser with no more success.

What did I forget in the setup ?
Thank you

2

Check if the remote host allow tunneling. You can check with the client if the tunnel is refused with:

ssh -v -L 3333:myHost:5432 myUser@myHost
  • yes, the tunnel is established correctly. The connection error seems to be when it tries to connect to postgres server. – user44744 Jun 2 '10 at 14:50
  • 3
    If PostgreSQL is refusing connection then you should be able to see something in the logs of the PostgreSQL server. Test the connectivity from the server where you are sshing. And if the ssh server and the PostgreSQL server are on the same machine, the connection is done locally, but in the tunnel you should try 127.0.0.1 instead of the IP on the ethernet interface. Eg. ssh -v -L 3333:127.0.0.1:5432 myUser@myHost. See also: cyberciti.biz/faq/postgresql-remote-access-or-connection – Mircea Vutcovici Jun 3 '10 at 12:44
  • That worked ! Sorry for the late answer, but thanks a million ! – user44744 Jul 1 '10 at 8:10
5

In my case, the problem was that the tunnel ended in an IPv6 address:

ssh -L 5434:localhost:5432 user@server

It was enough to replace localhost with the address I had assumed was previously used:

ssh -L 5434:127.0.0.1:5432 user@server

15 minutes of my life down the drain. :) Hope this spares someone else's time...

2

Sounds like you need to add permissions for your user to access postgresql non-locally.

I know it seems that by creating the tunnel, you're connecting locally, but you probably aren't accessing it by loopback/localhost.

You likely need to add a line to pg_hba.cong i would give an example, but i'm on my phone, sorry

  • 1
    host all all ip.add.re.ss md5, perhaps to allow access /from/ ip.add.re.ss, where that is the psql host's external IP, and requires password. – cpbills Jun 2 '10 at 17:41
1

If someone is still looking for solution:

Try to set in /etc/ssh/sshd_config:

AllowTcpForwarding yes
GatewayPorts yes

then:

service sshd restart
  • It's safer to specify GatewayPorts clientspecified. – kasperd Feb 28 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.