Check for object ownership with Prisma

Question

I'm new to working with Prisma. One aspect that is unclear to me is the right way to check if a user has permission on an object. Let's assume we have Book and Author models. Every book has an author (one-to-many). Only authors have permission to delete books.

An easy way to enforce this would be this:

prismaClient.book.deleteMany({
    id: bookId, <-- id is known
    author: {
        id: userId <-- id is known
    }
})

But this way it's very hard to show an UnauthorizedError to the user. Instead, the response will be a 500 status code since we can't know the exact reason why the query failed.

The other approach would be to query the book first and check the author of the book instance, which would result in one more query.

Is there a best practice for this in Prisma?

score 0 · Answer 1 · answered May 19 '21 at 12:19

Assuming you are using PostgreSQL, the best approach would be to use row-level-security(RLS) - but unfortunately, it is not yet officially supported by Prisma. There is a discussion about this subject here https://github.com/prisma/prisma/issues/5128

As for the current situation, to my opinion, it is better to use an additional query and provide the users with informative feedback rather than using the other method you suggested without knowing why it was not deleted.

Eventually, it is up to you to decide based on your use case - whether or not it is important for you to know the reason for failure.

score 0 · Answer 2 · answered Mar 24 '22 at 09:22

So this question is more generic than prisma - it is also true when running updates/deletes in raw SQL.

When you have extra where clauses to check for ownership, it's difficult to infer which of the clause(s) caused that if the update does not happen, without further queries.

You can achieve this with row level security in postgres, but even that does not come out the box and involves custom configuration to throw specific exceptions when rows are not found due to row level security rules. See this answer for more detail.

I tend to think that doing customised stuff like this is rarely worth the tradeoff, unless you need specialised UX for an uncommon circumstance.

What I would suggest instead in this case is to keep it simple and just use extra queries to check for ownership, but optimise the UX optimistically for the case where the user does own the entity and keep that common and legitimate usecase to a single query.

That is, catch the exception from primsa (or the fact that the update returns 0 rows, or whatever it is in different cases), and only then run a specific select for ownership, to check if that was the reason the update failed.

This is a nice tradeoff because it keeps things simple, and only runs extra queries in the (usually) far less common failure case.

Even having said all that, the broader caveat as always is that probably the extra queries simply won't matter regardless! It's, in 99% of cases, probably best to just always run the extra ownership query upfront as a pattern to keep things as simple as possible, which is what you really want to optimise for over performance until you're running at significant scale.

Check for object ownership with Prisma

2 Answers2