• Perhaps this note will be useful:

    Setting up certificate-based authentication in a PostgreSQL container boils down to five steps:
    1. Creating the root certificate authority (CA)
    2. Generating the PostgreSQL server key and certificate that it will use
    3. Generate the PostgreSQL client key and certificate that you will use to connect
    4. Configuring the PostgreSQL server to enable TLS (SSL) connections  and use certificate-based authentication
    5. Connect
    'Step 1: Creating a Root Certificate Authority (CA)'
    openssl req -new -x509 -days 3650 -nodes -out ca.crt -keyout ca.key -subj "/CN=root-ca"
    --copy ca.crt
    cp ca.crt /etc/ssl/postgresql/
    'Step 2: Generate the PostgreSQL server key and certificate'
    sudo mkdir -p /etc/ssl/postgresql
    openssl req -new -nodes -out server.csr -keyout /etc/ssl/postgresql/server.key -subj "/CN=localhost"
    openssl x509 -req -in server.csr -days 3650 -CA ca.crt -CAkey ca.key -CAcreateserial -out /etc/ssl/postgresql/server.crt
    rm server.csr
    sudo chown -R postgres:postgres /etc/ssl/postgresql
    sudo chmod -R og-rwx /etc/ssl/postgresql
    'Step 3: Generating the Client Key and Certificate (optional)'
    openssl req -new -nodes -out client.csr -keyout client.key -subj "/CN=postgres"
    openssl x509 -req -in client.csr -days 3650 -CA ca.crt -CAkey ca.key -CAcreateserial -out client.crt
    rm client.csr
    'Step 4: Configure PostgreSQL to Enable TLS / Certificate-Based Authentication'
    ssl = on # this enables TLS
    ssl_cert_file = '/etc/ssl/postgresql/server.crt' # this specifies the server certificate
    ssl_key_file = '/etc/ssl/postgresql/server.key' # this specifies the server private key
    ssl_ca_file = '/etc/ssl/postgresql/ca.crt' # this specific which CA certificate to trust
    --pg_hba.conf (example)
    hostssl all             remote_user     [CLIENT_IP_ADDRESS]/32  md5 clientcert=1
    cat << EOF > /etc/postgresql/13/main/pg_hba.conf
    # 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               reject
    hostssl all             all             ::/0                    cert
    hostssl all             all                  cert
    -- Restart postgres
    pg_ctlcluster 13 main stop -m fast && pg_ctlcluster 13 main start
    'Step 5: Connecting'
    -- Use environmental variables:
    export PGSSLCERT="/etc/ssl/postgresql/server.crt"
    export PGSSLKEY="/etc/ssl/postgresql/server.key"
    export PGSSLROOTCERT="/etc/ssl/postgresql/ca.crt"
    export PGSSLMODE="verify-ca"
    require      - Data should be encrypted and the overhead of doing so is accepted. The network is trusted and will send me to the desired server.
    verify-ca    - Data must be encrypted. Systems must be doubly sure that the connection to the right server is established.
    verify-full  - Strongest protection possible. Full encryption and full validation of the desired target server.
    PGSSLMODE=verify-ca PGSSLCERT="/etc/ssl/postgresql/server.crt" PGSSLKEY="/etc/ssl/postgresql/server.key" PGSSLROOTCERT="/etc/ssl/postgresql/ca.crt" psql -h -U postgres -d test
    -- Use ~/.postgresql/ directory:
    mkdir ~/.postgresql/ && cd ~/.postgresql/
    --# copy the following files (example):
    scp root@DB_SERVER_IP:/path/to/{ca.crt,client.key,client.crt} ./
    --# to simplify the connection, lets use the default names
    mv ca.crt ~/.postgresql/root.crt
    mv client.crt ~/.postgresql/postgresql.crt
    mv client.key ~/.postgresql/postgresql.key
    --# set permissions
    chmod -R og-rwx ~/.postgresql/
    -- check ssl for current pid
    select * from pg_stat_ssl where pid = pg_backend_pid();
    Edited by Vitaliy Kukharik
  • @akartasov

    1. Why do you generate and store certificates in the /tmp/certs directory? After restarting the server, these certificates will be lost. It may be worth specifying another path, for example ~/.dblab/engine/pgssl

    2. Do you think we can just mount certificates and set variables at the DLE level instead of using a preprocessing script. Certificates do not have to be stored in the PGDATA.

      --volume ~/.dblab/engine/pgssl/:/home/dblab/pgssl:ro \
      --env PGSSLCERT=/home/dblab/pgssl/postgresql.crt \
      --env PGSSLKEY=/home/dblab/pgssl/postgresql.key \
      --env PGSSLROOTCERT=/home/dblab/pgssl/root.crt \
      --env PGSSLMODE=verify-ca \
    1. We also have to explicitly specify the cert method in the ph_hba.conf file for authenticate using SSL client certificates. By default, the md5 method is used - Perform SCRAM-SHA-256 or MD5 authentication to verify the user's password. Doc: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

    cc @NikolayS

    Edited by Vitaliy Kukharik
    1. That's a good point. This directory is just used as an example. We can specify any other in the documentation.
    2. We could do this, however it requires development as the ~/.dblab/engine/pgssl/ directory is not mounted to clone containers by default. By the way, it looks like a good first issue
    3. It doesn't seem to be configurable right now. However, this can be done manually, although it does require a restart of the DLE server.
  • If I have configured multiple zfs pools, do I need the preprocessingScript to copy the certs over to each pool location? It seems like I do, though I'm still playing around with this on a new setup.

  • Yes, you should consider multiple pools in the preprocessing script so that each PGDATA directory contains certificates

Supports Markdown
0% or .
You are about to add 0 people to the discussion. Proceed with caution.
Finish editing this message first!
Please register or to comment