Search the statements the database saw#
pg_stat_statements keeps one row per normalized statement. It must be in shared_preload_libraries, which needs a restart, and CREATE EXTENSION pg_stat_statements in the database. We ran five queries from the application role on PostgreSQL 16.15, then searched for the column name as a whole word, in any case:
SELECT calls, query FROM pg_stat_statements
WHERE query ILIKE '%orders%' AND query ~* '\mstatus\M'
ORDER BY calls DESC;
| calls | query |
|---|---|
| 1 | SELECT id FROM orders WHERE STATUS = $1 LIMIT $2 |
| 1 | SELECT id, status FROM orders WHERE customer_id = $1 |
| 1 | SELECT count(*) FROM orders WHERE "status" = $1 |
The search handled upper case and a quoted identifier. It did not list SELECT * FROM orders WHERE id = $1, which returns status to the application as much as the second query does. An ORM that loads whole rows looks like this, and so does a report that exports a table. The search also sees only statements run since the last reset, on this server: a monthly job, a replica's readers and statements evicted by pg_stat_statements.max are missing.
Make the column unreadable in staging#
Column privileges turn every read of the column into an error, including the ones a text search cannot see. Take away the table-level grant and grant the remaining columns:
REVOKE SELECT, INSERT, UPDATE ON orders FROM orders_app;
GRANT SELECT (id, customer_id, state),
INSERT (id, customer_id, state),
UPDATE (state) ON orders TO orders_app;
Statement from orders_app | Result |
|---|---|
SELECT id, state FROM orders WHERE id = 4 | returns the row |
SELECT * FROM orders WHERE id = 3 | permission denied for table orders |
SELECT id, status FROM orders WHERE customer_id = 7 | permission denied for table orders |
UPDATE orders SET status = 'shipped' WHERE id = 5 | permission denied for table orders |
UPDATE orders SET state = 'PAID' WHERE status = 'paid' AND id = 6 | permission denied for table orders |
INSERT INTO orders (id, customer_id, state) VALUES (...) | inserted |
A WHERE clause that mentions status needs SELECT on it too, so the last update fails as well. The GRANT documentation explains that a table-level grant covers every column, which is why it has to go. The error names the table, not the column; the statement in the application's log shows which query hit it.
Where to run it#
Run it in staging with the application's real role and the previous release's integration tests, background jobs and reports. A test suite that passes has no reader of status on the paths it covers. Do not run it in production as a probe: every reader it finds is a failed request.
Triggers keep working where they use the row they were given. A BEFORE trigger that reads NEW.status ran for orders_app without a privilege error, because reading NEW is not a query. A trigger function that runs its own SELECT status FROM orders failed with permission denied, since it runs with the privileges of the role that fired it unless it is SECURITY DEFINER. So the grants test the application's queries, not the sync trigger; drop that in the release that stops writing status, as decide when to stop writing the old column describes.
The check before the drop#
Pass when the previous release's tests and jobs run with the column grants above and nothing reports permission denied, and when the statement search, run over a full business cycle, finds no reader. Tag PostgreSQL connections with the release that opened them covers the writers.