How I Connect to Postgres Databases
I often need to connect to PostgreSQL databases for projects I'm working on, and over time I've developed a method that works pretty well for me. It's pretty specific to how I like to work so I wouldn't recommend it for everyone. But since some of my coworkers have asked about it, I figured I'd write down the major pieces of the puzzle so others can adapt any parts they like to their own workflows.
For starters, I almost exclusively use the
psql command line client.
If you don't use
psql, then most of this is probably not relevant to you.
Otherwise, keep reading!
Throughout this page, I'll pretend that there's a database server that we want
to connect to called
db1.internal.net that listens on the default port of
When you use
psql to connect, you can use a connection URL, CLI flags, or you
can use a series of
$ psql 'host=db1.internal.net user=app dbname=db password=sesame port=5432'
If you're frequently connecting to the same database, it can be a little
annoying to constantly type in all those parameters or try to find them in your
shell history. To make life easier, you can put the
libpq options for your
frequently-used connections into a service file.
libpq tries to load service definitions from
This file uses an INI-style format, and can be populated with services like
[db1] # <-- name of the service host=db1.internal.net port=5432 user=app dbname=db password=sesame
Once you create this file, you can connect with
psql by simply referencing the
$ psql 'service=db1'
Storing passwords in
If you need to use password authentication for your database, and you don't want
to keep your passwords in
~/.pg_service.conf, you can use a separate
Each line of this password file describes the password to use for a particular
database connection or connections. The entry format is colon-delimited
host:port:database:username:password. For example to connect to our
service, you could remove
instead add the following line to
You can also use
* as a wildcard for any of the fields, e.g.
db1.internal.net:5432:*:app:sesame means to use password
sesame to connect
app user to any database on
Port forwarding with SSH
Often, the databases you need to connect to aren't directly available, and you
need to connect through a bastion host of some kind. For example, maybe we can
only connect to
db1.internal.net after we SSH into an internal network.
For the sake of example, we'll imagine that there is a server called
ssh.public.net that we can SSH into when we want to connect to our
We can forward a local port through a SSH tunnel by passing the
-L option to
$ ssh -L 15432:db1.internal.net:5432 email@example.com -p 2222
This will connect to
ssh.public.net on port 2222, and then set up a socket
on your local machine bound to port 15432, and any connections you make to that
port will be forwarded over the SSH channel to
db1.internal.net:5432 from the
remote machine you're SSH'd into.
This means that we can now connect to
psql by making
a connection to
localhost:15432. This can be wrapped up as an entry in your
~/.pg_service file where instead of listing
db1.internal.net:5432, you list
[db1] host=localhost port=15432 user=app dbname=db password=sesame
Instead of needing to remember to use
ben as the username for
ssh.public.net, and that
sshd is actually listening on port 2222, you can
add an entry to
~/.ssh/config similar to the way the Postgres service file
Host ssh.public.net User ben Port 2222
Now, you can omit the username and port and simply:
$ ssh -L 15432:db1.internal.net:5432 ssh.public.net
You can actually make the
Host label anything you want, it doesn't need to be
the real name of the server. This can be useful if you don't actually have a DNS
name to connect to and you don't want to remember the IP address:
Host my-internal-net User ben Port 2222 HostName 220.127.116.11
Headless ssh with a control socket
So now we can connect fairly easily to our database:
ssh -L 15432:db1.internal.net:5432 ssh.public.net.
- In a separate window, run
- When you are done with your
^Dto log out from the SSH connection.
This works pretty well, but for frequently used connections, it'd be even nicer to just have one command to run and not need to deal with multiple shell sessions.
ssh connections can be controlled headlessly through a Unix control
socket. Here's what this looks like:
$ ssh -M -S conn.sock -fnNT -L 15432:db1.internal.net:5432 ssh.public.net $ psql service=db1 $ ssh -S conn.sock -O exit ssh.public.net
In the first command, we establish the SSH connection and specify
the control socket for connection sharing. We also use the
-f option so that
ssh will go to background just before command execution. (You can read more
about the other options in the
ssh(1) manpage, but they basically prevent SSH
from starting an actual console session on the remote host so we're only doing
the port forwarding.)
Once the connection is established, we can run
psql as usual, and forward our
Postgres traffic over the established SSH connection.
Finally, when we're done with
psql, we can have
ssh send the
conn.sock to close the SSH connection.
Tying it all together
I tend to wrap all of this up in a short shell script named something like
db1-psql. The scripts look pretty much like what I described above:
#!/bin/sh SOCKET=db1-ssh.sock LOCAL_PORT=15432 REMOTE_DB_HOST=db1.internal.net REMOTE_DB_PORT=5432 SSH_HOST=ssh.public.net ssh -M -S "$SOCKET" -fnNT -L "$LOCAL_PORT:$REMOTE_DB_HOST:$REMOTE_DB_PORT" "$SSH_HOST" psql service=db1 ssh -S "$SOCKET" -O exit "$SSH_HOST"
With this in place, and the
db1-psql script in my
$PATH (usually for me this
means dropping it in
~/.bin/), I can connect to the database by simply
There are lots of ways to connect to databases, but this is what I've found works well for me. Feel free to take any bits and pieces of this that you like and use them in workflow!
libpqConnection Service File
libpqParameter Key Words (
sshConfiguration File Format