PostgreSQL via SSH Tunnel

Question

I'd like to use RazorSQL to connect to my database which is running on a remote server. I create a SSH tunnel on my localhost with the following command:

ssh -L 1111:remote.server.com:5432 [email protected]

I configure my connection via RazorSQL's GUI, specifying localhost as the host and 1111 as the port. When I click on "Connect", the following error message appears:

ERROR: An error occurred while trying to make a connection to
the database: 

JDBC URL: jdbc:postgresql://localhost:1111/myuser

FATAL:
no pg_hba.conf entry for host "aaa.bbb.ccc.ddd",
user "myuser", database "mydatabase", SSL off

where aaa.bbb.ccc.ddd is a remote server's IP address.

What is more, I am not allowed to change the contents of my pg_hba.conf file. That's how it look like at the moment:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

@remove-line-for-nolocal@# "local" is for Unix domain socket connections only
@remove-line-for-nolocal@local   all         all                               @authmethod@
# IPv4 local connections:
host    all         all         127.0.0.1/32          @authmethod@
# IPv6 local connections:
host    all         all         ::1/128               @authmethod@

Is it possible to connect to the database server via SSH tunnel using my current setup and without modifying the server's configuration?

Greggrega

What is @authmethod@ supposed to mean? – Conte

Answer 1

Your pg_hba.conf appears to permit connections from localhost. The easiest way of causing your SSH tunnel connections to appear from localhost is to make them to localhost.

The following SSH command connects to remote.example.com as user "user", and causes your ssh client to listen on localhost, port 1111/tcp. Any connections made to that port will be forwarded over the ssh tunnel, and on the ssh server side the connections will be made to localhost, port 5432/tcp. Since we're connecting to localhost, the connections will appear to be from localhost also, and should match your existing pg_hba.conf line.

ssh -L 1111:localhost:5432 [email protected]

If this is expected to be a long-running tunnel, I would recommend using autossh

To connect using the psql client on the host where you are running the ssh client, use something like this:

psql -h localhost -p 1111 -U your-db-username database-name

You should then be prompted for your database user's password.

Alternately, you can add a line line the following to a file called .pgpass in your home directory on the client where you're running psql:

localhost:1111:database-name:your-db-user:your-db-password

Teahan

Now I'm getting an error: ` FATAL: password authentication failed for user "myuser" ` – Greggrega

If you are receiving a FATAL: password authentication failed for user "myuser", confirm that your username and password are valid credentials for the remote PostgreSQL instance. I've updated the answer with an example of how to connect through the tunnel using the psql client. – Teahan

Nope, that's not the cause. I used ssh, autossh, I change psql client, tried it on windows, linux... Everywhere I get the aforementioned error. In the next few days I'm going to contact with the server's admin. I hope he find possible reason/solution to my problem. – Greggrega

ALTER USER my-db-user WITH ENCRYPTED PASSWORD 'my-db-password' solves the problem definietely. @jeff, thanks for replies! – Greggrega

kozoh did you find a way to make this the default setting? Kinda be lame to be expected to do this in ever database I create. – Scoggins

@PhilDonovan, nope, I didn't need it, sorry. But I think you can write a simple script that will automize your creation of a database. – Greggrega

@Greggrega Yes! Thank-you, thank-you!! Been messing with this for 3 days. If I ssh into machine, I can login to psql console using password. BUT, if I try to connect through an ssh tunnel, it would always give me an "password authentication failed..." error. WITH ENCRYPTED PASSWORD saved the day! – Regain

Answer 2

Here is a Demo:

Open two cmd windows

Enter password when prompting

First

ssh -N -L 1111:127.0.0.1:5432 [email protected]

Second

cd C:\Program Files\PostgreSQL\15\bin

psql -h localhost -p 1111 -U postgres postgres

Check Date and version

Thanks!

Pettiford