Who this is for

This chapter covers the core features and capabilities of Database Security Hardening.

Who this is for

• Backend engineers and DBAs standing up a new PostgreSQL/MySQL instance.
• Teams preparing for a security review or SOC 2 / ISO 27001 control mapping.
• Anyone who inherited a database where "the app connects as the superuser."

You should be comfortable running psql/mysql as an admin and editing server

config files. You do not need any paid tooling.

Contents

database-security-hardening/
├── README.md
├── LICENSE
├── sql/
│   ├── postgres-roles-grants.sql        # layered NOLOGIN groups + LOGIN roles, default privileges
│   ├── postgres-row-level-security.sql  # multi-tenant RLS with FORCE + WITH CHECK
│   ├── postgres-audit-logging.sql       # pgAudit + append-only row-history trigger
│   ├── postgres-encryption-pgcrypto.sql # AES-256 column encryption, HMAC lookup, bcrypt passwords
│   ├── mysql-users-grants.sql           # roles, host-pinned TLS accounts, resource limits
│   └── mysql-audit-logging.sql          # plugin notes + universal trigger-based trail
├── config/
│   ├── postgresql.conf.ssl              # TLS, SCRAM, logging, DoS guards
│   ├── pg_hba.conf                      # hostssl rules, cert auth, explicit deny
│   └── my.cnf                           # TLS, auth plugin, secure_file_priv, audit
├── scripts/
│   ├── privilege_audit.py               # offline grant auditor (stdlib only, has --demo)
│   ├── pg_privilege_audit.sql           # export queries that feed the auditor
│   └── check_tls.sh                     # verify a live endpoint's TLS + certificate
└── docs/
    ├── cis-postgres-checklist.md        # L1/L2 hardening checklist, PostgreSQL
    ├── cis-mysql-checklist.md           # L1/L2 hardening checklist, MySQL/MariaDB
    ├── threat-model.md                  # STRIDE breakdown + attacker walkthrough
    ├── secrets-management.md            # keep credentials out of code; rotate safely
    └── encryption-guide.md              # at-rest + in-transit, layer by layer

Prerequisites

Follow this guide to get Database Security Hardening up and running in your environment.

Prerequisites

• PostgreSQL 13–16 or MySQL 8.0/8.4 / MariaDB 10.6+.
• Admin access (postgres superuser / MySQL root@localhost) for the one-time setup.
• openssl on the machine running check_tls.sh.
• Python 3.10+ to run privilege_audit.py (standard library only — nothing to install).

All hostnames (db01.example.com), subnets, and passwords in these files are

placeholders. Replace them, and never commit real secrets — see

docs/secrets-management.md.

How to use it

A sensible order for a fresh instance:

1. Read the threat model (docs/threat-model.md) so the controls make sense

as a system, not a checklist.

2. Apply transport + auth config — drop config/postgresql.conf.ssl and

config/pg_hba.conf (or config/my.cnf) into place, generate certs (see

docs/encryption-guide.md), and restart/reload.

3. Create the role hierarchy — run sql/postgres-roles-grants.sql (or

sql/mysql-users-grants.sql). Point your app at the new least-privilege login.

4. Turn on auditing — run sql/postgres-audit-logging.sql /

sql/mysql-audit-logging.sql.

5. Add RLS / encryption where the data warrants it

(postgres-row-level-security.sql, postgres-encryption-pgcrypto.sql).

6. Verify — run scripts/check_tls.sh, export the privilege inventory with

scripts/pg_privilege_audit.sql, and audit it:

   # see the auditor's output immediately, no database required:
   python3 scripts/privilege_audit.py --demo

   # then against your real export, failing CI on HIGH findings:
   psql -h db01.example.com -U postgres -d app_db -At -F',' \
        -f scripts/pg_privilege_audit.sql > inventory.csv
   python3 scripts/privilege_audit.py --csv inventory.csv --fail-on high

7. Walk the checklist (docs/cis-*-checklist.md) and record every deviation

as a finding with an owner.

Test on a non-production instance first. Several statements (revoking PUBLIC,

requiring TLS) will break clients that relied on the loose defaults — that's the

point, but you want to discover it in staging.