How to architect an in-app search solution that accounts for access to data?

Question

I have a lot of data that I would like to enable end-users to search on. I plan to use ElasticSearch (but am open to other technologies). This would also be an AWS native solution. For simplicity, let's say each record of data is a Person that looks like the following:

{
  id: int (unique),
  firstName: text,
  lastName: text
}

And given three pieces of example data:

[
  {id: 1, firstName: "Tom", lastName: "Brady"},
  {id: 2, firstName: "Michael", lastName: "Jordan"},
  {id: 3, firstName: "Brad", lastName: "Pitt"}
]

Then there are two users:

User A: Has access to id 1 (Tom Brady) and id 2 (Michael Jordan)
User B: Has access to id 2 (Michael Jordan) and id 3 (Brad Pitt)

When User A performs a search, I would only like a subset of ids {1,2} to be returned based on their search criteria. When User B performs a search, I would only like a subset of ids {2,3} to be returned based on their search criteria. There is business logic that determines which user has access to which IDs, and returns that as a collection (which I refer to as scoped data).

What is the recommended architecture to support this behavior in a highly scalable, cloud native solution? How to handle the filtering of scoped data in a highly performant way?

One solution I was thinking is to load all IDs for a given user into a filesystem (S3, EFS, DynamoDB or Redis). So user A would have a file with their Json array, and user B would have a separate file with their Json array. Then when the user makes the search request, the application would query ElasticSearch and also specify the IDs in the filter of the query. The concerns I have with this approach is if it is scalable, especially if a user has a Json array of hundreds of thousands of IDs.

Answer 1

relational model

This is a classic RDBMS JOIN. So ditch ES and use some proper backend data store, perhaps Postgres. It will be performant.

RBAC

Creating a relation that models per-user fine grained permissions is certainly feasible. But that’s a lot of details to store. If you can lump multiple users into a common group, and model (most) permissions at the role / group level, you can get away with storing fewer rows.

---

open to other technologies

resources that you recommend if going the relational model approach?

I don't understand why this would be a question. SQL-92 is pretty straightforward, and a google search for JOIN will offer more than one result.

Here is what the OP told me about your data:

INSERT INTO subscriber VALUES ('A');
INSERT INTO subscriber VALUES ('B');

INSERT INTO person_permission VALUES ('A', 1);
INSERT INTO person_permission VALUES ('A', 2);
INSERT INTO person_permission VALUES ('B', 2);
INSERT INTO person_permission VALUES ('B', 3);

When User A performs a search, I would only like a subset of ids {1,2} to be returned based on their search criteria.

Given all that, with an FK relationship, the obvious query would be

SELECT person.*
FROM person
JOIN person_permission pm
WHERE pm.id = the_logged_in_subscriber_id
  AND pm.person_id = person.id

If your notion of "other technologies" is limited to a small set which excludes tools like an RDBMS, please tell us about the constraints you're working within.