CODEWITHSUNDEEP
postgresqlssh
KellyM
KellyM

Reputation: 2532

SSH Tunnel for PostgreSQL - Connection Refused

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.

Upvotes: 3

Views: 17831

Answers (1)

drdaeman
drdaeman

Reputation: 11490

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

Upvotes: 4

Related Questions