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.