Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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
share|improve this answer
    
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

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

share|improve this answer
    
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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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