cancel
Showing results for 
Search instead for 
Did you mean: 

How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

Level 2 Adventurer

How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

Hello,

I'm trying to establish SSL/TLS secure connection for my PostgreSQL v9.4.17 DB running on Windows 10 Enterprise 2016 LTSB. I have openssl installed in my machine to create certificates.

Unfortunately, I was not able to find any step by step guide for the same for Windows OS.

I would be grateful if somebody could let me know step by step procedure to setup ssl for PostgreSQL running on Windows OS.

 

10 REPLIES
EDB Team Member

Re: How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

Hi Akash,

 

By presuming that certificates are created at your end using openssl.Please find the below Server side steps to enable SSL on PostgreSQL 9.4.17.

 

Step1.Edit the below parameters in postgresql.conf file

ssl = on

 

Step2.you need to make sure the below files are present inside Postgres data directory with proper permissions and privilege.

server.crt (server certificate)
server.key (private key)

 

Step3.Start the database services.

 

Step4:To confirm whether SSL is configured properly or not run the below command:

psql.exe -p <port> -h localhost

If you get an output like below then the SSL is configured at your erver side.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

For more information on this please refer below link.

https://www.postgresql.org/docs/9.4/static/libpq-ssl.html

Kindly let us know how it goes at your end

Level 2 Adventurer

Re: How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

 Hi venkatagarine,

I'll give it a try and update you ASAP.

Thanks for your help.

Level 2 Adventurer

Re: How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

Hello,

If it's not too much can you please explain what needs to be done on the client machine?

I understand we need to add the certificate to the certificate store, but in particular which store? 

Is there anything other procedure that needs to be done on top of this?

EDB Team Member

Re: How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

Hello Akshay,

 

Thannk you for your response. As certificates are created by OpenSSL. We don't need to configure anything from client end.

 

In case you wish to test connectivity using SSL then you may verify using below command.

psql "sslmode=require host=localhost port=<port> user=<user>"

 

In addition to above, You may want to add/replace entry in pg_hba.conf file for "hostssl".

 

Thanks & Regds,

Ninad Shah

Level 2 Adventurer

Re: How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

Hello Venkat,

I was able to do the steps that you have listed and was able verify that SSL was configured for PostgreSQL.

But, I created a C# application and tried opening the connection using Npgsql connection connection string, I was getting exception. I have set SSL Mode=true and Trust Server Certificate=true in the connection string. But I get an exception stating:

key=value argument incorrect in ConnectionString
Parameter name: ssl mode

 

 

Can you please let me know if I'm making some mistake here?

Tags (2)
EDB Team Member

Re: How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

Hi Akash,

 

The exception you have received is due to invalid value has been provided for the parameter "SSL Mode".

The valid values for the "SSL Mode" parmeter is "Require,Prefer, Disable"

 

Also, find the below link for the reference:

http://www.npgsql.org/doc/connection-string-parameters.html

 

Can you please try it once with the above options and let us know if you face the issue again.

Level 2 Adventurer

Re: How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

Hi Saurabh,

You're right, I made a typo or I got confused with the values for SSL Mode and Trust Server Certificate.

But I was able to open a connection to my DB, using the parameter SSLMode=Require.

It was mentioned in the npgsql documentation that if we are using self signed certificate, we need to add Trust Server Certificate=true in the connection string.

But, using this I got the similar error stating wrong value for key. So, I removed the parameter from the connection string and I was able to open connection. Quite a contradiction, as I have used self signed certificate here.

 

Also, from my client application also, I was able to connect to my DB server by adding SSLMode=Require in the connection string.

But, I'm not able to understand, how client was able to create the connection as I have not added the server certificate in the client machine's certificate store. Is client machine accessing the data directory of Postgres in the server machine to get the certificate and verifies it before opening the connection?

 

Also, I'm pretty sure current setup does the Server authentication. What needs to be done for the client authentication?

Tags (2)
EDB Team Member

Re: How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

Hi akashvijayaraj,

 

If you are passing Trust Server Certificate parameter as true that means you are requesting to ignore the check whether server's certificate is valid.  Make sure you have done proper settings to enable ssl.

 

 

 

For you second request which was regarding client autehtication , you have to enable specific parameters in postgresql.conf and modify the pg_hba.conf file to enable authentication with client certificates.

 

Below is the link which will help you understand this :

 
Let us know in case you have any other queries.

 

Level 2 Adventurer

Re: How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl

Hi nshah,

I have a bunch of questions here. Hope you'll help me out here.

 

Why is that we need not configure anything from client end when we are using OpenSSL?

Without the CA's certificate how's client verifying the Server's certificate? 

Shouldn't we keep the CA's certificate in the client machine?

If we are using Trusted Root CA signed certificate how's the procedure different?

 

Thanks in advance

 

Regards,

Akash

Tags (1)