pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSetting up SSL for postgre
22+ messages / 7 participants
[nested] [flat]
* Setting up SSL for postgre
@ 2018-08-13 16:55 Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
0 siblings, 1 reply; 22+ messages in thread
From: Mark Williams @ 2018-08-13 16:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
I am new to Postgre. Migrating from MySQL.
I am trying to connect via SSL to a PostgreSQL using FireDac in Delphi. I
have followed the instructions at the following site:
<https://www.howtoforge.com/postgresql-ssl-certificates;
https://www.howtoforge.com/postgresql-ssl-certificates to create my
self-certified certificates and configure the config files.
I have coped the specified files to the client machine and installed the
root.crt certificate.
Via FireDAC's connection params I have specified the following:
Params.values[SSL_ca']:=sslCertsPath+'root.crt';
Params.values['SSL_cert']:=sslCertsPath+'postgresql.crt.';
Params.values['SSL_key']:=sslCertsPath+'postgresql.key';
I am getting a connection error re invalid client certificate.
I have used OpenSSL to verify against the root.crt and postgresql.crt and
this confirms the certificate is ok.
Mark
__
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
@ 2018-08-13 19:00 ` Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Bear Giles @ 2018-08-13 19:00 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
What's in pg_hba.conf?
What's in postgresql.conf? Did you remember to change the ssl cert file
and key? For authentication I think you might need to set the SSL ca file
as well but I'm not sure.
On Mon, Aug 13, 2018 at 10:55 AM, Mark Williams <markwillimas@gmail.com>
wrote:
> I am new to Postgre. Migrating from MySQL.
>
>
>
> I am trying to connect via SSL to a PostgreSQL using FireDac in Delphi. I
> have followed the instructions at the following site:
> https://www.howtoforge.com/postgresql-ssl-certificates to create my
> self-certified certificates and configure the config files.
>
>
>
> I have coped the specified files to the client machine and installed the
> root.crt certificate.
>
> Via FireDAC's connection params I have specified the following:
>
> Params.values[SSL_ca']:=sslCertsPath+'root.crt';
>
> Params.values['SSL_cert']:=sslCertsPath+'postgresql.crt.';
>
> Params.values['SSL_key']:=sslCertsPath+'postgresql.key';
>
>
>
> I am getting a connection error re invalid client certificate.
>
> I have used OpenSSL to verify against the root.crt and postgresql.crt and
> this confirms the certificate is ok.
>
> Mark
>
>
>
> __
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
@ 2018-08-13 19:57 ` Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
0 siblings, 1 reply; 22+ messages in thread
From: Mark Williams @ 2018-08-13 19:57 UTC (permalink / raw)
To: 'Bear Giles' <bgiles@coyotesong.com>; +Cc: pgsql-admin@lists.postgresql.org
What do you mean by “change the ssl cert file and key”
Pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local & remote connections:
host all all 127.0.0.1/32 trust
hostssl all www-data 0.0.0.0/0 md5 clientcert=1
hostssl all postgres 0.0.0.0/0 md5 clientcert=1
# IPv6 local connections:
host all all ::1/128 trust
Postgresql.conf
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 = ''
Thanks
__
From: Bear Giles <bgiles@coyotesong.com>
Sent: 13 August 2018 20:01
To: Mark Williams <markwillimas@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Setting up SSL for postgre
What's in pg_hba.conf?
What's in postgresql.conf? Did you remember to change the ssl cert file and key? For authentication I think you might need to set the SSL ca file as well but I'm not sure.
On Mon, Aug 13, 2018 at 10:55 AM, Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> > wrote:
I am new to Postgre. Migrating from MySQL.
I am trying to connect via SSL to a PostgreSQL using FireDac in Delphi. I have followed the instructions at the following site: <https://www.howtoforge.com/postgresql-ssl-certificates; https://www.howtoforge.com/postgresql-ssl-certificates to create my self-certified certificates and configure the config files.
I have coped the specified files to the client machine and installed the root.crt certificate.
Via FireDAC's connection params I have specified the following:
Params.values[SSL_ca']:=sslCertsPath+'root.crt';
Params.values['SSL_cert']:=sslCertsPath+'postgresql.crt.';
Params.values['SSL_key']:=sslCertsPath+'postgresql.key';
I am getting a connection error re invalid client certificate.
I have used OpenSSL to verify against the root.crt and postgresql.crt and this confirms the certificate is ok.
Mark
__
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
@ 2018-08-13 20:57 ` Bear Giles <bgiles@coyotesong.com>
2018-08-14 11:56 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
0 siblings, 2 replies; 22+ messages in thread
From: Bear Giles @ 2018-08-13 20:57 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
It never hurts to double check.
What is the CN on the client cert? Is there a corresponding user in the
database? You might need to add an entry in pg_ident.conf, e.g.,
cert bob@example.com bob
if you want user with the cert with a CN of 'bob@example.com' to access the
system as the postgresql user 'bob'. You won't need this if your CN is the
same as the database user. I've only set up Kerberos authentication, not
X.509 cert authentication, so I don't know the details about what this file
needs specifically.
Bear
On Mon, Aug 13, 2018 at 1:57 PM, Mark Williams <markwillimas@gmail.com>
wrote:
> What do you mean by “change the ssl cert file and key”
>
>
>
> *Pg_hba.conf*
>
>
>
> # TYPE DATABASE USER CIDR-ADDRESS METHOD
>
>
>
> # IPv4 local & remote connections:
>
> host all all 127.0.0.1/32 trust
>
> hostssl all www-data 0.0.0.0/0 md5 clientcert=1
>
> hostssl all postgres 0.0.0.0/0 md5 clientcert=1
>
>
>
> # IPv6 local connections:
>
> host all all ::1/128 trust
>
>
>
> *Postgresql.conf*
>
> 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 = ''
>
>
>
> Thanks
>
> __
>
>
>
> *From:* Bear Giles <bgiles@coyotesong.com>
> *Sent:* 13 August 2018 20:01
> *To:* Mark Williams <markwillimas@gmail.com>
> *Cc:* pgsql-admin@lists.postgresql.org
> *Subject:* Re: Setting up SSL for postgre
>
>
>
> What's in pg_hba.conf?
>
>
>
> What's in postgresql.conf? Did you remember to change the ssl cert file
> and key? For authentication I think you might need to set the SSL ca file
> as well but I'm not sure.
>
>
>
>
>
> On Mon, Aug 13, 2018 at 10:55 AM, Mark Williams <markwillimas@gmail.com>
> wrote:
>
> I am new to Postgre. Migrating from MySQL.
>
>
>
> I am trying to connect via SSL to a PostgreSQL using FireDac in Delphi. I
> have followed the instructions at the following site:
> https://www.howtoforge.com/postgresql-ssl-certificates to create my
> self-certified certificates and configure the config files.
>
>
>
> I have coped the specified files to the client machine and installed the
> root.crt certificate.
>
> Via FireDAC's connection params I have specified the following:
>
> Params.values[SSL_ca']:=sslCertsPath+'root.crt';
>
> Params.values['SSL_cert']:=sslCertsPath+'postgresql.crt.';
>
> Params.values['SSL_key']:=sslCertsPath+'postgresql.key';
>
>
>
> I am getting a connection error re invalid client certificate.
>
> I have used OpenSSL to verify against the root.crt and postgresql.crt and
> this confirms the certificate is ok.
>
> Mark
>
>
>
> __
>
>
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
@ 2018-08-14 11:56 ` Mark Williams <markwillimas@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: Mark Williams @ 2018-08-14 11:56 UTC (permalink / raw)
To: 'Bear Giles' <bgiles@coyotesong.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi,
The CN is “www-data” as per the howtoforge documentation and added to the pg_hba.conf file. Do I have to add an actual user called “www-data” to the database?
If so, do I have to have a different root and client certificate for every ssl user?
Re pg_ident. I have added “cert www-data” Postgres” to pg_ident. Doesn’t help. Still getting the “connection requires a valid client certificate” error.
Thanks
Mark
__
From: Bear Giles <bgiles@coyotesong.com>
Sent: 13 August 2018 21:57
To: Mark Williams <markwillimas@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Setting up SSL for postgre
It never hurts to double check.
What is the CN on the client cert? Is there a corresponding user in the database? You might need to add an entry in pg_ident.conf, e.g.,
cert <mailto:bob@example.com> bob@example.com bob
if you want user with the cert with a CN of ' <mailto:bob@example.com> bob@example.com' to access the system as the postgresql user 'bob'. You won't need this if your CN is the same as the database user. I've only set up Kerberos authentication, not X.509 cert authentication, so I don't know the details about what this file needs specifically.
Bear
On Mon, Aug 13, 2018 at 1:57 PM, Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> > wrote:
What do you mean by “change the ssl cert file and key”
Pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local & remote connections:
host all all 127.0.0.1/32 <http://127.0.0.1/32; trust
hostssl all www-data 0.0.0.0/0 <http://0.0.0.0/0; md5 clientcert=1
hostssl all postgres 0.0.0.0/0 <http://0.0.0.0/0; md5 clientcert=1
# IPv6 local connections:
host all all ::1/128 trust
Postgresql.conf
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 = ''
Thanks
__
From: Bear Giles < <mailto:bgiles@coyotesong.com> bgiles@coyotesong.com>
Sent: 13 August 2018 20:01
To: Mark Williams < <mailto:markwillimas@gmail.com> markwillimas@gmail.com>
Cc: <mailto:pgsql-admin@lists.postgresql.org> pgsql-admin@lists.postgresql.org
Subject: Re: Setting up SSL for postgre
What's in pg_hba.conf?
What's in postgresql.conf? Did you remember to change the ssl cert file and key? For authentication I think you might need to set the SSL ca file as well but I'm not sure.
On Mon, Aug 13, 2018 at 10:55 AM, Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> > wrote:
I am new to Postgre. Migrating from MySQL.
I am trying to connect via SSL to a PostgreSQL using FireDac in Delphi. I have followed the instructions at the following site: <https://www.howtoforge.com/postgresql-ssl-certificates; https://www.howtoforge.com/postgresql-ssl-certificates to create my self-certified certificates and configure the config files.
I have coped the specified files to the client machine and installed the root.crt certificate.
Via FireDAC's connection params I have specified the following:
Params.values[SSL_ca']:=sslCertsPath+'root.crt';
Params.values['SSL_cert']:=sslCertsPath+'postgresql.crt.';
Params.values['SSL_key']:=sslCertsPath+'postgresql.key';
I am getting a connection error re invalid client certificate.
I have used OpenSSL to verify against the root.crt and postgresql.crt and this confirms the certificate is ok.
Mark
__
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
@ 2018-08-20 12:44 ` Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
1 sibling, 1 reply; 22+ messages in thread
From: Mark Williams @ 2018-08-20 12:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
__
From: Mark Williams <markwillimas@gmail.com>
Sent: 14 August 2018 12:56
To: 'Bear Giles' <bgiles@coyotesong.com>
Cc: 'pgsql-admin@lists.postgresql.org' <pgsql-admin@lists.postgresql.org>
Subject: RE: Setting up SSL for postgre
Hi,
The CN is “www-data” as per the howtoforge documentation and added to the pg_hba.conf file. Do I have to add an actual user called “www-data” to the database?
If so, do I have to have a different root and client certificate for every ssl user?
Re pg_ident. I have added “cert www-data” Postgres” to pg_ident. Doesn’t help. Still getting the “connection requires a valid client certificate” error.
Thanks
Mark
__
From: Bear Giles <bgiles@coyotesong.com <mailto:bgiles@coyotesong.com> >
Sent: 13 August 2018 21:57
To: Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> >
Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: Setting up SSL for postgre
It never hurts to double check.
What is the CN on the client cert? Is there a corresponding user in the database? You might need to add an entry in pg_ident.conf, e.g.,
cert <mailto:bob@example.com> bob@example.com bob
if you want user with the cert with a CN of ' <mailto:bob@example.com> bob@example.com' to access the system as the postgresql user 'bob'. You won't need this if your CN is the same as the database user. I've only set up Kerberos authentication, not X.509 cert authentication, so I don't know the details about what this file needs specifically.
Bear
On Mon, Aug 13, 2018 at 1:57 PM, Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> > wrote:
What do you mean by “change the ssl cert file and key”
Pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local & remote connections:
host all all 127.0.0.1/32 <http://127.0.0.1/32; trust
hostssl all www-data 0.0.0.0/0 <http://0.0.0.0/0; md5 clientcert=1
hostssl all postgres 0.0.0.0/0 <http://0.0.0.0/0; md5 clientcert=1
# IPv6 local connections:
host all all ::1/128 trust
Postgresql.conf
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 = ''
Thanks
__
From: Bear Giles < <mailto:bgiles@coyotesong.com> bgiles@coyotesong.com>
Sent: 13 August 2018 20:01
To: Mark Williams < <mailto:markwillimas@gmail.com> markwillimas@gmail.com>
Cc: <mailto:pgsql-admin@lists.postgresql.org> pgsql-admin@lists.postgresql.org
Subject: Re: Setting up SSL for postgre
What's in pg_hba.conf?
What's in postgresql.conf? Did you remember to change the ssl cert file and key? For authentication I think you might need to set the SSL ca file as well but I'm not sure.
On Mon, Aug 13, 2018 at 10:55 AM, Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> > wrote:
I am new to Postgre. Migrating from MySQL.
I am trying to connect via SSL to a PostgreSQL using FireDac in Delphi. I have followed the instructions at the following site: <https://www.howtoforge.com/postgresql-ssl-certificates; https://www.howtoforge.com/postgresql-ssl-certificates to create my self-certified certificates and configure the config files.
I have coped the specified files to the client machine and installed the root.crt certificate.
Via FireDAC's connection params I have specified the following:
Params.values[SSL_ca']:=sslCertsPath+'root.crt';
Params.values['SSL_cert']:=sslCertsPath+'postgresql.crt.';
Params.values['SSL_key']:=sslCertsPath+'postgresql.key';
I am getting a connection error re invalid client certificate.
I have used OpenSSL to verify against the root.crt and postgresql.crt and this confirms the certificate is ok.
Mark
__
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
@ 2018-08-20 13:02 ` Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 13:36 ` Re: Setting up SSL for postgre Evan Bauer <evanbauer@mac.com>
0 siblings, 2 replies; 22+ messages in thread
From: Stéphane Dunand @ 2018-08-20 13:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Le 20/08/2018 à 14:44, Mark Williams a écrit :
>
> 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
>
> __
>
>
This page helped me :
https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentication/
Best regards,
Stéphane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
@ 2018-08-20 13:29 ` Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Wim Bertels @ 2018-08-20 13:29 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; s.dunand@sirap.fr <s.dunand@sirap.fr>
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
cert method for auth, hence this behaviour (client cert..)
extra tip:
https://duckduckgo.com/?q=letsencrypt+postgresql
for official server side certificates
mvg,
Bertels Wim
Mark
__
This page helped me :
https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentication/
Best regards,
Stéphane
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
@ 2018-08-20 14:51 ` Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
0 siblings, 1 reply; 22+ messages in thread
From: Mark Williams @ 2018-08-20 14:51 UTC (permalink / raw)
To: 'Wim Bertels' <wim.bertels@ucll.be>; pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Hi,
Sorry I don’t understand what you are suggesting re the pg_hba file.
__
From: Wim Bertels <wim.bertels@ucll.be>
Sent: 20 August 2018 14:30
To: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: Setting up SSL for postgre
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
cert method for auth, hence this behaviour (client cert..)
extra tip:
https://duckduckgo.com/?q=letsencrypt+postgresql
for official server side certificates
mvg,
Bertels Wim
Mark
__
This page helped me :
https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentic
ation/
Best regards,
Stéphane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
@ 2018-08-22 08:19 ` Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Wim Bertels @ 2018-08-22 08:19 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; s.dunand@sirap.fr <s.dunand@sirap.fr>
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
________________________________
Van: Mark Williams <markwillimas@gmail.com>
Verzonden: maandag 20 augustus 2018 16:51
Aan: Wim Bertels; pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Onderwerp: RE: Setting up SSL for postgre
Hi,
Sorry I don't understand what you are suggesting re the pg_hba file.
__
From: Wim Bertels <wim.bertels@ucll.be>
Sent: 20 August 2018 14:30
To: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: Setting up SSL for postgre
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
cert method for auth, hence this behaviour (client cert..)
extra tip:
https://duckduckgo.com/?q=letsencrypt+postgresql
for official server side certificates
mvg,
Bertels Wim
Mark
__
This page helped me :
https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentication/
Best regards,
Stéphane
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
@ 2018-08-23 16:53 ` Mark Williams <markwillimas@gmail.com>
2018-08-24 09:59 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
0 siblings, 1 reply; 22+ messages in thread
From: Mark Williams @ 2018-08-23 16:53 UTC (permalink / raw)
To: 'Wim Bertels' <wim.bertels@ucll.be>; pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Hi Wim,
I did intend Cert aut (at least I think I did!).
Still cannot connect to postgre database from my client app using FireDAC. I
can connect fine from PGAdmin3 on the same machine using the same
certificates.
The call made by FireDAC to libPQ.Dll is the following:
PQconnectdb [ConnInfo=hostaddr=192.168.0.12 port=5432 dbname=rees
user=postgres password=*** connect_timeout=10 sslmode=require
sslrootcert=C:\ProgramData\MWC\Viewer\Certs\root.crt
sslcert=C:\ProgramData\MWC\Viewer\Certs\postgresql.crt
sslkey=C:\ProgramData\MWC\Viewer\Certs\postgresql.key password=1234,
Result=$0000000003B262B0]
13222564840001 17:41:04.681 . ERROR: connection requires a valid client
certificate [Status=1]
The SSLmode is set to require when I connect with PGAdmin. So presumably,
there is no problem with the certificates. Is there anything that jumps out
from the FireDAC output as to why the SSL connection doesn’t work?
Many thanks,
Mark
__
From: Wim Bertels <wim.bertels@ucll.be>
Sent: 22 August 2018 09:19
To: Mark Williams <markwillimas@gmail.com>;
pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: Setting up SSL for postgre
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
_____
Van: Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> >
Verzonden: maandag 20 augustus 2018 16:51
Aan: Wim Bertels; pgsql-admin@lists.postgresql.org
<mailto:pgsql-admin@lists.postgresql.org> ; s.dunand@sirap.fr
<mailto:s.dunand@sirap.fr>
Onderwerp: RE: Setting up SSL for postgre
Hi,
Sorry I don’t understand what you are suggesting re the pg_hba file.
__
From: Wim Bertels < <mailto:wim.bertels@ucll.be> wim.bertels@ucll.be>
Sent: 20 August 2018 14:30
To: <mailto:pgsql-admin@lists.postgresql.org>
pgsql-admin@lists.postgresql.org; <mailto:s.dunand@sirap.fr>
s.dunand@sirap.fr
Subject: Re: Setting up SSL for postgre
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
cert method for auth, hence this behaviour (client cert..)
extra tip:
<https://duckduckgo.com/?q=letsencrypt+postgresql;
https://duckduckgo.com/?q=letsencrypt+postgresql
for official server side certificates
mvg,
Bertels Wim
Mark
__
This page helped me :
<https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authenti
cation/>
https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentic
ation/
Best regards,
Stéphane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
@ 2018-08-24 09:59 ` Wim Bertels <wim.bertels@ucll.be>
2018-08-27 18:22 ` FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Wim Bertels @ 2018-08-24 09:59 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; s.dunand@sirap.fr <s.dunand@sirap.fr>
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-S...
https://www.postgresql.org/docs/10/static/ssl-tcp.html
https://www.postgresql.org/docs/10/static/libpq-ssl.html
hth,
Wim
________________________________
Van: Mark Williams <markwillimas@gmail.com>
Verzonden: donderdag 23 augustus 2018 18:53
Aan: Wim Bertels; pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Onderwerp: RE: Setting up SSL for postgre
Hi Wim,
I did intend Cert aut (at least I think I did!).
Still cannot connect to postgre database from my client app using FireDAC. I can connect fine from PGAdmin3 on the same machine using the same certificates.
The call made by FireDAC to libPQ.Dll is the following:
PQconnectdb [ConnInfo=hostaddr=192.168.0.12 port=5432 dbname=rees user=postgres password=*** connect_timeout=10 sslmode=require sslrootcert=C:\ProgramData\MWC\Viewer\Certs\root.crt sslcert=C:\ProgramData\MWC\Viewer\Certs\postgresql.crt sslkey=C:\ProgramData\MWC\Viewer\Certs\postgresql.key password=1234, Result=$0000000003B262B0]
13222564840001 17:41:04.681 . ERROR: connection requires a valid client certificate [Status=1]
The SSLmode is set to require when I connect with PGAdmin. So presumably, there is no problem with the certificates. Is there anything that jumps out from the FireDAC output as to why the SSL connection doesn't work?
Many thanks,
Mark
__
From: Wim Bertels <wim.bertels@ucll.be>
Sent: 22 August 2018 09:19
To: Mark Williams <markwillimas@gmail.com>; pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: Setting up SSL for postgre
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
________________________________
Van: Mark Williams <markwillimas@gmail.com<mailto:markwillimas@gmail.com>>
Verzonden: maandag 20 augustus 2018 16:51
Aan: Wim Bertels; pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>; s.dunand@sirap.fr<mailto:s.dunand@sirap.fr>
Onderwerp: RE: Setting up SSL for postgre
Hi,
Sorry I don't understand what you are suggesting re the pg_hba file.
__
From: Wim Bertels <wim.bertels@ucll.be<mailto:wim.bertels@ucll.be>>
Sent: 20 August 2018 14:30
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>; s.dunand@sirap.fr<mailto:s.dunand@sirap.fr>
Subject: Re: Setting up SSL for postgre
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
cert method for auth, hence this behaviour (client cert..)
extra tip:
https://duckduckgo.com/?q=letsencrypt+postgresql
for official server side certificates
mvg,
Bertels Wim
Mark
__
This page helped me :
https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentication/
Best regards,
Stéphane
^ permalink raw reply [nested|flat] 22+ messages in thread
* FW: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-24 09:59 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
@ 2018-08-27 18:22 ` Mark Williams <markwillimas@gmail.com>
2018-08-27 22:05 ` Re: FW: Setting up SSL for postgre Tim Cross <theophilusx@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Mark Williams @ 2018-08-27 18:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
__
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.
Re the log on the “windows machine” both server and client are windows
machines. Which log should I check?
Finally, I’m pretty sure FireDAC doesn’t any restrictions on self-certified
connections. I connect to MySQL over SSL via FireDAC with a self-certified
certificate.
Thanks
Mark
__
From: Wim Bertels <wim.bertels@ucll.be <mailto:wim.bertels@ucll.be> >
Sent: 24 August 2018 10:59
To: Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> >;
pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org> ;
s.dunand@sirap.fr <mailto:s.dunand@sirap.fr>
Subject: Re: Setting up SSL for postgre
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#RUN
TIME-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
_____
Van: Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> >
Verzonden: donderdag 23 augustus 2018 18:53
Aan: Wim Bertels; pgsql-admin@lists.postgresql.org
<mailto:pgsql-admin@lists.postgresql.org> ; s.dunand@sirap.fr
<mailto:s.dunand@sirap.fr>
Onderwerp: RE: Setting up SSL for postgre
Hi Wim,
I did intend Cert aut (at least I think I did!).
Still cannot connect to postgre database from my client app using FireDAC. I
can connect fine from PGAdmin3 on the same machine using the same
certificates.
The call made by FireDAC to libPQ.Dll is the following:
PQconnectdb [ConnInfo=hostaddr=192.168.0.12 port=5432 dbname=rees
user=postgres password=*** connect_timeout=10 sslmode=require
sslrootcert=C:\ProgramData\MWC\Viewer\Certs\root.crt
sslcert=C:\ProgramData\MWC\Viewer\Certs\postgresql.crt
sslkey=C:\ProgramData\MWC\Viewer\Certs\postgresql.key password=1234,
Result=$0000000003B262B0]
13222564840001 17:41:04.681 . ERROR: connection requires a valid client
certificate [Status=1]
The SSLmode is set to require when I connect with PGAdmin. So presumably,
there is no problem with the certificates. Is there anything that jumps out
from the FireDAC output as to why the SSL connection doesn’t work?
Many thanks,
Mark
__
From: Wim Bertels <wim.bertels@ucll.be <mailto:wim.bertels@ucll.be> >
Sent: 22 August 2018 09:19
To: Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> >;
pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org> ;
s.dunand@sirap.fr <mailto:s.dunand@sirap.fr>
Subject: Re: Setting up SSL for postgre
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
_____
Van: Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> >
Verzonden: maandag 20 augustus 2018 16:51
Aan: Wim Bertels; pgsql-admin@lists.postgresql.org
<mailto:pgsql-admin@lists.postgresql.org> ; s.dunand@sirap.fr
<mailto:s.dunand@sirap.fr>
Onderwerp: RE: Setting up SSL for postgre
Hi,
Sorry I don’t understand what you are suggesting re the pg_hba file.
__
From: Wim Bertels < <mailto:wim.bertels@ucll.be> wim.bertels@ucll.be>
Sent: 20 August 2018 14:30
To: <mailto:pgsql-admin@lists.postgresql.org>
pgsql-admin@lists.postgresql.org; <mailto:s.dunand@sirap.fr>
s.dunand@sirap.fr
Subject: Re: Setting up SSL for postgre
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
cert method for auth, hence this behaviour (client cert..)
extra tip:
<https://duckduckgo.com/?q=letsencrypt+postgresql;
https://duckduckgo.com/?q=letsencrypt+postgresql
for official server side certificates
mvg,
Bertels Wim
Mark
__
This page helped me :
<https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authenti
cation/>
https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentic
ation/
Best regards,
Stéphane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: FW: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-24 09:59 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-27 18:22 ` FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
@ 2018-08-27 22:05 ` Tim Cross <theophilusx@gmail.com>
2018-08-28 18:52 ` RE: FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Tim Cross @ 2018-08-27 22:05 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: FW: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-24 09:59 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-27 18:22 ` FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-27 22:05 ` Re: FW: Setting up SSL for postgre Tim Cross <theophilusx@gmail.com>
@ 2018-08-28 18:52 ` Mark Williams <markwillimas@gmail.com>
2018-08-28 19:55 ` Re: FW: Setting up SSL for postgre Dai, Tino <tdai@loc.gov>
2018-08-30 07:55 ` Re: FW: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
0 siblings, 2 replies; 22+ messages in thread
From: Mark Williams @ 2018-08-28 18:52 UTC (permalink / raw)
To: 'Tim Cross' <theophilusx@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr; 'Wim Bertels' <wim.bertels@ucll.be>
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: FW: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-24 09:59 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-27 18:22 ` FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-27 22:05 ` Re: FW: Setting up SSL for postgre Tim Cross <theophilusx@gmail.com>
2018-08-28 18:52 ` RE: FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
@ 2018-08-28 19:55 ` Dai, Tino <tdai@loc.gov>
2018-08-29 16:36 ` RE: FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Dai, Tino @ 2018-08-28 19:55 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; Tim Cross <theophilusx@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; s.dunand@sirap.fr <s.dunand@sirap.fr>; Wim Bertels <wim.bertels@ucll.be>
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: FW: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-24 09:59 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-27 18:22 ` FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-27 22:05 ` Re: FW: Setting up SSL for postgre Tim Cross <theophilusx@gmail.com>
2018-08-28 18:52 ` RE: FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-28 19:55 ` Re: FW: Setting up SSL for postgre Dai, Tino <tdai@loc.gov>
@ 2018-08-29 16:36 ` Mark Williams <markwillimas@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Mark Williams @ 2018-08-29 16:36 UTC (permalink / raw)
To: 'Dai, Tino' <tdai@loc.gov>; 'Tim Cross' <theophilusx@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr; 'Wim Bertels' <wim.bertels@ucll.be>
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
=
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: FW: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-24 09:59 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-27 18:22 ` FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-27 22:05 ` Re: FW: Setting up SSL for postgre Tim Cross <theophilusx@gmail.com>
2018-08-28 18:52 ` RE: FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
@ 2018-08-30 07:55 ` Wim Bertels <wim.bertels@ucll.be>
2018-08-30 11:57 ` RE: FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Wim Bertels @ 2018-08-30 07:55 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; 'Tim Cross' <theophilusx@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; s.dunand@sirap.fr <s.dunand@sirap.fr>
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: FW: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-24 09:59 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-27 18:22 ` FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-27 22:05 ` Re: FW: Setting up SSL for postgre Tim Cross <theophilusx@gmail.com>
2018-08-28 18:52 ` RE: FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-30 07:55 ` Re: FW: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
@ 2018-08-30 11:57 ` Mark Williams <markwillimas@gmail.com>
2018-08-30 14:41 ` Re: FW: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
0 siblings, 1 reply; 22+ messages in thread
From: Mark Williams @ 2018-08-30 11:57 UTC (permalink / raw)
To: 'Wim Bertels' <wim.bertels@ucll.be>; 'Tim Cross' <theophilusx@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
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
=
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: FW: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-24 09:59 ` Re: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-27 18:22 ` FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-27 22:05 ` Re: FW: Setting up SSL for postgre Tim Cross <theophilusx@gmail.com>
2018-08-28 18:52 ` RE: FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-30 07:55 ` Re: FW: Setting up SSL for postgre Wim Bertels <wim.bertels@ucll.be>
2018-08-30 11:57 ` RE: FW: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
@ 2018-08-30 14:41 ` Bear Giles <bgiles@coyotesong.com>
0 siblings, 0 replies; 22+ messages in thread
From: Bear Giles @ 2018-08-30 14:41 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: wim.bertels@ucll.be; theophilusx@gmail.com; pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Little-known fact: Windows applications also accept forward slashes. The
reason you can't do it at the command line is because MS decided to use /,
instead of -, to indicate command line options, so any CLI application that
accepts options will be confused. (Commands that don't accept options can
also accept / in filenames.)
However you shouldn't have had to use double-quotes in your configuration.
In code, yes, but not in configuration files.
On Thu, Aug 30, 2018 at 5:58 AM Mark Williams <markwillimas@gmail.com>
wrote:
> 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
>
> =
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
@ 2018-08-20 13:36 ` Evan Bauer <evanbauer@mac.com>
2018-08-20 15:16 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Evan Bauer @ 2018-08-20 13:36 UTC (permalink / raw)
To: s.dunand@sirap.fr; +Cc: pgsql-admin@lists.postgresql.org
Mark,
Have you tried a remote connection from the client with something other than Delphi — psql or pgAdmin — to whether the the issue is on the server or client side of the connection?
Cheers,
- Evan
Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer
> On Aug 20, 2018, at 09:02, Stéphane Dunand <s.dunand@sirap.fr> wrote:
>
> Le 20/08/2018 à 14:44, Mark Williams a écrit :
>> 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 <mailto: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
>> __
>>
>
> This page helped me :
> https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentication/ <https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentication/;
>
> Best regards,
> Stéphane
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: Setting up SSL for postgre
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Re: Setting up SSL for postgre Bear Giles <bgiles@coyotesong.com>
2018-08-20 12:44 ` RE: Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Re: Setting up SSL for postgre Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:36 ` Re: Setting up SSL for postgre Evan Bauer <evanbauer@mac.com>
@ 2018-08-20 15:16 ` Mark Williams <markwillimas@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Mark Williams @ 2018-08-20 15:16 UTC (permalink / raw)
To: 'Evan Bauer' <evanbauer@mac.com>; s.dunand@sirap.fr; +Cc: pgsql-admin@lists.postgresql.org
Thanks for the suggestion. Should have thought of that before.
I can connect remotely with PGAdmin and ssl. So must be a FireDAC problem.
Thanks,
Mark
__
From: Evan Bauer <evanbauer@mac.com>
Sent: 20 August 2018 14:36
To: s.dunand@sirap.fr
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Setting up SSL for postgre
Mark,
Have you tried a remote connection from the client with something other than Delphi — psql or pgAdmin — to whether the the issue is on the server or client side of the connection?
Cheers,
- Evan
Evan Bauer
eb@evanbauer.com <mailto:eb@evanbauer.com>
+1 646 641 2973
Skype: evanbauer
On Aug 20, 2018, at 09:02, Stéphane Dunand <s.dunand@sirap.fr <mailto:s.dunand@sirap.fr> > wrote:
Le 20/08/2018 à 14:44, Mark Williams a écrit :
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 '/ <mailto:C=UK/ST=Wales/L=Cardiff/O=MWC/CN=192.168.0.12/emailAddress=info@mwconsult.co.uk> 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
__
This page helped me :
<https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentication/; https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentication/
Best regards,
Stéphane
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2018-08-30 14:41 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-08-13 16:55 Setting up SSL for postgre Mark Williams <markwillimas@gmail.com>
2018-08-13 19:00 ` Bear Giles <bgiles@coyotesong.com>
2018-08-13 19:57 ` Mark Williams <markwillimas@gmail.com>
2018-08-13 20:57 ` Bear Giles <bgiles@coyotesong.com>
2018-08-14 11:56 ` Mark Williams <markwillimas@gmail.com>
2018-08-20 12:44 ` Mark Williams <markwillimas@gmail.com>
2018-08-20 13:02 ` Stéphane Dunand <s.dunand@sirap.fr>
2018-08-20 13:29 ` Wim Bertels <wim.bertels@ucll.be>
2018-08-20 14:51 ` Mark Williams <markwillimas@gmail.com>
2018-08-22 08:19 ` Wim Bertels <wim.bertels@ucll.be>
2018-08-23 16:53 ` Mark Williams <markwillimas@gmail.com>
2018-08-24 09:59 ` Wim Bertels <wim.bertels@ucll.be>
2018-08-27 18:22 ` Mark Williams <markwillimas@gmail.com>
2018-08-27 22:05 ` Tim Cross <theophilusx@gmail.com>
2018-08-28 18:52 ` Mark Williams <markwillimas@gmail.com>
2018-08-28 19:55 ` Dai, Tino <tdai@loc.gov>
2018-08-29 16:36 ` Mark Williams <markwillimas@gmail.com>
2018-08-30 07:55 ` Wim Bertels <wim.bertels@ucll.be>
2018-08-30 11:57 ` Mark Williams <markwillimas@gmail.com>
2018-08-30 14:41 ` Bear Giles <bgiles@coyotesong.com>
2018-08-20 13:36 ` Evan Bauer <evanbauer@mac.com>
2018-08-20 15:16 ` Mark Williams <markwillimas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox