Re: using pgsql-odbc using client certificate auth

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