https://www.prisma.io/ logo
Join Slack
Powered by
Hello here. Anyone knows how row-level security ca...
# orm-help
b
Hello here. Anyone knows how row-level security can be implemented based on the currently logged-in user for postgres via Prisma? I am trying to figure out whether it is possible to lock editing of a record(which is a report) once it has been approved by the app user with a certain role(manager). Only managers should should enable/disable editing of an approved report, so I want this policy enforced both at the application level(which is doable) and also at the database level(via RLS policy) but using application users and that may not be database users. I have googled around but not found anything close to achieving this via Prisma. Thanks.
j
you might be able to write some hacky middleware to do the checks, but that'd require you having contextual information on the associated user making the change. you're probably going to end up needing to put the checks at the service-level tbh - thats what I do for scoped stuff like this
something like:
Copy code
async editReport(user, reportId, data) {
    let report = await prisma.report.findOne({ where: { id: reportId } });
    if (report.approved) {
      if (!['admin', 'manager'].includes(user.role)) {
        throw new Error(`User ${user.id} can't edit approved reports due to lack of permissions`);
      }
    }
    return prisma.report.update({
      where: { id: reportId },
      data,
    });
  }
alternatively you could offload the functionality to postgres itself via a function, but that'd definitely be outside of the scope of Prisma and honestly probably isn't a good idea to include core logic like that in the database
b
@James L Thanks, but my question really is whether one can implement Postgres row-level security policies via Prisma. I know Postgres supports this but only for postgres users(database users). I wanted to lift that logic to the ORM level and combine it with application level user management logic.
j
Hmm… with middlware, you could enforce row-level security, and require that you always query with the user. You’d have to add columns representing the access level, as well as a migration to set them on existing tables.
👍 1
n
You might want to have a look at cerbos for defining RLS with prisma, they have a prisma extension as well.
b
Thanks Nurul, on it.
2 Views
Open in Slack
PreviousNext
Powered by