Thread: using pgsql-odbc using client certificate auth

* Duffey, Blake A. (Blake.Duffey@noblis.org) wrote:
> What support does the current PG ODBC driver have for using client certificates for user authentication?  Anyone have
anyexperience with this? 

More specifically..  We're trying to make it work, but the ODBC driver
is crashing and we're not sure why.  The error information is:

Problem signature:
  Problem Event Name:    APPCRASH
  Application Name:    odbcad32.exe
  Application Version:    6.1.7600.16385
  Application Timestamp:    4a5bcd4c
  Fault Module Name:    CRYPT32.dll
  Fault Module Version:    6.1.7601.17514
  Fault Module Timestamp:    4ce7b841
  Exception Code:    c0000005
  Exception Offset:    0000e26b
  OS Version:    6.1.7601.2.1.0.144.8
  Locale ID:    1033
  Additional Information 1:    0a9e
  Additional Information 2:    0a9e372d3b4ad19135b953a78882e789
  Additional Information 3:    0a9e
  Additional Information 4:    0a9e372d3b4ad19135b953a78882e789

This is using the latest ODBC driver (we've tried both 32bit and 64bit
and received similar errors, the above is with the 32bit one).  We can
connect from this same system using client-side certificates with
pgAdmin (where we have to specify the file location of the key and
certificate), and we have the client certificate loaded in to the
certificate store in Windows, so we know the PG server is configured
correctly and that the key and certificate work.

The 'mylog' file contains:

[9792-0.000]globals.extra_systable_prefixes = 'dd_;'
[9792-0.000]exe name=odbcad32 plaformId=2
[9792-0.015]aszKey='DSN', value='beren_test'
[9792-0.015]copyAttributes:
DSN='beren_test',server='',dbase='',user='',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[9792-0.062]getDSNinfo: DSN=beren_test overwrite=0
[9792-0.062]force_abbrev=0 bde=0 cvt_null_date=0
[9792-0.062]globals.extra_systable_prefixes = 'dd_;'
[9792-0.078]calling getDSNdefaults
[9792-0.078]checking libpq library
[9792-0.093]psqlodbc path based libpq loaded module=00000000
[9792-0.093]libpq hmodule=00000000
[9792-0.093]secur32 hmodule=74630000
[9792-0.093]libpq_exist=1
[9792-1.484]EN_add_connection: self = 00326A08, conn = 00326A38
[9792-1.484]       added at 0, conn->henv = 00326A08, conns[0]->henv = 00326A08

Also, looking through the source code, one thing which worries us is
that the CN in the certificate doesn't match the PG username we're
trying to use (though we've tried to make them match and that doesn't
help with the above error..).  We'd really like to not have those match
and instead have the ODBC driver use a specific certificate or have a
way to tell the ODBC driver which CN to use.

Any thoughts on this would be greatly appreciated.

    Thanks,

        Stephen

Hi Stephen,

(2012/03/15 2:27), Stephen Frost wrote:
> * Duffey, Blake A. (Blake.Duffey@noblis.org) wrote:
>> What support does the current PG ODBC driver have for using client certificates for user authentication?  Anyone
haveany experience with this? 

I'm not sure if the client certificates works or not.
As for the 32bit driver aren't you using psqlodbc_09_01_0100?
If so please try psqlodbc_09_01_0100-1.
psqlodbc_09_01_0100 contains an illegal libpq.dll and the
client certificates functionality completely relies on libpq.

regards,
Hiroshi Inoue

> More specifically..  We're trying to make it work, but the ODBC driver
> is crashing and we're not sure why.  The error information is:
>
> Problem signature:
>    Problem Event Name:    APPCRASH
>    Application Name:    odbcad32.exe
>    Application Version:    6.1.7600.16385
>    Application Timestamp:    4a5bcd4c
>    Fault Module Name:    CRYPT32.dll
>    Fault Module Version:    6.1.7601.17514
>    Fault Module Timestamp:    4ce7b841
>    Exception Code:    c0000005
>    Exception Offset:    0000e26b
>    OS Version:    6.1.7601.2.1.0.144.8
>    Locale ID:    1033
>    Additional Information 1:    0a9e
>    Additional Information 2:    0a9e372d3b4ad19135b953a78882e789
>    Additional Information 3:    0a9e
>    Additional Information 4:    0a9e372d3b4ad19135b953a78882e789
>
> This is using the latest ODBC driver (we've tried both 32bit and 64bit
> and received similar errors, the above is with the 32bit one).  We can
> connect from this same system using client-side certificates with
> pgAdmin (where we have to specify the file location of the key and
> certificate), and we have the client certificate loaded in to the
> certificate store in Windows, so we know the PG server is configured
> correctly and that the key and certificate work.
>
> The 'mylog' file contains:
>
> [9792-0.000]globals.extra_systable_prefixes = 'dd_;'
> [9792-0.000]exe name=odbcad32 plaformId=2
> [9792-0.015]aszKey='DSN', value='beren_test'
> [9792-0.015]copyAttributes:
DSN='beren_test',server='',dbase='',user='',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
> [9792-0.062]getDSNinfo: DSN=beren_test overwrite=0
> [9792-0.062]force_abbrev=0 bde=0 cvt_null_date=0
> [9792-0.062]globals.extra_systable_prefixes = 'dd_;'
> [9792-0.078]calling getDSNdefaults
> [9792-0.078]checking libpq library
> [9792-0.093]psqlodbc path based libpq loaded module=00000000
> [9792-0.093]libpq hmodule=00000000
> [9792-0.093]secur32 hmodule=74630000
> [9792-0.093]libpq_exist=1
> [9792-1.484]EN_add_connection: self = 00326A08, conn = 00326A38
> [9792-1.484]       added at 0, conn->henv = 00326A08, conns[0]->henv = 00326A08
>
> Also, looking through the source code, one thing which worries us is
> that the CN in the certificate doesn't match the PG username we're
> trying to use (though we've tried to make them match and that doesn't
> help with the above error..).  We'd really like to not have those match
> and instead have the ODBC driver use a specific certificate or have a
> way to tell the ODBC driver which CN to use.
>
> Any thoughts on this would be greatly appreciated.
>
>     Thanks,
>
>         Stephen

Hiroshi,

* Hiroshi Inoue (inoue@tpf.co.jp) wrote:
> I'm not sure if the client certificates works or not.
> As for the 32bit driver aren't you using psqlodbc_09_01_0100?
> If so please try psqlodbc_09_01_0100-1.

Thank you for that!  Turns out that was exactly it.  Other than that,
you have to set the necessary environment variables to tell libpq where
to find the certificate, the key, and the root CA certificates.  Once
those are done, it 'just works'.

It would be nice if it could use the certificate store, and it looks
like there is actually code in the ODBC driver to do that, but this is
good enough for what I need it for.

Thanks again!

    Stephen

Hiroshi, all,

* Stephen Frost (sfrost@snowman.net) wrote:
> It would be nice if it could use the certificate store, and it looks
> like there is actually code in the ODBC driver to do that, but this is
> good enough for what I need it for.

Ok, I've been able to make it use the Windows certificate store for the
SSL Key (at least..).  Unfortunately, it won't use the certificate store
for the actual certificate or the root chain (yet...).  When it comes to
the ODBC distribution, here's what I'd really like to see:

Please add the 'capi.dll' file to the ODBC distribution, it's part of
OpenSSL and should be installed next to libeay32.dll.  Unfortunately,
that's not all that's needed to make it work- you also need an
openssl.cfg file to be installed, ideally with the ODBC driver too,
with these contents:

---------------------------------------------------
openssl_conf = openssl_init

[openssl_init]
oid_section = new_oids
engines = engine_section

[engine_section]
capi = capi_config

[capi_config]
engine_id = capi
dynamic_path = "c:\\program\ files\ \(x86\)\\psqlodbc\\0901\\bin\\capi.dll"
init=1
---------------------------------------------------

We also need to tell OpenSSL where to find that config file by setting
an environment variable called "OPENSSL_CONF" and putting the path to
the .cfg file there, like so:

OPENSSL_CONF="C:\Program Files (x86)\psqlODBC\0901\bin\openssl.cfg"

Once all of *that* is done, you configure the PG environment variables
like so:

PGSSLCERT="C:\path\to\my.crt"
PGSSLROOTCERT="C:\path\to\myrootchain.crt"
PGSSLKEY="capi:My Name"

(eg: "capi:Stephen P Frost")

Not sure how much of the environment variable stuff we want to include
in the distribution of the ODBC driver vs. just having it in the
documentation.  The more we put into the distro, the less documentation
and the fewer steps that I'll have to deal with, so I'd be inclined to
include more rather than less.

I'm going to look into what it'd take to have CAPI be used for the
actual certificate and root chain..  That really should be very simple
as OpenSSL has support for all of this stuff, we just need to use it.
That'll likely be a libpq change though.

    Thanks!

        Stephen