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

Highlighted
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 10
Highlighted
Adventurer

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

Highlighted
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.

Highlighted
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?

Highlighted
Silver Adventurer

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

Highlighted
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.

Highlighted
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)
Highlighted
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.

 

Highlighted
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)
Highlighted
Level 3 Adventurer

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

Hi Akash,

 

Please find below in-line comments regarding your concern :

 

1 :- Why is that we need not configure anything from client end when we are using OpenSSL?
Ans :
PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security.
This requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time.

 

2:- Without the CA's certificate how's client verifying the Server's certificate?
Ans :
If the server requests a trusted client certificate, libpq/client will send the certificate stored in file ~/.postgresql/postgresql.crt in the user's home directory.
The certificate must be signed by one of the certificate authorities (CA) trusted by the server.
A matching private key file ~/.postgresql/postgresql.key must also be present.
The private key file must not allow any access to world or group; achieve this by the command chmod 0600 ~/.postgresql/postgresql.key.
On Microsoft Windows these files are named %APPDATA%\postgresql\postgresql.crt and %APPDATA%\postgresql\postgresql.key, and there is no special permissions check since the directory is presumed secure.
The location of the certificate and key files can be overridden by the connection parameters sslcert and sslkey or the environment variables PGSSLCERT and PGSSLKEY.

 

3:- Shouldn't we keep the CA's certificate in the client machine?
Ans :
Presuming the client is on Windows :
To allow server certificate verification, the certificate(s) of one or more trusted CAs must be placed in the file %APPDATA%\postgresql\root.crt.
Certificate Revocation List (CRL) entries are also checked if the file %APPDATA%\postgresql\root.crl exists

File : %APPDATA%\postgresql\postgresql.crt
Contents : client certificate
Effect : requested by server

File : %APPDATA%\postgresql\postgresql.key
Contents : client private key
Effect : proves client certificate sent by owner; does not indicate certificate owner is trustworthy

4:- If we are using Trusted Root CA signed certificate how's the procedure different?
Ans :
By default, PostgreSQL will not perform any verification of the server certificate.
The client must be able to verify the server's identity via a chain of trust.
A chain of trust is established by placing a root (self-signed) certificate authority (CA) certificate on one computer and a leaf certificate signed by the root certificate on another computer. It is also possible to use an "intermediate" certificate which is signed by the root certificate and signs leaf certificates.

 

Once a chain of trust has been established, there are two ways for the client to validate the leaf certificate sent by the server.
1. If the parameter sslmode is set to verify-ca, libpq will verify that the server is trustworthy by checking the certificate chain up to the root certificate stored on the client.
2. If sslmode is set to verify-full, libpq/client will also verify that the server host name matches the name stored in the server certificate.

The SSL connection will fail if the server certificate cannot be verified. verify-full is recommended in most security-sensitive environments.

 

You may refer below URLs for further information
1. https://www.postgresql.org/docs/9.6/static/libpq-ssl.html
2. https://www.postgresql.org/docs/9.6/static/ssl-tcp.html

 

Hope this helps.!