10

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

user44744
  • 203
  • 1
  • 2
  • 4

5 Answers5

12

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

Tomislav Nakic-Alfirevic
  • 241
  • 2
  • 4
5

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
Mircea Vutcovici
  • 17,619
  • 4
  • 56
  • 83
  • yes, the tunnel is established correctly. The connection error seems to be when it tries to connect to postgres server. – user44744 Jun 02 '10 at 14:50
  • 4
    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: http://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/ – Mircea Vutcovici Jun 03 '10 at 12:44
  • That worked ! Sorry for the late answer, but thanks a million ! – user44744 Jul 01 '10 at 08:10
3

If someone is still looking for solution:

Try to set in /etc/ssh/sshd_config:

AllowTcpForwarding yes
GatewayPorts yes

then:

service sshd restart
Jenny D
  • 27,780
  • 21
  • 75
  • 114
Kamil
  • 31
  • 1
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

cpbills
  • 2,720
  • 18
  • 12
  • 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 02 '10 at 17:41
0

Make sure you're not actually connecting to a local instance of Postgres listening on the same port as the one you selected for the local end of the tunnel.

If the local port is in use, ssh will print an error message and skip setting up the tunnel. But it will still connect to the remote server, which can cause the error to scroll off the screen. Since you're able to connect, you don't suspect that anything's wrong with the tunnel until you, as a final resort, stop the remote Postgres altogether and find out that you can still connect.

Don't ask me how I know this.

Roger Dahl
  • 371
  • 3
  • 6