Can’t grant column-level SELECT privileges in Supabase Postgres

Hi I’m trying to give the anon role access to only a few columns in my bookings table. Here’s what I tried:

GRANT SELECT (employee_id, start_ts, end_ts, status) ON bookings TO anon;
But when I check with:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'bookings';

GRANT SELECT (employee_id, start_ts, end_ts, status) ON bookings TO anon;
But when I check with:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'bookings';

it doesn’t show up at all. If I do

GRANT SELECT ON bookings TO anon;

GRANT SELECT ON bookings TO anon;

then it works — but that gives access to all columns. So… is column-level GRANT SELECT not supported in Supabase’s Postgres setup? Or am I missing something?

18 Replies

ihm40•5d ago

you can do it via the ui i think https://supabase.com/dashboard/project/_/database/column-privileges?schema=public (though it is experimental) so i don't see why it wouldn't work via policies

ihm40•5d ago

See if this doc helps https://supabase.com/docs/guides/database/postgres/column-level-security

Column Level Security | Supabase Docs

Secure your data using Postgres Column Level Security.

garyaustin•5d ago

It is supported. Did you revoke select first? https://supabase.com/docs/guides/database/postgres/column-level-security Also note the CLI migration process will not pick up column level grants.

DoriimOP•5d ago

I'm on selfhost version I tried this but still nothing

revoke
select
  on table public.bookings
from
  anon;

grant
select
  (employee_id, start_ts, end_ts, status) on table public.bookings to anon;

revoke
select
  on table public.bookings
from
  anon;

grant
select
  (employee_id, start_ts, end_ts, status) on table public.bookings to anon;

garyaustin•5d ago

Should not matter. It is all postgres.

DoriimOP•5d ago

garyaustin•5d ago

Does it not actually work or just looking at the query to see?

DoriimOP•5d ago

there should be anon SELECT but this is non I tried to create a booking using anon role It didn't work

garyaustin•5d ago

I would not think you would have select for the table. You have to revoke that. You won't be able to insert from the API if you return data.

DoriimOP•5d ago

Sry my bad I mean select It didn't work for insert either

garyaustin•5d ago

I don't think there should be select for the table grants. You revoked that. You have to ask for each column that is valid in your select. Are you getting an error? Your query above is looking for table grants, not column grants... FROM information_schema.role_table_grants If you did select * then you would get a privileged error as you are asking for all columns.

DoriimOP•5d ago

Ohh I get it now I'm so dumb

garyaustin•5d ago

They are a pain to work with.

DoriimOP•5d ago

Thank you

garyaustin•5d ago

I avoid them and split data into two tables. Part of the reason Supabase backed off the UI is because of the number of support requests.

DoriimOP•5d ago

Big thanks. I understand it much better now

Paul•5d ago

@garyaustin they backed off CLS because of support tickets?

garyaustin•5d ago

The UI was made a by demand feature (you have to turn that on). The postgres support of it is has always been there. I was involved in early testing of the UI for them. It (support) was discussed as a concern as well as the fact the the CLI migration tool does not support it. So anyone migrating the DB from one instance to another with the tool would lose their column protection (and not know it). Prevalence of existing code using * was also discussed as that all breaks. I don't think enough people use/d it to generate many actual support requests. Saw your other post... if they were really only worried about support they would shut off RLS UI for sure 😎 ... And clearly joking as the API would not exist without it.

Gaming

Programming

Can’t grant column-level SELECT privileges in Supabase Postgres

Did you find this page helpful?