Skip to content

Instantly share code, notes, and snippets.

@noteed

noteed/psql-ssh-tunnel.md

Last active May 25, 2018 21:51
Show Gist options
  • Save noteed/5767393 to your computer and use it in GitHub Desktop.
Save noteed/5767393 to your computer and use it in GitHub Desktop.
Download ZIP
SSH tunnel for PostgreSQL connection
Raw
psql-ssh-tunnel.md

SSH tunnel for PostgreSQL connection

Create a .pgpass file that will be used to provide the password for any libpq-based program for the matching hostname/port/database/user. That file should be chmod'd 0600.

> cat .pgpass
127.0.0.1:5433:database:user:password

Create a SSH tunnel: we open the local port 5433 and, out of the tunnel, go to localhost:5432. You probably really want localhost and not remote: your PostgreSQL database is probably accepting connections only from locahost.

> ssh -N -L 5433:localhost:5432 user@remote

We connect to localhost:5433 locally providing enough data for the .pgpass file to kick in.

> psql -U user -h 127.0.0.1 -p 5433 database
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment