Support for SSL connections with PostgreSQL

Checked for duplicates

Yes - I've already checked

Alternatives considered

Yes - and alternatives don't suffice

Related problems

I'm trying to deploy Aerie to K8S (AWS EKS) and use Aurora Serverless as the databsae. I've got the pods running but It looks like by default PostgreSQL connections do not use SSL. Which is required by default in RDS.

For example the use of pg.Pool in aerie-action does not provide an option to pass in the ssl: true. Is this consistent across all Aerie services? :sadtrombones:

    import type { Pool } from "pg";
    import pg from "pg";
    import { configuration } from "./config";
    import logger from "./utils/logger";

    const { AERIE_DB, AERIE_DB_HOST, AERIE_DB_PORT, ACTION_DB_USER, ACTION_DB_PASSWORD } = configuration();

    export class ActionsDbManager {
      private static pool: Pool;

      static getDb(): Pool {
        // singleton DB pool, shared by the process
        // saved as a static to prevent accidental re-initialization
        if (ActionsDbManager.pool) return ActionsDbManager.pool;

        try {
          logger.info(`Creating PG pool`);
          ActionsDbManager.pool = new pg.Pool({
            host: AERIE_DB_HOST,
            port: parseInt(AERIE_DB_PORT, 5432),
            database: AERIE_DB,
            user: ACTION_DB_USER,
            password: ACTION_DB_PASSWORD,
            max: 3,
            min: 3,
          });
          return ActionsDbManager.pool;
        } catch (error) {
          logger.error(error);
          throw error;
        }
      }
    }

Describe the feature request

Please make the use of SSL in Postgres configurable. This will open up options for it's use typical cloud based deployments in which postgres is managed externally to Aerie itself.

Start by inspecting the aerie-action pg.Pool setup shown in the issue, then locate PostgreSQL pool creation across the other Aerie services. Determine how SSL should be configured and verify that the setting reaches each pool; done means externally managed PostgreSQL deployments can enable SSL without breaking existing connections.