Multi Tenant Architecture - PostgreSQL Row Level Security (RLS) Questions

[mike-f]

I’m hoping I can beg a little advice from one or more of the Wappler users here.

I’m trying to create a proof of concept in Wappler of a multi tenant database system, and while I’ve got something that works I have a strong feeling that it could be better.

Goal: using PostgreSQL, create a system where Users can register for a site and create data for their account, and for that data to be completely discrete from other people potentially using the same website and system and creating their own data.

I’ll add the important note here that I am not (yet) a backend developer, so I don’t have the experience or skill to just know this stuff.

I’m not after a hand holding either though, perhaps just a push in the right direction.

Anyway, here’s what I have:

I recently ran through the entirety of @Hyperbytes Wappler 5 tutorial, and found this an amazing resource, one I can’t recommend highly enough for any Wappler beginner.

Based loosely on that tutorial I have created a process where:

• User registers for site
• Account ID code is generated and assigned to User.
• Every API call has conditions added to it that check the Account ID of the currently logged in user.

If you’re wondering at this point why I bothered with the Account ID, it’s because at some point the User can invite other Users, so they would access the same data and share the same Account ID.

And very simply that’s kind of what I have - and it works, it does what I hoped it would do. The obvious limitations are administrative - every API call needs to have a condition to check the identity and the account ID of the logged in user and filter data accordingly - if I get one wrong, or leave one out, then the system breaks down and Users lose all confidence.

What I’m wondering is - what would I have done if I was a backend developer? What would best (or better) practice be? Am I missing a trick here in my ignorance, where I’m making this much more difficult than I need to?

Is there a way to integrate the row-level security of PostgreSQL in Wappler? And is it something that can be managed from Wappler? This RLS concept comes up a lot in my research, and has been mentioned on the forum before by @JonL, but there’s nothing about its usage or setup.

Any tips, pointers, examples or advice from anyone would be greatly appreciated.

[Apple]

Unfortunately, Wappler has a non-ideal permission system and your concerns are totally valid.

There’s really no easy way to make the security “forgot-proof”. I don’t know about RLS, JonL will probably comment soon

You can see my past concern here in this topic:

Do we really need to Server Restrict every single Server Action we want to protect?

Do we really need to apply server restrict to each server action we want to protect? e.g.: /admin/user/delete Wouldn’t it be better to just apply the server restrict to a group of routes? e.g. /admin Am I missing something? It feels like Wappler didn’t nail this right yet. In other frameworks we have middlewares, so we can apply common logic to a group of routes instead of manually going to every possible page/server action we want to protect Manually protecting every single Server Action is …

In my own projects I’m using Global steps to perform the check at each URL, but I wouldn’t consider it to be the “ideal” solution either (mistakes can still happen)

[scalaris]

There’s no real solution at the application level other than ensuring your tenantID is used everywhere. Tips to enforce it would include making the in first column of each secondary index, tracking it in the user session and using Library actions for all database queries where the first $PARAM is always your tenantID and always populated using the session.tenantID in each server connect call.

Given those tiers, you’re far less likely to forget it anywhere and will soon fall into a process.

I don’t believe RLS is applicable as you’re not logging into the DB explicitly

[JonL]

RLS is a great way to secure a multi tenant architecture but it has its drawbacks as you introduce another layer of complexity in your stack.

Once you write an RLS policy that will restrict rows that belong to a specific tenant it’s a done deal. Whatever query you write in Wappler will never bypass that.

Unfortunately lack of time has made me postpone again and again figuring out how to integrate it with Wappler. Mainly how to pass safely to PG Wappler’s security identity in order to make use of it in RLS policies.

So for now I stick with:

@Apple

In my own projects I’m using Global steps to perform the check at each URL, but I wouldn’t consider it to be the “ideal” solution either (mistakes can still happen)

[mike-f]

Thanks so much for the replies. @scalaris, what you say about not logging in directly to the DB makes sense. And using Library actions for all the security stuff makes sense too.

@Apple, Global steps seems like it’s the way to go for me, but does that work if url is the same for all tenants? I can see how a Global check that the correct user is in the correct page could work, but if the url is, say, www.example.com/myaccount then how would you use a Global check to enforce the correct person saw the correct data?

@JonL, thanks for you input too. It makes a big difference knowing that this is a valid approach to move the project forward. I don’t need RLS, I just want to create something robust.

Thanks again all!

[Apple]

@mike-f

Apple, Global steps seems like it’s the way to go for me, but does that work if url is the same for all tenants? I can see how a Global check that the correct user is in the correct page could work, but if the url is, say, www.example.com/myaccount then how would you use a Global check to enforce the correct person saw the correct data?

Hi,

I’m not quite sure how you’re implementing tenants. Regardless, everything you want is possible

Check this past reply of mine to see if it helps:

Subdomain routing for Saas

Create a test Server Action, and put there: Set Value env = {{$_ENV}} Set Value server = {{$_SERVER}} Try to find the current (sub)domain in one of those variables, so there you have how to find the current subdomain being accessed. On your DNS provider you have to make a wildcard DNS entry (like *.example.com) to point to your server’s IP address. If you click on Globals, these are Steps that run on every page. There, check the current subdomain (from variable like $_SERVER.something), do…

It’s not exactly what you want but it may help you too

[mike-f]

That’s really useful, thank you. I’m on it!

[Audioboy]

I too would like wappler to support Postgres RLS.