# Self-hosted database encryption and TLS

Source: https://codeherder.com/docs/self-host-database/

Encrypt the PostgreSQL database with your own KMS key, then require verified TLS on every connection, and check both with commands.

Your organization owns the PostgreSQL database of a self-hosted server. CodeHerder does not create it for you. This guide states two settings you must make and shows how to check each one.

## Encrypt the database with your own KMS key

Use a customer-managed KMS key for the database storage. Do not use the AWS-managed `aws/rds` key. A customer-managed key lets you rotate it, restrict it, and audit every use.

1. Create a symmetric customer-managed key in the same region as the database.
2. Turn on automatic rotation: `aws kms enable-key-rotation --key-id <key-arn>`.
3. Create the instance with `--storage-encrypted --kms-key-id <key-arn>`.
4. Give the same key to snapshot copies, so that a restore can open them.

You cannot change the key of an existing instance. To move one, copy a snapshot with the new key. Then restore the copy as a new instance. Plan a maintenance window for the switch.

Only key administrators may call `kms:ScheduleKeyDeletion` or `kms:PutKeyPolicy`. A deleted key makes the database and every snapshot unreadable. See [Self-hosted backup and recovery](https://codeherder.com/docs/self-host-backups/).

### Key policy

The RDS service uses this key, not the CodeHerder server. The server instance role needs no grant on it. Do not add one.

Scope the key to RDS in your account. Name the role that creates the instance. Name your key administrators.

```
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "KeyAdministrators",
      "Effect": "Allow",
      "Principal": { "AWS": "arn:aws:iam::<account-id>:role/<key-admin-role>" },
      "Action": [
        "kms:Create*", "kms:Describe*", "kms:Enable*", "kms:List*",
        "kms:Put*", "kms:Update*", "kms:Revoke*", "kms:Disable*",
        "kms:Get*", "kms:ScheduleKeyDeletion", "kms:CancelKeyDeletion"
      ],
      "Resource": "*"
    },
    {
      "Sid": "UseOnlyThroughRdsInThisAccount",
      "Effect": "Allow",
      "Principal": { "AWS": "arn:aws:iam::<account-id>:role/<database-provisioning-role>" },
      "Action": [
        "kms:Encrypt", "kms:Decrypt", "kms:ReEncrypt*",
        "kms:GenerateDataKey*", "kms:CreateGrant",
        "kms:ListGrants", "kms:DescribeKey"
      ],
      "Resource": "*",
      "Condition": {
        "StringEquals": {
          "kms:ViaService": "rds.<region>.amazonaws.com",
          "kms:CallerAccount": "<account-id>"
        }
      }
    }
  ]
}
```

### Verify the key

Run these four commands. Check each result.

```
aws rds describe-db-instances --db-instance-identifier <id> \
  --query 'DBInstances[0].[StorageEncrypted,KmsKeyId]'
aws kms describe-key --key-id <key-arn> \
  --query 'KeyMetadata.[KeyManager,KeyState,Origin]'
aws kms get-key-rotation-status --key-id <key-arn>
aws kms get-key-policy --key-id <key-arn> --policy-name default
```

- The first command must print `true` and your key ARN.
- The second must print `CUSTOMER`, `Enabled` and `AWS_KMS`.
- The third must show `KeyRotationEnabled` as `true`. The `describe-key` command does not show rotation.
- The fourth must show only the principals and conditions you chose.

## Require verified TLS on every connection

Encryption on the wire is not enough. The client must also check who it talks to. Use `sslmode=verify-full` in the `--db` value.

```
./codeherder --db "postgres://user:pass@<db-host-name>:5432/codeherder?sslmode=verify-full&sslrootcert=/etc/codeherder/rds-bundle.pem"
```

1. Use the DNS name of the database in the DSN. Never use an IP address, because the name check would fail.
2. Download the regional CA bundle from `https://truststore.pki.rds.amazonaws.com/<region>/<region>-bundle.pem`. Point `sslrootcert` at the file.
3. Download the bundle again when AWS rotates the CA. Restart the server after you replace the file.
4. Set `rds.force_ssl` to `1` in a custom parameter group. Attach the group to the instance. Do not rely on the engine default.
5. Set `CH_ENV=production` on the server. The server then refuses to start with a DSN that lacks `sslmode=verify-full` for a database that is not on the same host.
6. Keep the DSN in an environment file with mode `0600`. It holds the database password.

Under `CH_ENV=production`, the server and its maintenance commands refuse a non-local DSN without `verify-full`. This includes `sslmode=require` and `verify-ca`.

### Verify TLS

```
aws rds describe-db-parameters --db-parameter-group-name <group> \
  --query "Parameters[?ParameterName=='rds.force_ssl'].[ParameterValue,Source]"
psql "postgres://user@<db-host-name>:5432/codeherder?sslmode=disable"
psql "postgres://user@<db-host-name>:5432/codeherder?sslmode=verify-full&sslrootcert=/etc/codeherder/rds-bundle.pem" \
  -c "select ssl, version from pg_stat_ssl where pid = pg_backend_pid()"
```

- The first command must print `1` and `user`.
- The second command must fail with a message about encryption being required.
- The third command must print `t` and a TLS version.

## Related guides

- [Self-hosting CodeHerder](https://codeherder.com/docs/self-hosting/) — start the server and set the `--db` value
- [Self-hosted backup and recovery](https://codeherder.com/docs/self-host-backups/) — snapshots need the same key
- [Self-hosted infrastructure](https://codeherder.com/docs/self-host-infrastructure/) — the AWS permissions the server needs