0041638: Not possible to configure jdbc connection to Postgres using SSL in a usable way

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0041638

Openbravo ERP

A. Platform

public

2019-08-13 09:30

2024-02-01 13:56

Reporter

shuehner

Assigned To

cberner

Priority

normal

Severity

major

Reproducibility

have not tried

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

Fixed in Version

PR24Q1

Merge Request Status

approved

Review Assigned To

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0041638: Not possible to configure jdbc connection to Postgres using SSL in a usable way

Description

In some scenarios it is wanted to use SSL encryption between the application and the database which is a feature supported by jdbc.

Currently openbravo does not allow to configure this in any usable way and relies on workarounds getting it to work only partially.

To configure it using postgres database it is requried to add ssl=true connection parameter when creating the database connection.

Openbravo currently constructs a jdbc URL String to connect to the database. In that URL connection properties come at the end of the String.

Currently people use the fact and in most connections the bbdd.sid line from Openbravo.properties (database name) is the last value used to build said URL and just change it from i.e.

bbdd.sid=openbravo
to
bbdd.sid=openbravo?ssl=true

While that makes typical things work with SSL it immediately causes some regressions and other code assuming (correctly) that bbdd.sid is the database name now needs to strip out that extra values (i.e. openbravo-backup scripts where patched to do that).

Other side-effects:
- install.source has code not using above database name as it needs to create the database initially. Meaning that code will not use SSL and when ssl is required on db side cause issues like 41296.
- Other code may connect to jdbc differently than assumed above and again miss to use SSL i.e. 41314.

Steps To Reproduce

Setup postgres database with SSL connection and configure pg_hba.conf to use 'hostssl' effectively requiring SSL to be used.

Proposed Solution

Add a new Openbravo.properties line i.e. bbdd.params to define any jdbc connection parameter (like ssl).

Find & fix every code creating a database connection to properly use that new parameter.
At least:
- pi code running Openbravo itself (in tomcat)
- every build.xml target working with the database

Ideally also:
- automation/pi
- automation/pi-mobile
- scripts used in CI

The last 3 are important if we ever want to add automated tests for this scenario (ssl required on jdbc level)

Additional Information

Tags

No tags attached.

Relationships

has duplicate	defect	0041296		closed	Triage Platform Base	[19Q3] install source not working if the DB is in a different machine and SSL connection is required
blocks	defect	0041314		closed	Triage Platform Base	[19Q3] [HA] Error while trying to update a HA context via the Module Console