External postgres database connection over SSL always verify the certificate against CA with sslmode: require

[manfuin]

Please confirm the following

• I agree to follow this project's code of conduct.
• I have checked the current issues for duplicates.
• I understand that the AWX Operator is open source software provided for free and that I might not receive a timely response.

Bug Summary

When PostgreSQL is using self-signed certificate, there is no straight-forward way to allow connectivity from AWX.

Setting the ssmode to requirestill validates certificate. According to documentation it should not, unless CA certificate is explicitly specified (that turns it to verify-ca mode).

Due to the fact that sslrootcert is unconditionally set in the credentials.py there is no way to tell AWX to avoid certificate validation.
Adjusting the Jinja template to conditionally define the sslrootcert should solve the issue.

In our particular case the PostgreSQL is provisioned by Zalando Postgres Operator that in turns don't allow non-SSL connections, so setting sslmode to disable/allow/prefer does not work.

AWX Operator version

1.1.4

AWX version

21.11.0

Kubernetes platform

openshift

Kubernetes/Platform version

4.11.0-0.okd-2023-01-14-152430

Modifications

no

Steps to reproduce

1. Create postgres configuration secret with connection details to the external database with non-trusted certificate, specifying:

apiVersion: v1
kind: Secret
metadata:
  name: test-awx-postgres-configuration
type: Opaque
stringData:
  ...
  sslmode: require
  type: unmanaged

Create AWX resource with:

---
spec:
  postgres_configuration_secret: test-awx-postgres-configuration

Expected results

Connection works, skipping certificate verification, as ssmode: require should not verify certificate unless the sslrootcert is explicitly provided.

Actual results

AWX is not able to work with database, complaining about certificate verification.

Additional information

No response

Operator Logs

No response

[fosterseth]

@manfuin sounds like a valid ask. Sounds like your suggested solution would be a good approach -- do you think you could open a PR to implement that?