[treis]

If you do it that way then you don't gain much security. Any SQL exploit would just need to add the Set Local Role to break out of the tenant row level security. Any code error would (probably) still allow unauthorized access because that error will likely also set the incorrect user.

It adds a layer of security so it might prevent some bugs leading to exploits. But in itself is not enough to rely on to separate tenants.

[rubber_duck]

Well if you have SQL injection bugs then you have bigger issues to worry about - I've used this to enforce multi-tenancy on database access level (like another poster said - preventing queries accessing wrong data by accident, which is far more common I think).

[treis]

A SQL injection bug is (probably) not that big of a deal as long as the tenant boundary isn't crossed. They'd be stealing their own data.

[rubber_duck]

True, I'm just not sure that I'd trust the DB isolation once the user has SQL injection. I never saw a SQL injection report on a project (well since the PHP days) ORMs solved this for the most part, but I did see multiple instances of accidental data leaks from bugs on different projects.

It looks like you could also use SET SESSION AUTHORISATION for this but I haven't used it so I don't know how this works with data access/pooling

[kevincox]

If you are running a copy of the same software for each tenant anyways it doesn't matter much as a SQL injection for one tenant is most likely available on all tenants.

I think for this use case security is focused on accidentally returning the wrong tenant's data (fully or partially)

[lixtra]

> available on all tenants

Yes, but typically not across tenants. Maybe the flaw is only exploitable to admins of each tenant and they shouldn’t see other tenants data.

I.e. https://news.ycombinator.com/item?id=24216009