Postgres Pro
Downloads
Home   >   mailing lists

Re: Setting up SSL for postgre - Mailing list pgsql-admin

From Bear Giles
Subject Re: Setting up SSL for postgre
Date
Msg-id CALBNtw7+xDCocj6M-K6mna4Ujresami2YfvdfVS0Q-q-B5PFTg@mail.gmail.com
Whole thread Raw
In response to RE: Setting up SSL for postgre  ("Mark Williams" <markwillimas@gmail.com>)
Responses RE: Setting up SSL for postgre  ("Mark Williams" <markwillimas@gmail.com>)
List pgsql-admin
Tree view
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

 

__

 

 


pgsql-admin by date:

Previous
From: Mariel Cherkassky
Date:
Subject: increase insert into local table from remote oracle table preformance
Next
From: Ilyass Kaouam
Date:
Subject: Re: Secure dump file