Connection using ODBC and SSL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Connection using ODBC and SSL

Corbit, Dann
I figured out that my TLS version was too low in the libpq call and increased it to TLS v1.1
Should I go to 1.2?  I am wondering because I do not want to limit compatibility.

Once I got past that hurdle, I am getting the error "ssl error: the certificate verify failed"
Since I built the certificates myself self-signed, I am assuming I did something that Postgres does not like.
I should mention that I am using the Windows environment for testing (I will test Linux after Windows succeeds).

I would like to have all my certificates and keys on the same machine (localhost for local connections and dcorbit for tcp/ip).
I found a couple tutorials and tried them but it failed.
I saw one document that said the common name should be the postgres user name and that it should also be the connecting machine name.  Is that correct?
Is there a document or tutorial that explains the correct steps?
Equally important, is there a way to get more complete diagnostics when something goes wrong (like WHY did the certificate verify fail)?

Reply | Threaded
Open this post in threaded view
|

Re: Connection using ODBC and SSL

Tom Lane-2
"Corbit, Dann" <[hidden email]> writes:
> I figured out that my TLS version was too low in the libpq call and increased it to TLS v1.1
> Should I go to 1.2?  I am wondering because I do not want to limit compatibility.

PG 13 and up consider that 1.2 is the *minimum* secure version.
Quoting from the commit log:

    Change libpq's default ssl_min_protocol_version to TLSv1.2.
   
    When we initially created this parameter, in commit ff8ca5fad, we left
    the default as "allow any protocol version" on grounds of backwards
    compatibility.  However, that's inconsistent with the backend's default
    since b1abfec82; protocol versions prior to 1.2 are not considered very
    secure; and OpenSSL has had TLSv1.2 support since 2012, so the number
    of PG servers that need a lesser minimum is probably quite small.
   
    On top of those things, it emerges that some popular distros (including
    Debian and RHEL) set MinProtocol=TLSv1.2 in openssl.cnf.  Thus, far
    from having "allow any protocol version" behavior in practice, what
    we actually have as things stand is a platform-dependent lower limit.
   
    So, change our minds and set the min version to TLSv1.2.  Anybody
    wanting to connect with a new libpq to a pre-2012 server can either
    set ssl_min_protocol_version=TLSv1 or accept the fallback to non-SSL.
   
    Back-patch to v13 where the aforementioned patches appeared.

> Once I got past that hurdle, I am getting the error "ssl error: the certificate verify failed"
> Since I built the certificates myself self-signed, I am assuming I did something that Postgres does not like.

The process in our docs worked for me last time I tried it:

https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CERTIFICATE-CREATION

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Connection using ODBC and SSL

Andrew Dunstan
In reply to this post by Corbit, Dann

On 11/20/20 4:54 PM, Corbit, Dann wrote:
>
> I would like to have all my certificates and keys on the same machine
> (localhost for local connections and dcorbit for tcp/ip).
> I found a couple tutorials and tried them but it failed.
> I saw one document that said the common name should be the postgres
> user name and that it should also be the connecting machine name.  Is
> that correct?
> Is there a document or tutorial that explains the correct steps?



I did a webinar about a year ago that went into some detail about what
you need in the CN, where the certificates go, etc.


See
<https://resources.2ndquadrant.com/using-ssl-with-postgresql-and-pgbouncer>
(Yes, this is a corporate webinar, sorry about that)




> Equally important, is there a way to get more complete diagnostics
> when something goes wrong (like WHY did the certificate verify fail)?
>

The diagnostics in the Postgres log are usually fairly explanatory.



cheers


andrew



Reply | Threaded
Open this post in threaded view
|

Re: Connection using ODBC and SSL

Corbit, Dann
Thank you for the assistance.


From: Andrew Dunstan <[hidden email]>
Sent: Saturday, November 21, 2020 11:14
To: Corbit, Dann <[hidden email]>; PostgreSQL Developers <[hidden email]>
Cc: Luton, Bill <[hidden email]>; Fifer, Brian <[hidden email]>; Lao, Alexander <[hidden email]>
Subject: Re: Connection using ODBC and SSL
 

On 11/20/20 4:54 PM, Corbit, Dann wrote:
>
> I would like to have all my certificates and keys on the same machine
> (localhost for local connections and dcorbit for tcp/ip).
> I found a couple tutorials and tried them but it failed.
> I saw one document that said the common name should be the postgres
> user name and that it should also be the connecting machine name.  Is
> that correct?
> Is there a document or tutorial that explains the correct steps?



I did a webinar about a year ago that went into some detail about what
you need in the CN, where the certificates go, etc.


See
<https://resources.2ndquadrant.com/using-ssl-with-postgresql-and-pgbouncer>
(Yes, this is a corporate webinar, sorry about that)




> Equally important, is there a way to get more complete diagnostics
> when something goes wrong (like WHY did the certificate verify fail)?
>

The diagnostics in the Postgres log are usually fairly explanatory.



cheers


andrew