ODBC Using Windows Cert Store
From PostgreSQL wiki
Revision as of 20:22, 18 May 2012 by Boshomi
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:
- 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
- This needs to be set to the user.crt file, eg: "C:\user.crt"
- 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
- 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
- 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
- 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.