# 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]`

<details>
<summary><pre>generate_client_cert</pre> script</summary>

```
#!/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."
```
</details>

## Maintainers

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