How I Connect to Postgres Databases

Posted on Tuesday December 13, 2022

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

Using `.pg_service.conf`

When you use psql to connect, you can use a connection URL, CLI flags, or you can use a series of libpq options:

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

By default, libpq tries to load service definitions from ~/.pg_service.conf. This file uses an INI-style format, and can be populated with services like this:

[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 service name:

$ psql 'service=db1'

Storing passwords in `.pgpass`

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 ~/.pgpass file.

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 db1 service, you could remove password=sesame from ~/.pg_service.conf and instead add the following line to ~/.pgpass:

db1.internal.net:5432:db:app:sesame

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 as the app user to any database on db1.internal.net:5432.

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 db1 service.

We can forward a local port through a SSH tunnel by passing the -L option to ssh:

$ ssh -L 15432:db1.internal.net:5432 ben@ssh.public.net -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 db1.internal.net using 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 localhost:15432:

[db1]
host=localhost
port=15432
user=app
dbname=db
password=sesame

Using `~/.ssh/config`

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 works:

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 192.0.32.7

Headless ssh with a control socket

So now we can connect fairly easily to our database:

Run ssh -L 15432:db1.internal.net:5432 ssh.public.net.
In a separate window, run psql service=db1.
When you are done with your psql session, use ^D to 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.

Luckily, 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 conn.sock as 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 exit control command over 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 running:

$ db1-psql

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!

How I Connect to Postgres Databases

Using .pg_service.conf

Storing passwords in .pgpass