FW: Setting up SSL for postgre

From: "Mark Williams" <markwillimas(at)gmail(dot)com>
To: <pgsql-admin(at)lists(dot)postgresql(dot)org>, <s(dot)dunand(at)sirap(dot)fr>
Subject: FW: Setting up SSL for postgre
Date: 2018-08-27 18:22:44
Message-ID: 000e01d43e32$f35aad40$da1007c0$@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

__

From: Mark Williams <markwillimas(at)gmail(dot)com>
Sent: 25 August 2018 18:14
To: 'Wim Bertels' <wim(dot)bertels(at)ucll(dot)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(dot)bertels(at)ucll(dot)be <mailto:wim(dot)bertels(at)ucll(dot)be> >
Sent: 24 August 2018 10:59
To: Mark Williams <markwillimas(at)gmail(dot)com <mailto:markwillimas(at)gmail(dot)com> >;
pgsql-admin(at)lists(dot)postgresql(dot)org <mailto:pgsql-admin(at)lists(dot)postgresql(dot)org> ;
s(dot)dunand(at)sirap(dot)fr <mailto:s(dot)dunand(at)sirap(dot)fr>
Subject: Re: Setting up SSL for postgre

Hallo Mark,

you should not include the password option,

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

another option: check the posgresql log on the windows machine

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

maybe check:

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

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

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

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

hth,

Wim

_____

Van: Mark Williams <markwillimas(at)gmail(dot)com <mailto:markwillimas(at)gmail(dot)com> >
Verzonden: donderdag 23 augustus 2018 18:53
Aan: Wim Bertels; pgsql-admin(at)lists(dot)postgresql(dot)org
<mailto:pgsql-admin(at)lists(dot)postgresql(dot)org> ; s(dot)dunand(at)sirap(dot)fr
<mailto:s(dot)dunand(at)sirap(dot)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(dot)bertels(at)ucll(dot)be <mailto:wim(dot)bertels(at)ucll(dot)be> >
Sent: 22 August 2018 09:19
To: Mark Williams <markwillimas(at)gmail(dot)com <mailto:markwillimas(at)gmail(dot)com> >;
pgsql-admin(at)lists(dot)postgresql(dot)org <mailto:pgsql-admin(at)lists(dot)postgresql(dot)org> ;
s(dot)dunand(at)sirap(dot)fr <mailto:s(dot)dunand(at)sirap(dot)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(at)gmail(dot)com <mailto:markwillimas(at)gmail(dot)com> >
Verzonden: maandag 20 augustus 2018 16:51
Aan: Wim Bertels; pgsql-admin(at)lists(dot)postgresql(dot)org
<mailto:pgsql-admin(at)lists(dot)postgresql(dot)org> ; s(dot)dunand(at)sirap(dot)fr
<mailto:s(dot)dunand(at)sirap(dot)fr>
Onderwerp: RE: Setting up SSL for postgre

Hi,

Sorry I don’t understand what you are suggesting re the pg_hba file.

__

From: Wim Bertels < <mailto:wim(dot)bertels(at)ucll(dot)be> wim(dot)bertels(at)ucll(dot)be>
Sent: 20 August 2018 14:30
To: <mailto:pgsql-admin(at)lists(dot)postgresql(dot)org>
pgsql-admin(at)lists(dot)postgresql(dot)org; <mailto:s(dot)dunand(at)sirap(dot)fr>
s(dot)dunand(at)sirap(dot)fr
Subject: Re: Setting up SSL for postgre

pg_hba.conf

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# IPv4 local & remote connections:

host all all 127.0.0.1/32 trust

hostssl all postgres 0.0.0.0/0 cert

cert method for auth, hence this behaviour (client cert..)

extra tip:

<https://duckduckgo.com/?q=letsencrypt+postgresql>
https://duckduckgo.com/?q=letsencrypt+postgresql

for official server side certificates

mvg,

Bertels Wim

Mark

__

This page helped me :

<https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authenti
cation/>
https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentic
ation/

Best regards,
Stéphane

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Christoph Berg 2018-08-27 18:38:19 Re: Space Related Errors in Postgres 10.4 Logical Replication
Previous Message Mariel Cherkassky 2018-08-27 12:05:55 Re: Could not open file "pg_subtrans/01EB"