2

After reviewing the different options for creating a multi-tenant database architecture, I have decided to use the 'Single database and same tables for all client, but we have tenant_id in all tables, so we query the data according to specific client' approach due to the ease of scaling and maintenance/upgrading.

The general approach seems to be to simply include WHERE tenant_id = $ID for every query, however I've learned that you can further isolate the data and remove the need to include the WHERE clause by implementing Row Level Security.

The guides I've found on how to do this are not very clear though. They all seem to implement row level security based on the signed-in database user which won't work for my case as I only have a single database user.

Basically, when a user sends an API request to our server it will include a JSON web token which includes an id in the payload. The server then queries the 'users' database for that id to get the user's orgId (aka tenant_id), then when the server queries the database as per the API request, it will set $ID in WHERE orgId = $ID to this returned orgId value.

How do I implement row level security in this scenario?

I've searched for other topics and do not believe this is a duplicate question.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Jake
  • 3,865
  • 5
  • 25
  • 58
  • Hey Jake, I'm currently implementing tenancy with `tenant_id` approach too. Did you manage to implement the 'row-level security'? – kyw May 27 '20 at 03:32

1 Answers1

1

Normally, you cannot use row level security with such a setup.

Row level security is typically tied to the database user, so it can only be used if your application implements its security concept with database users.

As a_horse_with_no_name commented, a workaround may be to set configuration parameters like application_name from your application and base row level security on that setting.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    Can't you define a RLS policy that uses the value of a configuration setting? It would of course be in the responsibility of the application to make sure that the property is initialized properly when the connection is established. –  Apr 17 '19 at 11:31
  • You are right, that can be done. Write it up in an answer, and I'll upvote. I'll revise my answer with a remark. – Laurenz Albe Apr 17 '19 at 14:13
  • Here's a good article that outlines @a_horse_with_no_name's idea using Rails. https://pganalyze.com/blog/postgres-row-level-security-ruby-rails – user1032752 Apr 08 '22 at 21:47