mysql and postgres ODBC setup for SSL, notes on iodbc and unixODBC

ldd.out

# this uses iodbc, compile postgresql-odbc with --with-iodbc

$ ldd /usr/lib64/psqlodbcw-iodbc.so | grep odbc
        libiodbc.so.2 => /lib64/libiodbc.so.2 (0x00007f686e30a000)
        libiodbcinst.so.2 => /lib64/libiodbcinst.so.2 (0x00007f686e0f7000)

# this uses unixODBC, compile postgresql-odbc with --with-unixodbc

$ ldd /usr/lib64/psqlodbcw-unixodbc.so | grep odbc
        libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007fed0c26e000)
        libodbc.so.2 => /lib64/libodbc.so.2 (0x00007fed0c006000)

By default, it only generates one library linked against whichever ODBC library you specify, need to compile twice and name the resultant .so files differently, as above.

odbc.ini

[ODBC Data Sources]
postgres-database-ssl = PostgreSQL
mysql-database-ssl = MySQL

postgres-database = PostgreSQL
mysql-database = MySQL

; mysql reads Uid and Pwd (which is the same as if these values
;     were given in a ODBC connection string)
; postgres reads Username and Password

; by default, it will use the name of the current user

[postgres-database-ssl]
Driver           = PostgreSQL
Description      = <comments>
Database         = <dbname>
Username         = <user>
Password         = <pass>
Servername       = <host address>
Port             = <port 5432>
SSLmode          = require
Protocol         = 6.4
ReadOnly         = Yes
RowVersioning    = No
FakeOidIndex     = No
ShowOidColumn    = No
ShowSystemTables = No
; isql -v postgres-database-ssl
; iodbctest DSN=postgres-database-ssl

[mysql-database-ssl]
Driver           = MySQL
Description      = <comments>
Database         = <dbname>
Uid              = <user>
Pwd              = <pass>
Server           = <host address>
Port             = <port 3306>
useSSL           = Yes
sslca            = <path to CA's certificate>
sslkey           = <path to client's key>
sslcert          = <path to client's certificate>
ReadOnly         = Yes
ShowOidColumn    = No
ShowSystemTables = No
RowVersioning    = No
FakeOidIndex     = No
; isql -v mysql-database-ssl
; iodbctest DSN=mysql-database-ssl


[postgres-database]
Driver           = PostgreSQL
Description      = <comments>
Database         = <dbname>
Username         = <user>
Password         = <pass>
Servername       = <host address>
Port             = <port 5432>
; isql -v postgres-database
; iodbctest DSN=postgres-database

[mysql-database]
Driver           = MySQL
Description      = <comments>
Database         = <dbname>
Uid              = <user>
Pwd              = <pass>
Server           = <host address>
Port             = <port 3306>
; isql -v mysql-database
; iodbctest DSN=mysql-database

odbcinst-iodbc.ini

[ODBC Drivers]
MySQL = Installed
PostgreSQL = Installed

[MySQL]
Description = MySQL driver
Driver = /usr/lib64/libmyodbc5a.so
Setup  = /usr/lib64/libodbcmyS.so
UsageCount=1

[PostgreSQL]
Description = PostgreSQL driver, compiled for iodbc
Driver = /usr/lib64/psqlodbcw-iodbc.so
Setup  = /usr/lib64/libodbcpsqlS.so
UsageCount=1

odbcinst-unixodbc.ini

[ODBC Drivers]
MySQL = Installed
PostgreSQL = Installed

[MySQL]
Description = MySQL
Driver = /usr/lib64/libmyodbc5a.so
Setup  = /usr/lib64/libodbcmyS.so
UsageCount=1

[PostgreSQL]
Description = PostgreSQL driver, compiled for unixODBC
Driver = /usr/lib64/psqlodbcw-unixodbc.so
Setup  = /usr/lib64/libodbcpsqlS.so
UsageCount=1