ODBC Using Windows Cert Store

== How to have the ODBC driver use the Windows Certificate Store ==
This is just a quick run-down of what's needed. Formal user documentation should also be created.
* Acquire a certificate
** This should be placed in the Windows Certificate store w/ private key
** This can be verified using the MMC and the Certificate Management plugin
* Export the public portion of the certificate using the Windows certificate export utility
** The way I do this is with the MMC, might be possible to do it another way..
** Make sure to export it in "PEM Encoded" format
** Should create a .crt file (eg: user.crt)
* Acquire the root chain (might be able to export it from the Windows Certificate Store, or get it from the Certificate Authority)
** Should be in PEM format and include all certificates necessary to verify the *server's* certificate 
** Should be a .crt file (eg: rootchain.crt)
* Install the latest version of the PG ODBC driver
** Configure it as expected, more-or-less..
** Use PostgreSQL Unicode (either 32bit or 64bit, but make sure to run the correct ODBC manager depending on which you're using..)
** Data Source and Description can be whatever
** Database should be 'the right one'
** SSL mode: require (with valid certs on both sides, you should try with "verify-full"...)
** Server is 'the right one'
** User Name: The *PG* user name which is assigned to the user (eg: sfrost)
** Password: *BLANK*
** Under Data Source - Check "Declare/Fetch"
* Setting up environment variables
** PG cares about three environment variables:
** PGSSLKEY
*** This should be set to "capi:User Name", eg: "capi:Stephen P Frost", where 'User Name' is the CN of the certificate, or what's displayed in the Windows Certificate Store for the certificate
** PGSSLCERT
*** This needs to be set to the user.crt file, eg: "C:\user.crt"
** PGSSLROOTCHAIN
*** This needs to be set to the rootchain.crt file, eg "C:\rootchain.crt"
** OpenSSL requires a couple things too:
*** Need to acquire the capi.dll file to put in the ODBC install directory (see below)
*** Need to set the OPENSSL_CONF environment variable - should be set to the openssl.cfg file - C:\Program Files (x86)\psqlODBC\0901\bin\openssl.cfg
*** We should probably distribute the openssl.cfg, but what it requires is this info:
 [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
* Server-side
** It's really straight-forward and the pg_hba.conf documentation around mapping gives all the info needed
** In pg_hba.conf, need something like this:
*** hostssl all all 0.0.0.0/0 cert map=certmap
** Then, in pg_ident.conf:
*** certmap /"^Stephen P Frost$" sfrost
** In postgresql.conf
*** ssl=true
** In your data directory, you should have either real files or symlinks for:
*** root.crt -- Root chain
*** server.crt -- Server public certificate
*** server.key -- Server private key
* Future
** I've asked the ODBC maintainers to include the default openssl.cfg (maybe with the stanzas above..) and capi.dll in their distribution
** I'm going to be working w/ the PG community to get them to support CAPI for the actual certificate and root chain.
** Obviously, if/when those happen, we'll need to adjust/shrink this documentation.
Category:Windows