Hospital Patient Records with Row-Level Security

Project roadmap

1. 01

   Design the schema

   ~2h

   Write schema.sql with staff (linked to a database login name, with a role: doctor, nurse or admin), wards, patients (with a ward), doctor_patient assignments, visits (patient, doctor, date and notes) and prescriptions (visit, medication, dose and dates). Write SECURITY.md describing exactly who may read and change what. Done when: the schema loads, and SECURITY.md lists every access rule.

2. 02

   Add fictional sample data and roles

   ~1.5h

   Create PostgreSQL roles (doctor_role, nurse_role, auditor_role and a login for each sample staff member that belongs to the right role), and grant only the table privileges each group needs. Fill the tables with clearly fictional patients, staff, assignments, visits and prescriptions. Done when: the data loads, and each login can connect.

3. 03

   Write row-level security policies

   ~2.5h

   Enable RLS on patients, visits and prescriptions (and FORCE it so table owners are also restricted). Write policies: doctors see and change only rows of patients assigned to them (checking assignments against current_user), nurses see only patients on their ward and can't see prescriptions' notes, and admins manage assignments but not medical data. Use a security definer helper function for the lookup, with a fixed search_path. Done when: SELECT * as each user returns only the rows they're allowed to see.

4. 04

   Record an audit trail

   ~2h

   Create an audit_log table (time, database user, action, table, row id, and the old and new values for changes) that normal roles can insert into only through triggers and can't update or delete. Add triggers for INSERT, UPDATE and DELETE on the sensitive tables. Since PostgreSQL has no SELECT triggers, record reads through a read_patient(id) security definer function that logs and returns the row (or explain the pgAudit extension as the alternative). Done when: every change and every read through the function appears in the log.

5. 05

   Prove the rules work

   ~2h

   Write test_security.sql that switches between users with SET ROLE and checks: each doctor sees only their patients, a doctor can't update another doctor's patient, nurses can't read prescriptions, admins can't read visits, nobody can change the audit log, and bypass attempts (such as a query joining through another table) still reveal nothing. Raise an error on any failure. Done when: all security tests pass.

6. 06

   Build a compliance report

   ~1.5h

   Write queries for auditors: accesses per staff member per day, access to patients outside a staff member's normal assignments (which should be none), unusual activity such as one user reading many records in a short time, and a full access history for one patient. Save them as views that only the auditor role can read. Done when: the reports show the activity from your tests, and only auditors can run them.

7. 07

   Check performance

   ~1h

   RLS policies add conditions to every query. Use EXPLAIN ANALYZE as a doctor to check the policies use indexes (add indexes on the assignment and ward columns if needed), and record the query times with and without RLS in README.md. Done when: README.md shows that policy checks use indexes and the cost is small.

Resources

• DocsPostgreSQL: row security policies
• DocsPostgreSQL: CREATE POLICY
• DocsPostgreSQL: database roles and privileges
• DocsPostgreSQL: trigger functions
• DocspgAudit