CASE STUDY · SECURITY
Postgres, REST API, permissions
Closed twice
Production database · A secret column, readable from outside
Result: closed at the table level, then granted back column by column
01 / THE PROBLEM
A secret column, readable by anyone through the REST API.
The database exposes a REST API. Bot protection sat in front of it. The anonymous role could read the column anyway, and so could the authenticated role.
02 / THE CONSTRAINT
Protection in front of an API does not protect the database behind it.
The bot filter was doing its job, at the wrong floor. The real permission lives in Postgres, and Postgres was answering yes. The first fix failed silently: a table-level GRANT cancels a REVOKE placed on a column. The command succeeds and changes nothing.
03 / WHAT I CHOSE
Take the whole table away, then give it back one column at a time.
It is longer to write and longer to read back, but it is the only shape where the permission actually says what it looks like it says. Anything not named explicitly stays shut.
04 / WHAT IT DOES TODAY
The same query, under each role, before and after.
- Anonymous rolecould read the columnaccess denied
- Authenticated rolecould read the columnaccess denied
- Bot protectionbypassed through the APIstill there, no longer alone
05 / WHAT I WOULD CHANGE
I would not let an automated fixer near permissions again.
The leak was closed twice. In between, an automated scanner "fixed" it in the wrong direction and reopened it. A permission is not a style problem to be corrected on the fly. What I would add: a test that replays the query under each role and fails if it answers, so the next regression is seen rather than assumed away.
A problem like this at your place? Describe it.