Hi Tim,
Yeh datatype is even not supported right now. As tenantId is numeric and encryption/decryption would go through text/bytea , so even tougher to encrypt that column data.
On Thu, Feb 11, 2021 at 7:38 AM Tim Cross <theophilusx@xxxxxxxxx> wrote:
Jagmohan Kaintura <jagmohan@xxxxxxxxxxxxxx> 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.
--
Tim Cross
Best Regards,
Jagmohan
Senior Consultant, TecoreLabs.
