> On Feb 10, 2021, at 6:45 PM, Tim Cross <theophil...@gmail.com> wrote: > > > Jagmohan Kaintura <jagmo...@tecorelabs.com <mailto:jagmo...@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 rather > unusual to me. Isolating one tenant from adversely impacting another > would seem complicated and I'm not sure how you would implement a clear > security model. Your model has effectively bypassed all the provided PG > facilities for isolation of data. Disaster recovery and business > continuity 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? How > would you select the data for your tenant views if all that data is > encrypted with different keys? How would you manage these keys in a > secure manner? > > With the model you have adopted, I would be looking at performing > encryption/decryption at the client level. However, depending on your > data types, this could be challenging. this is really a requirement > which should have been factored into the initial architecture design. > Anything you try to bolt on now is likely to be complex and have > significant performance impact and that is assuming you can re-interpret > the 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.
