Column-level privileges feedback

[alaister]

We're excited to announce the release of the column-level privileges management UI: It allows you to grant or revoke privileges on tables and columns based on user roles.

What do you like most about it? Least?
Are there any features you find particularly useful?
Anything you think could be improved?
Anything you think is missing?

Happy to hear your thoughts, suggestions, and experiences.

[rohankm]

Hi, custom roles are not being shown in the select option

[markledwich2]

I can't find this in the product atm

[GaryAustin1]

It is a preview feature. Down on the bottom left is a "person" icon. Click that and you will see a preview option.

[FilipeMarch]

The UI does not work.

When I click on Apply Changes, it resets everything to the original state

supabase_col_privilege_bug-2024-02-13_18.24.04.mp4

Has anyone ever used this feature?

[GaryAustin1]

It applies correctly for me.

You might check the Postgres logs for errors that are silently stopping the process. Do you use Prisma (which messes with grants)?

[FilipeMarch]

It applies correctly for me.

You might check the Postgres logs for errors that are silently stopping the process. Do you use Prisma (which messes with grants)?

The error happens when your table is named "users".
I don't use Prisma.

[joaoguidev]

Hello.

Great feature!

The only thing is that my custom roles are not there to be selected.

The only roles available are the usual, anon, service_role and authenticated.

[ofekd]

Can you explain why you treat this as an advanced feature and recommend avoiding it if possible? Is that the Alpha state or some inherent thing about it?

[trunghuynh291199]

How can I hide some columns even when I select (*)?

[savikko]

Just as a feedback two "issues" (otherwise seems to be working nicely):

• Lets say we have a table where one user has select permission to subset of the fields. Then, when using Supabase UI "impersonate" feature on Table Editor, if that target user does not have permission to all fields, the UI just times out (even though the request fails with HTTP status 400 - permission denied)
• On Column Level Privileges admin, if i have smaller browser window (vertically), i am unable to select tables which do not fit the dropdown (or actually the window)

[alitnk]

Would this be available on a local Docker instance? I can't find the page nor the preview toggle.

[mikob]

The page is here: http://127.0.0.1:54323/project/default/database/column-privileges
But even after enabling it, it doesn't seem to load and CLS doesn't seem to be working correctly for me (select * still gets all the columns)

[rossbishop]

Seeing the same as @mikob here - are there any plans to fix this for running locally?

[alaister]

Hi,

Thanks for reporting. This should now be fixed, please try updating your CLI.

[smaldd14]

I'm having issues with setting CLS using migrations scripts, or via the SQL editor. I've enabled the feature preview, so I have CLS available in my local supabase studio

I have a simple clubs table:

create table
  public.clubs (
    id uuid not null default gen_random_uuid (),
    slug text not null,
    name character varying(255) not null,
    address text null,
    phone character varying(20) null,
    capacity integer null,
    created_at timestamp with time zone not null default timezone ('utc'::text, now()),
    updated_at timestamp with time zone not null default timezone ('utc'::text, now()),
    image_url text null,
    stripe_customer_id text null,
    auto_pay_enabled boolean null default false,
    auto_pay_max_per_promoter_cents bigint null default 50000,
    constraint clubs_pkey primary key (id),
    constraint clubs_slug_key unique (slug)
  ) tablespace pg_default;

And I am trying to restrict access to only allow a certain role to be able to read the stripe_customer_id column.

For starters, I had this:

-- Revoke SELECT on sensitive columns from public
REVOKE SELECT ON table clubs FROM public;
GRANT SELECT (id, slug, name, address, phone, capacity, image_url, created_at, updated_at) ON table clubs TO public;

it returns Success when I run it in SQL editor, but querying select * from clubs; still returns all columns no matter what role I impersonate.

I see this warning saying Column privileges are not supported in this version of the Supabase CLI, but I just upgrade to latest version 2.1.1

Any ideas on how I can get this to work and test in SQL editor?

[GaryAustin1]

The UI should work and SQL to set them should work.
But migrations will not work. There is no current way around that.
The 3rd party code used for the migrations does not support them.

[GaryAustin1]

supabase/cli#1716

[smaldd14]

Thanks for the response. SQL editor doesn't seem to do it. I seem to be following the CLS syntax correctly, no?
https://github.com/user-attachments/assets/5517fa94-8aa1-4cc9-bc6a-18715fe57e24
(stripe ID is test ID)

I can confirm that the UI works, but this is not ideal for me

[GaryAustin1]

SQL pretty much has to work as it is a postgres thing and not a supabase thing. Select * should actual error if you have any select grant turned off. When you use column grants you have to name each row. You also did not revoke from anon/authenticated just public. EDIT although I've never tried revoking public for all roles, just using individual roles.

Seems like your table select is still applied though.

[smaldd14]

shouldn't select * be returning an error? it instead returns everything like normal. Only when I use the UI and turn off the column, then select * returns an error.

Even doing

-- Revoke SELECT on sensitive columns from public
REVOKE SELECT ON table clubs FROM public;
REVOKE SELECT ON table clubs FROM anon;
REVOKE SELECT ON table clubs FROM authenticated;

GRANT SELECT (id, slug, name, address, phone, capacity, image_url, created_at, updated_at) ON table clubs TO public;
GRANT SELECT (id, slug, name, address, phone, capacity, image_url, created_at, updated_at) ON table clubs TO anon;
GRANT SELECT (id, slug, name, address, phone, capacity, image_url, created_at, updated_at) ON table clubs TO authenticated;

doesn't change the result of select *

[GaryAustin1]

Yes select * should error if your select on table is revoked and any column is revoked.

Too late for me to test, but this is all postgres and not Supabase so likely something you are doing wrong (or some very new permission bug). I just don't see it at quick glance. Seems like the table revoke is not working. The UI being on or off or at any rev should not impact SQL.

[GaryAustin1]

Your code copied exactly for table and last 6 lines with anon user did this for me on latest hosted Supabase.

selecting a single column updated_at did not error. No data in table.

[smaldd14]

Ah I apologize, I had an old RLS policy

CREATE POLICY "Everyone can view basic club information" ON public.clubs
    FOR SELECT USING (true);

That I had not deleted locally. If I delete this, now any queries to select * from clubs; from anon or authenticated role, just return "success. no rows returned" which makes more sense.

I appreciate the help