Guide to enable SSL in clones (logical mode) ($2265946) · Snippets · Postgres.ai / Database Lab Engine

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'

--postgresql.conf
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

--or
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        0.0.0.0/0               reject
#
hostssl all             all             ::/0                    cert
hostssl all             all             0.0.0.0/0               cert
EOF

-- Restart postgres
pg_ctlcluster 13 main stop -m fast && pg_ctlcluster 13 main start



'Step 5: Connecting'

-- Use environmental variables:
https://www.postgresql.org/docs/current/libpq-envars.html

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 172.31.23.34 -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();


Links:
https://blog.crunchydata.com/blog/ssl-certificate-authentication-postgresql-docker-containers
https://www.cybertec-postgresql.com/en/setting-up-ssl-authentication-for-postgresql/
https://www.gab.lc/articles/postgresql_with_ssl/

@akartasov

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
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 \

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

That's a good point. This directory is just used as an example. We can specify any other in the documentation.
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
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