Обсуждение: Setting up SSL for postgre

I have started all over again to see if I can resolve this issue. Unfortunately not. I am still pulling my hair out.

 

I am still following the instructions howtoforge.

 

I am working with pg10. I am trying to use SSL on a small network server (running on Windows 7. I am trying to connect from a client machine running on Windows  10.

 

Commands for certificate creation

openssl genrsa -des3 -out c:\certs\server.key 1024

 

openssl rsa -in c:\certs\server.key -out c:\certs\server.key

 

openssl req -new -key c:\certs\server.key -days 3650 -out c:\certs\server.crt -x509 -subj '/C=UK/ST=Wales/L=Cardiff/O=MWC/CN=192.168.0.12/emailAddress=info@mwconsult.co.uk'

 

{192.168.0.12 is the ipaddress of the server machine on the local network.

 

 

cp server.crt root.crt {manually copied as on Windows}

openssl genrsa -des3 -out c:\certs\postgresql.key 1024

 

openssl rsa -in c:\certs\postgresql.key -out c:\certs\postgresql.key

 

openssl req -new -key c:\certs\postgresql.key -out c:\certs\postgresql.csr -subj '/C=UK/ST=Wales/L=Cardiff/O=MWC/CN=postgres'

 

openssl x509 -days 3650 -req -in c:\certs\postgresql.csr -CA c:\certs\root.crt -CAkey c:\certs\server.key -out c:\certs\postgresql.crt -CAcreateserial

 

I then copy the server.key, server.crt and root.crt file to the postgres data folder on the server machine.

 

Postgresql.conf

listen_addresses = '*'

ssl = on

#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers

#ssl_prefer_server_ciphers = on

#ssl_ecdh_curve = 'prime256v1'

#ssl_dh_params_file = ''

ssl_cert_file = 'server.crt'

ssl_key_file = 'server.key'

ssl_ca_file = 'root.crt'

#ssl_crl_file = ''

#password_encryption = md5                    # md5 or scram-sha-256

#db_user_namespace = off

#row_security = on

 

pg_hba.conf

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

 

# IPv4 local & remote connections:

host    all             all             127.0.0.1/32            trust

hostssl all         postgres    0.0.0.0/0             cert

 

# IPv6 local connections:

host    all             all             ::1/128                 trust

 

I restart the service.

 

Client Machine

I am trying to connect from an application written in Delphi and using FireDAC.

The FireDAC params are set as follows

        Params.Values['UseSSL'] := 'True';

        Params.values['SSL_ca'] := sslCertsPath + 'root.crt';

        Params.values['SSL_cert'] := sslCertsPath + 'postgresql.crt.';

        Params.values['SSL_key'] := sslCertsPath + 'postgresql.key';

 

The client certs are copied to “sslCertsPath”

 

When I connect I get the “connection requires a valid client certificate” error.

 

Is there something else I need to do? Do I have to added any of the self-certified certificates to the Windows Trusted certificate store and, if so, which ones on which machines?

 

Hopefully, somebody can work out why this connection fails, if not, I can see no alternative to booking myself in t Dignitas!

 

Many thanks.

 

Mark

__

Hallo Mark,

as i quickly read the error message in your question,

these we're my first suggestions.

either

* did you intent cert aut for the postgres user?

* u use a selfsigned certificate, hence software that checks for the validity will fail or ask for this

** using for example the free, but official letsencrypt certificates this should be solved

hth,

Wim

Hallo Mark,

you should not include the password option,

so possibly you are connecting with the password in pgadmin (with another user) .. instead of the cert meth;

another option: check the posgresql log on the windows machine

sslmode=require: firedac might require a valid (official or installed) certificate?

maybe check:

https://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-CERT

https://www.postgresql.org/docs/10/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SECURITY

https://www.postgresql.org/docs/10/static/ssl-tcp.html

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

hth,

Wim

Mark Williams <markwillimas@gmail.com> writes:

>  
>
>  
>
> __
>
>  
>
> From: Mark Williams <markwillimas@gmail.com> 
> Sent: 25 August 2018 18:14
> To: 'Wim Bertels' <wim.bertels@ucll.be>
> Subject: RE: Setting up SSL for postgre
>
>  
>
> Hi Wim,
>
>  
>
> I don't understand. If I don't include the password option, the connection
> will be refused because I have not included it.
>
>  
>
> I am connecting via PGAdmin with the same user ie postgres.
>

I suspect Wim was referring to private certificate authentication rather
than connections over SSL - use the same basic technologies, but for
different goals.

While it may or may not be useful, I believe that recent versions of
Debian actually come with SSL connections enabled by default (using self
signed cert). Might provide the example you need?

Tim

-- 
Tim Cross

Hi Tim,

Thanks for the reply.

Unfortunately, I don't know what private certificate authorisation is. I
assume this is different to SSL and is not the same as a self signed cert. I
have created my certificate with OpenSSL so I assume I am not in the arena
of private certificate authorisation.

Thanks for the tip re Debian, but sadly client and server are all Windows
machines.

I think I will put a plea out there to anyone who uses FireDAC and has
managed to get SSL working with Postgre. Absent anything useful there, I
will give up on Postgre.

All the best.

Mark

__

-----Original Message-----
From: Tim Cross <theophilusx@gmail.com> 
Sent: 27 August 2018 23:05
To: Mark Williams <markwillimas@gmail.com>
Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: FW: Setting up SSL for postgre


Mark Williams <markwillimas@gmail.com> writes:

>  
>
>  
>
> __
>
>  
>
> From: Mark Williams <markwillimas@gmail.com>
> Sent: 25 August 2018 18:14
> To: 'Wim Bertels' <wim.bertels@ucll.be>
> Subject: RE: Setting up SSL for postgre
>
>  
>
> Hi Wim,
>
>  
>
> I don't understand. If I don't include the password option, the 
> connection will be refused because I have not included it.
>
>  
>
> I am connecting via PGAdmin with the same user ie postgres.
>

I suspect Wim was referring to private certificate authentication rather
than connections over SSL - use the same basic technologies, but for
different goals.

While it may or may not be useful, I believe that recent versions of Debian
actually come with SSL connections enabled by default (using self signed
cert). Might provide the example you need?

Tim

--
Tim Cross

Before you do give up on postgres, take a look at this. I'm not sure if you have tried this:

https://stackoverflow.com/questions/16496199/firedac-sql-server-connection-untrusted-certificate

-T
________________________________________
From: Mark Williams <markwillimas@gmail.com>
Sent: Tuesday, August 28, 2018 2:52 PM
To: 'Tim Cross'
Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr; 'Wim Bertels'
Subject: RE: FW: Setting up SSL for postgre

Hi Tim,

Thanks for the reply.

Unfortunately, I don't know what private certificate authorisation is. I
assume this is different to SSL and is not the same as a self signed cert. I
have created my certificate with OpenSSL so I assume I am not in the arena
of private certificate authorisation.

Thanks for the tip re Debian, but sadly client and server are all Windows
machines.

I think I will put a plea out there to anyone who uses FireDAC and has
managed to get SSL working with Postgre. Absent anything useful there, I
will give up on Postgre.

All the best.

Mark

Hi Tino,

Thanks for the link. Unfortunately, it relates to a MS SQL server, which has
the TrustServerCertificate connection parameter. That doesn't exist for
Postgre.

But thanks anyway. Look like I will have to give up on Postgre after all!

Mark

__

-----Original Message-----
From: Dai, Tino <tdai@loc.gov> 
Sent: 28 August 2018 20:55
To: Mark Williams <markwillimas@gmail.com>; 'Tim Cross'
<theophilusx@gmail.com>
Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr; 'Wim Bertels'
<wim.bertels@ucll.be>
Subject: Re: FW: Setting up SSL for postgre

Before you do give up on postgres, take a look at this. I'm not sure if you
have tried this:

https://stackoverflow.com/questions/16496199/firedac-sql-server-connection-u
ntrusted-certificate

-T
________________________________________
From: Mark Williams <markwillimas@gmail.com>
Sent: Tuesday, August 28, 2018 2:52 PM
To: 'Tim Cross'
Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr; 'Wim Bertels'
Subject: RE: FW: Setting up SSL for postgre

Hi Tim,

Thanks for the reply.

Unfortunately, I don't know what private certificate authorisation is. I
assume this is different to SSL and is not the same as a self signed cert. I
have created my certificate with OpenSSL so I assume I am not in the arena
of private certificate authorisation.

Thanks for the tip re Debian, but sadly client and server are all Windows
machines.

I think I will put a plea out there to anyone who uses FireDAC and has
managed to get SSL working with Postgre. Absent anything useful there, I
will give up on Postgre.

All the best.

Mark

=

Hallo Mark,

in your pg_hha.conf you have used

      cert

as authentication,
which is authorization using a certificate
(not a password)
(as mailed before with documentation links)

did you test pgadmin and firedac from the same client machine?

hth,
Wim
________________________________________
Van: Mark Williams <markwillimas@gmail.com>
Verzonden: dinsdag 28 augustus 2018 20:52
Aan: 'Tim Cross'
CC: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr; Wim Bertels
Onderwerp: RE: FW: Setting up SSL for postgre

Hi Tim,

Thanks for the reply.

Unfortunately, I don't know what private certificate authorisation is. I
assume this is different to SSL and is not the same as a self signed cert. I
have created my certificate with OpenSSL so I assume I am not in the arena
of private certificate authorisation.

Thanks for the tip re Debian, but sadly client and server are all Windows
machines.

I think I will put a plea out there to anyone who uses FireDAC and has
managed to get SSL working with Postgre. Absent anything useful there, I
will give up on Postgre.

All the best.

Mark

__

-----Original Message-----
From: Tim Cross <theophilusx@gmail.com>
Sent: 27 August 2018 23:05
To: Mark Williams <markwillimas@gmail.com>
Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: FW: Setting up SSL for postgre


Mark Williams <markwillimas@gmail.com> writes:

>
>
>
>
> __
>
>
>
> From: Mark Williams <markwillimas@gmail.com>
> Sent: 25 August 2018 18:14
> To: 'Wim Bertels' <wim.bertels@ucll.be>
> Subject: RE: Setting up SSL for postgre
>
>
>
> Hi Wim,
>
>
>
> I don't understand. If I don't include the password option, the
> connection will be refused because I have not included it.
>
>
>
> I am connecting via PGAdmin with the same user ie postgres.
>

I suspect Wim was referring to private certificate authentication rather
than connections over SSL - use the same basic technologies, but for
different goals.

While it may or may not be useful, I believe that recent versions of Debian
actually come with SSL connections enabled by default (using self signed
cert). Might provide the example you need?

Tim

--
Tim Cross

Hi,

I have finally discovered the problem and thanks to everyone for their help.

I have changed the 
Pg_hha.conf file to md5 clientcert=1 instead of just cert.

It still didn't work and I read a suggestion on a link provided by Wim which
suggested change sslmode to verify-ca. 

This threw up a new error, namely that it couldn't find the root certificate
at the location I had specified. The reason for this was that although my
file path was being ready by FireDAC correctly, when it was passed through
to Postgre, it was removing the path delimiters. The answer was to escape
the delimiters with a backslash eg "c:\\pathtomycerts\\postgre.sql.cert"

I'm assuming you guys are all on Linux and don't have this problem.

For the benefit of future Windows users, who may be tempted to give up on
Postgre due to the agony of trying to connect with SSL it would be well
worth a little addition to the manual at
https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONN
STRING to let Windows users know they need to escape their path delimiters.

I will let Embarcadero know of this issue for FireDAC users.

Aside from that little niggle, it's great to know that Postgre users are so
willing to help. Many thanks again.


__

-----Original Message-----
From: Wim Bertels <wim.bertels@ucll.be> 
Sent: 30 August 2018 08:56
To: Mark Williams <markwillimas@gmail.com>; 'Tim Cross'
<theophilusx@gmail.com>
Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: FW: Setting up SSL for postgre

Hallo Mark,

in your pg_hha.conf you have used

      cert

as authentication,
which is authorization using a certificate (not a password) (as mailed
before with documentation links)

did you test pgadmin and firedac from the same client machine?

hth,
Wim        
________________________________________
Van: Mark Williams <markwillimas@gmail.com>
Verzonden: dinsdag 28 augustus 2018 20:52
Aan: 'Tim Cross'
CC: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr; Wim Bertels
Onderwerp: RE: FW: Setting up SSL for postgre

Hi Tim,

Thanks for the reply.

Unfortunately, I don't know what private certificate authorisation is. I
assume this is different to SSL and is not the same as a self signed cert. I
have created my certificate with OpenSSL so I assume I am not in the arena
of private certificate authorisation.

Thanks for the tip re Debian, but sadly client and server are all Windows
machines.

I think I will put a plea out there to anyone who uses FireDAC and has
managed to get SSL working with Postgre. Absent anything useful there, I
will give up on Postgre.

All the best.

Mark

__

-----Original Message-----
From: Tim Cross <theophilusx@gmail.com>
Sent: 27 August 2018 23:05
To: Mark Williams <markwillimas@gmail.com>
Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: FW: Setting up SSL for postgre


Mark Williams <markwillimas@gmail.com> writes:

>
>
>
>
> __
>
>
>
> From: Mark Williams <markwillimas@gmail.com>
> Sent: 25 August 2018 18:14
> To: 'Wim Bertels' <wim.bertels@ucll.be>
> Subject: RE: Setting up SSL for postgre
>
>
>
> Hi Wim,
>
>
>
> I don't understand. If I don't include the password option, the 
> connection will be refused because I have not included it.
>
>
>
> I am connecting via PGAdmin with the same user ie postgres.
>

I suspect Wim was referring to private certificate authentication rather
than connections over SSL - use the same basic technologies, but for
different goals.

While it may or may not be useful, I believe that recent versions of Debian
actually come with SSL connections enabled by default (using self signed
cert). Might provide the example you need?

Tim

--
Tim Cross

=