postgres: tls-mode verify-ca does not work correctly (behaves like verify-full)

[tomcastro89]

summary by @gabor : the postgres TLS mode verify-ca does not work correctly, and behaves the same as verify-full. to reproduce: try this scenario: https://github.com/grafana/oss-big-tent-tools/tree/main/tls-setups/postgres#verify-server-cert-ignore-host . it should work, but it does not.

---

What happened:
Grafana Versions >= 9.4.0 cause the following issues in Panels that use an PostgreSQL (AWS RDS) as Datasource and
the following TLS settings:

Issues

db query error: x509: certificate is valid for <DB instance url>, not <route53 url to db>

db query error: x509: certificate is not valid for any ....

Settings

Host: "insert AWS RDS Postgres Host route53"
Database: "insert DB name"
User+Password
TLS/SSL Mode: verify-ca
TLS/SSL Method: Certificate content
TLS/SSL Root Certificate: "insert official aws rds certificate"

PostgreSQL details
Version: 11

One(the first) certificate out of the official AWS RDS root certificates eu-central-1 bundle is used:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html
eu-central-1-bundle.pem

What you expected to happen:
The panels should work in the same way they do in Grafana v9.3.8

How to reproduce it (as minimally and precisely as possible):

1. Use Grafana v9.3.8
2. Add an AWS RDS Postgres Database as Datasource incl. the TLS settings above
3. use a route53 to access the AWS RDS
4. it should work
5. upgrade Grafana to a Version >=9.4.0
6. the issues mentioned above should appear and the panels dont work

Anything else we need to know?:

Environment:

• Grafana version: >= 9.4.0
• Data source type & version: PostgreSQL
• OS Grafana is installed on: Grafana official image deployed as Stateful Set in Openshift

[idastambuk]

I think this is a ticket for the @grafana/grafana-bi-squad, as it’s a PostgreSQL datasource

[mymasse]

Well we just ran into this same type of issue upgrading to Concourse 7.9.1, and after debugging more we think the problem is due to the upgrade of lib/pq to 1.10.7. An issue was raised in lib/pq: lib/pq#1106

[tomcastro89]

Hi,

the same issue appears now when updating grafana from 10.1.5 to 10.2.0.

Please look into it. Thanks!

[zoltanbedi]

Thanks for letting us know @tomcastro89. I'm going to reopen this issue.

[gabor]

fixed in #81353

[gabor]

unfortunately we had to revert the fix, has problems in certain corner-cases, so i'll reopen this one too. we'll adjust our fix and do it again.

[papagian]

re-opening since #83892 fixes when postgres is used as database backend not as a data source

[zoltanbedi]

We also fixed it for the Postgres datasource in #83768

[gabor]

fixed again in #85530 ( we had to roll back the previous fix)