KB484539: MicroStrategy support for PostgreSQL certificate based authentication

What is Certificate Authentication? 

This authentication method uses SSL client certificates to perform authentication. It is therefore only available for SSL connections. When using this authentication method, the server will require that the client provide a valid, trusted certificate. No password prompt will be sent to the client.
Strategy currently supports PostgreSQL certificate based authentication using the PostgreSQL native driver. This articles demonstrate how to connect to the PostgreSQL database using certificate based authentication in Strategy.
 

Server Configuration 

1. Create a folder and change the permissions.
   

   
   $ mkdir /opt/TLS/
   $ cd /opt/TLS/
   $ mkdir keys certs pgconf
   $ chmod og-rwx keys certs pgconf
   

   
   
2. Create a key-pair that will serve both as the root CA and the server key-pair. The "ca.crt" name is used to match what it expects later.
   

   
   $ openssl req -new -x509 -days 365 -nodes -out certs/ca.crt \ -keyout keys/ca.key -subj "/CN=root-ca"
   $ cp certs/ca.crt pgconf/ca.crt
   

   
   
3. Create the server key and certificate request (CSR) and sign with the root key.
   

   
   $ openssl req -new -nodes -out server.csr \
   -keyout pgconf/server.key -subj "/CN=tec-l-012363"
   $ openssl x509 -req -in server.csr -days 365 \
   -CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \ -out pgconf/server.crt
   

   
   
4. The configuration for

   postgresql.conf

   (

   /var/lib/pgsql/12/data/postgresql.conf

   ):
   

   
   # Default listen addresses is localhost,change based on your needs. listen_addresses = '*'
   # here are the SSL specific settings
   ssl = on # this enables SSL
   ssl_cert_file = '/opt/TLS/pgconf/server.crt' # this specifies the server certificacte
   ssl_key_file = '/opt/TLS/pgconf/server.key' # this specifies the server private key
   ssl_ca_file = '/opt/TLS/pgconf/ca.crt' # this specific which CA certificate to trust
   

   
   
5. Configuration for

   pg_hba.conf

   (

   /var/lib/pgsql/12/data/pg_hba.conf

   ). We only configure certificate based authentication for remote connections.
   

   
   # TYPE       DATABASE       USER          ADDRESS      METHOD
    local        all             postgres                    peer
    # do not let the "postgres" superuser login via a certificate
    hostssl      all             postgres      ::/0          reject      
    hostssl      all             postgres      0.0.0.0/0     reject
    #
    hostssl      all             all           ::/0          cert
    hostssl      all             all           0.0.0.0/0     cert
   

   
   
6. Restart the server to pick up the configurations.

Client Configuration 

1. Create the client certificate. By default, PostgreSQL looks for these in the

   ~/.postgresql

    directory. Copy the certificate to the machine if you are connecting from a remote client.
   

   
   $ openssl req -new -nodes -out client.csr \ -keyout keys/client.key -subj "/CN=tpch"
   $ openssl x509 -req -in client.csr -days 365 \
   -CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \ -out certs/client.crt

   
   
2. Set the PostgreSQL connection variables to point to these certs. Use "verify-full" mode, which will verify that the server certificate was signed by the trusted root CA AND that the hostname matches that on the server certificate.
   

   
   $ export PGSSLMODE="verify-full"
   $ export PGSSLCERT="`pwd`/certs/client.crt" $ export PGSSLKEY="`pwd`/keys/client.key"
   $ export PGSSLROOTCERT="`pwd`/certs/ca.crt"
   

   
   
3. Run the below command in the remote host to verify connection.
   

   
   $ psql -h tec-l-012363 -p 5432 -U tpch -d tpch
   psql (10.13, server 12.3)
   WARNING: psql major version 10, server major version 12.
   Some psql features might not work.
   SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
   bits: 256, compression: off) Type "help" for help.
   tpch=> \q

   
   

Connect to PostgreSQL in Strategy Using Certificate Based Authentication

PostgreSQL Native Driver

We will demonstrate using the PostgreSQL native driver how to connect to the PostgreSQL database using certificate based authentication in Strategy.

1. Create a data source name (DSN).
   

   
   [PGN]
   Driver=/usr/pgsql-11/lib/psqlodbcw.so
   Description=PostgreSQL Unicode(x64)
   Database=tpch
   Servername=tec-l-012363
   Port=5432
   Protocol=9.3
   ReadOnly=No
   RowVersioning=No
   ShowSystemTables=No
   ConnSettings=
   DriverUnicodeType=1
   SSLmode=verify-full Pqopt=sslrootcert=/home/ec2-user/TLS/certs/ca.crt sslcert=/home/ec2-
   user/TLS/certs/client.crt sslkey=/home/ec2-user/TLS/keys/client.key

   
   
2. Test the connectivity using the mstrodbcx program.
   

   
   [ec2-user@CentOS-RELEASE-10-244-20-230 Linux]$ ./bin/mstrtodbcx Strategy ODBC Test Tool
   Version 11.3.0000.3619
   Type .help (including the period) for help.
   - > .cn -d PGN -u tpch
   # Data Source Name: PGN
   # Login: tpch
   # Connection type : Strategy DB Classes
   PGN > .quit

   
   

Strategy ODBC Driver for PostgreSQL Wire Protocol

Since Strategy 2021 Update 4, certificate based authentication with Strategy ODBC Driver for PostgreSQL Wire Protocol is also supported.
Create a data source name (DSN).

[Postgres]
ApplicationUsingThreads=1
AlternateServers=
AuthenticationMethod=18
Description=Strategy ODBC Driver for PostgreSQL Wire Protocol
FailoverPreconnect=0
FetchTWFSasTime=1
Driver=/opt/mstr/MicroStrategy/install/lib/MYpsqlXX.so
ConnectionRetryDelay=3
ClientSSLCertificate=/tmp/abc/client.crt
ClientSSLKey=/tmp/abc/client.key
EnableDescribeParam=1
InitializationString=
FetchRefCursors=1
Database=postgres
QueryTimeout=0
HostName=tec-l-1034973
FetchTSWTZasTimestamp=1
ReportCodePageConversionErrors=0
FailoverMode=0
LoadBalancing=0
ConnectionRetryCount=0
ExtendedColumnMetaData=0
XMLDescribeType=-10
LoginTimeout=15
TransactionErrorBehavior=0
FailoverGranularity=0
PortNumber=5432
LogonID=tpch
TrustStore=/tmp/abc/ca.crt