Column-level SELECT REVOKE not preserved after table-level GRANT — expected behavior?

[00018730]

I'm hitting a PostgREST 42501 "permission denied for table credentials" on INSERT from an anon-key client, and my investigation surfaced what looks like a Postgres/PostgREST privilege interaction I don't fully understand.

Setup:

Table public.credentials with sensitive column password_hash
RLS enabled with permissive policies for INSERT/SELECT/UPDATE
Session 1: REVOKE SELECT ON public.credentials FROM anon then GRANT SELECT (col1, col2, ...) ON public.credentials TO anon — omitting password_hash from the GRANT list. This worked correctly — anon could SELECT allowed columns but got permission-denied on password_hash.

Problem: Now anon-key INSERT (via PostgREST) fails with 42501 permission denied for table credentials. Raw SQL as anon (SET LOCAL ROLE anon; INSERT ...) succeeds. PostgREST fails.

Hypothesized fix: grant table-level SELECT back so PostgREST's INSERT preflight works, then re-REVOKE the specific column. Tested in a rolled-back transaction:

sql
BEGIN;
GRANT SELECT ON public.credentials TO anon;
SET LOCAL ROLE anon;
SELECT password_hash FROM public.credentials LIMIT 1;
-- Expected: permission denied for column password_hash
-- Actual: returned the password_hash value
ROLLBACK;

So the earlier column-level REVOKE is NOT preserved after a subsequent table-level GRANT. Is this expected Postgres behavior? What's the correct pattern for "anon can SELECT most columns but never password_hash, and PostgREST can process INSERTs"?

Supabase project ref: mqlvjwycqoxddooqzxbf (production)

[k1ng-arthur]

So judging by this https://supabase.com/docs/guides/database/postgres/column-level-security this is expected behaviour. You can set table level privileges and column level and the more general privilege always succeeds so it makes sense that granting select on public.credentials would lead to mean every column succeeds. Back to your initial issue about why insert fails, sometimes you also need a select when doing an insert, this guide explains it better https://supabase.com/docs/guides/troubleshooting/storage-error-403-forbidden-new-row-violates-row-level-security-policy-on-upload-a94384 but this make be the reason you are seeing some issues now. What does your javascript look like at the moment?

[basildraz-arch]

Two separate things are happening here, and the first one is not a bug.

Postgres has no negative grants. REVOKE SELECT (password_hash) does not record "anon may not read this column" - it only removes a column-level ACL entry. The check is an OR: table-level privilege or column-level privilege. So the moment you GRANT SELECT ON public.credentials TO anon, the table-level grant satisfies the check for every column, password_hash included. Nothing was lost. There was never a rule to preserve.

The same asymmetry bites in the other direction: a column-level revoke issued while the role still holds table-level SELECT does nothing at all, and Postgres raises no error when you run it. That silent no-op is the part that catches people.

The only shape that works is to drop the table-level grant first:

revoke select on public.credentials from anon, authenticated;
grant  select (id, email, created_at) on public.credentials to anon;

Then ask the database rather than trusting the migration:

select has_table_privilege ('anon','public.credentials','select');                 -- expect false
select has_column_privilege('anon','public.credentials','password_hash','select'); -- expect false
select has_column_privilege('anon','public.credentials','email','select');         -- expect true

Now the INSERT. Your own evidence points straight at it: raw SQL succeeds, PostgREST fails. A plain INSERT needs no SELECT at all. PostgREST returns the inserted row by default (Prefer: return=representation, which is what supabase-js .insert() sends), so it is not only inserting, it is reading back. Once table-level SELECT is gone, that read is checked column by column, and anything in the returned set you did not grant fails the whole request.

Two ways out, depending on what you want back:

// return nothing - no SELECT needed
await supabase.from('credentials').insert(row)

// or return only columns anon actually holds SELECT on
await supabase.from('credentials').insert(row).select('id, email')

And grant INSERT per column too, for the same reason:

revoke insert on public.credentials from anon;
grant  insert (email, password_hash) on public.credentials to anon;

One more thing worth checking while you are in there, because it fails in the opposite direction from reads:

a new column + SELECT  ->  denied  until you grant
a new column + UPDATE  ->  allowed until you revoke

If anon or authenticated still holds table-wide UPDATE here, every column you add later is writable from the browser the moment it exists, including ones you were careful never to grant SELECT on.

select grantee, privilege_type
from information_schema.role_table_grants
where table_schema = 'public' and table_name = 'credentials';

Two things outside the question, and I would want to be told if it were mine.

You posted a production project ref in the opening post. Worth editing out.

A table named credentials holding password_hash, with permissive RLS and anon able to INSERT into it, is a lot of exposure to carry. Anyone holding your anon key can write rows to it. If those are your users' passwords, Supabase Auth already handles this and never puts hashes anywhere the anon key can reach.

The catalog checks I use for exactly this class of problem are in one read-only SQL file, if it helps: https://github.com/basildraz-arch/supabase-rls-audit