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.
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).
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)
treis|5 years ago
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|5 years ago
kevincox|5 years ago
I think for this use case security is focused on accidentally returning the wrong tenant's data (fully or partially)