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 <theophil...@gmail.com> wrote: > > Jagmohan Kaintura <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. > > -- > Tim Cross > > > -- *Best Regards,* Jagmohan Senior Consultant, TecoreLabs.
