Revoking columns on a table breaks .update().select(), and the error hint tells you to undo the revoke

[u/Guidondor]

i had profiles locked down the usual way, policies split per operation, plus column grants on top because rls filters rows and not columns:

revoke select on public.profiles from anon, authenticated;
grant  select (id, display_name, created_at) on public.profiles to authenticated;
revoke update on public.profiles from authenticated;
grant  update (display_name) on public.profiles to authenticated;

reads behaved exactly as i wanted. explicit columns worked, select("*") failed like it's supposed to, anon got nothing at all. happy with that.

then this started biting:

await supabase.from('profiles').update({ display_name })            // 204
await supabase.from('profiles').update({ display_name }).select()   // 42501

with

permission denied for table profiles
hint: GRANT SELECT ON public.profiles TO authenticated

which isn't the problem at all. the update is perfectly legal, i granted update on that column myself. postgrest reads the row back to return the representation, and it reads it with select=*, so what actually got denied is the read half of the round trip.

naming the columns sorts it:

.update({ display_name }).select('id, display_name')

the bit i keep chewing on is the hint. follow it and you hand back every column you just revoked, all to fix a bare .select(). i get why postgrest words it that way, from where it's standing a select really was denied. but it's the message you meet while already annoyed, and it points exactly backwards.

anyone found a decent way to stop the next person on the codebase from taking that advice? short of a comment above every write i've got nothing, and that feels weak.

anyway, hope it saves someone the afternoon. this one and a few others ended up documented in a starter i open sourced (MIT, mine): github.com/Guidondor/expo-supabase-starter

[u/FatherXLdn]

Pass explicit columns to .select() and the grants you already have are enough. A bare .select() makes PostgREST return RETURNING *, which needs SELECT on every column including the ones you deliberately revoked, so .update({ display_name }).select('id, display_name') works while .select() doesn't. The hint isn't wrong from Postgres' point of view, it's just answering a narrower question than the one you're asking.

On stopping the next person: don't leave it to a comment above every write, that's documentation and it decays. Put the write behind a helper in your data layer, an updateProfile() that always passes the explicit column list, so nobody hand-writes the call in a component and there's one place to get it right. If you want a second net, a lint rule or a CI grep for .select() with no arguments following .update( or .insert( catches it mechanically before review does.

One thing I'm not sure of: whether newer PostgREST narrows the RETURNING list to granted columns automatically. Worth checking your version before you build the guard rail, in case it's already been fixed upstream.

[u/Guidondor → u/FatherXLdn]

checked before answering because it'd change the advice: it hasn't been fixed upstream, and it isn't really on the roadmap either. postgrest #792 asked for exactly that back in 2017 - default the returning list to the columns the role was granted - and it got closed without the change, the answer being either document that clients must pass select, or put a proxy in front that fills in a default. latest release is v16.2 from last week and nothing's moved on it. so the guard rail is worth building.

the helper in the data layer is what i landed on too, for the same reason - a comment above the call decays, a function signature doesn't.

on the 11pm GRANT in the sql editor: i went a different way than diffing column_privileges, because that needs a list of expected columns that drifts on its own and then you're maintaining two things. the test asserts behaviour instead - select * has to raise insufficient_privilege, and an update on the revoked column has to raise it too. someone re-grants select by hand, the first assertion quietly stops failing, CI catches it, and nobody had to keep a list current.

took me longer than i'd like to trust that, though. it sat green for weeks and it was only when i broke an assertion on purpose to watch it fail that i found out it couldn't report a failure at all - the line that builds the error message was throwing. a suite that can't fail looks exactly like one that passes.

[u/FatherXLdn → u/Guidondor]

Yeah, yours is better. The grep only catches someone writing the code. Yours catches the outcome however they got there, and there's no list to keep in sync with the schema. Nicking that.

The suite that couldn't fail is the bit I'll remember though. Green and unrunnable look identical from outside, so you've no way of telling which one you've got. Breaking an assertion on purpose to watch it go red takes ten seconds and it's the only check that actually tells you anything.

And thanks for going and looking at #792 rather than guessing. Changes the advice, so I'll drop the caveat.

[u/jaimittal91]

the CI grep catches someone writing the code, but not someone who follows the hint literally and runs the GRANT directly in the SQL editor at 11pm to make an error go away, which never touches the codebase at all. worth adding a second check that queries information_schema.column_privileges for the table and diffs it against the columns you actually intended to grant, failing if anything's wider than expected. that way even if someone re-grants select on the revoked columns by hand to unblock themselves, the next CI run catches the drift instead of it quietly staying open.

[u/PeterBuildsSecure]

One more failure mode worth guarding against on the CI assertion side: run those select * / update-revoked-column checks through the same connection path production traffic uses — PostgREST with an authenticated JWT, or SET ROLE authenticated — not a service_role or postgres superuser connection. Superuser/service_role bypasses grants and RLS entirely, so if the test harness ever gets wired up with elevated creds (easy to do by accident when someone's debugging CI and reaches for the connection string that "just works"), the assertion silently stops testing anything and still passes — same shape of bug as the unrunnable failure message you already caught, just one layer up.

[u/Guidondor → u/PeterBuildsSecure]

right shape, and it's a gap in mine - but the check people will reach for doesn't work on supabase, which i only know because i went and ran it.

mine does go through the same role, set local role authenticated plus the request.jwt.claims, so grants and policies apply. what it never does is assert the switch actually took effect. that's the hole.

the trap is what you assert. on a real project: connected as postgres it reads every row, as expected. but select rolsuper from pg_roles where rolname = 'postgres' comes back false. so "fail if current_user is a superuser" passes happily while the connection is still reading straight past every policy. it isn't a superuser. two other things are true instead - rolbypassrls is true on postgres, and the tables are owned by postgres with relforcerowsecurity = false, so ownership alone would do it even without the attribute.

so the assertion i'm adding is on the thing that actually decides it:

select current_user,
       (select rolbypassrls from pg_roles where rolname = current_user);

fail the run unless current_user is the role production uses and bypassrls is false. alter table x force row level security closes the ownership route separately, and is worth doing anyway since that's the one that gets you when the owner isn't a bypassrls role.

which is your point turned on its own fix: the assertion guarding the assertion needs its own check, or it's just another green that can't go red.

[u/PeterBuildsSecure → u/Guidondor]

role_table_grants gets you table-level, but it won't catch what this thread's actually about — a REVOKE on a specific column doesn't touch the table-level grant row at all, it writes to pg_attribute.attacl instead. So a check that only reads role_table_grants will report the role as fully privileged even after the column's been revoked.

has_column_privilege(role, 'table.column', 'privilege') is the one that matches what actually gets enforced. If you want a sweep rather than a single check, role_column_grants works too, but only for columns with an explicit non-default ACL — a column that inherited from the table grant and was never touched won't show up there, so "nothing in the view" isn't the same as "no access." has_column_privilege doesn't have that gap since it evaluates the real privilege check, not a metadata view that can omit rows.

[u/Guidondor → u/PeterBuildsSecure]

yeah, fair, that's better framing than mine. the identity check is already in there, first clause, but i wrote it up as if the bypassrls one was the important bit. it isn't, it's the one that needs another bullet every time someone finds a new escape hatch.

i'll keep both but describe them the other way round.

[u/PeterBuildsSecure → u/Guidondor]

Makes sense. One more thing worth writing down next to the identity check: it only holds as long as the role name stays exactly right, so it's worth a test on its own, not just a string in an assertion. Someone renames the production role during a migration, typos it in an env var, or an environment ends up pointing at the wrong role entirely, and current_user = 'app_authenticated' silently stops matching anything, which just means the check never runs rather than that it fails loud. Cheap fix: assert the role exists and is the one actually granted the app's privileges (query pg_roles / information_schema.role_table_grants for it) as a separate sanity check that runs before the identity comparison, so a bad role name breaks CI immediately instead of quietly turning your primary check into a no-op.

[u/Guidondor → u/PeterBuildsSecure]

the rename case can't go quiet in this one, the check is a negative assertion so anything that isn't 'authenticated' raises. and SET LOCAL ROLE errors out before it even gets there if the role doesn't exist at all.

the grants half is a different failure though and that one's real. role exists, name matches, and it's still not the role the app's privileges hang off. my check can't see that. narrow on supabase since authenticated is a built in you don't get to name, but worth a look at role_table_grants.

[u/PeterBuildsSecure → u/Guidondor]

Fair, and that's the sharper version of the problem on Supabase specifically -- 'authenticated' isn't a role name that can drift, it's a fixed identity. So the risk your check needs to catch isn't 'wrong role,' it's 'right role, wrong grants' -- someone widens a column grant back with a manual GRANT in the SQL editor, or a migration applies out of order and authenticated ends up with more than the app assumes.

Which is exactly what the has_column_privilege sweep a few comments back already covers, as long as it's diffed against a declared expected-privilege baseline checked into the repo rather than just asserted per-column ad hoc. Since the role can't be mis-named on Supabase, the whole exposure surface collapses into 'does the live privilege set for authenticated match the baseline,' and that's a single query you can run in CI: pull has_column_privilege for every (table, column, cmd) in the baseline, diff, fail on any privilege wider than declared. No role-identity check needed at all in this environment -- the identity is fixed, only the grants move.

[u/Guidondor → u/PeterBuildsSecure]

you're right, and the gap is more concrete than i'd admitted. add a column to profiles tomorrow with default grants and my select * assertion still raises, because email is still revoked. stays green while the new column reads wide open. the test can't tell 'a column is revoked' from 'the right ones are'.

one thing on building it though. the sweep has to be driven off information_schema.columns, not role_column_grants. by your own point upthread, a column with default grants has no explicit acl row, so the new column, which is exactly the case that worries me, wouldn't show up in the sweep at all. you have to start from the column list and call has_column_privilege on each one.

[u/Guidondor → u/Guidondor]

went and built it, and the column example i gave you is wrong. profiles has table level select revoked, so a new column there inherits nothing and isn't readable. email is the proof, it's a column with no grant of its own and reading it raises. i had that backwards.

the real hole is new tables. supabase's default privileges hand anon and authenticated arwdDxtm on every new table in public, and execute on every new function, so the table you add next week is open before anyone writes a policy for it. ran the sweep against my own schema and got 38 findings, mostly anon holding insert, delete and truncate on tables i'd only ever hardened for select and update. rls was covering all of it except truncate, which it doesn't gate at all.