[FOLIO-2412] Clients should verify PostgreSQL SSL/TLS server certificate Created: 20/Dec/19  Updated: 28/May/22

Status: Blocked
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: Task Priority: P2
Reporter: Johannes Drexl Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: platform-backlog, postgres, security, security-reviewed
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Blocks
is blocked by FOLSPRINGS-55 PostgreSQL SSL CA Certificate configu... Open
is blocked by VERTXLIB-20 Unit test DB_SERVER_PEM and TLS verif... Open
is blocked by ERM-2186 PostgreSQL SSL CA Certificate configu... Closed
is blocked by OKAPI-792 PostgreSQL SSL CA Certificate configu... Closed
is blocked by RMB-547 PostgreSQL SSL CA Certificate configu... Closed
Duplicate
is duplicated by FOLIO-2406 SSL/TLS, SCRAM-SHA-256, migration to ... Closed
Relates
relates to OKAPI-787 Support SSL connections to Postgres Closed
relates to RMB-546 Support TLS/SSL connections to Postgres Closed
Sprint:
Development Team: Core: Platform

 Description   

Enable SSL server certificate pinning when upgrading. PostgreSQL will by default allow connections to servers with unknown/self-signed certificates and doesn't bother about server verification:
https://www.postgresql.org/docs/current/libpq-ssl.html#LIBQ-SSL-CERTIFICATES
Introduce a parameter to make certificate verification enforceable or better make it mandatory.
There are several options: https://vertx.io/docs/vertx-core/java/#_enabling_ssl_tls_on_the_client

  • setHostnameVerificationAlgorithm("HTTPS")
  • new JksOptions().setValue(myTrustStoreAsABuffer) for truststore.jks
  • setPfxTrustOptions(new PfxOptions().setValue(myTrustStoreAsABuffer) for truststore.pfx
  • setPemTrustOptions(new PemTrustOptions().addCertValue(myTrustStoreAsABuffer) for ca-cert.pem

For each module and for Okapi ensure that it reads the DB_SERVER_PEM environment variable (Okapi: postgres_server_pem), and if this variable is defined then all connections to PostgreSQL

  • get rejected if unencrypted
  • get rejected if TLS versions lower than TLSv1.3
  • get rejected if the DB_SERVER_PEM certificate doesn't match

For Okapi this is unit tested in https://github.com/folio-org/okapi/blob/v4.14.0/okapi-core/src/test/java/org/folio/okapi/service/impl/PostgresHandleTest.java#L106-L129

For RMB this is unit tested in https://github.com/folio-org/raml-module-builder/blob/master/domain-models-runtime/src/test/java/org/folio/rest/persist/PostgresClientSslTest.java and is available for RMB 34.0.0 (to be released).

For folio-vertx-lib this partly unit tested in https://github.com/folio-org/folio-vertx-lib/blob/v1.1.0/core/src/test/java/org/folio/tlib/postgres/TenantPgPoolTest.java#L203-L218 , a unit test for the DB_SERVER_PEM env variable is missing.

For Spring way to be investigated.

For ERM modules (Grails based) to be investigated.



 Comments   
Comment by Johannes Drexl [ 20/Dec/19 ]

This is a Subtask for ticket https://folio-org.atlassian.net/browse/OKAPI-787

It will affect communication between Okapi and the modules too when enabling SSL there.

Comment by Julian Ladisch [ 20/Dec/19 ]

Which PostgreSQL client doesn't check the PostgreSQL server certificate?
The vertx-mysql-postgresql-client used by Okapi? https://github.com/folio-org/okapi/blob/v2.35.2/okapi-core/pom.xml#L83
The vertx-mysql-postgresql-client-jasync used by modules that use RAML Module Builder? https://github.com/folio-org/raml-module-builder/blob/master/domain-models-runtime/pom.xml#L142

Comment by Johannes Drexl [ 08/Jan/20 ]

The PostgreSQL system client doesn't check certificates. Okapi doesn't even support SSL.

Comment by Craig McNally [ 14/Oct/21 ]

Discussed with CP team and the thought is that https://folio-org.atlassian.net/browse/RMB-546 encompasses this.  We bumped that story to P2

Generated at Thu Feb 08 23:20:27 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.