Lab: attack your own database

Before you start

This lab changes table ownership, so run it in a separate database loaded with the shop dataset, not the one you use for the other topics:

createdb sec
psql -d sec -f shop.sql

Connect to sec as a superuser and create three roles: an owner nobody logs in as, the application with ordinary grants, and a careless application login that is a member of the owner role.

CREATE ROLE shop_owner NOLOGIN;
ALTER TABLE customers   OWNER TO shop_owner;
ALTER TABLE orders      OWNER TO shop_owner;
ALTER TABLE products    OWNER TO shop_owner;
ALTER TABLE order_items OWNER TO shop_owner;

CREATE ROLE app LOGIN;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers, orders, order_items, products TO app;

CREATE ROLE app_owner LOGIN IN ROLE shop_owner;

Results below are from PostgreSQL 18.3. On a local install these logins need no password; on anything shared, give them one.

1. What a fresh role can do

Connect as app (psql -d sec -U app) and check what it got without being granted anything beyond the table privileges:

SELECT has_database_privilege('sec', 'TEMP')        AS temp_tables,
       has_schema_privilege('public', 'CREATE')     AS create_in_public;
SELECT count(*) AS functions_app_can_execute
FROM pg_proc WHERE has_function_privilege(oid, 'EXECUTE');

Remember the first line. It is all step 3 needs.

2. Injection, as owner and as app

The endpoint glues the user’s input into SELECT id, name FROM customers WHERE email = '<input>'. Paste what it would send, first as app_owner, then as app, with each malicious input:

-- input: x'; DROP TABLE order_items; --
SELECT id, name FROM customers WHERE email = 'x'; DROP TABLE order_items; --';

-- input: x' OR true --
SELECT id, name FROM customers WHERE email = 'x' OR true --';

If you dropped order_items, reload the dataset (psql -d sec -f shop.sql) and redo the ownership statements from the setup.

Now send the same inputs as parameters:

SELECT id, name FROM customers WHERE email = $1 \bind 'x'' OR true --' \g
SELECT id, name FROM customers WHERE email = $1 \bind 'x''; DROP TABLE orders; --' \g

Both return 0 rows and drop nothing: the input arrived as a value to compare with.

3. Hijack a SECURITY DEFINER function

Finance wants some users to approve refunds without giving them UPDATE on orders. As the superuser, create a list of approvers and a function, owned by shop_owner, that checks the list and then cancels the order:

CREATE TABLE refund_approvers (username text PRIMARY KEY);
INSERT INTO refund_approvers VALUES ('finance_lead');
ALTER TABLE refund_approvers OWNER TO shop_owner;

CREATE FUNCTION approve_refund(p_order int) RETURNS text
LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM refund_approvers WHERE username = session_user) THEN
    RAISE EXCEPTION '% may not approve refunds', session_user;
  END IF;
  UPDATE orders SET status = 'cancelled' WHERE id = p_order;
  RETURN 'refund approved for order ' || p_order;
END $$;
ALTER FUNCTION approve_refund(int) OWNER TO shop_owner;

As app, try it:

SELECT approve_refund(101);

ERROR:  app may not approve refunds

Now attack. The function says refund_approvers without a schema, and the temporary schema is searched first for table names unless the search path says otherwise:

CREATE TEMP TABLE refund_approvers (username text);
INSERT INTO refund_approvers VALUES ('app');
GRANT SELECT ON refund_approvers TO PUBLIC;   -- so the function's owner can read it
SELECT approve_refund(101);

        approve_refund
-------------------------------
 refund approved for order 101

Order 101 is now cancelled. The function ran with shop_owner’s privileges and checked the attacker’s list. Fix it as the superuser, with the pattern the documentation gives: trusted schemas first, pg_temp last.

UPDATE orders SET status = 'shipped' WHERE id = 101;
ALTER FUNCTION approve_refund(int) SET search_path = public, pg_temp;

Repeat the attack in a new app session. It fails with app may not approve refunds, and order 101 stays shipped.

4. Row-level security and who it ignores

As the superuser, make a table shared by three tenants, give it to shop_owner, and turn on a policy that shows each session only its own tenant’s rows:

CREATE TABLE invoices (id serial PRIMARY KEY, tenant_id int NOT NULL, amount numeric NOT NULL);
INSERT INTO invoices (tenant_id, amount) SELECT 1 + g % 3, g * 10 FROM generate_series(1, 9) g;
ALTER TABLE invoices OWNER TO shop_owner;
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app;

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::int);

Run this as app, as app_owner, and as the superuser:

SET app.tenant_id = '2';
SELECT tenant_id, count(*) FROM invoices GROUP BY 1 ORDER BY 1;

Then, as the superuser, ALTER TABLE invoices FORCE ROW LEVEL SECURITY; and run it again for each login.

Nothing warned that the policy was being ignored. The owner skips row security unless the table is forced; superusers and roles with BYPASSRLS skip it always, FORCE or not. An application that connects as either sees every tenant.

What you found

• Least privilege turned DROP TABLE into an error, but the OR true injection read every customer either way. Parameters stopped both.
• One temporary table was enough to make a privileged function trust the attacker. SET search_path = ..., pg_temp closed it.
• Row-level security silently did nothing for the owner until FORCE, and nothing for superusers at all.

Clean up

dropdb sec

DROP ROLE app, app_owner, shop_owner;

Check yourself

1. Why did the temporary table work as an attack, when app cannot create anything in public?

   Show answer

   Every role can create temporary tables by default, and the temporary schema is searched first for table names unless search_path lists it explicitly. The function’s unqualified refund_approvers found the attacker’s temporary table before the real one.

   I could explain this without looking

2. Why does SET search_path = public, pg_temp on the function fix it?

   Show answer

   It fixes the search path for the function’s whole execution, whatever the caller has set, and puts the temporary schema last, so the real public.refund_approvers is found first.

   I could explain this without looking

3. A team enables row-level security and their tests pass, but in production every tenant sees every invoice. Name two likely causes from this lab.

   Show answer

   The application connects as the table’s owner (or a member of the owning role) and the table is not FORCEd; or it connects as a superuser or a role with BYPASSRLS, which no setting on the table can override.

   I could explain this without looking