Re: Encryption of Data Specific to a Tenant in PostgreSQL database | General Idea

> On Feb 10, 2021, at 6:45 PM, Tim Cross <theophilusx(at)gmail(dot)com> wrote:
>
>
> Jagmohan Kaintura <jagmohan(at)tecorelabs(dot)com <mailto:jagmohan(at)tecorelabs(dot)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.