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

Поиск
Список
Период
Сортировка

Setting up SSL for postgre

От
"Mark Williams"
Дата:

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

 

__

 

Re: Setting up SSL for postgre

От
Bear Giles
Дата:
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

 

__

 


RE: Setting up SSL for postgre

От
"Mark Williams"
Дата:

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

 

__

 

 

Re: Setting up SSL for postgre

От
Bear Giles
Дата:
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

 

__

 

 


RE: Setting up SSL for postgre

От
"Mark Williams"
Дата:

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

 

__

 

 

 

RE: Setting up SSL for postgre

От
"Mark Williams"
Дата:

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

 

__

 

 

 

Re: Setting up SSL for postgre

От
Stéphane Dunand
Дата:
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

Re: Setting up SSL for postgre

От
Wim Bertels
Дата:


 

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

Re: Setting up SSL for postgre

От
Evan Bauer
Дата:
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

RE: Setting up SSL for postgre

От
"Mark Williams"
Дата:

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

RE: Setting up SSL for postgre

От
"Mark Williams"
Дата:

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

 

Re: Setting up SSL for postgre

От
Wim Bertels
Дата:

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

RE: Setting up SSL for postgre

От
"Mark Williams"
Дата:

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

Re: Setting up SSL for postgre

От
Wim Bertels
Дата:

Hallo Mark,


you should not include the password option,

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


another option: check the posgresql log on the windows machine


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


maybe check:

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

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

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

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


hth,

Wim



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

FW: Setting up SSL for postgre

От
"Mark Williams"
Дата:

 

 

__

 

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/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SECURITY

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

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

 

hth,

Wim

 


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

Re: FW: Setting up SSL for postgre

От
Tim Cross
Дата:
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


RE: FW: Setting up SSL for postgre

От
"Mark Williams"
Дата:
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



Re: FW: Setting up SSL for postgre

От
"Dai, Tino"
Дата:
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



RE: FW: Setting up SSL for postgre

От
"Mark Williams"
Дата:
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

=



Re: FW: Setting up SSL for postgre

От
Wim Bertels
Дата:
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



RE: FW: Setting up SSL for postgre

От
"Mark Williams"
Дата:
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

=



Re: FW: Setting up SSL for postgre

От
Bear Giles
Дата:
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

=