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(at)example(dot)com bob

if you want user with the cert with a CN of 'bob(at)example(dot)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(at)gmail(dot)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(at)coyotesong(dot)com>
> *Sent:* 13 August 2018 20:01
> *To:* Mark Williams <markwillimas(at)gmail(dot)com>
> *Cc:* pgsql-admin(at)lists(dot)postgresql(dot)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(at)gmail(dot)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
>
>
>
> __
>
>
>
>
>