Kingfisher: PostgresSQL sslmode=verify-full

[jpmckinney]

We are changing pg_hba.conf to require SSL for remote connections, effectively changing clients from the default sslmode=prefer to the stronger sslmode=require.

To prevent man-in-the-middle (MITM) attacks, we'd need to use sslmode=verify-full, but this requires clients to configure sslcert and sslkey sslrootcert connection parameters.

• Analysts might connect using psql, pgAdmin, Python or Google Colaboratory. For these use cases, sslmode=require is acceptable, and avoids the overhead of setting up certificates. However, we can add instructions to opt-in to sslmode=verify-full: https://ocdsdeploy.readthedocs.io/en/latest/use/databases.html
• Since Redash's data sources are configured for all users, we'd prefer to use sslmode=verify-full. It is supported as of SSL for Postgres getredash/redash#2000
• If/once we start doing database backups or replication, we can use sslmode=verify-full. Setup PostgreSQL replication server #86

All other connections are local. If we move Kingfisher Process or Views to a different server than the database, they can be configured to use sslmode=verify-full.

A quick search turns up this article on how to do it with Docker. I figure the steps would be quite similar for a traditional deployment: https://info.crunchydata.com/blog/ssl-certificate-authentication-postgresql-docker-containers

[jpmckinney]

Using verify-full means that if the replica server or main server goes down, then we can't simply bring them back up as new servers; we'll also have to update the root certificates on any client servers (at this time, redash and kingfisher-replica). (PostgreSQL server naming was discussed at #86 (comment))

We can already use verify-full with a command like:

psql -vv "dbname=ocdskingfisherprocess user=jmckinney host=replica1.kingfisher.open-contracting.org sslmode=verify-full sslrootcert=root.crt"

Where root.crt on the local machine has the contents of /etc/ssl/private/ssl-cert-snakeoil.pem from the remote server. The certificate is for the specific server (replica1.kingfisher.open-contracting.org), not the service (postgres-readonly.kingfisher.open-contracting.org), and the host needs to be set accordingly.

It's easy to similarly configure Redash at, for example, https://redash.open-contracting.org/data_sources/5

Since we want to remain flexible and generally identify services rather than servers, I'll close this issue as unplanned.

The only case (with respect to PostgreSQL) where we identify the server is between the replica and main servers (they use the process1.kingfisher and replica1.kingfisher server names rather than the service names). That said, the likelihood of MITM attacks are low, and it's not really worth configuring verify-full for only one case.

Docs: https://www.postgresql.org/docs/current/ssl-tcp.html

---

I had added these states while exploring this, before realizing that /etc/postgresql/postgresql.conf sets ssl_cert_key and ssl_key_file to /etc/ssl/private/ssl-cert-snakeoil.pem and /etc/ssl/private/ssl-cert-snakeoil.key. If we want to generate own own private key and public certificate, we'd have to change those lines.

{% if 'ssl' in pillar.postgres %}
/var/lib/postgresql/{{ pillar.postgres.version }}/main/server.crt:
  file.managed:
    - contents_pillar: postgres:ssl:pubcert
    - user: postgres
    - group: postgres
    - mode: 644
    - watch_in:
      - module: postgresql-reload

/var/lib/postgresql/{{ pillar.postgres.version }}/main/server.key:
  file.managed:
    - contents_pillar: postgres:ssl:privkey
    - user: postgres
    - group: postgres
    - mode: 600
    - watch_in:
      - module: postgresql-reload
{% endif %}