prisma Support for row-level security (RLS)

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,
    },
  })

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: https://github.com/prisma/prisma/issues/5128#issuecomment-1059814093 I'd extremely encourage going over all points I've made.

Apr 08 '22 13:04 wladpaiva

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

Apr 08 '22 20:04 janpio

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")
}

Apr 09 '22 07:04 sergiocarneiro

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.

Apr 11 '22 00:04 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

Apr 11 '22 01:04 wladpaiva

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

Apr 11 '22 01:04 sergiocarneiro

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 }})

Apr 22 '22 00:04 Liam-Tait

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]
                }
            }
        }
    }
})

Apr 25 '22 18:04 wladpaiva

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 }}}}}
  )
}

Apr 26 '22 02:04 Liam-Tait

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

Apr 26 '22 13:04 wladpaiva

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.

May 02 '22 18:05 eduhenke

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.

Oct 25 '22 05:10 mime29

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

Oct 25 '22 12:10 wladpaiva

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?

Nov 02 '22 11:11 janpio

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

[^2]: for me: user_id, tenant_id for multi-tenant, also app-specific current workspace_id

Nov 02 '22 12:11 andyjy

(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.)

Nov 02 '22 23:11 janpio

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

Nov 14 '22 14:11 SorenHolstHansen

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.

Nov 14 '22 22:11 janpio

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

Nov 14 '22 22:11 Liam-Tait

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

Nov 30 '22 15:11 MoSattler

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

Nov 30 '22 19:11 janpio

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

Dec 01 '22 09:12 MoSattler

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

Dec 01 '22 09:12 kachar

this approach worked for me

const getAuthendPrisma = (userId: string) => {
  return prisma.$extends({
    query: {
      $allModels: {
        // @ts-expect-error there seems to be a bug in `$allOperations` typing
        async $allOperations({ args, query }) {
          const [,result] = await prisma.$transaction([
            prisma.$executeRaw`select set_config('user.userId',${userId},true)`,
            query(args)
          ]);
          return result
        },
      },
    },
  });
}

and in the POLICY statement I can access the value with current_setting('user.userId', true)::text

E.g. like this:

CREATE POLICY my_policy ON "Contract"
  USING (
  "Contract"."organizationId" = (
    SELECT "User"."organizationId"
    FROM "User" 
    WHERE "User"."id" = current_setting('user.userId', true)::text
  )
);

Dec 01 '22 09:12 MoSattler

@kachar aren't LOCALs scoped to transactions?

Dec 01 '22 09:12 MoSattler

@MoSattler Alright, just confirmed the docs - indeed it says the effect is only while the transaction is active.

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not. A special case is SET followed by SET LOCAL within a single transaction: the SET LOCAL value will be seen until the end of the transaction, but afterwards (if the transaction is committed) the SET value will take effect.

Thanks for pointing that out.

Dec 01 '22 14:12 kachar

@MoSattler I think the use of await prisma.$transaction in your example is problematic? Since it would be accessing the original client, not the extended client.

This is what I came to using NestJS / Prisma:

Use async-hooks to provide the org/tenant id for the request:

request.context:

import { RequestContext } from '@medibloc/nestjs-request-context';

export class APIRequestContext extends RequestContext {
  orgId: number;
}

main.ts:

import { RequestContext, requestContextMiddleware } from '@medibloc/nestjs-request-context';
import { APIRequestContext } from './util/request.context';
...
async function bootstrap() {
...
app.use(requestContextMiddleware(APIRequestContext));

// use a global middleware to attach the tenant to the async context (first in line after request handling)
app.use((req: Request, res: Response, next: NextFunction) => {
        const ctx: APIRequestContext = RequestContext.get();

        // just setting a static orgId, but pull the org from `req`, header or whatever you desire
        ctx.orgId = 1;
        
        next();
        
        // make sure to unset it, just in case
        ctx.orgId = undefined;
    });
...
}

prisma.service.ts:

import { RequestContext } from '@medibloc/nestjs-request-context';
import { INestApplication, Injectable, OnModuleInit } from '@nestjs/common';
import { Prisma, prisma, PrismaClient } from '@prisma/client';
import { APIRequestContext } from './util/request.context';

const authedClient = Prisma.defineExtension((client) => {
    const ctx: APIRequestContext = RequestContext.get();
    const orgId = ctx.orgId.toString();
    
    return client.$extends({query: {
        $allModels: {
          async $allOperations({ args, query }) {
            const [,result] = await client.$transaction([
              client.$executeRaw`select set_config('user.userId',${orgId},true)`,
              query(args)
            ]);
            return result;
          },
        },
      },
    });
})

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {

    constructor() {
        super({log: ['query', 'info', 'warn', 'error']});
    }
    
  async onModuleInit() {
    await this.$connect();
  }

  authed() {
    return this.$extends(authedClient);
  }

  async enableShutdownHooks(app: INestApplication) {
    this.$on('beforeExit', async () => {
      await app.close();
    });
  }
}

posts.service.ts:

@Injectable()
export class PostsService {
    constructor(private prisma: PrismaService) {}

    ...
    findAll() {
        return this.prisma.authed().posts.findMany();
    }
    ...
}

The model:

model Posts {
  id  Int   @default(autoincrement()) @id
  content String
  orgId Int
}

In a migration to set up the posts model:

ALTER TABLE "Posts" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Posts" FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy ON "Posts"
USING (
    "Posts"."orgId"::text = current_setting('user.userId', true)::text   
);

CREATE POLICY admin_isolation_policy ON "Posts"
USING (
    current_setting('user.admin', true) = 'true'
);

Doing this also meant that seed was unable to function (creating rows belonging to other organisations would fail using an RLS-obeying user, which our pg user is by default. Hence, the override if user.admin is set.

seed.ts:

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
async function main() {
    // for this seed, ignore RLS
    // you could also turn it off/on, but there are plenty of scenarios from within your API
    // that you'd want to freely access the tables
    await prisma.$executeRaw`select set_config('user.admin','true',false)`;

    for (let i = 0; i < 10; i++) {
        await prisma.posts.create({
            data: {
                content: "this is a post for org 1",
                orgId: i+1
            }
        })
    }
}
main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

For allowing free access to tables, we can then also create an unauthed client extension in the service that sets user.admin to true, or check it against the user's organisation's attributes, or etc.

Works really well though, thanks for all the ideas in the thread!

Dec 03 '22 14:12 jamesmeneghello

Actually, one issue I'm now handling is that any query errors are instead thrown as client extension errors, e.g.:

PrismaClientExtensionError: Error caused by an extension: 
          Invalid `this.prisma.authed().posts.update()` invocation in
          /src/nestjs-template/api/src/posts/posts.service.ts:23:43
          
            20 }
            21 
            22 update(id: number, updatePostDto: UpdatePostDto) {
          → 23     return this.prisma.authed().posts.update(
          An operation failed because it depends on one or more records that were required but not found. Record to update not found.

Dec 03 '22 14:12 jamesmeneghello

@jamesmeneghello this looks finally right, though i still not sure if all this headache with RLS worth it - all this hacking prisma client, writing 2 policies for each table, and then need to un-hack it back for seeding, and we also have not only seeding but whole second app for managing tenants..

Im still thinking of if this has sense to implement, or just to write lint rule to enforce each prisma where to contain tenantId

Btw how update-create queries works with RLS, you still need to pass orgId there in code?

Dec 04 '22 00:12 itspers

I mean, that's shared schema multitenancy for you, really. A bit of pain here, or pain on the deployment end when deploying an environment-per-tenant (or pain when migrating schema-per-tenant), or etc. Much like everything in IT, you're dealing with sets of trade-offs, not a single obvious solution. I don't think this is too bad, considering.

Update queries work without passing orgId as part of the where - it just checks the existing orgId value on the row to be updated. Creates would require you to supply an orgId.

Dec 04 '22 13:12 jamesmeneghello

@jamesmeneghello yeah, but i started it few months ago, saw this issue and decided to postpone this problem. Now i have same nestjs setup, just without RLS - all controllers have :orgId in path, they validate it, pass to methods, methods to services, services insert it to prisma calls.

And if i will add RLS on top of it - i will just have more code, problems with seeding and extra cpu-work by postgres of processing all this RLS and set_config.. And all 'security' it adds - ability to write rare queries with findMany({})...

Dec 04 '22 22:12 itspers

prisma
prisma copied to clipboard

Support for row-level security (RLS)

Problem

Suggested solution

Alternatives

Additional context

prisma prisma copied to clipboard

Support for row-level security (RLS)

Problem

Suggested solution

Alternatives

Additional context

prisma
prisma copied to clipboard