11 ms·

>That makes connection pooling difficult and presents its own security issues on limiting the tenant to only their DB user. Can you use SET LOCAL ROLE <user> o

>That makes connection pooling difficult and presents its own security issues on limiting the tenant to only their DB user. Can you use SET LOCAL ROLE <user> on each transaction ?

treis 6y ago
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.
- kevincox 6y ago
  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 6y ago
    > 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 https://news.ycombinator.com/item?id=24216009
- rubber_duck 6y ago
  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 6y ago
    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 6y ago
      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

>That makes connection pooling difficult and presents its own security issues on limiting the tenant to only their DB user. Can you use SET LOCAL ROLE <user> on each transaction ?

treis 6y ago

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.

kevincox 6y ago
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 6y ago
  > 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 https://news.ycombinator.com/item?id=24216009
rubber_duck 6y ago
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 6y ago
  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 6y ago
    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