SSH Tunnel for PostgreSQL - Connection Refused

Question

I am, for the first time, attempting to set up an application on a remote/cloud VPS (I am using Digital Ocean if it matters). I am attempting to create an SSH tunnel from my client to the remote database. As this is not something I have attempted before, I referenced this, this, and this.

After looking over the articles, I ran the following on my client/local machine:

 ssh -L 5433:localhost:5432 user@REMOTE_IP

then I tried to connect:

 psql -h localhost -p 5433 postgres;

However, I receive the following error:

psql: could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5433?

To my knowledge, my pg_hba.conf (on the remote server) is the default:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

I changed "listen_addresses" in postgresql.conf to *

    # - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

I also tried substituting 127.0.0.1 forlocalhost with no success.

Any advice would be appreciated; SSH tunnels and the like are not something I am familiar with.

Thanks. EDIT:

Per @drdaeman excellent advice, I ran the following:

sudo ssh -N -vvv -L 5433:localhost:5432 user@host

The last few debug lines are as follows:

    debug1: Local forwarding listening on 127.0.0.1 port 5433.
debug2: fd 5 setting O_NONBLOCK
debug3: fd 5 is O_NONBLOCK
debug1: channel 1: new [port listener]
debug2: fd 3 setting TCP_NODELAY
debug3: ssh_packet_set_tos: set IP_TOS 0x10
debug1: Requesting [email protected]
debug3: send packet: type 80
debug1: Entering interactive session.
debug1: pledge: network
debug3: receive packet: type 80
debug1: client_input_global_request: rtype [email protected] want_reply 0

Output from sudo netstat -ltpn | grep 5432

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      5835/postgres

It stops there, unresponsive to any commands.

Thanks for any direction.

Thanks sibert. I tried restarting both PostgreSQL and the server itself but no change. — KellyM
– KellyM, Commented Aug 20, 2017 at 15:30

drdaeman · Accepted Answer · 2017-08-20 14:10:01Z

4

Based on your description, everything looks OK to me - don't see where the problem is, but the commands you're running and your configuration looks correct. Here are the general steps you can take to diagnose the issue:

First, check if your PostgreSQL server is actually listening. On your server, run this:

$ sudo netstat -ltpn | grep 5432

(Or you can use ss -ltpn from iproute2 instead of older netstat)

If you don't see anything, it means no process is listening on tcp/5432. You can try to see if PostgreSQL is listening anywhere at all:

$ sudo netstat -lpn | grep postgre

If it doesn't - check whenever your server is actually running (depends on the OS and distribution, but check ps aux output first) and check your server logs (probably in /var/log) if you see any problems there.

Then, make sure you don't accidentally run psql on your server (when you SSH, it also opens the shell session unless you specify the -N flag). You need to run it on your local machine ;)

Then, you may also consider adding -v (or even -vvv) to your ssh command - it'll spew a lot of useful debug information, e.g. a normal operation looks like this:

debug1: Connection to port 5433 forwarding to localhost port 5432 requested.
debug1: channel 3: new [direct-tcpip]
debug1: channel 3: free: direct-tcpip: listening port 5433 for localhost port 5432, connect from ::1 port 60039 to ::1 port 5433, nchannels 4

If you see something like channel 3: open failed: connect failed: Connection refused instead, this means PostgreSQL had refused the connection - and you need to check its logs for the reasoning - possibly, after enabling log_connections and log_disconnections in the config (don't forget to reload the configuration).

answered Aug 20, 2017 at 14:10

drdaeman

11.6k8 gold badges63 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

drdaeman Over a year ago

Oh, and don't forget to reconfigure listen_addresses so you don't end up with your PostgreSQL instance exposed to the world. You don't need it listening on 0.0.0.0 and/or [::], if you use SSH tunneling. It only has to listen on 127.0.0.1 and/or ::1.

KellyM Over a year ago

Thanks much. Excellent advice. I have added the debug output above.

drdaeman Over a year ago

@KellyMarchewa Two questions to diagnose this further: 1) have you checked if the server is running listening correctly? And 2) the ssh output you had shown is normal (and, yes, it'll get stuck like this - you can terminate it with ctrl+c when you don't need it anymore) but does anything happen to the ssh output when you run psql? It should say e.g. debug1: Connection to port 5433 forwarding to localhost port 5432 requested. when you do. Port forwarded connections are only established when they're requested, not when you run ssh and connect.

drdaeman Over a year ago

Ok, your Postgres is listening, ssh -vvv -N -L 5433:127.0.0.1:5432 your.server.example.org should work. You can try telnet localhost 5433 instead of psql -h 127.0.0.1 -p 5433 postgres just to see if it connects. Watch out for ssh output - it must say something if there are any connection attempts. If it doesn't then there is no connection (or something is really broken). Seeing as you have marked answer as accepted, I hope you have figured the exact cause. I suspect it's something about psql invocation, or IPv4/IPv6 mix issue (e.g. ssh trying to connect to ::1 instead of 127.0.0.1).

Collectives™ on Stack Overflow

SSH Tunnel for PostgreSQL - Connection Refused

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related