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

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(at)gmail(dot)com> wrote:

>
> Jagmohan Kaintura <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.
>
> --
> Tim Cross
>
>
>

--
*Best Regards,*
Jagmohan
Senior Consultant, TecoreLabs.