Re: PQgetssl() and alternative SSL implementations - Mailing list pgsql-hackers

On Tue, Aug 19, 2014 at 4:48 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Heikki Linnakangas (hlinnakangas@vmware.com) wrote:
>>   server_cert_valid: Did the server present a valid certificate?
>> "yes" or "no"
>>
>>   server_cert_matches_host: Does the Common Name of the certificate
>> match the host connected to? "yes" or "no"
>
> Aren't these questions addressed by sslmode?

Not entirely. You can have sslmode=require and have a matching
certificate. You don't *have* to have sslmode=verify-full for that.

However, whether it makes *sense* without sslmode is another story -
but assuming you use something like kerberos for auth, it might. For
password, you've already lost once you get that far.


>> Exposing the SSL information as generic key/value pairs allows
>> adding more attributes in the future, without breaking the ABI, and
>> it also allows exposing implementation-specific information in a
>> generic way. The attributes listed above cover the needs of psql.
>> What else do we need?
>
> At first blush, I'd say a whole bunch..  Off the top of my head I can
> think of:
>
> For all certificates:
> (client, server, cert that signed each, any intermediate CAs, root CAs)
>   Certificate itself (perhaps in DER, PEM, X509 formats..)

Yeah, if we can extract it in PEM for example, that would be useful.

>   Fingerprint

Definitely.

>   Signed-By info

If we can get the full cert, do that one instead.

>   Common Name

Definitely.

>   Organization (et al)
>   Alternate names
>   Issue date, expiration date
>   CRL info, OCSP info
>   Allowed usage (encryption, signing, etc)

All those would also be covered by the "certificate itself" part I
think - they're not that common.


> CRL checking done?
> OCSP used?
>
>> I think it would also be nice to get more information from the
>> server's certificate, like the hostname and the organization its
>> issued to, and expiration date, so that an interactive client like
>> pgAdmin or even psql could display that information like a web
>> browser does. Would it be best to add those as extra attributes in
>> the above list, perhaps with a "server_cert_*" prefix, or add a new
>> function for extracting server cert's attributes?
>
> This really shouldn't be for *just* the server's certificate but rather
> available for all certificates involved- on both sides.

Well, if you are already the client, wouldn't you know your own certificate?


>> The other question is: What do we do with PQgetssl()? We should
>> document it as deprecated, but we'll have to keep it around for the
>> foreseeable future for backwards-compatibility. We obviously cannot
>> return a valid OpenSSL struct when using any other implementation,
>> so I think it'll have to just return NULL when not using OpenSSL.
>> Probably the most common use of PQgetssl() is to just check if it
>> returns NULL or not, to determine if SSL is enabled, so a client
>> that does that would incorrectly think that SSL is not used, even
>> when it is. I think we can live with that.
>
> That's not ideal, but the only other option I can think of offhand is to
> break the existing API and force everyone to update and that seems
> worse.

Agreed.

If we just return an arbitrary pointer, then any application that
*did* actually try to use it would crash.

It's not ideal, but errorring in the way of not saying we're secure
when we are, is acceptable - unlike the opposite.

Of course, we need to publish it very clearly in the release notes,
and I would suggest backpatching into the documentation in old
versions etc as well.


> Have you looked at how this change will play out with the ODBC driver..?
> Especially on Windows with the SSL library you're proposing we use
> there..  I recall that at one point the ODBC driver simply used libpq to
> handle the authentication and set everything up, and then switched to
> talking directly without libpq.  In any case, it'd probably be good to
> make sure the attributes you're suggesting are sufficient to meet the
> needs of the ODBC driver too.

+1.


-- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/