Reinstall PostgreSQL with OpenSSL using asdf

I spun up a render.com PostgreSQL instance. They gave my a psql connection string, so I copied that into my terminal and hit enter. I half expected it to Just Work™. To Render's credit, everything on their end went smoothly. The issue was with my PostgreSQL psql client.

$ psql postgres://username:password@oregon-postgres.render.com/dbname
psql: error: FATAL:  SSL/TLS required

Hmm 🤔 I guess I need to tell psql to require SSL for this connection by adding sslmode=require.

$ psql postgres://username:password@oregon-postgres.render.com/dbname?sslmode=require
psql: error: sslmode value "require" invalid when SSL support is not compiled in

That gives me an enlightening and more actionable error message. The psql client that I have installed on my machine was not compiled/built with SSL support (i.e. OpenSSL).

I used asdf to install Postgres, which comes with the psql client. I don't remember having SSL support as an option when I installed it. Though I would have expected it to have been a default.

I can check how this version of Postgres was installed—based on the configuration flags that were used—using the pg_config command.

First though, let's set a local variable for the prefix location for many of these commands.

$ local ASDF_PG_PREFIX=$(asdf where postgres 13.1)
$ echo $ASDF_PG_PREFIX
/Users/jbranchaud/.asdf/installs/postgres/13.1

Now I can run pg_config from that directory's bin with the --configure flag.

$ $ASDF_PG_PREFIX/bin/pg_config --configure
 '--prefix=/Users/jbranchaud/.asdf/installs/postgres/13.1'

The only configuration flag that asdf used when I installed Postgres 13.1 was the --prefix flag. It has been a few months, but I assume I ran asdf install postgres 13.1 with no other options.

So, what can I do about this?

I can reinstall Postgres 13.1 with OpenSSL support and then I should be able to connect with sslmode=require to that remote database.

This is going to involve a few steps.

1. Create a dump of all the existing 13.1 databases I have. I don't want to lose this local data.
2. Uninstall Postgres 13.1
3. Install Postgres 13.1 with OpenSSL using the asdf-postgres POSTGRES_EXTRA_CONFIGURE_OPTIONS flag.
4. Restore my 13.1 databases using pg_restore with the dump I created in Step 1.

Dumping All That Data

Another one of the handy executables that comes with a Postgres install is pg_dumpall. This command will dump all schema and data for each database to stdout as SQL statements. This can then be redirected to a .sql file.

$ pg_dumpall \
    --exclude-database="template*" \
    > reinstall_13_1_dump.sql

I included the --exclude-database flag with the template* pattern to excluding dumping (and eventually restoring) the template0 and template1 databases. These will be included with a fresh install of Postgres, so there is no need to carry them around in a dump file.

Uninstall PostgreSQL

I first stop the Postgres server.

$ $ASDF_PG_PREFIX/bin/pg_ctl -D $ASDF_PG_PREFIX/data stop
server stopped

Then I can tell asdf to uninstall the version I'll be reinstalling.

$ asdf uninstall postgres 13.1

Install PostgreSQL with OpenSSL

The asdf-postgres plugin explains that extra configuration options can be included with this option:

POSTGRES_EXTRA_CONFIGURE_OPTIONS - append these configure options along with ones that this plugin already uses

Following after their example, I use the --with-openssl flag and then specify my libraries and includes, based on where my openssl is installed.

$ POSTGRES_EXTRA_CONFIGURE_OPTIONS="--with-openssl --with-libraries=/usr/local/lib:/usr/local/opt/openssl/lib --with-includes=/usr/local/include:/usr/local/opt/openssl/include" \ 
  asdf install postgres 13.1

...
Success. You can now start the database server using:
...

Success! This likely took a while to compile and longer than it would have without the OpenSSL support. Good job hanging in there.

I can now tell asdf I want to use the newly installed version of Postgres, start the server up, and try connecting:

$ asdf local postgres 13.1
$ $ASDF_PG_PREFIX/bin/pg_ctl -D $ASDF_PG_PREFIX/data start
server started
$ psql
2021-02-27 17:00:35.118 CST [72552] FATAL:  database "jbranchaud" does not exist
psql: error: FATAL:  database "jbranchaud" does not exist

That last error is a good sign. It means the default database named for my unix user hasn't been created yet. That's the sign of a freshly installed Postgres instance. That will take us to the next step of restoring the dumped data.

But first, just real quick, let's check how this Postgres instance has been configured.

$ $ASDF_PG_PREFIX/bin/pg_config --configure
 '--prefix=/Users/jbranchaud/.asdf/installs/postgres/13.1' '--with-openssl' '--with-libraries=/usr/local/lib:/usr/local/opt/openssl/lib' '--with-includes=/usr/local/include:/usr/local/opt/openssl/include'

Yay, the flags we specified were used. Moving on.

Restoring My Dumped Databases

I can restore my dumped database with psql command pointed at that file.

$ psql -f reinstall_13_1_dump.sql postgres

The last argument (postgres) is the database that the psql command is running against. Without specifying that, it was trying to preemptively connect to the not-yet-existing jbranchaud database before processing the lines of SQL in the dump file.

Check that all the database were restored as expected:

$ psql -c '\l'

This sends the \l command to psql which will list out all the database in this cluster.

I can double check that SSL is enabled from psql:

show ssl;
 ssl
-----
 on

It is. And I am now able to connect to that SSL-required Render database. 🎉

Cover photo by Marek Piwnicki on Unsplash