🚧 Under construction — VoTechWorks is being built. A free tech program — complimentary, as our commitment to get America working.
voTechWorksGrade 13 · a vocational technology learning center, by urbanicity
newSchoolWorks.com › VoTechWorks › Technology & Design › Data Management / Database Administration › Database Security & Role Management › Lesson 8
TEC-DATA86-05 · Lesson 8 · 12 clock hours · lab

Integrated Security Lab: Harden a Production-Like Database

Objective. Learners independently apply all course security controls — authentication, RBAC, SSL, encryption, and auditing — to a preconfigured insecure PostgreSQL instance, producing a hardened database and a completed CIS Benchmark checklist.

⚠ Pending professional review

This lesson, its worked examples and its practice questions has not yet been verified by an industry-certified professional or by a certification or approval body. It is a study draft and may contain errors. Do not use it as instructions for work on a job site or on live equipment. Always follow your instructor, your supervising professional, and current official references and regulations.

VoTechWorks program approval and accreditation are still in progress. Credentials are issued only by the certifying bodies named in each program, after their own exams.

Integrated Security Lab: Harden a Production-Like Database

This lab brings together every security control covered in the course — authentication, role-based access control (RBAC), SSL/TLS, encryption, and auditing — and applies them to a single, intentionally misconfigured PostgreSQL 16 instance. Completing this lab gives you a concrete artifact (a hardened database and a signed checklist) that demonstrates job-ready skills recognized by the PostgreSQL CE (EDB), Oracle DBA CA, Microsoft DP-300, and DAMA CDMP frameworks.

Why a Hardening Lab Matters

Real production databases are rarely deployed in a perfectly secure state. Licensing constraints, legacy configurations, rushed deployments, and operator error leave gaps. A database administrator must be able to identify those gaps using an authoritative benchmark and close them systematically. The Center for Internet Security (CIS) PostgreSQL Benchmark provides exactly that: a prioritized, evidence-based checklist that maps each item to a specific risk. Working through it item by item is standard professional practice.

Step 1 — Assess the Insecure Baseline

When you first log into the lab VM, the PostgreSQL instance will have several deliberate misconfigurations:

Before making any change, record the current state of each setting. Use `SHOW ssl;`, `\du` in psql to list roles and their attributes, and `cat /etc/postgresql/16/main/pg_hba.conf` to capture the authentication rules. This before-state is the evidence baseline for your Hardening Summary Report.

Step 2 — Fix Authentication (pg_hba.conf)

Replace every `trust` entry with `scram-sha-256`, which is the strongest built-in password hashing method PostgreSQL 16 supports. Update `postgresql.conf` to set `password_encryption = scram-sha-256`. After editing, reload the configuration with `SELECT pg_reload_conf();` or `pg_ctlcluster 16 main reload`. Verify by attempting to connect from a remote address without credentials — the connection must be refused.

Step 3 — Enable SSL/TLS

Generate or install a server certificate and key using OpenSSL. Set `ssl = on` and point `ssl_cert_file` and `ssl_key_file` to the correct paths. Restrict the key file permissions so only the postgres OS user can read it (`chmod 600`). Update `pg_hba.conf` to require SSL for remote connections using the `hostssl` record type and remove plain `host` entries for untrusted networks. Confirm with `SHOW ssl;` returning `on` and by attempting an unencrypted connection, which must fail or be rejected.

Safety note: File permission errors on the SSL key will prevent PostgreSQL from starting. Always test a reload or restart in a maintenance window on production systems and keep a console session open so you are not locked out.

Step 4 — Enforce Least-Privilege RBAC

Review all roles with `\du`. Revoke `SUPERUSER`, `CREATEROLE`, and `CREATEDB` from any application or reporting role that does not need them. Use `REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;` to eliminate the default public access that PostgreSQL historically granted. Create purpose-specific roles (for example, `app_readonly`, `app_writer`, `etl_loader`) and grant only the minimum privileges each needs. Verify with `\dp tablename` that permission sets match the intended design.

Step 5 — Enable Auditing with pgaudit

Install the pgaudit extension and add it to `shared_preload_libraries` in `postgresql.conf`. After restarting, run `CREATE EXTENSION pgaudit;`. Configure `pgaudit.log = 'ddl, role, connection, object'` to capture the categories relevant to a hardening audit. Check `postgresql.log` (or your configured log destination) to confirm that connection attempts, role changes, and DDL statements are appearing. Without audit logs, you cannot prove that attacks were detected — this is a core DAMA data governance control as well.

Step 6 — Encrypt the Sensitive Column

Using `pgcrypto`, convert the plaintext sensitive column to an encrypted form. A typical pattern uses `pgp_sym_encrypt(plaintext, key)` for storage and `pgp_sym_decrypt(ciphertext, key)` for retrieval. In this lab, you will apply the function to the existing column data and alter the column type to `bytea`. Document the encryption key management approach — even in a lab, note that in production the key must never be stored in the same database.

Step 7 — Run the Penetration Simulation

Your instructor (or a designated peer following a script) will attempt five attacks:

1. Unauthenticated remote connection — must be blocked by `scram-sha-256` and firewall/pg_hba rules.

2. SQL injection probe on a test endpoint — must be logged by pgaudit and blocked by parameterized query design or restricted role privilege.

3. Direct file read of `pg_hba.conf` as a low-privilege OS user — must fail because the file is owned by `postgres` and not world-readable.

4. Privilege escalation via `SET ROLE` — must fail because the attacker's session role has no granted path to a privileged role.

5. Unencrypted connection attempt — must be rejected by `hostssl` rules.

For each attack, capture the error message or log entry. A score of 5 of 5 blocked or logged is required to pass.

Writing the Hardening Summary Report

Your two-page report must list each configuration change made, the CIS Benchmark item number it satisfies, and at least one piece of evidence — a command output snippet or a log line — confirming the control is active. Use the provided template. Organize the report by control category: Authentication, Transport Security, Authorization, Auditing, Encryption. Your instructor will sign the completed CIS checklist only after reviewing the report and witnessing or reviewing the penetration simulation results.

Professionalism and Safety

Always work on the designated lab VM — never on a shared or production system. Confirm with your instructor before making any change outside the lab environment. Keep notes of every command you run; in real DBA work, undocumented changes are a compliance liability.

Key terms8

CIS BenchmarkA set of consensus-based, evidence-backed configuration guidelines published by the Center for Internet Security for a specific technology (such as PostgreSQL) that organizations use to harden systems against known attack vectors.
scram-sha-256The Salted Challenge Response Authentication Mechanism using SHA-256, the strongest built-in client authentication method supported by PostgreSQL 16; it prevents password sniffing and replay attacks.
pg_hba.confThe PostgreSQL host-based authentication configuration file that controls which hosts, users, and databases may connect and what authentication method is required for each combination.
pgauditAn open-source PostgreSQL extension that provides detailed session and object audit logging, recording events such as DDL statements, role changes, and connection attempts to the server log.
pgcryptoA PostgreSQL extension that provides cryptographic functions including symmetric and asymmetric encryption, hashing, and random data generation; used to encrypt sensitive column values at the database layer.
Least PrivilegeA security principle requiring that every user, role, or process is granted only the minimum permissions necessary to perform its intended function, reducing the blast radius of a compromise.
hostsslA record type in pg_hba.conf that accepts only TCP/IP connections that have been established with SSL/TLS encryption, rejecting plaintext connections from the matching host and user.
Privilege EscalationAn attack in which a user with limited permissions gains higher-level access than authorized, often by exploiting misconfigured role grants, SET ROLE paths, or SUPERUSER attributes.
In class: Learners receive an intentionally misconfigured PostgreSQL VM (trust authentication for all, no SSL, no audit logging, overprivileged roles, plaintext sensitive column) and a CIS PostgreSQL Benchmark checklist; they work through each checklist item, applying the fix and documenting the before/after configuration change. · Penetration simulation: instructor (or a designated peer) attempts five scripted attacks against the hardened instance (unauthenticated remote connection, SQL injection probe on a test endpoint, direct file read of pg_hba.conf as a low-privilege OS user, privilege escalation via SET ROLE, unencrypted connection attempt); learner must show that all five are blocked or logged. · Learner writes a two-page Hardening Summary Report listing each change made, the CIS checklist item it satisfies, and evidence (command output or log snippet) confirming the control is active.
Materials: Linux VM with intentionally misconfigured PostgreSQL 16 (provided by instructor), CIS PostgreSQL Benchmark (cisecurity.org), pgaudit, OpenSSL, pgcrypto, Hardening Summary Report template
Check: All five penetration simulation attacks must be blocked or captured in audit log (pass = 5 of 5); CIS checklist must have at least 90% of applicable items checked with evidence; Hardening Summary Report must reference each major control implemented. Instructor signs off checklist.

Practice check8

Answer each question, then check it. Nothing is recorded; this is your own practice.

PostgreSQL CE (EDB) — Server Configuration & Authentication
1. After editing pg_hba.conf to use scram-sha-256, which PostgreSQL command reloads the configuration without restarting the server?
PostgreSQL CE (EDB) — Security Hardening
2. A low-privilege OS user attempts to read /etc/postgresql/16/main/pg_hba.conf and receives 'Permission denied.' Which file system control produced this result?
Microsoft DP-300 — Implement a Secure Environment
3. Which pg_hba.conf record type ensures that remote client connections are accepted ONLY when SSL/TLS encryption is in use?
PostgreSQL CE (EDB) — Role Management
4. A database has a role called 'report_user' that was inadvertently granted SUPERUSER. Which SQL statement correctly removes that attribute?
DAMA CDMP — Data Security
5. The DAMA CDMP framework categorizes audit logging primarily under which data management knowledge area?
PostgreSQL CE (EDB) — Data Encryption
6. A learner wants to encrypt a column named ssn (text) using pgcrypto symmetric encryption. Which function stores the encrypted value?
PostgreSQL CE (EDB) — RBAC & Privilege Management
7. During the penetration simulation, the attacker attempts SET ROLE dba_superrole in a low-privilege session and receives an error. What configuration most directly caused this result?
DAMA CDMP — Data Security / Oracle DBA CA — Security Management
8. The CIS Benchmark scoring methodology distinguishes between 'Level 1' and 'Level 2' profile recommendations. What does a Level 1 recommendation generally indicate?
Record this lesson on my transcript. Sign in with your key and enrollment email to record lessons (enroll free if you have no key). This page stays open to read and practise.
← Previous lessonProgram plan