A repository of bitesize articles, tips & tricks
(in both English and French) curated by Mirego’s team.

Use “asdf-postgres” to connect to SSL-enabled PostgreSQL databases

Most of the databases we use in our Web applications are hosted by Cloud SaaS products such as Amazon Relational Database Service (RDS) and Heroku Postgres.

All of them require us to do the SSL dance to connect to them — which is a good thing. All of the ORMs we use (eg. Ecto, Active Record) support this.

We also sometimes need to connect to databases to run some manual operations, for example with psql or pg_dump. To be able to do that, we need to make sure our locally-installed PostgreSQL version has SSL support, otherwise we’ll get this error:

$ psql "postgres://foo:bar@host.example.com:5432/name"
# → psql: FATAL:  no pg_hba.conf entry for host "1.2.3.4", user "foo", database "name", SSL off

If that’s the case, we need to manually pass it a few configuration options when installing it to make sure we get SSL support compiled in. For example, with asdf-postgres:

$ POSTGRES_EXTRA_CONFIGURE_OPTIONS="--with-uuid=e2fs --with-openssl --with-libraries=/usr/local/lib:/usr/local/opt/openssl@1.1/lib --with-includes=/usr/local/include:/usr/local/opt/openssl@1.1/include" asdf install postgres 12.4
$ asdf global postgres 12.4
$ psql "postgres://foo:bar@host.example.com:5432/name"
# → psql (12.4, server 10.14 (Ubuntu 10.14-1.pgdg16.04+1))
# → SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

TIL! ✌️