i'm currently searching for a good approach to filter DB results based on permissions which are stored in another services DB. Let me first show the current state: There's one Document-Service with 2 tables (permission, document) in its MySQL DB. When documents for a user are requested, a paginated result should be returned. For brevity let's ignore the pagination for now.
Permission table: Document table:
user_id| document_id document_id| more columns
-------|------------ A
1 | A B
2 | A C
2 | B
2 | C
The following request "GET /documents/{userId}" will result in the following query against the DB:
SELECT d.* FROM document d JOIN permission p WHERE p.user_id = '{userId}' AND p.document_id = d.document_id;
That's the current implementation and now i am asked to move the permission table into its own service. I know, one would say that's not a good idea, but this question is just a broken down example and in the real scenario it's a more meaningful change than it looks like. So let's take it as a "must-do".
Now my problem: After i move the table into another DB, i cannot use it in the sql query of Document-Service anymore to filter results.
My question is: Is there any best practise or suggested approach for this kind of situation?
I already had 2 ideas which i would like to list here, even though i'm not really happy with either of them:
For the sake of this answer, I'm going to assume that it is logical for Permissions to exist completely independently of Documents. That is to say - if the ONLY place a Permission
is relevant is with respect to a DocumentID
, it probably does not make sense to split them up.
That being the case, either of the two options you laid out could work okay; both have their caveats.
Option 1: Request Documents with ID Array
This could work, and in your simplified example you could handle pagination prior to making the request to the Documents
service. But, this requires a coordinating service (or an API gateway) that understands the logic of the intended actions here. It's doable, but it's not terribly portable and might be tough to make performant. It also leaves you the challenge of now maintaining a full, current list of DocumentID
s in your Permissions service which feels upside-down. Not to mention the fact that if you have Permissions related to other entities, those all have to get propagated as well. Suddenly your Permissions service is dabbling in lots of areas not specifically related to permissions.
Option 2: Eventual Consistency
This is the approach I would take. Are you using a Messaging Plane in your Microservices architecture? If so, this is where it shines! If not, you should look into it.
So, the way this would work is any time you make a change to Permissions, your Permissions Service generates a permissionUpdatedForDocument
event containing the relevant new/changed Permissions info. Your Documents
service (and any other service that cares about permissions) subscribes to these events and stores its own local copy of relevant information. This lets you keep your join, pagination, and well-bounded functionality within the Documents
service.
There are still some challenges. I'd try to keep your Permissions
service away from holding a list of all the DocumentID
values. That may or may not be possible. Are your permissions Role or Group-based? Or, are they document-specific? What Permissions does the Documents
service understand?
If permissions are indeed tied explicitly to individual documents, and especially if there are different levels of permission (instead of just binary yes/no access), then you'll have to rethink the structure in your Permissions
service a bit. Something like this:
Permission table:
user_id| entity_type| entity_id | permission_type
-------|------------|-----------|----------------
1 | document | A | rwcd
2 | document | A | r
2 | document | B | rw
2 | document | C | rw
1 | other | R | rw
Then, you'll need to publish serviceXPermissionUpdate
events from any Service that understands permissions for its entities whenever those permissions change. Your Permissions
service will subscribe to those and update its own data. When it does, it will generate its own event and your Documents
service will see confirmation that its change has been processed and accepted.
This sounds like a lot of complication, but it's easy to implement, performant, and does a nice job of keeping each service pretty well contained. The Messaging plane is where they interact with each other, and only via well-defined contracts (message names, formats, etc.).
Good luck!