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 1fpIxs-0002Ex-G5 for pgsql-admin@arkaria.postgresql.org; Mon, 13 Aug 2018 19:57:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fpIxo-0001yE-98 for pgsql-admin@arkaria.postgresql.org; Mon, 13 Aug 2018 19:57:16 +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 1fpIxn-0001y7-Nl for pgsql-admin@lists.postgresql.org; Mon, 13 Aug 2018 19:57:16 +0000 Received: from mail-wm0-x241.google.com ([2a00:1450:400c:c09::241]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fpIxk-0003hb-Iv for pgsql-admin@lists.postgresql.org; Mon, 13 Aug 2018 19:57:14 +0000 Received: by mail-wm0-x241.google.com with SMTP id y9-v6so10037030wma.5 for ; Mon, 13 Aug 2018 12:57:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:thread-index:content-language; bh=U/pFVjBaf0zYk3Bojg2D0G1KWh5Bdz3gCGhrtW89/LI=; b=FmWQTlnCNTkBFgSjHVM405T4tep9XnR3PlaFHeMNkF5eC/83ni0mFdpgSYAsQPkmPD AFOF3q4Quft0xCOF3seET9BFAPZoddkPHrr2MaisiUTTOM6oJmTN3NAxzMkCqsBlR6dG dBcG0YbWenjvKeUwLNPhpwEHiJjrZz8C5YouaAsJk7B+mOJ2w6H3q0UgQPIZIOIENmAe DaFbZ0mgxtbmekTMBWdAgNHUqMe7obKuX7JoWmlE+M2CTNuIZnFycZeXTDHyG+FAYl0t 2giysYsluwwQ7bRZJLd+hSQQJ6oJCYWjmKgKglJnWIOEI0MMHrRm0HmZIyYRtD46h0Pb ChJQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=U/pFVjBaf0zYk3Bojg2D0G1KWh5Bdz3gCGhrtW89/LI=; b=j3lFt0lHLcfaX+xHKCnnf372KWiutEhbRf1mjfLN20O4K7ie1nVQl092H8S3bkcDJ2 FEdcFhYY05hx51UfkctrCDEbvl7hpLP+yMCCyJkTookQrgkeehOGgYqqXQyADvojxrTZ P9Eol0s2aFRc8NtPbkfP5orpZ6gpZ/jo+SuszsGlQin2dGYQ22SDYQRHAsWGOYG1dVy2 El9KqdMms0H9pfPodDOzQMOdPTuzeelSsBRPzE0+OA21e8EjiSZVv/fDieTnRkTEClO1 woPwzr/bFjAYEdGJ4GqU2UJdRD1WiNEe7BLdxWOZo/gadWmfEsQk5Sr1aUfdfTmZND4K QNqA== X-Gm-Message-State: AOUpUlHO1IKp4DNjtK2MZILzimR+DKHp7x3t0lbULiiBm0mUUa7FLtRe LE9I5zntAKs0KexYfbrjTY4= X-Google-Smtp-Source: AA+uWPxqhtq0vTKLJyYnROgYZTK68g3RZFgdfCTJHP6ViWVQHJBUA1hSpwNYhSruD/rM86wwCI6Kqw== X-Received: by 2002:a1c:8414:: with SMTP id g20-v6mr9844679wmd.90.1534190231174; Mon, 13 Aug 2018 12:57:11 -0700 (PDT) Received: from LAPTOPE7EH7MDP (167.221-30-62.static.virginmediabusiness.co.uk. [62.30.221.167]) by smtp.gmail.com with ESMTPSA id y33-v6sm20140971wmh.37.2018.08.13.12.57.10 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 13 Aug 2018 12:57:10 -0700 (PDT) From: "Mark Williams" To: "'Bear Giles'" Cc: References: <032401d43326$6c275d80$44761880$@gmail.com> In-Reply-To: Subject: RE: Setting up SSL for postgre Date: Mon, 13 Aug 2018 20:57:10 +0100 Message-ID: <034901d4333f$d2aa7650$77ff62f0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_034A_01D43348.34703DE0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJ64nPKQfAewiPZ9t7gQ7SiT+1fnwIMhi/1o2BUzKA= 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_034A_01D43348.34703DE0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =20 Sent: 13 August 2018 20:01 To: Mark Williams 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 ------=_NextPart_000_034A_01D43348.34703DE0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

What do you mean by = =E2=80=9Cchange the ssl = cert file and key=E2=80=9D

 

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= =A0 METHOD

 

# 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 = www-data=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 md5 clientcert=3D1

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 md5 clientcert=3D1

 

# 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

 

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 = ''

 

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.

&= 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=3Dm4917588911217483979pln>root.crt';
Params.values['SSL_cert']:=3DsslCertsPath+'<= span class=3Dm4917588911217483979pln>postgresql.crt.';Params.values['SSL_key']:=3DsslCertsPath+'<= span class=3Dm4917588911217483979pln>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>

 

------=_NextPart_000_034A_01D43348.34703DE0--