Replace the sciety-demo database with a Postgres run by the operator in the cluster

[giorgiosironi]

No description provided.

[giorgiosironi]

First attempt:

{"timestamp":"2024-05-16T10:32:25.060Z","level":"info","message":"Database connection pool and logger available","payload":{"databaseHost":"sciety--demo-primary.sciety.svc"}}
Unable to start:
{
  "length": 161,
  "name": "error",
  "severity": "FATAL",
  "code": "28000",
  "file": "auth.c",
  "line": "502",
  "routine": "ClientAuthentication"
}
Error object: {
  "length": 161,
  "name": "error",
  "severity": "FATAL",
  "code": "28000",
  "file": "auth.c",
  "line": "502",
  "routine": "ClientAuthentication",
  "message": "no pg_hba.conf entry for host \"10.0.21.238\", user \"sciety--demo\", database \"sciety--demo\", SSL off",
  "stack": "error: no pg_hba.conf entry for host \"10.0.21.238\", user \"sciety--demo\", database \"sciety--demo\", SSL off\n    at /app/node_modules/pg-pool/index.js:45:11\n    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)"
}

[erkannt]

After changing the host to pgbouncer

Unable to start:
{
  "length": 33,
  "name": "error",
  "severity": "FATAL",
  "code": "08P01"
}
Error object: {
  "length": 33,
  "name": "error",
  "severity": "FATAL",
  "code": "08P01",
  "message": "SSL required",
  "stack": "error: SSL required\n    at /app/node_modules/pg-pool/index.js:45:11\n    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)"
}

[erkannt]

Looks like our assumption/their docs were wrong. The pg library that we use reads the ENVVAR that libpg uses but most of them result in the same behaviour. We might want to use no-verify.
https://github.com/brianc/node-postgres/blob/81e2ff05247c58c152ed52f1cc7299d30cc89284/packages/pg/lib/connection-parameters.js#L21-L34

[giorgiosironi]

Dumping the last error we got to:

{"timestamp":"2024-05-16T13:58:51.467Z","level":"info","message":"Database connection pool and logger available","payload":{"databaseHost":"sciety--demo-pgbouncer.sciety.svc"}}
Unable to start:
{
  "code": "SELF_SIGNED_CERT_IN_CHAIN"
}
Error object: {
  "code": "SELF_SIGNED_CERT_IN_CHAIN",
  "name": "Error",
  "message": "self-signed certificate in certificate chain",
  "stack": "Error: self-signed certificate in certificate chain\n    at /app/node_modules/pg-pool/index.js:45:11\n    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)"
}

[giorgiosironi]

Also to compare, RDS can provide SSL too, and does as a default from Postgres 15; when it does, it requires a certificate to be provided to the client for verification:

> $ psql "host=db-name.555555555555.ap-southeast-1.rds.amazonaws.com \
  port=5432 dbname=testDB user=testuser sslrootcert=rds-ca-rsa2048-g1.pem sslmode=verify-full"

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.General.SSL.html#PostgreSQL.Concepts.General.SSL.Requiring

[scottaubrey]

@giorgiosironi @erkannt To avoid alerts firing all evening, I've applied this patch which has allowed sciety to deploy successfully.

Not sure where you're thinking is at with regards to packaging sciety to be able to connect via TLS or not, but at least the option to not have it works if config is set correctly.