Hey everyone, Does anyone have an example of integrating Keto with an application database so queries only load the authorized data? I feel like I’ve missed something in my thinking on how to integrate this, so far I have this flow: imagine a recipe database 1. Request list of all recipes (id) the identity has access to from Keto 2. query the database with

SELECT * FROM recipes WHERE id IN (<list of ids from keto>)

This seems like an incredibly slow solution for larger more complicated queries, and also really awkward to maintain in source code, so I feel like I’m missing something fundamental here, anyone have a better approach?

I think that has nothing to do with keto directly. There should be a additional column in your recipe table, e.g. user_id. You have to add the user_id for each entry in the table. Then you can query by the user_id

Okay, so the granularity is higher, e.g. is the user allowed to list the recipes, not which recipes does the user have access to? I must have not understood it right, cos the docs point to ‘does the user have access to a specific document’ as an example https://www.ory.sh/docs/keto/modeling/create-permission-model#determine-relationships-between-objects

yeah. Your “Object” is the Item in the Database. Your “Subject” is the user_id. Your relation for the access (SELECT * FORM) is for example “access”

Thanks for the help, I’m still a bit lost, so looking for an example I know I’m moving the goalpost a bit here, but it’s probably important to specify. How the user got access to the recipe in this example doesn’t matter, it might be shared with them from a different user, or they might have access through a parent object. You can do it with using

IN

in the query, but it feels like it will have a pretty big performance impact on the db if the list of objects is large. So hoping there are some smarter people out there that can show me the way

I'm running up against the same problem now as well! what did you end up going with? In your example case I think it would probably be fine if the list of recipes is relatively small and you use the values subquery a la https://dba.stackexchange.com/a/91539, but i wonder what the performance implications are with Keto if the returned list gets large as well. I dont have an easy identifier i can use to determine who should have what results returned from a list so using an IN query is looking like the only way to go for me