Lion Cipher Lab
Harden a PostgreSQL database on your own machine
- Cyber / Security Engineer
- Intermediate
- ~75-90 min
- Free · no account required
Why it matters
Almost every application you use is really a thin layer in front of a DATABASE -- a program whose whole job is to store and hand back data (accounts, messages, orders, health records) in an organized way. PostgreSQL is one of the most widely used open-source databases in the world, running behind countless companies' apps. Here is the uncomfortable truth about database security: a huge share of real-world data breaches are not clever code exploits at all -- they are databases that were left wide open. A database reachable from the whole internet, protected by a default or blank password, or…
What you'll learn
- what a database server is, and why the DB itself is a security target (not just the app in front of it)
- installing PostgreSQL from nothing on Linux, macOS, Windows, or in a Docker container
- connecting with the psql command-line client and reading who you are connected as
- what a ROLE (user) is in PostgreSQL and how the all-powerful 'postgres' superuser works
- setting strong authentication with scram-sha-256 in pg_hba.conf (and why never 'trust')
- least privilege: revoking the PUBLIC schema's default access and building per-app roles with only the grants they need
- shrinking the network surface with listen_addresses so the DB is not exposed to the whole network
- turning on TLS so connections to the database are encrypted in transit
- turning on logging (log_connections, log_disconnections, failed logins) and what pgAudit adds on top
- TESTING the lockdown by trying an over-privileged action as a limited user and watching it get DENIED
Steps in this lab
- First: what a database is, why it is a target, and the one safety rule
- Get PostgreSQL running (from nothing) -- pick one path
- Open a terminal, connect with psql, and meet the 'postgres' superuser
- Strong authentication: scram-sha-256 in pg_hba.conf (and never 'trust')
- Least privilege: revoke PUBLIC's default access and build a limited app role
- Shrink the network surface: listen_addresses
- Encrypt connections in transit: turn on TLS
- Turn on logging and auditing: log_connections, failed logins, and pgAudit
- PROVE it: try an over-privileged action as the limited role and watch it get denied
- Clean up, make it a habit, and where to take this next