Background
We are building a new project and are currently at the database design. We stumbled upon a problem which we cannot solve, we believe we have some solutions to it but we are not sure. The problem seems a tad ungoogleable, probably because we don’t know enough keywords on this topic.
Problem
In our case we are building a rest-api where a user authenticates with a token to do CRUD-operations to the endpoints.
Thats alright, you need to provide a valid token to access or modify a resource. Standard stuff.
However, being authenticated does not prevent you from modifying rows that doesn’t belong to you.
Thus a user could possibly change other users data, since there is no logic that handles that. In the case a table is located far between each other, you cannot rely on foreign key or primary key constraints - unless see Solution 2.
Solution 1
Add a field to every table that is either just a value holder or an actual fk. That field references the primary key of the “owner” or user in our case.
Solution 2
Take the performance hit and actually traverse all the way back up to the "owner" of the object/row.
Solution 3
Implement a RLS (row level security) solution but that does not seem to be the usecase for RLS or at least feels a bit to advanced for our usecase.
Question
So the question is what is the most performant while still solving the security issue? are there other solutions to this?
Solution 2 example:
Tables
user
id - pk
…
company
id - pk
…
user_id - fk
note
id - pk
…
user_id
Session
user_session
user_id
…
Data user can change:
SELECT
*
FROM
note
WHERE
user_id = :session.user_id
When user wants to update the data:
UPDATE note
SET text = “foobar”
WHERE user_id = :session.user_id AND id=payload.id
If there exists a row with user_id that corresponds to the requesting user, and if the supplied primary key of the note exists.
References
