Row level security using prisma and postgres

3
votes

I am using prisma and yoga graphql servers with a postgres DB.

I want to implement authorization for my graphql queries. I saw solutions like graphql-shield that solve column level security nicely - meaning I can define a permission and according to it block or allow a specific table or column of data (on in graphql terms, block a whole entity or a specific field).

The part I am stuck on is row level security - filtering rows by the data they contain - say I want to allow a logged in user to view only the data that is related to him, so depending on the value in a user_id column I would allow or block access to that row (the logged in user is one example, but there are other usecases in this genre).

This type of security requires running a query to check which rows the current user has access to and I can't find a way (that is not horrible) to implement this with prisma.

If I was working without prisma, I would implement this in the level of each resolver but since I am forwarding my queries to prisma I do not control the internal resolvers on a nested query.

But I do want to work with prisma, so one idea we had was handling this in the DB level using postgres policy. This could work as follows:

  1. Every query we run will be surrounded with “begin transaction” and “commit transaction”
  2. Before the query I want to run “set local context.user_id to 5"
  3. Then I want to run the query (and the policy will filter results according to the current_setting(‘context.user_id’))

For this to work I would need prisma to allow me to either add pre/post queries to each query that runs or let me set a context for the db.

But these options are not available in prisma.

Any ideas?

2
postgresqlgraphqlprismarow-level-securityprisma-graphql
Without the schema, I can't give a definitive answer but have you tried to create a policy using ((id)::name = SESSION_USER) or something in those lines. SESSION_USER is the role used to connect to the DB. – FXD
I assume you are using prisma-binding for the forwarding. Maybe using prisma-client would be a better choice so you would implement this logic inside resolvers ? (That would also work with nested queries) – Errorname
Regarding session user my connection to the db through prisma is always with the same user and role. The users are managed in the applicatuon level, not the db. I dont think it is even possible to have a specific role per query to prisma. If that was possible it could solve the problem. Is it possible and i am missing something? – brafdlog

2 Answers

2
votes

You can use prisma-client instead of prisma-binding.

With prisma-binding, you define the top level resolver, then delegates to prisma for all the nesting.

On the other hand, prisma-client only returns scalar values of a type, and you need to define the resolvers for the relations. Which means you have complete control on what you return, even for nested queries. (See the documentation for an example)

I would suggest you use prisma-client to apply your security filters on the fields.

0
votes

With the approach you're looking to take, I'd definitely recommend a look at Graphile. It approaches row-level security essentially the same way that you're thinking of. Unfortunately, it seems like Prisma doesn't help you move away from writing traditional REST-style controller methods in this regard.