The Column List Matters as Much as the Rule: Row Policies Do Not Constrain Which Fields a User Can Write

Mostafa Dhouib·11 min read

The Column List Matters as Much as the Rule: Row Policies Do Not Constrain Which Fields a User Can Write

The short answer. Row-level security decides which rows a statement may see or produce. It says nothing at all about which columns a write may touch. So a policy permitting a user to update their own row is exactly right for a display name and catastrophic for a role, a plan tier, a credit balance or a verification flag, because all of those live in the same row. The user updates their own record, which the policy allows, and sets themselves to administrator on an unlimited plan. The rule was correct. The column list was never specified. The controls that fix it are column-level grants, which default to denying a newly added column, and a schema split that puts user-editable fields and system-owned fields in different tables.

A members table. A policy that reads, in effect, a user may update the row whose identifier matches their session identity. Reviewed, approved, obviously correct.

The table has a display name, an avatar, a biography and a locale. It also has a role, a plan tier, a remaining credit count, a verification flag, an organisation identifier and a trial end date, because that is what happens to a members table over eighteen months.

Every one of those is in the row the user is allowed to update.

Row-level security answers a different question

Worth being precise about the mechanism, because the gap is in the semantics rather than in anybody's SQL.

A policy has two predicates. The using expression decides which existing rows the statement may see for the purposes of the operation. The with check expression decides which resulting rows are permitted to exist after the write. Both are evaluated per row, over the row as a whole.

Neither has a notion of columns. There is no using clause that means these four fields and not those six. So for update using (id = current_setting('app.user_id')::uuid) authorises a statement to rewrite every column of the matching row, and the policy is doing exactly what it says.

You can get partway with with check, because it constrains the resulting row. A check that the resulting row still has the member role rejects a write that promotes to administrator. The technique is brittle: it hard-codes a value, so it breaks for users who legitimately hold a different role. What you want to express is that the column's value must be unchanged, and with check sees the new row rather than the old one, so it cannot say that without a self-referencing subquery that is itself evaluated under the policy. At that point you are writing something hard to read and easy to get subtly wrong.

The conclusion is simply that row-level security is the wrong layer for a column question. Use the layer that has columns in it.

Self-escalation is a class, not a bug

The reason this is worth a taxonomy rather than a fix is that it recurs. Six categories of column that must never be writable from a user's session.

Authorisation. Role, permission set, administrator flags, group membership. The direct version: a user grants themselves power.

Entitlement. Plan tier, seat count, credit or token balance, quota, trial end date, feature flags. Nobody breaks in, they just stop paying while keeping everything.

Verification and trust. Verified flags, identity-check status, suspension or ban flags, reputation scores. A user who can set their own verified flag defeats whatever that flag gates, which is often the thing other users rely on.

Identity linkage. The owning user, organisation, tenant or account identifier. This one is different in kind and gets missed most often. A user who can change the tenant identifier on their own row is not escalating their privileges, they are relocating a record into somebody else's tenancy, or claiming membership of it. Row-level security is explicitly satisfied: before the write the row is theirs, and after the write the row may still pass the check, depending on how the check is written.

Financial. Balance, amount, price, discount, fee, applied promotion.

Audit. Creation timestamp, created-by, updated-by, any signature or hash column. These are the ones that make an incident investigable, so they are also the ones worth corrupting.

The test that covers all six without memorising them: if a column's value changes what the system will permit, or what somebody is owed, it is system-owned and a user's session must have no write path to it.

Column-level grants

Postgres privileges operate per column for select, insert, update and references. That is the mechanism, and it is the one that fits.

revoke update on public.members from app_user;

grant update (display_name, avatar_url, bio, locale)
  on public.members to app_user;

After that, a statement attempting to set role fails with a permission error no matter what any policy says, because privilege checks and policy checks are separate gates and both must pass.

Three properties make this the right default rather than merely an available option.

It fails loudly. A permission error is a stack trace, a log line and a bug report. Silent filtering is none of those.

It is a whitelist. Add a column next quarter and it is not writable by that role until somebody grants it. Compare with row-level security, where a new column is immediately writable by everyone the update policy admits. The direction of the default is the whole argument.

It is enforced by the privilege system, which means it applies regardless of how the statement reached the database, including through a view in invoker mode, an ad-hoc query or a generated API layer.

Four gotchas, all of which have bitten somebody.

Privileges are checked against the columns the statement mentions, so a statement listing role is rejected even when it sets it to the value it already has. Clients that send the whole object back on every save will break, which is a prompt to fix the client rather than the grant.

Referencing a column in a where clause requires select privilege on that column, so narrowing select grants has effects on queries that never return the column.

A table-level grant update or a grant all erases the narrowing in one line, and migration tooling and convenience scripts do this constantly. That is why the audit below has to be a test rather than a one-time cleanup.

The table owner is unaffected, exactly as with row-level security. If your application connects as the owner, none of this applies to it.

Better still, split the table

The structural version of the same control. Two tables with the same primary key: one holding fields the user may edit, one holding fields only the system writes.

The user-editable table gets an update policy and column grants. The system-owned table gets no update grant to the user role at all, select only, and its writes come from elsewhere.

What you buy is that the boundary becomes visible in the schema instead of living in a grant list nobody reads. A developer adding a column has to choose a table, which forces the classification at the moment of design rather than at the moment of audit. And the sensitive table can have a completely different policy shape, including no user-reachable write path whatsoever.

What it costs is a join and a second row to create at signup. That is the entire cost, and it is low enough that for a table holding both kinds of field, I would default to splitting it.

Where writes to system-owned columns come from

Three legitimate sources, all auditable and none reachable from a user's session.

A server-side process holding elevated credentials, such as the handler that sets the plan tier after a payment event arrives. A database function with definer semantics and a deliberately narrow signature, so the only way to change the column is to call something with a name and arguments you can review. Or a trigger that recomputes a derived value from facts the user cannot forge.

A cheap belt-and-braces measure is also worth knowing. A before-update trigger that restores system-owned columns to their previous values survives a careless grant. Prefer raising an exception to restoring silently, because a silent restore lets a client believe it changed something that never changed.

How to audit for it

Four steps, the last of which is the one that keeps working after everyone forgets this article.

List the update privileges per column.

select table_name, grantee, column_name
from information_schema.column_privileges
where table_schema = 'public'
  and privilege_type = 'UPDATE'
order by 1, 2, 3;

Compare against table-level grants. If a role holds update at the table level, every column is writable and the per-column listing is noise.

Classify every column of every table that has an update policy against the six categories above. The output is a list of columns that are not plainly user-editable, and that list is the finding.

Write one failing test per sensitive column. As an ordinary user, attempt to set it, then assert two things: that the statement raised a permission error, and that the stored value is unchanged. Both assertions, because a trigger may silently ignore the write and a test that checks only the stored value cannot distinguish rejected from ignored. You want to know which one you have.

Add a drift test. Enumerate the columns the user role may update and compare the list against an allow list committed to the repository. A newly added column fails the test until somebody classifies it and updates the allow list deliberately. This converts a recurring review into a build failure, which is the only form of control that survives staff turnover.

The honest limit

None of the database mechanisms help if your application writes through a privileged connection that owns the tables, which is an extremely common architecture. There the control has to be an explicit field allow list in the application's write path, which is code, and therefore testable but not enforced. That is the same defect one layer up, where it is usually called mass assignment, and the audit is the same: enumerate the columns the request handler can set, classify them, and test that the others are rejected.

Column grants are also coarse. They express whether a column may be written, not under what conditions. They cannot say that a plan tier may only move downward, or that a verification flag may be set only when a valid token is presented. Those need a function with the rule inside it, and the grant then applies to the function rather than to the column.

And the specific mechanisms here are Postgres ones. The class of defect is universal and the layer that fixes it will not be, so the transferable part is the taxonomy and the drift test rather than the SQL.

FAQ

Does row-level security stop a user editing fields they should not? No. Policies are evaluated per row and have no notion of columns, so a policy permitting a user to update their own row authorises a rewrite of every column in that row, including role, plan tier, credit balance and verification flags. The rule is about which rows. The damage comes from which columns, and that has to be controlled separately.

Which columns must never be writable from a user session? Six categories: authorisation such as role and administrator flags, entitlement such as plan tier and credit balance, verification and trust flags, identity linkage such as tenant or owner identifiers, financial amounts, and audit fields. The single test that covers them is whether the column's value changes what the system will permit or what somebody is owed.

How do column-level grants work in Postgres? Revoke update on the table from the user role, then grant update on a named list of columns. Privilege checks and policy checks are separate gates and both must pass, so a statement mentioning a non-granted column fails with a permission error whatever the policy says. Crucially, a column added later is not writable until somebody grants it.

Why is changing a tenant or owner identifier dangerous? Because it is not privilege escalation, it is record relocation, which is why it gets missed. The row belongs to the user before the write, so the policy is satisfied, and after the write the record sits inside somebody else's tenancy or claims membership of it. Treat every ownership and linkage column as system-owned and unwritable from a session.

Is splitting the table better than column grants? For a table holding both user-editable and system-owned fields, usually yes. Two tables sharing a primary key make the boundary visible in the schema rather than in a grant list, and a developer adding a column has to choose a side at design time. The cost is a join and a second row at signup, which is low enough to default to.

How do I keep this from regressing? With a drift test. Enumerate the columns the user role may update, compare against an allow list committed to the repository, and fail the build when they differ. A table-level grant or a grant all from a migration script erases column narrowing in one line, so a one-off cleanup does not hold. Pair it with a rejection test per sensitive column.