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.

[LucianBuzzo]

For anyone looking to implement RLS in Prisma, I developed a package for doing this https://github.com/cerebruminc/yates
It uses a client extension to bootstrap and enforce RLS policies based on a provided user role.

[recurrence]

That Yates link is great, they appear to workaround all kinds of Prisma problems peering through the source. Has anyone used that in production? Have they encountered problems?

[LucianBuzzo]

@recurrence We use it in production at https://www.cerebrum.com/ for a number of services and it works very well. You may need to add additional indexes to your models to maintain good performance, and it's worth experimenting to make sure you get good values for wait and transaction timeouts.

[sprutner]

I am very interested in a solution for this. I am also using Supabase which sadly is reliant on RLS for security. This is a major drawback of Prisma and at some point I would need to migrate off prisma or Supabase because of this. This seems like a big want from the community. I will check out Yates although I'm trepidatious to use something so new with my database security.

[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.

[chrisvander]

const [, result] = await client.$transaction(transaction=> {
await transaction.$executeRawSELECT set_config(${claimsSetting}, ${claims}, TRUE)
return query(args)
})

I'm confused. Why wouldn't this work?

[LucianBuzzo]

@chrisvander IIRC prisma will run the inner $executeRaw in a different transaction to the query.
If you use the batch style transaction where the statement is in an array it works, although you will run into issues with nested transactions and rollbacks (currently being worked on here prisma/prisma-engines#4375 ).

[chrisvander]

Hm. In that case would I need to override the $transaction function type to make the override taking in a function as it's first argument return never? Might be a good way to avoid that issue for the time being.

[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 👍