Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1frjY4-0004dh-5J for pgsql-admin@arkaria.postgresql.org; Mon, 20 Aug 2018 12:44:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1frjY1-0002Ec-Ka for pgsql-admin@arkaria.postgresql.org; Mon, 20 Aug 2018 12:44:41 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1frjY0-0002Cm-SI for pgsql-admin@lists.postgresql.org; Mon, 20 Aug 2018 12:44:41 +0000 Received: from mail-wr1-x42f.google.com ([2a00:1450:4864:20::42f]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1frjXs-0000R5-Ng for pgsql-admin@lists.postgresql.org; Mon, 20 Aug 2018 12:44:39 +0000 Received: by mail-wr1-x42f.google.com with SMTP id e7-v6so12868743wrs.9 for ; Mon, 20 Aug 2018 05:44:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :thread-index:content-language; bh=AciVFZRkWmmBRr6J0P/iIWhNX8//Xo+CWRn6o9xHKqk=; b=WxFt/Gbj6/3LBuoXSf47uqXuuZmnU9vPWnOFvMKqeJCHWmKlko+ijLQ0NGmr0rtD7C V3x6VlnWGbTNg4hGJDYJS7mJVJwWViwn6wWqclF0MUq6cBEGEbTg44Y7l2u/PUlrVp5N B+rg0r98uuogVhsFzvUlSol/a7IUx59DC70jHFPuNekK53AIB4DnYmPgrTxvY3sqJm4P Y3wZkdJNX3fIWWfIt9qQutcRH/gmnvK5BXTMcXYOZXdZd/dvXrb90i3r+RT5+4NgpFnP Kni3scblIcCtICP7H5c+JyDUmQM8J2TiKJ9reKb96NeLz6dj/CiRtMAVa2zpkuSBh3Yz PCkg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=AciVFZRkWmmBRr6J0P/iIWhNX8//Xo+CWRn6o9xHKqk=; b=PpAHTI+8n1WVmDOSKnHdLfCjqqnrpzYY4qgxbBKvunf3VYAV/NdvWkSd7gk2V5RQU7 CMiSTN47azEeRdb4J33svSX0RMP4QbwwmkniR9zY1EXjYFwj1641Slo6NBuX4dPUQ5X5 IrJCWlGEXcYn334CcQd77c8durv6UZJkcGIWTTQxHu8XbU8gP/mSZ1hL1ZNNx1Re7NSa eoKpOtVBVXnlEbQugpX9GHwXNQxmy41j9j9lkPpv/5Jj2JA1TmrfgXCSmmaghCiVFcSW U4ypFeIYYRrHKtnaZ0swOq1Y3iADvzViyfW263cblU7a4gtzOP2Rj3gnDDCK1U6yHE7+ nVfw== X-Gm-Message-State: APzg51AlH1t170uivol80QyOLXeuHMYIJR6q8MS/wpDTGReV8/KgD54v W1AUBrDT5DUcN/VwQZZA3NqmyGfI X-Google-Smtp-Source: ANB0Vdbrv76rimJbCUeGJDU+DvfAhEzGggdrgdAiJuV+W5PdBMTNEg4K7BUfApimuQD1MrWxExxAdA== X-Received: by 2002:adf:dc88:: with SMTP id r8-v6mr2252038wrj.166.1534769070741; Mon, 20 Aug 2018 05:44:30 -0700 (PDT) Received: from LAPTOPE7EH7MDP (167.221-30-62.static.virginmediabusiness.co.uk. [62.30.221.167]) by smtp.gmail.com with ESMTPSA id i4-v6sm2598504wrs.85.2018.08.20.05.44.29 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 20 Aug 2018 05:44:30 -0700 (PDT) From: "Mark Williams" To: References: <032401d43326$6c275d80$44761880$@gmail.com> <034901d4333f$d2aa7650$77ff62f0$@gmail.com> In-Reply-To: Subject: RE: Setting up SSL for postgre Date: Mon, 20 Aug 2018 13:44:29 +0100 Message-ID: <076401d43883$89ec6830$9dc53890$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0765_01D4388B.EBB452A0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJ64nPKQfAewiPZ9t7gQ7SiT+1fnwIMhi/1AcD6KUcCiXXq5aM+/62ggAmE0jA= Content-Language: en-gb List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0765_01D4388B.EBB452A0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I have started all over again to see if I can resolve this issue. = Unfortunately not. I am still pulling my hair out. =20 I am still following the instructions howtoforge. =20 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. =20 Commands for certificate creation openssl genrsa -des3 -out c:\certs\server.key 1024 =20 openssl rsa -in c:\certs\server.key -out c:\certs\server.key =20 openssl req -new -key c:\certs\server.key -days 3650 -out = c:\certs\server.crt -x509 -subj = '/C=3DUK/ST=3DWales/L=3DCardiff/O=3DMWC/CN=3D192.168.0.12/emailAddress=3D= info@mwconsult.co.uk' =20 {192.168.0.12 is the ipaddress of the server machine on the local = network. =20 =20 cp server.crt root.crt {manually copied as on Windows} openssl genrsa -des3 -out c:\certs\postgresql.key 1024 =20 openssl rsa -in c:\certs\postgresql.key -out c:\certs\postgresql.key =20 openssl req -new -key c:\certs\postgresql.key -out = c:\certs\postgresql.csr -subj = '/C=3DUK/ST=3DWales/L=3DCardiff/O=3DMWC/CN=3Dpostgres' =20 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 =20 I then copy the server.key, server.crt and root.crt file to the postgres = data folder on the server machine.=20 =20 Postgresql.conf listen_addresses =3D '*' ssl =3D on #ssl_ciphers =3D 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers #ssl_prefer_server_ciphers =3D on #ssl_ecdh_curve =3D 'prime256v1' #ssl_dh_params_file =3D '' ssl_cert_file =3D 'server.crt' ssl_key_file =3D 'server.key' ssl_ca_file =3D 'root.crt' #ssl_crl_file =3D '' #password_encryption =3D md5 # md5 or scram-sha-256 #db_user_namespace =3D off #row_security =3D on =20 pg_hba.conf # TYPE DATABASE USER CIDR-ADDRESS METHOD =20 # IPv4 local & remote connections: host all all 127.0.0.1/32 trust hostssl all postgres 0.0.0.0/0 cert=20 =20 # IPv6 local connections: host all all ::1/128 trust =20 I restart the service. =20 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'] :=3D 'True'; Params.values['SSL_ca'] :=3D sslCertsPath + 'root.crt'; Params.values['SSL_cert'] :=3D sslCertsPath + 'postgresql.crt.'; Params.values['SSL_key'] :=3D sslCertsPath + 'postgresql.key'; =20 The client certs are copied to =E2=80=9CsslCertsPath=E2=80=9D =20 When I connect I get the =E2=80=9Cconnection requires a valid client = certificate=E2=80=9D error. =20 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? =20 Hopefully, somebody can work out why this connection fails, if not, I = can see no alternative to booking myself in t Dignitas! =20 Many thanks. =20 Mark __ =20 From: Mark Williams =20 Sent: 14 August 2018 12:56 To: 'Bear Giles' Cc: 'pgsql-admin@lists.postgresql.org' = Subject: RE: Setting up SSL for postgre =20 Hi, =20 The CN is =E2=80=9Cwww-data=E2=80=9D as per the howtoforge documentation = and added to the pg_hba.conf file. Do I have to add an actual user = called =E2=80=9Cwww-data=E2=80=9D to the database? =20 If so, do I have to have a different root and client certificate for = every ssl user? =20 Re pg_ident. I have added =E2=80=9Ccert www-data=E2=80=9D = Postgres=E2=80=9D to pg_ident. Doesn=E2=80=99t help. Still getting the = =E2=80=9Cconnection requires a valid client certificate=E2=80=9D error. =20 Thanks =20 Mark =20 __ =20 From: Bear Giles > = Sent: 13 August 2018 21:57 To: Mark Williams > Cc: pgsql-admin@lists.postgresql.org = =20 Subject: Re: Setting up SSL for postgre =20 It never hurts to double check. =20 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., =20 cert bob@example.com bob =20 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. =20 Bear =20 =20 On Mon, Aug 13, 2018 at 1:57 PM, Mark Williams > wrote: What do you mean by =E2=80=9Cchange the ssl cert file and key=E2=80=9D =20 Pg_hba.conf =20 # TYPE DATABASE USER CIDR-ADDRESS METHOD =20 # IPv4 local & remote connections: host all all 127.0.0.1/32 = trust hostssl all www-data 0.0.0.0/0 = md5 clientcert=3D1 hostssl all postgres 0.0.0.0/0 = md5 clientcert=3D1 =20 # IPv6 local connections: host all all ::1/128 trust =20 Postgresql.conf ssl =3D on #ssl_ciphers =3D 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers #ssl_prefer_server_ciphers =3D on #ssl_ecdh_curve =3D 'prime256v1' #ssl_dh_params_file =3D '' ssl_cert_file =3D 'server.crt' ssl_key_file =3D 'server.key' ssl_ca_file =3D 'root.crt' #ssl_crl_file =3D '' =20 Thanks __ =20 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 =20 What's in pg_hba.conf? =20 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. =20 =20 On Mon, Aug 13, 2018 at 10:55 AM, Mark Williams > wrote: I am new to Postgre. Migrating from MySQL. =20 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. =20 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']:=3DsslCertsPath+'root.crt'; Params.values['SSL_cert']:=3DsslCertsPath+'postgresql.crt.'; Params.values['SSL_key']:=3DsslCertsPath+'postgresql.key'; =20 I am getting a connection error re invalid client certificate.=20 I have used OpenSSL to verify against the root.crt and postgresql.crt = and this confirms the certificate is ok. Mark =20 __ =20 =20 =20 ------=_NextPart_000_0765_01D4388B.EBB452A0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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=C2=A0 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=3DUK/ST=3DWales/L=3DCardiff/O=3DMWC/CN=3D192.168.0.12/emailAddress=3D= 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=3DUK/ST=3DWales/L=3DCardiff/O=3DMWC/CN=3Dpostgres'<= /p>

 

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 =3D = '*'

ssl =3D on

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

#ssl_prefer_server_ciphers =3D = on

#ssl_ecdh_curve =3D = 'prime256v1'

#ssl_dh_params_file =3D = ''

ssl_cert_file =3D = 'server.crt'

ssl_key_file =3D = 'server.key'

ssl_ca_file =3D = 'root.crt'

#ssl_crl_file =3D = ''

#password_encryption =3D = md5=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 # md5 or = scram-sha-256

#db_user_namespace =3D = off

#row_security =3D = on

 

pg_hba.conf

# = TYPE=C2=A0 DATABASE=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = USER=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = CIDR-ADDRESS=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0METHOD

 

# IPv4 = local & remote connections:

host=C2=A0=C2=A0=C2=A0 = all=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= = all=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= = 127.0.0.1/32=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 trust

hostssl = all=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = postgres=C2=A0=C2=A0=C2=A0 = 0.0.0.0/0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 cert

 

# IPv6 = local connections:

host=C2=A0=C2=A0=C2=A0 = all=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= = all=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= = ::1/128=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 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

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 Params.Values['UseSSL'] :=3D 'True';

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = =C2=A0=C2=A0Params.values['SSL_ca'] :=3D sslCertsPath + = 'root.crt';

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = =C2=A0=C2=A0Params.values['SSL_cert'] :=3D sslCertsPath + = 'postgresql.crt.';

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = =C2=A0=C2=A0Params.values['SSL_key'] :=3D sslCertsPath + = 'postgresql.key';

 

The client = certs are copied to = =E2=80=9CsslCertsPath=E2=80=9D

 

When I = connect I get the =E2=80=9Cconnection requires a valid client = certificate=E2=80=9D 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 = =E2=80=9Cwww-data=E2=80=9D as per the howtoforge documentation and added = to the pg_hba.conf file. Do I have to add an actual user called = =E2=80=9Cwww-data=E2=80=9D 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 =E2=80=9Ccert www-data=E2=80=9D Postgres=E2=80=9D = to pg_ident. Doesn=E2=80=99t help. Still getting the =E2=80=9Cconnection = requires a valid client certificate=E2=80=9D = 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.postgr= esql.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 =E2=80=9Cchange the ssl = cert file and key=E2=80=9D

 

Pg_hba.co= nf

 <= /o:p>

# = TYPE  DATABASE        = USER            = CIDR-ADDRESS          &= nbsp; METHOD

 <= /o:p>

# IPv4 = local & remote connections:

host &n= bsp;  = all           &nbs= p; = all           &nbs= p; 127.0.0.1/32      &nb= sp;     trust

hostssl = all         = www-data    0.0.0.0/0       =       md5 clientcert=3D1

hostssl = all         = postgres    0.0.0.0/0       =       md5 clientcert=3D1

 <= /o:p>

# IPv6 = local connections:

host &n= bsp;  = all           &nbs= p; = all           &nbs= p; = ::1/128           =       trust

 <= /o:p>

Postgresq= l.conf

ssl =3D = on

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

#ssl_prefer_= server_ciphers =3D on

#ssl_ecdh_cu= rve =3D 'prime256v1'

#ssl_dh_para= ms_file =3D ''

ssl_cert_fil= e =3D 'server.crt'

ssl_key_file= =3D 'server.key'

ssl_ca_file = =3D 'root.crt'

#ssl_crl_fil= e =3D ''

 <= /o:p>

Thanks<= /o:p>

__

 <= /o:p>

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

 <= /o:p>

What's in = pg_hba.conf?

 <= /o:p>

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.

 <= /o:p>

 <= /o:p>

On Mon, Aug = 13, 2018 at 10:55 AM, Mark Williams <markwillimas@gmail.com> = wrote:

I= am new to Postgre. Migrating from MySQL.

&= nbsp;

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.

&= nbsp;

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'>I= have coped the specified files to the client machine and installed the = root.crt certificate.

V= ia FireDAC's connection params I have specified the = following:

Params.values[SSL_ca']:=3DsslCertsPath+'<=
span class=3Dm-1438415984718051057m4917588911217483979pln>root.crt';
Params.values['SSL_cert']:=3DsslCertsPath+'<= span class=3Dm-1438415984718051057m4917588911217483979pln>postgresql.crt.';Params.values['SSL_key']:=3DsslCertsPath+'<= span class=3Dm-1438415984718051057m4917588911217483979pln>postgresql.key';

&= nbsp;

<= span = style=3D'font-size:11.5pt;font-family:"Arial",sans-serif;color:#242729'>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

 <= /o:p>

__

 <= /o:p>

 <= /o:p>

 

------=_NextPart_000_0765_01D4388B.EBB452A0--