Column Level Security missing in diff

[anarkrypto]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Postgres grant / revoke queries missing in diff

To Reproduce

revoke update on public.profiles from authenticated;
grant update (avatar_url, name) on public.profiles to authenticated;

supabase db diff

Expected behavior

Column privileges queries should be present in diff. Therefore how can I save migrations ? Is this a expected behavior ?

System information

• OS: Linux
• Version of supabase-js: 1.115.4

[anarkrypto]

garyaustin via Supabase Discord:

Supabase I believe uses migra as part of diff... I don't know if this is the right migra but this is interesting... https://github.com/djrobstep/migra/blob/da6671acae0b4acebdec804ae25a0c65cf8561c2/docs/options.md?plain=1#L28

> ## `--with-privileges`
> This tells migra to spit out permission-related change statements (grant/revoke). This is False by default: Often one is comparing databases from different environments, where the users and permissions are completely different and not something one would want to sync.

It is the same migra they use... so dig into if there is a way to set it's parameters...

[anarkrypto]

Since --with-privileges is false by default in migra, Supabase cli should allow us to pass this parameter.

Columns privileges are very important to ensure db security when allowing authenticated users to crud the DB directly.

The lack of a way to migrate these privileges it's not just a lack of functionality, but also a security issue as Supabase strongly encourages devs to adopt end-to-end user security from the browser to the database.

[sweatybridge]

This sounds like a sensible default. But I'm not sure if it will handle column privileges as the upstream PR is still open https://github.com/djrobstep/schemainspect/pull/67. We will have to test it out after the next release.

[anarkrypto]

This sounds like a sensible default. But I'm not sure if it will handle column privileges as the upstream PR is still open djrobstep/schemainspect#67. We will have to test it out after the next release.

Yes, I can confirm it's not working with column privileges yet. After adding --with-privileges, migra only returns table privileges.

I believe this issue should not be marked as completed since the issue persists, depending on migra.

[GaryAustin1]

I asked about this today and the new branching evidently uses migra also. But I think the answer was it does not use diff, so maybe this is not an issue. I'm going to comment on the column dashboard issue as to me not having diff work with columns makes that a no go. If you set up your column privileges in the UI and can't get them thru diff seems they will be extremely hard to get set correctly on your new instance unless they give you ALL the SQL used to set them up you could put into a migration file.

Like the comment The lack of a way to migrate these privileges it's not just a lack of functionality, but also a security issue as Supabase strongly encourages devs to adopt end-to-end user security from the browser to the database.

I think this is a hidden security flaw in that if you use column privileges and assume since diff does not error that every thing is cool. You will also be running a new instance with no column privileges blocked and no errors. But in fact a user can change any query to read those columns or update them.

[anarkrypto]

This sounds like a sensible default. But I'm not sure if it will handle column privileges as the upstream PR is still open djrobstep/schemainspect#67. We will have to test it out after the next release.

Thanks to 13745, Column-level Privileges is already an experimental feature, but the CLI still not working with CLS migration.

As I explain in this discussion, Migra hasn't received updates for a while. We already have a solution for CLS, but the PR (https://github.com/djrobstep/schemainspect/pull/67) is unlikely to be approved by the migra maintainer.

I patched supabase-cli with this solution and it is working fine https://github.com/anarkrypto/supabase-cli

My suggestion is really that Migra should be forked to the Supabase org so we can work in this funtionality