Recommended way to restrict fields access with @casl/prisma ?

[LilaRest]

Hi !
I'm currently building Prismary, which is actually a prototype, but aims to became "the missing glue" between Prisma, tRPC, CASL and Zod.

In short, Prismary auto-generates tRPC procedures from a Prisma schema file, where each procedure is validated and permissioned respectively by Zod and CASL.

I'm trying to enforce field-level access rules efficiently, actually I've thought to many solutions which are individually pretty complex and sometimes inefficient.

@stalniy While developing @casl/prisma had you thought of a way to enforce field restrictions on Prisma queries ?

For example, how would you validate / restrict a call to:

await prisma.article.update({/*...*/})

considering the following CASL rules ?

can('update', 'Article', ['color']);
can('update', 'Article', ['title', 'description'], { authorId: user.id });

I know that the { authorId: user.id } rule can be easily enforced by putting accessibleBy(ability, "update").Article in the where clause of the update() call.

But how to ensure the user isn't allowed to update fields that aren't in ['title', 'description'] if it is not the author of this article ?

[stalniy]

Please check this docs -> https://casl.js.org/v6/en/api/casl-ability-extra#permitted-fields-of

[LilaRest]

Thanks @stalniy for your quick answer, I hadn't mentioned it but I have already considered using the permittedFieldsOf() method.

The problem with this one is that in order to validate read, update and delete operations, all the rows selected by the where statement of those operations must be retrieved in order to be given as subject to permittedFieldsOf() which:

• consumes a lot of RAM (which is a high disadvantage when the backend runs on services like Vercel that charge you on a RAM GB-hours basis)
• implies reading a lot of DB rows (which is a high disadvantage when using DB like Planetscale that charge you on the number of read rows)

Any other idea ? :)

[stalniy]

I do not understand why you need to select all rows in order to validate that. If we are talking about update only operation, then what you need is to restrict what fields client may update. for that case permittedFieldsOf extracts allowed fields from rules into an array which you then can use to pick only allowed fields from request body.

If you want to restrict what fields can be selected, then it's a bit harder when there is a condition because different records may have different allowed fields based on condition. In this case you need to take the most wide subset of allowed fields for a subject type

const allAllowedFieldsForArticleIgnoringConditions = permittedFieldsOf(ability, 'update', 'Article', {
    fieldsFrom: rule => rule.fields || [/* list of all fields for Article */]
});

This will restrict amount of data read from db and at the end, you additionally need to preprocess all fetched records in memory in order to ensure that private fields are not exposed publicly.

Another thought on per field permission, is that it should be used wise, I mean in most cases when per field permission become complex, it means that we should extract additional domain model/resource/type from the model we are trying to set permission on. usually such models/tables have too much fields which is additional sign that entity should be split into multiple. When you split entity into N, you may find out that you actually don't need per field permission anymore or they become much simpler.
Don't have particular example here, but I remember this from experience

[LilaRest]

1. Advanced SQL query, which may be generated from rules and do additional permission checks on db level but obviously prisma works not only with SQL. So, this may not work for mongo (not sure) - you need to double check what they suggest for advanced cases but anyway there will be restrictions

I had considered this solution, but it appeared way too complex to me. As you said it, it would require generating different queries for each DB supported by Prisma.

I'm now focusing on solutions entirely based on the Prisma Client API, and especially on features commonly supported by all databases available with Prisma.

2. select the record before update, get what fields allowed to be updated and update. This may be optimized for cases when there is no conditions on per field permissions.

This is the solution I have currently retained.
Also, to reduce the amount of requests sent to DB, I'm trying to generate a single read query that retrieves at once :

• all the data required for validation
• all the data requested by the user

Then, before returning data to user I filter the one it requested and update fields concerned by an update query ^^

So in theory, validation + request would never require more than:

• 2 DB requests for "update" and "delete" operations,
• 1 DB request for "create" and "read" operations.

Except of that, I don't see any other options.

I had also thought of using the "count" operation to individually test WhereInput of permissions rules. But while counting data is usually fairly light, this solution quickly involves sending a large number of requests to the database.

As most of the time is usually spent while the data travels between server and DB, the previously mentioned solution is more relevant as it sends fewer but bigger requests.

[stalniy]

UserPassword could be a virtual model that is represented on persistence layer as a single field.

Not sure whether it helps but just an alternative thought

[LilaRest]

UserPassword could be a virtual model that is represented on persistence layer as a single field.

Not sure whether it helps but just an alternative thought

Do you mean storing the password unhashed in the DB and compute its hash dynamically when a virtual field (e.g., "passwordHash") is accessed ?

[stalniy]

No :) I mean it could be a model in DDD but a field of some collection/table on persistence level. Pure Prisma does not support this

[LilaRest]

Oh ok got it! Indeed pure Prisma doesn't support this and it would require manually creating classes to represent data, which in additions would differ from real DB tables. I usually prefer remaining consistent between in-code classes and DB models, this make the whole system more intuitive & maintainable :)

Thanks for your time and for this great lib ^^