Jagmohan Kaintura <jagmohan@tecorelabs.com> writes:HI All,
For POstgreSQL database to store data for multiple tenants, the approach
decided was to have
Shared Database (Holding data for all tenants)
=> Data would be segregated on basis of some additional column
(tennatid,different tenants having different tenantId)
=> Data would be accessed through Views on the basis of tenantId
value.
This is the basic process of most of the customers who are trying to
implement multiple tenants in PostgreSQL, rather than choosing
separate databases for each tenant.
Now we need to encrypt the data related to a tenantId, so that now one
knows this data belongs to which tenant even from Operations group.
Is there a method in POstgreSQL for encrypting data with different keys
with respect to different values in a single column. Moreover pg_crypto
will impose a single key on the column.
Please share your thoughts in which direction i can start analysing this
area for encryption of data specific to a tenant.
The decision to have all tenants in a single database seems ratherunusual to me. Isolating one tenant from adversely impacting anotherwould seem complicated and I'm not sure how you would implement a clearsecurity model. Your model has effectively bypassed all the provided PGfacilities for isolation of data. Disaster recovery and businesscontinuity planning under this model must be a nightmare!I doubt you can adopt a solution which is solely within the database.How would the database know which key to use for which rows of data? Howwould you select the data for your tenant views if all that data isencrypted with different keys? How would you manage these keys in asecure manner?With the model you have adopted, I would be looking at performingencryption/decryption at the client level. However, depending on yourdata types, this could be challenging. this is really a requirementwhich should have been factored into the initial architecture design.Anything you try to bolt on now is likely to be complex and havesignificant performance impact and that is assuming you can re-interpretthe requirement to make the objective feasible.
Yeah, I lost that same arguement in ~2007, where the forces against my push for separation was shouted down with rants on scheme maintenance (divergence) and multiple rollouts per update. I hadn’t had any coffee before the 9:00am meeting so the hotshot from Amazon got his way. Then we tried “veils” (a concoction of view and rule re-writing) and we all know how that went. The company folded before our “next gen” software saw the light of day.
I get the feeling multi-tenancy is, if not the rule these days, at least quite common (on the last of “big iron”?) but it still doesn’t sit well with me.
