71 lines
1.9 KiB
Markdown
71 lines
1.9 KiB
Markdown
# 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
|