its-network/docs/space/srv-acraze/database.md

About

This file contains the documenation for our local database server. This was originally postgres only, which has changed now to postgres and mariadb making the name a bit confusing.

Location

The VM is running as pgsql.srv.it-syndikat.org on acraze.

PostgreSQL

The PostgreSQL database uses client certificates for authentication. These are stored in /etc/postgresql/client_certs/; the server is configured to use /etc/postgresql/client_certs.pem (a concatenation of all the individual certificates) as a certificate authority, removing the need for a "proper" PKI.

To set up a new postgresql client:

• Generate a new client key and certificate using sudo generate_client_cert DBNAME KEY_OUTFILE
• Copy the generated keyfile and certificate (from /etc/postgresql/client_certs/) as well as the server certificate (from /etc/postgresql/server.pem) to the client
• Specify the following postgres arguments:
  • user=DBNAME
  • database=DBNAME
  • sslmode=verify-ca
  • sslkey=[client keyfile.key]
  • sslcert=[client cert.pem]
  • sslrootcert=[server cert.pem]

generate_client_cert

script

#!/usr/bin/env bash
set -euo pipefail

CERTS_DIRECTORY=/etc/postgresql/client_certs
COMBINED_CERTS_FILE=/etc/postgresql/client_certs.pem

[[ $# -eq 2 ]] || { echo "Usage: $0 DBNAME KEY_OUTFILE" >&2; exit 1; }

dbname=$1
keyfile=$2

openssl req \
	-new \
	-x509 \
	-sha256 \
	-days 358201 \
	-extensions usr_cert \
	-newkey rsa:4096 \
	-noenc \
	-out "$CERTS_DIRECTORY/$dbname.pem" \
	-keyout "$keyfile" \
	-subj "/CN=$dbname"

echo "Key has been generated as $keyfile."

echo "Adding certificate to certificate store..."
cat "$CERTS_DIRECTORY"/*.pem > "$COMBINED_CERTS_FILE"
systemctl reload postgresql.service

echo "Done."

Maintainers

• @xiretza: VM maintenance and postgresql
• @tyrolyean: mariadb database