Return
-
Title
How to setup Foglight connect PostgreSQL backend repository via SSL with client certificate Authentication -
Description
How to setup Foglight connect PostgreSQL backend repository via TLS with client certificate Authentication -
Resolution
At the time this knowledge base article written (Foglight 5.9.4 and FVE 8.8.5), Foglight does not support connect PostgreSQL backend repository via TLS with client certificate authentication during the setupHowever, here the workaround
Preparation
1. Setup PostgreSQL normally without SSL and client certificate authentication2. Install Foglight and use backend Postgres database3. Her are some assumption; Postgres database server called dbHOST; Foglight database called foglightDB and user connect to Postgres is foglightUSER4. Stop FMS and PostgreSQL5. Have a CA to issue and sign certificate for dbHOST and foglightUSER
Set PostgreSQL to allow SSL and client certificate authentication
1. Issue server certificate for dbHOST and singed (we need two files, certificate and private key)2. Issue user certificate for foglightUSER and singed (we need two files, certificate and private key)3. Modify postgresql.conf file and put dbHost certificate, something like thisssl=on # (chg reqs restart)ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers (chg reqs restart)ssl_prefer_server_ciphers=on # (chg reqs restart)ssl_ecdh_curve='prime256v1' # (chg reqs restart)ssl_cert_file='_your_PostgreSQL_path/dbHOST.cert' # (chg reqs restart)ssl_key_file='_your_PostgreSQL_path/dbHOST.key' # (chg reqs restart)ssl_ca_file='_your_PostgreSQL_path/rootCA.cert' # (chg reqs restart)4. Modify pg_hba.conf file; put clientcert=0 line before clientcert=1 so we can connect via TLS/SSL without client certificate authenticationSomething like this# Remote connections:hostssl all all all md5 clientcert=0hostssl all all all md5 clientcert=15. Start PostgreSQLSet Foglight to connect PostgreSQL with TLS with client certificate authentication
1. Go to $FGLHOME/config2. Copy foglightUSER certificate and private key files to $FLGHOME/config directory3. Modify $FGLHOME/config/datasource/datasource-postgresql.properties and add following lines(please replace certificate and private key files to your own)db.ssl=truedb.sslmode=requiredb.sslcert=/home/foglight/Quest/Foglight/config/foglightUSER.certdb.sslkey=/home/foglight/Quest/Foglight/config/foglightUSER.key5. Start FMSNote:
If your user certificate issued by openSSL, you might need run following command to convert i if you FMS logs shows following error2019-04-23 10:14:40.549 FATAL [forge-startup] com.quest.nitro.startup.ForgeServer - Unexpected error during server startup, exiting with error code 1133.java.lang.RuntimeException: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not read SSL key file /home/foglight/Quest/Foglight/config/FoglightUSER.key.)…Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not read SSL key file /home/foglight/Quest/Foglight/config/FoglightUSER.key.)at …Caused by: org.postgresql.util.PSQLException: Could not read SSL key file /home/foglight/Quest/Foglight/config/FoglightUSER.key...Caused by: java.io.IOException: extra data given to DerValue constructorHere are the steps:
1. Login to FMS server with foglight user and go to config directory2. Run this commandopenssl pkcs8 -topk8 -inform PEM -outform DER -in _your_original_key_file -out new_key_file -nocrypt3. Modify datasource-postgresql.properties and change db.sslkey from _your_original_key_file to new_key_file4. Start FMS5. Please verify from psql that FoglightUSER user from Foglight connected via TLS and client certificate.Here are some query you can useselectpid,datname,usename,application_name,client_hostname,client_port,statefrom pg_stat_activity;select * from pg_stat_ssl;