[Docs]: Follow-up of #406 - wiki page, compose example and default passwords for TLS to PostgreSQL #412

Summary

Follow-up to #406, which #410 implemented: configurable TLS and certificate verification for the PostgreSQL connection, with the CI integration suite running against a TLS-only PostgreSQL with SslMode=VerifyFull. The code part is done. This issue tracks the documentation and deployment examples that #406 also asked for and that were not part of #410.

Remaining work

• Wiki page "TLS between WebServices and PostgreSQL", linked from _Sidebar.md and from the Backtier section of Configuration.md:
  • the Backtier TLS settings, which SslMode to use when, and the order in which the CA is looked up (RootCertificate, then PGSSLROOTCERT, then the default root.crt, then the OS trust store)
  • the PostgreSQL side: ssl = on, ssl_min_protocol_version, hostssl instead of host in pg_hba.conf. Only the server can stop a fallback to plain text.
  • certificates in Docker: mounted read-only, readable by the container user ($APP_UID); the certificate name must match Backtier:HostName (the Compose service name, e.g. comet-db)
  • managed PostgreSQL (Azure, AWS): VerifyFull with the provider's CA
  • optional client-certificate login (cert in pg_hba.conf)
  • the startup log messages, their causes and fixes
• Configuration.md (done): document the new Backtier settings (SslMode, RootCertificate, ClientCertificate, ClientCertificateKey, ClientCertificatePassword, CheckCertificateRevocation) in the example and the setting list.
• Note about the default passwords (done, section "Default passwords" of the wiki page): the compose files and images use postgres / pass (database) and cdp4 / cdp4 (COMET database user). State that they must be changed in production, and how.
• Compose example with a TLS-only database in this repository, e.g. docker-compose-db-tls.yml, with a compose.bat target:
  • PostgreSQL with ssl = on, ssl_min_protocol_version = TLSv1.2 and a pg_hba.conf that only allows hostssl over the network
  • WebServices with Backtier__SslMode=VerifyFull and the CA mounted read-only
  • a short script or README section that creates a test CA and server certificate. The steps "Create TLS certificates for PostgreSQL" and "Start PostgreSQL Container (TLS only)" in .github/workflows/CodeQuality.yml are a working template.
• Optional, in the database image repository (cdp4-database-community-edition): built-in TLS support, e.g. environment variables for ssl, the certificate paths and an SSL-only pg_hba.conf. Users then don't need to pass -c … arguments and their own pg_hba.conf. That would be an issue in that repository; link it here.

Notes

• Certificate key ownership: PostgreSQL only accepts a private key owned by its own user. In the Alpine-based images that is uid 70, in Debian-based images uid 999. The compose example and the wiki should mention this, since it is the most common reason for the database not starting with TLS.

Related

• [Feature]: Configurable TLS and certificate verification for the PostgreSQL connection #406: TLS for the PostgreSQL connection (closed by [Add] configurable TLS and certificate verification for the PostgreSQL connection #410).
• [Bug]: Duplicate console log lines in the Docker image, and startup logging must explain failures #409: logging must explain failures (troubleshooting page with log message → cause → fix).
• [Feature]: Support HTTPS directly in Kestrel without an nginx reverse proxy #405: HTTPS directly in Kestrel.