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

Improve this question
1
  • See the answer I've written here
    – Tim Siwula
    Jan 4, 2017 at 21:28

5 Answers 5

Reset to default
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
Improve this answer
3
  • yes, the tunnel is established correctly. The connection error seems to be when it tries to connect to postgres server.
    – user44744
    Jun 2, 2010 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: cyberciti.biz/faq/postgresql-remote-access-or-connection
    – Mircea Vutcovici
    Jun 3, 2010 at 12:44
  • That worked ! Sorry for the late answer, but thanks a million !
    – user44744
    Jul 1, 2010 at 8:10
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...

Improve this answer
3

If someone is still looking for solution:

Try to set in /etc/ssh/sshd_config:

AllowTcpForwarding yes
GatewayPorts yes

then:

service sshd restart
Improve this answer
1
  • It's safer to specify GatewayPorts clientspecified.
    – kasperd
    Feb 28, 2019 at 21:25
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

Improve this answer
1
  • 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, 2010 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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .