use of Certificates to access PostGreSQL on ASDI-DB from rserver

This is my own version of Jonathan Katz's Certificate Authentication Recipe for PostgreSQL Docker Containers except that we're not using Docker Containers.

Step 1: Creating a Root Certificate Authority (CA)

From his document: Both the PostgreSQL server and client will reference the root CA when determining if they can trust each other as part of the connection process.

We're going to generate the (common) root ca files on asdi-db:

on asdi-db:

    $ cd ~/ciws/certs
    $ mkdir keys certs
    $ chmod og-rwx keys certs
    $ openssl req -new -x509 -days 365 -nodes -out certs/ca.crt \
           -keyout keys/ca.key -subj "/CN=root-ca"

    Generating a RSA private key
    ......+++++
    ..............+++++
    writing new private key to 'keys/ca.key'
    -----

and

    mkdir pgconf
    cp certs/ca.crt pgconf/ca.crt

Step 2: Generate the PostgreSQL server key and certificate

Note: hostname on asdi-db returns: ASDI-DB

again, on asdi-db:

    $ openssl req -new -nodes -out server.csr \
         -keyout pgconf/server.key -subj "/CN=ASDI-DB"

    Generating a RSA private key
    ................................................................+++++
    .........................+++++
    writing new private key to 'pgconf/server.key'
    -----

and

    $ openssl x509 -req -in server.csr -days 365 \
            -CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \
            -out pgconf/server.crt

Signature ok
subject=CN = ASDI-DB
Getting CA Private Key

and cleanup:

    $ rm server.csr

Step 3: Configure PostgreSQL to Enable TLS / Certificate-Based Authentication

After lots of attempts, the results were:

Then, carefully (watch those owner, group, and permissions), copy the server-side cert files to the postgresql data directory:

    $ $ cd ~/ciws/certs
    $ cp pgconf/* /cygdrive/c/Program\ Files/PostgreSQL/12/data
  1. edit postgresql.conf for:
    ssl = on
    ssl_cert_file = 'server.crt' # this specifies the server certificate
    ssl_key_file = 'server.key' # this specifies the server private key
    ssl_ca_file = 'ca.crt' # this specific which CA certificate to trust
  1. edit pg_hba.conf for:
    hostssl ciwsdb      ajrg_user     ::/0          cert
    hostssl ciwsdb      ajrg_user     0.0.0.0/0     cert

Finally, in DOS window:

    C:\Program Files\PostgreSQL\12\bin>pg_ctl.exe -D ..\data start

    waiting for server to start.....2020-11-12 10:05:12.906 EST [12828] LOG:  starting PostgreSQL 12.4, compiled by Visual C++ build 1914, 64-bit
    2020-11-12 10:05:12.908 EST [12828] LOG:  listening on IPv6 address "::", port 5432
    2020-11-12 10:05:12.909 EST [12828] LOG:  listening on IPv4 address "0.0.0.0", port 5432
    2020-11-12 10:05:13.275 EST [12828] LOG:  redirecting log output to logging collector process
    2020-11-12 10:05:13.275 EST [12828] HINT:  Future log output will appear in directory "log".
    ... done
    server started

Step x: Actually create PostGreSQL user

TODO: psql: CREATE USER ajrg_user ...

Step 4: Generating the Client Key and Certificate

First, in a secure manner, copy certs/ca.crt and certs/ca.key from asdi-db to rserver:

on asdi-db:

    scp certs/ca.crt ...
    scp keys/ca.key ...

on rserver:

    cd certs
    scp .../certs/ca.crt .
    cd ../keys
    scp .../keys/ca.key .

using the common ca.xxx files, generate the client certs:

    $ openssl req -new -nodes -out client.csr \
      -keyout keys/client.key -subj "/CN=ajrg_user"

    Generating a 2048 bit RSA private key
    .....+++
    ..+++
    writing new private key to 'keys/client.key'
    -----

    chmod og-rwx keys/*

and

    openssl x509 -req -in client.csr -days 365 \
        -CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \
        -out certs/client.crt

    Signature ok
    subject=/CN=ajrg_user
    Getting CA Private Key

    rm client.csr

Step x: recompile psql using --with-openssl

Note: we don't need postgresql to have openssl, just psql

    $ cd /home/wturner/ciws/pkgs/postgresql-11.4_ssl
    $ make clean
    $ ./configure --prefix=/home/data/local --with-python --with-openssl
    $ make

TODO: install!

Step 5: Connecting

We'll use the environment variable mechanism described in that document:

    # the first parameter specifies which TLS mode to use to connect
    export PGSSLMODE="verify-full"

    # the following two parameters point to the client key/certificate
    export PGSSLCERT="`pwd`/certs/client.crt"
    export PGSSLKEY="`pwd`/keys/client.key"

    # this parameter points to the trusted root CA certificate
    export PGSSLROOTCERT="`pwd`/certs/ca.crt"

until the install happens, use an "unaltered" LD LIB PATH:

    # remove our own libs from LD PATH:
    $ LD_LIBRARY_PATH=/opt/R/3.6.0/lib64/R/lib::/lib:/usr/lib/jvm/jre/lib/amd64/server:/home/data/local/lib/instantclient_19_3

then, what we've all been waiting for:

    $ src/bin/psql/psql  -h asdi-db.cssiinc.com -p 5432 -U ajrg_user ciwsdb

    psql: could not connect to server: Connection timed out
            Is the server running on host "asdi-db.cssiinc.com" (192.124.249.65) and accepting
            TCP/IP connections on port 5432?

Monday, Nov 16

More usage notes are on the howto-ciws page

MORE resolution on the ssl certificate:

then, set LD PATH to use that one first, and run that version of psql:

$ export LD_LIBRARY_PATH=/home/wturner/local/lib:/home/data/local/lib:/opt/R/3.6.0/lib64/R/lib::/lib:/usr/lib/jvm/jre/lib/amd64/server:/home/data/local/lib/instantclient_19_3:

$ bin/psql -h asdi-db.cssiinc.com -p 5432 -U ciwsuser ciwsdb
psql: server certificate for "ASDI-DB" does not match host name "asdi-db.cssiinc.com"