3 ms·

Multi-tenant databases feel like the result of a decision where a team either doesn't know how to architect data models well or doesn't want to put the effort i

Multi-tenant databases feel like the result of a decision where a team either doesn't know how to architect data models well or doesn't want to put the effort into doing so. Referential integrity was solved 50 years ago. To demand that one's data not be commingled with someone else's in a database is as arbitrary as demanding it not be transmitted by the same pool of network connections used by the server. Our data must not reside within the same physical disk storage or memory as that used by other customers!

fennecfoxen 5y ago
To turn this practical: A good security model makes the right thing happen by default, and makes doing the wrong thing hard. A secure data model should make the tenant identifier necessary to successfully complete a query. Haven’t composite keys (including composite primary keys) been around since SQL86? A good application layer, likewise, enforces that a tenant identifier is set on every endpoint, with no additional code, just from creating the endpoint.
e1g 5y ago
The issue is backups. We're a B2B/Enterprise SaaS and most tenants require that we erase all their data at the end of the contract. Some require customer-managed encryption keys. The only way to meet this requirement is to have every tenant isolated in their own database (and their own S3 bucket etc). If data is mixed, when one tenant leaves you must go through all copies of all backups, purge their rows, then re-save the cleaned up backups. Nearly impossible in practice.
- chromanoid 5y ago
  Crypto-shredding is not an option? https://en.wikipedia.org/wiki/Crypto-shredding https://en.wikipedia.org/wiki/Crypto-shredding
  - e1g 5y ago
    In practice, I'm not sure where to begin with that strategy if you have a single .sql dump file with ~10 tables containing sensitive data from 50 different tenants. Even if theoretically possible, I'll take 50 files like tenantN.sql for both saving and restoring sanity.
chromanoid 5y ago
I think it depends. The concept of a tenant is a spectrum that ranges from simple end user to big company with thousands of users. Depending on what your application provides, different solutions are useful.

fennecfoxen 5y ago

To turn this practical: A good security model makes the right thing happen by default, and makes doing the wrong thing hard. A secure data model should make the tenant identifier necessary to successfully complete a query. Haven’t composite keys (including composite primary keys) been around since SQL86? A good application layer, likewise, enforces that a tenant identifier is set on every endpoint, with no additional code, just from creating the endpoint.

e1g 5y ago

The issue is backups. We're a B2B/Enterprise SaaS and most tenants require that we erase all their data at the end of the contract. Some require customer-managed encryption keys. The only way to meet this requirement is to have every tenant isolated in their own database (and their own S3 bucket etc). If data is mixed, when one tenant leaves you must go through all copies of all backups, purge their rows, then re-save the cleaned up backups. Nearly impossible in practice.

chromanoid 5y ago
Crypto-shredding is not an option? https://en.wikipedia.org/wiki/Crypto-shredding https://en.wikipedia.org/wiki/Crypto-shredding
- e1g 5y ago
  In practice, I'm not sure where to begin with that strategy if you have a single .sql dump file with ~10 tables containing sensitive data from 50 different tenants. Even if theoretically possible, I'll take 50 files like tenantN.sql for both saving and restoring sanity.

chromanoid 5y ago

I think it depends. The concept of a tenant is a spectrum that ranges from simple end user to big company with thousands of users. Depending on what your application provides, different solutions are useful.