Let's say we want to return a paginated list of document id that a user can view. In DB, we have:
We use an external authorization service, so we can query if the user has view permission on specific doc_id, by using doc_id and user_id
<doc_id>::<user_id>::view == true example: has_view_permission = check_permission("abc::user_1::view")
My question is, how to make the query efficient?
Without the permission check, I can take advantage of DB Keyset pagination. However for external authorization service, DB doesn't have data to do data join.
I have come up with a few ways, but I still thinking the solutions are complicated.
- Store a local copy of permission data into DB. We need to sync the data regularly (or based on event), we then can do a table join - table join is expensive too.
- Retrieve the data using keyset pagination, and send the result to AuthZ service. If some of the results don't have the view permission, we fetch the next data from db. This process repeat until we able to return enough data.
- Every time there's a change in doc visibility, we pre-calculate the list and store in a different table, let's say,
user_viewable_doc. This will use up a lot of storage.
What are the standard/efficient practice for dealing with this issue?
I have found this question, but I am not really satisfied