Row-Level Security Hid Rows From the Job That Deletes Them

HMS is a hospital management SaaS I've been building. Many hospitals share one Postgres cluster, and row-level security keeps them apart: every table has a policy that only shows rows where tenant_id matches the tenant set for the current transaction. The API connects as a role that can't bypass that.

Up to that point everything had only ever run on my laptop in Docker. It was well tested. There were hundreds of API tests, a cross-tenant test suite, backup and restore drills, and a retention job for the audit log. Then I built the production setup and rehearsed it locally, set up the way a real server would be. Two of those pieces turned out to be quietly broken.

What the laptop was hiding

Locally, the database owner was the container's postgres user, which is a superuser. Superusers skip row-level security completely, even with FORCE ROW LEVEL SECURITY turned on.

On a server you don't run migrations as a superuser, so I made hms_owner a plain role. Now FORCE applies to it. I checked it with one patient registered: as postgres the count was 1, and as hms_owner it was 0.

That's RLS doing exactly what I asked. The owner never sets a tenant, so the policy matches nothing. The trouble is that some code relied on the owner seeing everything.

The retention job

The audit log is split into monthly partitions. After the retention period, a job archives a month to a file in object storage, checks the archive, and then drops the partition. I built it to be careful. It counts the rows, writes the archive, counts again under a lock, and only drops the partition if everything matches.

I ran it as hms_owner in the server rehearsal. It counted 0 rows, wrote an empty archive, counted 0 again under the lock, and everything matched. The next step would have been dropping the partition.

Every safety check passed because every check was reading through the same filter. Zero equals zero. The job would have deleted a month of the audit log, the one table a hospital needs to prove who looked at a patient's record, and logged it as a success.

The restore drill had the same problem

The backup restore drill compares a fingerprint of the database before and after a restore, using row counts per table. It connected as a role that RLS was filtering too. So it counted 0 on both sides and reported a match. Any restore would have passed, even an empty one.

A check that can't fail isn't a check. I'd been treating these drills as proof that backups worked, and on a server they would have told me that no matter what.

The fix

I split the work by what each role should be doing:

• Reading and inserting rows in the retention and restore paths now runs as hms_platform, a separate role that is allowed to see across tenants. The job switches to it with SET LOCAL ROLE, so it only lasts for that transaction.
• Dropping and attaching partitions stays with hms_owner, since that's DDL and the owner should own it.
• The drill scripts now run psql as the admin user, so their counts are real.

Then I planted a test month and ran it again. It archived 4 rows and restored 4, and the hashes matched. Both restore drills pass from the off-site bucket.

What I'd tell someone else

If you use row-level security, your local setup is probably lying to you. The moment everything runs as a superuser, RLS is off for anything that doesn't go through the app's role, and background jobs are usually the thing that doesn't.

The part that worried me most is how quiet it was. Nothing threw an error. RLS doesn't say "you can't see these rows", it just returns fewer of them. So any job that decides what to do based on a count needs to be tested as the exact role it will run as in production, on a setup that looks like production, before a real hospital's data is in there.