ODBC Using Windows Cert Store

From PostgreSQL wiki
Jump to navigationJump to search

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.