Support for row-level security (RLS)

[wladpaiva]

Problem

Currently, if we want to guarantee that user's requests have enough permissions to run a query, we have to keep coding where:{...} clauses everywhere in our code. Depending on the size of the code base, it can be extremely hard to make sure that all query conditions are correct, updated and concise with the data model.

// check if the venue is the actual owner of the ticket
 if (id) {
    const ticket = await prisma.ticket.findFirst({
      where: {
        id,
        venueId: context.venue.id,
      },
    })

    if (!ticket) {
      throw new Error('Ticket not found')
    }
  }

 // there's no way to verify to make sure the upsert has enough permission to update or create
 await prisma.ticket.upsert({
    where: {
      id,
    },
    update: data,
    create: {
      ...data,
      venueId: context.venue.id,
    },
  })

Suggested solution

We could have a way to implement some sort of @@security directly on the schema.prisma file. This way, we can check the user's permissions directly on the database, saving some back-and-forth queries and also making sure that rules will always be respected anywhere in the codebase.

An API that follows the same where principles applied to the models will allow a more flexible and powerful way to implement a granular security.

@@security(
    name: 'Blah',           // Name of the rule (important for debugging)
    create: {               // Command: update, delete, read, all...
        row: { ... },       // where clause from the current row
        context: { ... },   // where clause from the current context
        
        // since it follows the same principle, we can use the same syntax for conditions
        OR: [
            { row: { ... } },
            { context: { ... } },
        ]
    }
)

Here's a quick example:

// defines the context structure that should be sent by the prisma client
context {
    id         String
    role       Role
}

enum Role {
    USER
    ADMIN
}

model Cart {
    id         String     @id @default(uuid())
    customer   Profile   @relation(fields: [customerId], references: [id])
    customerId String    


  @@security(
        name: 'Carts are only visible by owners',
        read: { row: { customerId: [id] } }
    )

  @@security(
        name: 'Admins can do anything with carts',
        all: { context: { role: ADMIN } }
    )
}

(Note: a more in depth example can be found in the here)

The migration tool would then make sure that this RLS rule would be synced with the database

-- Policies
CREATE POLICY "Carts are only visible by owners"
  on "Cart" for select
  using ( customerId = current_setting('context.id') );


CREATE POLICY "Admins can do anything with carts"
  on "Cart" for all
  using ( current_setting('context.role') = 'ADMIN' );

and the prisma client would make sure that the context is provided to the query.

await prisma.cart.findMany({
  context: { id: '...', role: 'ADMIN' },
  data: {...},
})

await prisma.$transaction([
  ...
  await prisma.cart.findMany({
    // context: {...} should not exist
    data: {...},
  })
], {context: {...})

Alternatives

As suggested by @psugihara, the RLS could be implemented 'virtually' within prisma for unsupported databases.

Additional context

I've tried to implement it off prisma and ended up making a full report of the adventure here: #5128 (comment)
I'd extremely encourage going over all points I've made.

[janpio]

Can you provide some sample SQL the Migration tooling would need to create for the hypothetical schema you provided above? Thanks.

[sergiocarneiro]

This would be really useful, and I'm also in favor of code-splitting, as most of the rules will be repetitive.

Here's my naming proposal, to be more descriptive and paradigm-agnostic:

model Cart {
 ...	
 @@security(read: "ownerOrAdmins")
}

[wladpaiva]

Good point @sergiocarneiro. Allowing them be more descriptive could improve readability for those cases
"teamMemberWithPermisisonToReadOrAdmin" as well

EDIT: Just realized that policies should have unique names in the database so maybe the whole point of reusing policies is not ideal.

Maybe we can use the multiple policies strategy to handle reusability.

[wladpaiva]

Can you provide some sample SQL the Migration tooling would need to create for the hypothetical schema you provided above? Thanks.

Sure, I've updated both alternatives with the migration script

[sergiocarneiro]

@wladiston Just realized that policies should have unique names in the database so maybe the whole point of reusing policies is not ideal.

I was thinking Prisma could proxy those policies, so read: "ownerOrAdmins" would point to a JS function at prisma.security.ts.

// prisma.security.ts

export function ownerOrAdmins(row, session): boolean {
  return row.customerId === session.id
    || session.role === ADMIN
}

This would also not prevent the "CREATE POLICY" use-case, Prisma could just handle it behind-the-scenes and give unique names.

[Liam-Tait]

What if the new security attribute worked more like a where query.

I think this would be beneficial as:

• the schema maintains source of truth
• developers use a querying system they already know

Some rules for use:

• context security must be defined to use @@security otherwise schema is invalid
• @@security attribute must have a name
• context: {security} can be added to queries, must match context security structure

Using the above examples and converting to this approach. It would look like:

enum Role {
  USER
  ADMIN
}

context security {
  id         String
  role       Role
}

model Cart {
  cartId     String     @id @default(uuid())
  customer   Profile   @relation(fields: [customerId], references: [id])
  customerId String    

  @@security(name: 'customer_cart', read: { id: { equals: customerId }})
  @@security(name: 'admin_cart', all: { role: { equals: ADMIN }})
}

This would generate for PostgreSQL

-- Add Security Policy
CREATE POLICY "customer_cart" on "Cart" for select using ( session().id = customerId);

-- Add Security Policy
CREATE POLICY "admin_cart" on "Cart" for ALL using ( session().role = "ADMIN"  );

When querying, a context with security can optionally be added

const security = { id: 'the customer id', role: 'USER' }
await prisma.cart.findMany({ context: { security }})

[wladpaiva]

I personally love @Liam-Tait's solution. I just can't make my head around a way where we could have a more complex scenario like permissions for "a member of the team". That would need a subquery. Maybe instead of going from the context to the row, we could go from the row to the context.

Not sure if something like this would make sense

@@security(name: 'team_cart', read: { customerId: [id] })

That way we could use the context in a subquery

@@security(name: 'team_cart', read: {
    customer: { 
        where: {
            team: {
                where: {
                    id: [id]
                }
            }
        }
    }
})

[Liam-Tait]

I think it is a requirement to go from context to row, this is because if starting from the row it is not possible for use cases such as role matching. Which imo will be the most common situation.

// Super user can do anything
@@security(name: 'superuser_cart', all: { role: { equals: SUPERUSER }})
// Admin can read anything
@@security(name: 'admin_cart', read: { role: { equals: ADMIN }})
// User can do anything on their own cart
@@security(name: 'user_cart', all: { role: { equals: USER }, id: { equals: customerId })

Treating the security context more like a model and allowing relations there could help solve this.

context security {
  id   String
  user User   @relation(fields: [id], references: userId)

  role Role
}

The security for "User can access carts from Users in the same team" could then reference the context's user

enum Role {
  USER
  ADMIN
}

context security {
  id   String
  user User   @relation(fields: [id], references: userId)

  role Role
}

model Team {
  teamId String @id @default(uuid())
  User   User[]
}

model User {
  userId String @id @default(uuid())

  teamId   String
  team     Team       @relation(fields: [teamId], references: [teamId])
  cart     Cart[]
}

model Cart {
  cartId String @id @default(uuid())

  userId String
  user   User?  @relation(fields: [userId], references: [userId])
  
  // A user can access carts created by users on the same team
  @@security(
    name: 'team_cart', 
    read: { user: { team: { teamId: { equals: user.teamId }}}}}
  )
}

[wladpaiva]

I see. Both ways are actually valid. I kinda don't feel comfortable doing a channelled reference of some property in the context because there's no consistency with the rest of the schema file.
Maybe cart is not the best example to represent all possible use cases.

I think we can let developers decided which way they want go and something like this would work amazingly well:

enum Role {
    USER
    ADMIN
}

enum Permission {
    EDITOR
    ADMIN
}

context {
    // in this context, the props represent the user request
    id   String
    role Role
    teams String[] // so that we can use IN operators as well? but that would require to save the list of the teams in the jwt or something
}

model Team {
    teamId String @id @default(uuid())
    members   MemberOf[]
    websites   Website[]
}

model MemberOf {
    id String @id @default(uuid())
    teamId String
    team   Team  @relation(fields: [teamId], references: [teamId])

    userId String
    user   User  @relation(fields: [userId], references: [userId])

    permission Permission
}

model User {
    userId String @id @default(uuid())
    teams  MemberOf[]
    role Role
}

model Website {
    cartId String @id @default(uuid())

    teamId String
    team   Team?  @relation(fields: [teamId], references: [teamId])

    // it would match props from context
    @@security(
        name: 'Team members can view websites',
        read: { row: { teamId: { IN: [teams] } } }
    )

    // or, for nested row props
    // @@security(
    //     name: 'Team members can view websites',
    //     read: { row: { team: { members: { userId: [id] } } } }
    // )

     // to match values from context
    @@security(
        name: 'Admins can do anything to websites',
        all: { context: { role: ADMIN } }
    )
    
    @@security(
        name: 'Only team admins can delete websites',
        delete: { row: { team: { members: { userId: [id], permission: ADMIN } } } }
    )

    // @@security(
    //     name: 'Some random usecase that uses row AND context',
    //     create: { 
    //         row: { ... },
    //         context: { ... },
    //     }
    // )
    // @@security(
    //     name: 'Some random usecase that uses row OR context',
    //     update: { 
    //         OR: [
    //             { row: { ... } },
    //             { context: { ... } },
    //         ]
    //     }
    // )
}

Schema files could get immensely long though. At least it wouldn't have to have a different file to handle permissions and since it would use the same "where" structure, transforming it in a SQL or making it virtually would be simple. I'm just not sure how the Prisma vscode plugin would handle to have autocomplete before we prisma generate the new structure

[eduhenke]

I'd just like to provide a complex policy that we've described in our system, in case it helps designing with these use-cases in mind(SELECTing from other tables, transforming the data from the context, arbitrary conditional expressions):

CREATE POLICY station_policy ON "Station" USING (
  -- See only your organization's stations.
  "orgId" = any(string_to_array(current_setting('app.current_organizations'), ','))
  AND EXISTS (
      SELECT * FROM "Agreement"
      WHERE (
          "Station"."id" = "Agreement"."stationId"
          AND "Agreement"."agreementId" = any(string_to_array(current_setting('app.current_agreements', true), ','))
          AND "Agreement"."active" = true
      ))
  AND (
    -- Evaluate whether the requester is a member
    (current_setting('app.current_user_id', true)::int = -1)
    -- Non-exclusive stations
    OR (cardinality("groupIds"::text []) = 0)
    -- Get a station that has a common group with the requester
    OR (cardinality(current_setting('app.current_groups')::text []) <> 0 AND "groupIds" && current_setting('app.current_groups')::text [])
  )
);

We're passing the variables via the current_setting on PostgreSQL.

[mime29]

Thanks a lot for the work on this feature. Is there a way to set a milestone for this? I'm asking because we are considering dropping Prisma in our project if RLS is not supported in a close future. If the feature is to be added, we will simply keep Prisma with our explicit where condition related to the user_id until we can update our code base. The reason behind this is because we want to rely on the configuration more than on our unit tests when it comes to security.
Thank you in advance for the updates on this topic.

[wladpaiva]

@mime29 Idk if you have been following the #15074 discussion but it seems like it would allow for a half solution to this problem. Even though it drives me crazy having to verify the user's ownership of the data for every single query, having to map everything to transform data is even more annoying. I'd encourage you to keep prisma until the extension comes around and see if that doesn't help at all.

[janpio]

I recently dug into PostgreSQL RLS a bit and now understand the discussion above a lot better than before.

The suggested @@security (or variants) for CREATE POLICY is pretty straightforward, but will be really hard to get right as you already identified - but solvable. No big unknowns there, just needs a really thorough design to cover all the things possible in SQL (which usually are... many).

Where I am currently still a bit unsure is how queries optimally get the "context" set, to make sure all queries are properly covered (so no way to accidentally not have a context set when running a query - and then failing) and reset (so no way to leak some context to another query that does not set its own context) and what is the correct/best way to actually run the SQL queries. Is it really as simple as running SET LOCAL before each query? How do other ORMs or database clients handle this, especially if they maintain a connection pool as Prisma does?

[andyjy]

no way to leak some context to another query [that does not set its own context]

AFAIK yes - the only way to achieve this (assuming connection pooling) is wrapping context inside transactions using SET LOCAL

no way to accidentally not have a context set when running a query

I'm currently using the dependency injection pattern to pass request-specific context to a wapping function that I use around all my prisma calls. This works, but isn't super pleasing developer experience. I'm optimistic about refactoring to use a Proxy object around Prisma instead of the wrapper, and potentially using AsyncLocalStorage instead of dependency injection to pass the context from my request handlers.

Assuming this approach works(!), having native support for e.g. prismaClient.setRequestContext(..) that uses AsyncLocalStorage internally to scope to the current async call stack (i.e. current request only) and handles the transaction-wrapping + SET LOCAL could feasibly make life a lot easier in the future in terms of passing the context for each query. (Or potentially as a Client extension as recently proposed - haven't investigated the feasibility of this yet.)

Update 2023-04-14: I successfully rewrote my implementation as a prisma Client Extension similar to the example.
This dramatically simplified my codebase and improved the DX vs. my prior approach I originally posted below. Hooray for client extensions!

While the example illustrates a dependency-injection pattern where a separate extended Prisma client is created for each user, I am instead using AsyncLocalStorage to retrieve the current context within the extension from where it was set in my request handler.

How do other ORMs or database clients handle this?

Frankly I haven't discovered native RLS functionality in other ORMs. So also interested in answers to this! It may be fair to say Prisma would be paving new ground if it added native support for RLS vs. having to custom-build.

A useful reference point could be noting Supabase's approach - essentially just providing their request context within Postgres via a couple helper functions, leaving the developer to build everything else with raw SQL: https://supabase.com/docs/guides/auth/row-level-security

[janpio]

(We are talking to Supabase about this as well, and currently looking in how their API layer (PostgREST, which the Supabase JS SDK talks to) implements this under the hood.)

[SorenHolstHansen]

One approach, and I don't know how well this fits with prisma as a whole, is the solution described in this article from aws SaaS factory, and specifically the "Alternative approach" part (though it says there are some problems with PgBouncer and similar).

The basic idea is to inject the user into a database session when you get a pool connection. I know prisma does not allow for getting a connection and working with that, instead only working on a "Pool level", but if it could be possible to just do something like

const connection = await prisma.getConnection();
connection.$executeRaw(`SET app.current_tenant = '${TenantContext.getTenant()}'`);

// Fetch, or do whatever you want with the connection
connection.myRLSModel.findMany(...) // No need for any context in here, just do a regular query

It would enable RLS immediately. The connection would have the same API as the prisma client, but it would ensure that the function using the connection owns that connection, and within that connection, the user is verified.

I don't know how difficult this would be to pull off, but it seems somewhat easier than the above, with a context.

It would still require the @@security attribute to define the RLS on the model

[janpio]

I think the "Alternative Approach" is actually what we are mostly talking about, and others found ways to implement via middleware and similar before. For Prisma that raises exactly the problem, that we use a pool of connections and require to be able to use any free connection. As soon as we start pinning connections to a specific user (or web session etc), the performance behavior of Prisma changes completely and most users would run out of database connections. To counteract that, you have to reset each connection before or after using it - which is what we are trying to find a good way to implement.

[Liam-Tait]

If Prisma supported setting runtime configuration parameters, it would make it easier to implement row level security without Prisma needing to directly support row level security as a feature.

This could look like:

await prisma.post.findUniqueOrThrow({
  where: { id: 1 },
  local: { accountId: 2 }
)

If local is provided, the query would be a transaction and before the query add SET LOCAL for each key in local e.g SET LOCAL accountId TO 2

This solution is probably useful (not for me, I'm guessing) for other situations too such as setting the timezone, setting the date style for a query, because it is not tied directly to row level security.

I use PostgreSQL so that's what I am familiar with, but a quick look shows that this should work similarly in MySQL and MariaDb too but not SqLite

A simplified version of our current solution looks something like the following.

const transactionAsUser = async (args,{ prisma, user }) => {
  const results = await prisma.$transaction([
    prisma.$executeRaw`SET LOCAL current_user_id TO ${user.id}::int)`,
    ...args
  ])
  return results.slice(1)
}

await transactionAsUser([
    prisma.post.findUniqueOrThrow({ where: { id: 1 } })
  ],
  { prisma, user }
)

PostgreSQL SET LOCAL

[MoSattler]

@Liam-Tait Might it be possible to somehow encapsulate your approach in middleware?

[janpio]

Before starting to convert that RLS approach to a middleware, maybe take a look at Prisma Client Extensions (preview): https://www.prisma.io/docs/concepts/components/prisma-client/client-extensions The query component is probably a better way to do similar things: https://www.prisma.io/docs/concepts/components/prisma-client/client-extensions/query

[MoSattler]

So @Liam-Tait's approach with client extension would look like this?

const prismaWithRLS = prisma.$extends({
    query: {
      $allModels: {
        async $allOperations({ args, query }) {
          const [,result] = await prisma.$transaction(
            prisma.$executeRaw`SET LOCAL current_user_id TO ${user.id}::int)`,
            query(args)
          );
          return result;
        },
      },
    },
  });

This looks good, though I am running into typing issues with using $allOperations

[kachar]

@MoSattler I think it's a good practice to add $before and $after statements to clear the value that we set in the beginning of the transaction.

If we don't do that we might end up having the same current_user_id for multiple users that share a connection.

[shoooe]

One solution that could solve @moraisp's automatic batching problem and the nested transaction issues is to wrap every "request" (e.g. in an API) with a transaction (READ UNCOMMITTED by default to prevent deadlocks and performance issues?).
Am I missing something? Did somebody try this?

[chrisvander]

By default, on every request, sounds like a breaking change and high overhead. Should be opt-in.

[shoooe]

By default, on every request, sounds like a breaking change and high overhead. Should be opt-in.

Oh I'm not talking at the Prisma level. Rather at the web server level (assuming you are using Prisma in a Web API server like I do).

[wmtrinu]

@sprutner I have migrated my project away from Prisma due to the lack of native JOIN expressions causing incredibly poor performance for my use case - but when I did use Prisma, my solution was to:

• apply migrations through supabase tooling, not through prisma itself
• hand-write migrations to write the row level security policies
• set transaction variables corresponding to the user for the current request, allowing the same policies predicated on e.g. auth.uid() to work for both supabase API requests and prisma requests.

what are you using at the moment?

[ciekawy]

is this a blocker to use prisam with supabase RLS? or should it be possible to use proposed workarounds or yates?

[LazerJesus]

this would be such a nice value add for prisma and keep it relevant in the context of supabase.

[fnimick]

@sprutner I have migrated my project away from Prisma due to the lack of native JOIN expressions causing incredibly poor performance for my use case - but when I did use Prisma, my solution was to:

• apply migrations through supabase tooling, not through prisma itself
• hand-write migrations to write the row level security policies
• set transaction variables corresponding to the user for the current request, allowing the same policies predicated on e.g. auth.uid() to work for both supabase API requests and prisma requests.

what are you using at the moment?

I migrated to Drizzle. Here's my solution for constructing an RLS client based on the current user info in the session: drizzle-team/drizzle-orm#594 (comment)

[kiptoomm]

Has anyone here considered the Zenstack solution offered by @jiashengguo? #5128 (comment)

I'm curious to hear about their experience and performance considerations, if any

[grokpot]

@kiptoomm I just went down a rabbit hole on this.
I'm using Vue + Nuxt + Supabase + Supabase GraphQL + Prisma
Supabase RLS with Prisma kept getting stickier. Zenstack looks cool (nice job @jiashengguo), but:

1. It makes me feel uncomfortable to use Zenstack, because it's another layer on top of Prisma which is another layer on the DB. Zenstack should really just be native Prisma (sorry if that's a naive take)
2. Regardless, I still need to use supabase migrations to enable RLS (you could argue "just don't use supabase!" but that's a different discussion.
3. If I use Zenstack, I need to "enhance" my prisma client. This is more straightforward with NEXT, but Nuxt/supabase has it's own library, which makes this more difficult (especially considering I'd probably need to write a Nitro plugin, which is a new-ish thing and not well documented)
4. I'm using Supabase GraphQL, which means adding permissions to Zenstack wouldn't affect my GraphQL calls 😆

I ended up just removing Prisma and going with straight Supabase.
Cons: migrations are more Django-ish with Prisma (rollbacks), I like the declarative schema file of Prisma
Pros: layer of complexity removed, graphQL and REST abide by the same rules, don't have to install another layer (Zenstack), maintain one migration folder instead of two, get better at SQL

Hope that helps someone out there.

[kiptoomm]

@kiptoomm I just went down a rabbit hole on this. I'm using Vue + Nuxt + Supabase + Supabase GraphQL + Prisma Supabase RLS with Prisma kept getting stickier. Zenstack looks cool (nice job @jiashengguo), but:

1. It makes me feel uncomfortable to use Zenstack, because it's another layer on top of Prisma which is another layer on the DB. Zenstack should really just be native Prisma (sorry if that's a naive take)
2. Regardless, I still need to use supabase migrations to enable RLS (you could argue "just don't use supabase!" but that's a different discussion.
3. If I use Zenstack, I need to "enhance" my prisma client. This is more straightforward with NEXT, but Nuxt/supabase has it's own library, which makes this more difficult (especially considering I'd probably need to write a Nitro plugin, which is a new-ish thing and not well documented)
4. I'm using Supabase GraphQL, which means adding permissions to Zenstack wouldn't affect my GraphQL calls 😆

I ended up just removing Prisma and going with straight Supabase. Cons: migrations are more Django-ish with Prisma (rollbacks), I like the declarative schema file of Prisma Pros: layer of complexity removed, graphQL and REST abide by the same rules, don't have to install another layer (Zenstack), maintain one migration folder instead of two, get better at SQL

Hope that helps someone out there.

@grokpot thanks for the insightful reply. I have the same concerns about 'layering' w.r.t Zenstack, but will still give it a try because the declarative approach looks elegant (the primary feature that drew me to Prisma in the first place). On the other hand, Prisma's solution utilizing client extensions seems too convoluted to me. Per the example project:

• We still have to write some SQL anyway (yet the ORM should ideally remove this need for those of us who aren't comfortable putting app logic into the database). See steps 1-6
• The solution is not production-ready:

This extension is provided as an example only. It is not intended to be used in production environments.

👆 makes me wonder what the value/goal of these extensions are

[jason-curtis]

@LucianBuzzo taking a deep look at your Yates package. Seems like a great concept.

The most surprising thing to me is that you chose to create RLS roles and grants on startup (rather than using migrations for instance). I love the promise that I don't have to maintain migrations for RLS, but can you comment on the tradeoffs there? Are there overhead costs of doing all of this on startup? Does a new set of roles and grants get created on every instance or is there some kind of diffing/updating approach?

[LucianBuzzo]

@jason-curtis When creating Yates, I really wanted to make it as low-config as possible. Setting up user permissions can be tricky, so being able to iterate quickly and make changes without having to go through multiple setup/configuration steps is very convenient. Adding extra steps also creates additional points of failure that can create confusion and frustration for the end user.

On startup, Yates will compute the expressions for each of the abilities defined, and check them against the database to see if they need to be created or updated. They will only be changed if necessary. This compute and check process adds some overhead to the server startup time, on our production system at Cerebrum, we have 148* default abilities and 133 custom abilities and it takes ~2.5s to get through the setup process for Yates. This delay is completely acceptable for us as we are using a K8s cluster for deployment and waiting for health checks to complete before switching the container live.

The startup time could certainly be an issue if you are running Prisma in a serverless/edge-function style and have a lot of models and abilities, but as this is not our architecture I've not spent much time improving startup performance. This conversation has definitely got my mind turning on what you could do to improve startup performance, and I think there is a lot of headroom to get the time down.

*37 models each with 4 default CRUD abilties

[jason-curtis]

@LucianBuzzo thanks for the insight. The app I'm currently working on is serverless (likely scaling-to-zero) so reducing startup time is important. Would love to hear more about this possibility without spamming this thread, so I opened a separate discussion: cerebruminc/yates#90

[sbue]

@LucianBuzzo, thinking of implementing this for Supabase RLS

Would be awesome if the repo had some kind of example branch for working with Supabase

[LucianBuzzo]

@sbue Feel free to open an issue about supabase support on the Yates repo and we can focus discussion there 👍

[janpio]

Hey, a while ago I had written this:

Hey, everyone that previously commented, subscribed or upvoted - or wants to have generated columns support in Prisma: Could you please share as many examples of RLS policies as possible?

We want to figure out how much we want to or can understand and represent the "expression" of a policy in Prisma Schema Language (and use that to for example validate the expression before it is created in the database, by checking field/column names and so on). Otherwise, we could only treat them as magic SQL strings without understanding what they do, and "only" take care of migrating and introspecting them.

In the comments after, some of you thankfully provided examples for specific CREATE POLICY statement that you use. Thanks! 🙇

Now it is time to expand that a bit, and I would love to have full database schemas that include both the database tables and the security policies that apply to these tables. If you have a database that uses PostgreSQL Row Level Security, I would love if you could share that with me via schemas@prisma.io. 💌

We will use that to further understand the details on how we could represent these policies in Prisma Schema Language, to enable migration and introspection of RLS and its policies. Thank you.

[davidgomes]

I took at stab at how the Prisma ORM language could be expanded to support PG RLS. I put together an example in this Gist: https://gist.github.com/davidgomes/cfa4607d6d004b262802dedc365541f2.

This is a bit of a 1-1 mapping between PG RLS Syntax and new Prisma syntax. The reason why I went with that approach as opposed to something like what ZenStack does, is because ZenStack works very differently from Prisma. ZenStack generates an API server which writes queries according to authorization rules. But I would expect Prisma at its core level to not depend on some proxy and instead just generate PG RLS Policies natively.

Curious to know if others have any input on this API!

[chrisvander]

@davidgomes I like your gist but my only thought is that nested lookups don't really appear possible. I have an app, let's say I have a User who belongs to an Organization and has access to Projects. While the Organization may have many Projects, the User would only have access to a subset. So, a context would be something like

context {
  user User
  organization Organization
  projects Project[]
}

^ or just the corresponding IDs.

The nesting case would be something like: I have a Document which contains ContentBlock models. In this case, the ContentBlock model doesn't care about the User, Organization, or Project, it only cares about the Document it's related to. Access to a ContentBlock depends on access to the Document. So a security policy for the ContentBlock would need to be something like:

@@security({
  name: 'access_content_block'
  read: { document: { project: { in: context.projects } } }
})

I think that bit is essential to an RLS implementation, so that you don't need to add columns to a table where there isn't an application need.

I will acknowledge, I don't know how to make it a 1-to-1 mapping. Being able to generate some nested JOIN seems important, though.

[sorenbs]

@chrisvander ,

What do you think about implementing something simple like David suggests for 80% of the use cases, and then have a catch-all mechanism for more complex use cases like what you describe?

The catch-all would simply read an arbitrary RLS statement from a .sql file in your prisma folder:

With this in your schema

@@security({
  name: 'access_content_block'
})

Prisma will expect a file located at ./prisma/rls/access_content_block.sql

[chrisvander]

@sorenbs I could see that working well for complex use cases, absolutely. Being able to reference a SQL file (which I assume contains a policy definition?) would ensure that whatever model you use, there will be ways to implement more complex use cases.

Having the option to reference a column directly (like "userId") or be able to reference a path to a reference (like "x.y.userId") still represents a "simple use case" in my mind. Anything beyond looking up a single column through relations or directly would represent the complex case. Being able to work with columns on relations, for my code base at least, would eliminate 90% of any arbitrary .sql statements in a ./prisma/rls folder.

But, where Prisma is right now, I certainly wouldn't complain about a basic but Prisma-managed RLS implementation with a complex use case mechanism, and have something like the above come later.

[jiashengguo]

@davidgomes I like your gist but my only thought is that nested lookups don't really appear possible. I have an app, let's say I have a User who belongs to an Organization and has access to Projects. While the Organization may have many Projects, the User would only have access to a subset. So, a context would be something like

context {
  user User
  organization Organization
  projects Project[]
}

^ or just the corresponding IDs.

The nesting case would be something like: I have a Document which contains ContentBlock models. In this case, the ContentBlock model doesn't care about the User, Organization, or Project, it only cares about the Document it's related to. Access to a ContentBlock depends on access to the Document. So a security policy for the ContentBlock would need to be something like:

@@security({
  name: 'access_content_block'
  read: { document: { project: { in: context.projects } } }
})

I think that bit is essential to an RLS implementation, so that you don't need to add columns to a table where there isn't an application need.

I will acknowledge, I don't know how to make it a 1-to-1 mapping. Being able to generate some nested JOIN seems important, though.

I think @chrisvander has made a great point. The nested lookup ability is essential, both in terms of value and implementation.

[davidgomes]

@ChristianJacobsen I think you bring up a great point, and I especially like that you re-surfaced the context part of this. It might be a good idea for Prisma to support typing the connection context (i.e., the set of local variables that should be set on every database connection). And I would also also hope that the Prisma client is able to validate and reject database connections that do not meet the right context. A lot of people prefer to use RLS with a rich context such as a JWT object instead of relying on Postgres roles.

So, maybe Prisma could indeed have a context definer, as has been discussed before on this issue:

context {
    id        String
    role      String
}

And for JWT contexts, we could have something like:

context {
  @jwt("id")     id    String
  @jwt("role")   role  String
}

And then, these could be used in different places, perhaps even inside dbgenerated:

@@rls({
  name: "only admins can manage books",
  for: ["create", "update", "delete"],
  check: dbgenerated(`${context.role} = "admin"`),
})

And for nested lookups, we can then do the following (imagine that the application sets a set of project IDs in an array in the database connection's local variables, which is a common pattern for RLS users):

context {
  userId    String
  projects  String[]
  role      String
}

...
model Review {
  ...
  authorId        Int      @id @default(autoincrement())
  project     Project @relation(fields: [projectId], references: [id])

  @@rls({
    name: "either admins or project members can edit reviews",
    for: ["create", "update", "delete"],
    check: `dbgenerated(${context.role} = "admin") OR ANY (
    SELECT 
      projectId
    FROM 
      ${context.projects}
  )`
  })

  ...
}

@chrisvander ,

What do you think about implementing something simple like David suggests for 80% of the use cases, and then have a catch-all mechanism for more complex use cases like what you describe?

The catch-all would simply read an arbitrary RLS statement from a .sql file in your prisma folder:

With this in your schema

@@security({
  name: 'access_content_block'
})

Prisma will expect a file located at ./prisma/rls/access_content_block.sql

I really like this! I see it as a fallback utility for the 10-20% of more complicated RLS policies.

[chrisvander]

It might be a good idea for Prisma to support typing the connection context (i.e., the set of local variables that should be set on every database connection). And I would also also hope that the Prisma client is able to validate and reject database connections that do not meet the right context. A lot of people prefer to use RLS with a rich context such as a JWT object instead of relying on Postgres roles.

It's funny that you bring this up, I hadn't even thought of it, but that's how my basic RLS setup is wired up anyway.

You would share a Prisma Client instance between multiple requests and so you'd want to pass a different context for each database call. Maybe via a $extends middleware or something similar.

One other thought: if you extended the Prisma client to be an RLS client with context you could effectively have an unauthenticated client (useful for when you have no JWT requests, for server side use cases) and an authenticated client. You could add on the "authenticated" models and procedures in a type safe way, and leave them off in an unauthenticated context.

[dennemark]

It might good to revisit some of the RLS client extensions and only allow interactive transactions, since sequential transactions can easily break and won't roll back when used with client extensions! I wrote a little guide: #25034 . Haven't tested it for RLS, but for my other extension ( i am going to ditch RLS for my prisma-extension-casl which integrates casl.js authorization in my client even for nested queries - still WIP).

Prisma Client 5.14.0