Обсуждение: Setting up SSL for postgre
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
__
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
__
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
__
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
__
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 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
__
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>
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 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
__
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
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
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 creationopenssl genrsa -des3 -out c:\certs\server.key 1024openssl rsa -in c:\certs\server.key -out c:\certs\server.keyopenssl 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 1024openssl rsa -in c:\certs\postgresql.key -out c:\certs\postgresql.keyopenssl 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 -CAcreateserialI then copy the server.key, server.crt and root.crt file to the postgres data folder on the server machine.Postgresql.conflisten_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 = onpg_hba.conf# TYPE DATABASE USER CIDR-ADDRESS METHOD# IPv4 local & remote connections:host all all 127.0.0.1/32 trusthostssl all postgres 0.0.0.0/0 cert# IPv6 local connections:host all all ::1/128 trustI restart the service.Client MachineI am trying to connect from an application written in Delphi and using FireDAC.The FireDAC params are set as followsParams.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
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
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
+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'
{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
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
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
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>
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
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/ssl-tcp.html
https://www.postgresql.org/docs/10/static/libpq-ssl.html
hth,
Wim
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>
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
__
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>
Sent: 24 August 2018 10:59
To: Mark Williams <markwillimas@gmail.com>; pgsql-admin@lists.postgresql.org; 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/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>
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
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 =
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
=