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

Hi Guyren,

Yup right now data is being accessed in this manner only.
application access using tenant user only who have specific tenantId in
that session and can see its own data only. It doesn't know about anyone
else's data and neither can get/fetch.

So isolation is 100% guaranteed right now.

But isolation is not enough from an operations perspective, so I need
encryption too in some way or another way, whatever postgreSQL supports
and encryption key should differ for a tenant .

On Thu, Feb 11, 2021 at 8:56 AM Guyren Howe <guyren(at)gmail(dot)com> wrote:

> An interesting option is to make your tenants work through views, and have
> the views work through a variable that contains the tenant’s id. There
> would be a bit of coding, but it would be the same for every table, so you
> could automate it easy enough.
>
> When you’re done, client software just sets the id at the beginning of the
> session and the database looks like it only contains the tenant’s data.
>
> This is a particularly nice security guarantee: if you make the id a UUID
> (ie unguessable) then someone can entirely compromise the client
> application, and can still only get at the data for one tenant, and then
> only given their ID.
> On Feb 10, 2021, 18:44 -0800, Rob Sargent <robjsargent(at)gmail(dot)com>, wrote:
>
>
>
> On Feb 10, 2021, at 6:45 PM, 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.
>
> 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.
>
>
>

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