0

Please help with the following problem ...

OS - Windows. I want to configure SSL on Postgresql 12. Then my Java application will add entries to the database, delete, etc.

I created certificates: CA, server, client. CA and server are located in the directory C:\Program Files\PostgreSQL\12\data The client is located in C:\Users\User\AppData\postgresql Then I added CA certificate to trusted in Windows.

Configs:

pg_hba:

hostnossl     all          all            0.0.0.0/0                reject
hostssl       all          all           0.0.0.0/0            cert clientcert=1

postgresql.conf:

ssl = on
ssl_ca_file = 'root.crt'

I can connect server throw pgAdmin with my certificates, but there are some errors in cmd (and java) Thant's what I tried to do in cmd

 psql.exe -U postgres -h 127.0.0.1

Result: SSL: certificate verify failed FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "postgres", database "prod", SSL off

Logs: tlsv1 alert unknown ca FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "postgres", database "prod", SSL off

Please, tell me what's can be wrong and how can I fix it...

Also I did not find information, how to transfer my certificates to the database from Java application. Maybe, anybody can help me with that problem))

Thanks!

Hasuni
  • 21
  • 3

2 Answers2

0

use this in your connection string and it will work:

ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
Raj Verma
  • 1,050
  • 1
  • 7
  • 19
0

Then I added CA certificate to trusted in Windows.

psql does not integrate with the Windows certificate manager. The CA to be used by the client needs to go in %APPDATA%\postgresql\root.crt, or if elsewhere its location must be specified by a connection parameter (sslrootcert) or environment variable (PGSSLROOTCERT). These must be files, I don't think there is way to say "go get it from the cert manager".

I think jdbc is the same way, it does not integrate with the Windows cert manager either, for how to specify the locations see https://jdbc.postgresql.org/documentation/head/ssl-client.html

SSL: certificate verify failed FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "postgres", database "prod", SSL off

Maybe you already know this, but first it tried using SSL and failed to verify the cert (it does not make it clear which side failed, the client cert or the server cert), then it tried to fall back to no SSL and got rejected by the pg_hba. If you had set the client's sslmode to "require" or higher, it would not have tried to fall back to the no SSL, it would have stopped at the first error.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Thanks! - Yes, my certificates are in Appdata (I wrote it in the begining), but something goes wrong... Thank you, I'll try to do something with certificates on java-client side as on documentation you have sent. – Hasuni Feb 04 '20 at 18:37
  • About the last comment... Actually I didn't configurating server check for cmd, so it should work, I think... It write something about CA, that's why I have a theory that it just doesn't trust my self-signed certs. – Hasuni Feb 04 '20 at 18:41
  • After making convertation from doc my cmd-part works. java-client ssl has not worked yet( – Hasuni Feb 04 '20 at 20:35
  • I understood that java just doesn't see matching client certificate... – Hasuni Feb 05 '20 at 06:26