[BUG] Inputs PostgreSQL plugin will not work with CA cert validation

[ZPascal]

Relevant telegraf.conf:

[[inputs.postgresql]]
 interval = "1m"
 address = "host=192.168.0.10 port=5432 user=user1 password=xxx database=x1 sslmode=verify-ca sslrootcert=/srv/telegraf_tls/ca.crt"
 databases = ["x1"]
[[inputs.postgresql_extensible]]
 interval = "1m"
 address = "host=192.168.0.10 port=5432 user=user1 password=xxx database=x1 sslmode=verify-ca sslrootcert=/srv/telegraf_tls/ca.crt"
 databases = ["x1"]
 [[inputs.postgresql_extensible.query]]
sqlquery='''
 SELECT t2.rolname as rolname, t3.datname as datname, queryid, calls, ...
'''

System info:

• Docker container
• OS: Ubuntu 20.04.2 LTS
• Telegraf version: 1.16.3

Docker

Dockerfile

Steps to reproduce:

1. Start Telegraf
2. Show the Telegraf logs

Expected behavior:

I expect the establishment of an encrypted connection to the database and smooth functionality of the PostgreSQL plugin.

Actual behavior:

We have configured an encrypted connection to the PostgreSQL database including CA-Cert validation in our Telegraf configuration. After starting Telegraf, the following error is displayed in the error log and no connection to the database can be established.
[inputs.postgresql] Error in plugin: x509: cannot validate certificate for 192.168.0.10 because it doesn't contain any IP SANs

Additional info:

Of course, we have already checked the certificate and established a connection to the DB using the psql client. There were no problems and the encrypted connection could be established without problems in verify-ca mode. Our CA certificate does not have a SAN entry, but in my opinion this entry should be checked in verify-full mode.
I have the feeling that the database driver interprets the rules wrong and uses verify-full instead of verify-ca.

Workaround:
As a workaround we changed the ssl mode to require and were able to connect to the database.

[helenosheaa]

Hi,

So I've found a few things which I think might be related/helpful:

There is an open issue on jackc/pgx with someone getting the same error message as you. It may be worth commenting on this thread to see if this is something that would be fixed if we upgraded to jackc/pgx v4 (we are currently using v3.6.0).

In these postgreSQL docs it references using sslmode=require instead of verify-ca for older versions of postgreSQL. What version of postgreSQL are you using?

It looks from the source code of jackc/pgx that in either case of setting verify-full or verify-ca it's setting the serverName to the host name whereas in the case of require it sets insecure skip verify to true. Which may be the reason why setting it to require is fixing this for you.

Are you willing to reach out on that issue to see if this is something that would change if we upgraded? Otherwise I think we may need to update the readme to explain this behavior.

[ZPascal]

Hi @helenosheaa ,

thank you for your answer.

There is an open issue on jackc/pgx with someone getting the same error message as you. It may be worth commenting on this thread to see if this is something that would be fixed if we upgraded to jackc/pgx v4 (we are currently using v3.6.0).

I think the creator of the issue is using an old version of pgx.

In these postgreSQL docs it references using sslmode=require instead of verify-ca for older versions of postgreSQL. What version of postgreSQL are you using?

We use PostgreSQL version 11.

It looks from the source code of jackc/pgx that in either case of setting verify-full or verify-ca it's setting the serverName to the host name whereas in the case of require it sets insecure skip verify to true. Which may be the reason why setting it to require is fixing this for you.

I think that will be the problem :)

Are you willing to reach out on that issue to see if this is something that would change if we upgraded? Otherwise I think we may need to update the readme to explain this behavior.

I had a closer look at the pgx code and found the following issues:

• Within v4, the low level API has been moved out and can now be found here.
• Furthermore, in this dependency in version 1.5.0 the error was apparently fixed.
• This corresponding version is used for the first time in pgx in version 4.6.0.

I think an update to V4 (at least 4.6.0) should solve the problem.

Should I prepare a PR?

Many greetings

Pascal

[helenosheaa]

Hi @ZPascal,

Thanks for looking into that and showing what you found!

If you could prepare a PR that would be great. It seems like V4.0.6 will solve the problem so upgrading will make sense.

Feel free to @ me on the PR when it comes in so I can review.

[ZPascal]

Hi @helenosheaa ,

I have finished the PR so far. I will now run the integration tests and fix them if necessary.

FYI: However, I noticed another error in my upper post. I had written about version 4.0.6, but linked v4.6.0. The current error is fixed since version 4.6.0 of pgx.