[Feature Request] Generate supabase RLS policies on migration based on Zmodel

[Jonatthu]

It's great to have this feature for future use. I'm excited about the potential of this project and currently, I'm manually creating all these policies on migration files. It would be convenient if zmodel could automatically generate these policies on a migration file from the @@allow() rules, also row level. Let's say on a future version 2 or 1.5

DO $$ 
DECLARE 
    v_table_name TEXT;
BEGIN 
    FOR v_table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name not in ('spatial_ref_sys', 'geography_columns', 'geometry_columns'))
    LOOP
        EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', v_table_name);
    END LOOP;
END $$;

Here a snippet to enable RLS on all tables automatically, zmodel migration should include or execute this at the end of each migration.

[ymc9]

Hey @Jonatthu , I'm glad you see potential in this project!

I'd like to understand the scenario better. Do you plan to use ZModel as an authoring tool for generating Postgres RLS? ZenStack is more like a database-agnostic implementation of RLS (and CLS) in that the policy enforcement is achieved with the "enhanced" PrismaClient but not inside the database.

People have been asking about the relationship between Postgres RLS and ZenStack, and I think it's a very interesting feature to support such a ZModel->RLS transformation. Just wanted to make sure I understand your desired workflow.

[Jonatthu]

https://github.com/dthyresson/prisma-extension-supabase-rls/issues/2 This individual creates RLS policies based on comments in a Prisma file using custom generators. If the zmodel file could integrate with a user-friendly syntax and auto-completion, and compile with a gen generator from Zenstack, it would greatly improve multi-tenancy cases. This is particularly useful when using tools like Retool and providing a custom connection string, where creating a backend is not feasible due to time constraints. Additionally, having the generator generate RLS policies on the Prisma migration would be highly beneficial.I understand zenstack can generate CRUD's but simply does not cover the user case where you can not deploy an extra backend. This could be a paid feature easily for zenstack to monetize.

[ymc9]

Got it. Thank you for giving the reference project. Let me look into it. ZenStack has a plugin mechanism similar to Prisma's generators. A plugin can traverse the parsed ZModel AST and output anything it wants. For the time being generating RLS seems to be outside of ZenStack's core scope, but maybe it can be implemented as a community plugin.

Is it something that you'll be interested to work on 😄?

[Jonatthu]

If there is documentation, I will go for it!

[ymc9]

Hi @Jonatthu , my apologies that there isn't detailed documentation about writing plugins yet. The best reference for the time being are the implementation of various existing ones, e.g.: https://github.com/zenstackhq/zenstack/tree/main/packages/plugins/openapi

It's mostly about traversing the parsed & linked ZModel AST and translating it to another artifact. Let me know if you decide to proceed with the work and I'll be happy to have close collaboration,

[ymc9]

Btw, I saw the original discussion was from RedwoodJS's discord. Do you plan to use this approach in a RW project?

[Jonatthu]

I am full supabase atm and edge functions

[Azzerty23]

@Jonatthu Did you intentionally close this issue?

[Jonatthu]

Yes, I have decided that RLS is no longer useful for me since I won't be using anon keys or auto generated API's that expose my database. Instead use zenstack auto generated api on a server.

[hongkongkiwi]

I think this is a very interesting feature for Supabase users. This will allow us to easily manage and deploy RLS via Zenstack's easy to use format.

[ymc9]

Reopening the issue to keep collecting feedback.

[HStromfelt]

I just came across the need for RLS as I will be using Supabase Realtime. For me the whole "single source of truth" being the zmodels is super desirable. If I could specify the RLS based on the model-level access policies, I would be very happy. Otherwise it's a bit of a game of manually making sure everything lines up.

[eraoul]

I'm interested in this feature as well. My use case is for an app with lots of highly-sensitive user data. I'll need to have my app pass an external security audit, and one thing they look for is presence of row-level security. I am using ZenStack, but I'd like to be enforcing ownership guarantees as close to the DB layer as possible. Ideally I'd run zenstack and use its auth mechanism to check for ownership on each row, but also enforce this on the DB side with RLS policies.

[kiptoomm]

Same here! Zenstack's solution for declaratively-defined access control makes a lot of sense, but it'd be even better if we can use the client in 'serverless' environments like Supabase. Ideally, the entire app logic should be live in one place i.e. the model definition