Why should you care?

PostgresVerse clinic app must audit every access to diagnosis column and block nurse from billing tables.

See it live — copy this example

Run in pgAdmin or psql.

CREATE TABLE patients (
  patient_id bigserial PRIMARY KEY,
  mrn text UNIQUE NOT NULL,
  full_name text NOT NULL,
  diagnosis text
);
CREATE TABLE access_log (
  log_id bigserial PRIMARY KEY,
  patient_id bigint,
  accessed_by text DEFAULT current_user,
  accessed_at timestamptz DEFAULT now()
);

What happened?

• mrn is medical record number.
• diagnosis is PHI.
• access_log append-only populated by trigger on SELECT sensitive view or application audit.
• Roles GRANT minimal.

Practice next

1. Create patients and access_log.
2. CREATE ROLE nurse LOGIN; GRANT SELECT (patient_id, mrn, full_name) only.
3. Login as nurse; SELECT diagnosis denied.
4. RLS policy provider sees only assigned patients.
5. pgcrypto encrypt diagnosis column; view decrypts for doctor role only.

Remember

Column-level GRANT hides PHI from some roles. Audit log who touched which patient. Encrypt backup and enable SSL.

PostgresVerse clinic pilot

Regional clinic runs appointments on PostgresVerse; audit passes state review.

Outcome: Insurance partner approves data handling checklist.