When you have a 3 tier system with an application server in the middle, using a single database connection user but managing application users differently as it is typically done, how do you implement postgresql row-based access control?

Or can we say that adding potentially millions of users as regular postgresql users is feasible from the side of postgresql? I have used this design before, because I like to have individual users on the database, and it works for a few dozen or hundred users. But I do not expect this to work with millions. Even just because we need to do connection pooling and load balancing. So whatever row based access control feature might be in postgresql would not work if you have millions of users.

But I thought of a very simple and powerful scheme that I plan on using. I'm just going to add a column owner DEFAULT current_app_user to each of my main tables which, to start with, will just have a single "owner" with the application user id. Then I use rewrite rules to always add a WHERE owner = current_app_user to SELECT and UPDATE queries.

That's super straight-forward and would probably carry me very far because in my system there is public information and then there is private information, where private is almost never shared between users, with just few exceptions maybe in the future. If that should be necessary I would just extend that owner to be an ARRAY of user ids, and there would usually be only one or perhaps two, rarely ever 3 and just about never ever more than 3.

I realize that there is this very informative and similar question here: Row level security with a single DB user and connection pooling but I am wondering if this row level access control is not more expensive than the simple idea I am having. Mainly in my design, the row owner id comes directly from the row and needs no other join. Isn't that faster (given my assumptions)?

Am I tripping into a trap with my idea?

  • 1
    Beware of parent-child relationships where you risk ending up with different owners for parent and child (may or may not be a problem). May 19, 2022 at 16:38
  • 1
    You also need to consider situations like SELECT ... FROM A LEFT JOIN B USING (....) If you just add 1 WHERE predicate per table the results may be counter intuitive for the user unaware of the additional predicates. May 19, 2022 at 16:43
  • 1
    What is current_app_user? That is usually the hard part May 19, 2022 at 17:01


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.