Defense in Depth: Remove One Layer, the Next Still Holds

No single control is perfect. A validator has a gap, a developer writes one query with an f-string, a grant gets widened during an incident. Defense in depth stacks independent controls so that any one of them failing doesn’t decide the outcome. This lesson puts three layers in front of a PostgreSQL table holding a (fake) API key, attacks it with one SQL injection string, and removes the layers one at a time. The injection only ever targets the lesson’s own throwaway database. Tested with PostgreSQL 17.11, Python 3.12.15 and psycopg 3.3.6.

The lab: a products table next to a secrets table

The application only ever reads products. The same database also holds api_keys, and the application connects as shop_app, a role granted SELECT on products and nothing else.

-- init.sql
CREATE TABLE products (id serial PRIMARY KEY, name text NOT NULL);
INSERT INTO products (name) VALUES ('lamp'), ('desk'), ('chair');

CREATE TABLE api_keys (owner text, key text);
INSERT INTO api_keys VALUES ('billing', 'sk_test_FAKE_0000_not_a_real_key');

CREATE ROLE shop_app LOGIN PASSWORD 'FAKE-app-password';
GRANT SELECT ON products TO shop_app;

Start PostgreSQL with that script, and build a small image for the Python side:

FROM python:3.12-slim
RUN pip install --no-cache-dir "psycopg[binary]"
WORKDIR /work

docker network create did-lab
docker run -d --name db --network did-lab -e POSTGRES_PASSWORD=FAKE-dev-only-password \
  -v "$PWD/init.sql":/docker-entrypoint-initdb.d/init.sql postgres:17
docker build -t did-app .

Every python command below runs in that image on the same network: docker run --rm --network did-lab -v "$PWD":/work did-app python search.py ....

One search function, three switchable layers

Each layer can be turned off with a flag, so you can see exactly which one stops the attack.

# search.py
import re, sys, psycopg

VALIDATE = "--no-validate" not in sys.argv        # layer 1
PARAMS = "--no-params" not in sys.argv            # layer 2
ROLE = "postgres" if "--as-owner" in sys.argv else "shop_app"   # layer 3
PASSWORD = {"postgres": "FAKE-dev-only-password", "shop_app": "FAKE-app-password"}

def search(conn, term):
    if VALIDATE and not re.fullmatch(r"[a-z ]{1,30}", term):
        return "REJECTED by input validation"
    if PARAMS:
        sql, args = "SELECT name FROM products WHERE name = %s", (term,)
    else:
        sql, args = f"SELECT name FROM products WHERE name = '{term}'", None
    try:
        return [row[0] for row in conn.execute(sql, args)]
    except psycopg.Error as e:
        return f"{type(e).__name__}: {str(e).strip()}"

attack = "x' UNION SELECT key FROM api_keys --"
with psycopg.connect(host="db", dbname="postgres", user=ROLE, password=PASSWORD[ROLE]) as conn:
    print(f"validate={VALIDATE} params={PARAMS} role={ROLE}")
    print("  lamp   ->", search(conn, "lamp"))
    print("  attack ->", search(conn, attack))

With every layer removed, the secret leaks

First, the system as it often starts: no validation, a string-built query, and the connection made as the superuser that owns everything.

$ python search.py --no-validate --no-params --as-owner
validate=False params=False role=postgres
  lamp   -> ['lamp']
  attack -> ['sk_test_FAKE_0000_not_a_real_key']

The search for lamp works, so a functional test passes. The attack string closes the quoted literal with ', appends UNION SELECT key FROM api_keys, and comments out the trailing quote with --. The database runs the combined query and returns the API key as if it were a product name. Nothing stood between the input and the secret.

All three layers on: stopped at the first

Now the intended configuration: all layers on.

$ python search.py
validate=True params=True role=shop_app
  lamp   -> ['lamp']
  attack -> REJECTED by input validation

Product names are lowercase letters and spaces, so the allowlist regex rejects anything containing ' before a query is built. If that were the only layer, the system’s safety would rest on one regex being right forever. The rest of the lesson asks what happens when it isn’t.

Remove validation: the parameterized query holds

Suppose someone relaxes the validator to allow apostrophes (“O’Brien lamp” is a real product name) and gets the regex wrong.

$ python search.py --no-validate
validate=False params=True role=shop_app
  lamp   -> ['lamp']
  attack -> []

The attack string reaches the database, but as a value, not as SQL. The query searched for a product literally named x' UNION SELECT key FROM api_keys --, found none, and returned an empty list. With %s placeholders, psycopg sends the SQL text and the parameters separately, so no input can change the structure of the query. This is the layer that should do the real work.

Remove parameterization too: least privilege holds

Now a second failure on top of the first: one code path builds its SQL with an f-string.

$ python search.py --no-validate --no-params
validate=False params=False role=shop_app
  lamp   -> ['lamp']
  attack -> InsufficientPrivilege: permission denied for table api_keys

The injection worked: the server received and parsed the UNION SELECT from api_keys as SQL. What stopped it is that shop_app was never granted access to that table, so PostgreSQL’s permission check refused the query before it read a row. Compare this with the first run, which used the same two broken layers but connected as the owner. The difference is one GRANT statement. This also turns an invisible breach into a loud error that an alert can fire on. Grants, column privileges and row-level security are covered in depth in SQL Security.

Each layer stopped the attack on its own, and each had a realistic way to fail. That’s the point of defense in depth. To leak the key, all three had to fail at once. Clean up with docker rm -f db && docker network rm did-lab.

Practice

1. A destructive payload. With layers 1 and 2 still removed, try x'; DELETE FROM products; -- as shop_app. Does least privilege stop writes as well as reads? Count the products afterwards.

# destructive.py
import psycopg

term = "x'; DELETE FROM products; --"
sql = f"SELECT name FROM products WHERE name = '{term}'"      # layers 1 and 2 removed

with psycopg.connect(host="db", dbname="postgres",
                     user="shop_app", password="FAKE-app-password") as conn:
    try:
        conn.execute(sql)
    except psycopg.Error as e:
        print(f"{type(e).__name__}: {str(e).strip()}")
        conn.rollback()
    print("products left:", conn.execute("SELECT count(*) FROM products").fetchone()[0])

$ python destructive.py
InsufficientPrivilege: permission denied for table products
products left: 3

2. Turn least privilege into a test. Write a script that reads shop_app’s table grants from information_schema.role_table_grants, compares them with the expected set, and exits non-zero on any difference.

# grants_check.py
import sys, psycopg

EXPECTED = {("products", "SELECT")}

with psycopg.connect(host="db", dbname="postgres",
                     user="postgres", password="FAKE-dev-only-password") as conn:
    rows = conn.execute(
        "SELECT table_name, privilege_type FROM information_schema.role_table_grants"
        " WHERE grantee = 'shop_app'").fetchall()

actual = set(rows)
for table, priv in sorted(actual - EXPECTED):
    print(f"EXCESS  shop_app has {priv} on {table}")
for table, priv in sorted(EXPECTED - actual):
    print(f"MISSING shop_app lacks {priv} on {table}")
print("grants OK" if actual == EXPECTED else "grants DRIFTED")
sys.exit(actual != EXPECTED)

$ python grants_check.py; echo "exit=$?"
grants OK
exit=0

3. The convenience grant. During an incident someone runs GRANT SELECT ON ALL TABLES IN SCHEMA public TO shop_app “to unblock things”. Rerun the two-layers-removed attack and the grants check, then revoke the excess.

$ docker exec db psql -U postgres -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO shop_app"
GRANT
$ python search.py --no-validate --no-params
validate=False params=False role=shop_app
  lamp   -> ['lamp']
  attack -> ['sk_test_FAKE_0000_not_a_real_key']
$ python grants_check.py; echo "exit=$?"
EXCESS  shop_app has SELECT on api_keys
grants DRIFTED
exit=1
$ docker exec db psql -U postgres -c "REVOKE SELECT ON api_keys FROM shop_app"
REVOKE
$ python grants_check.py; echo "exit=$?"
grants OK
exit=0