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

Jagmohan Kaintura <jagmohan@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

On Feb 10, 2021, at 6:45 PM, Tim Cross <theophilusx@gmail.com> wrote:

Jagmohan Kaintura <jagmohan@tecorelabs.com <mailto:jagmohan@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.

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.

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.

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@gmail.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@gmail.com>, wrote:

On Feb 10, 2021, at 6:45 PM, Tim Cross <theophilusx@gmail.com> wrote:

Jagmohan Kaintura <jagmohan@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.

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.

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@gmail.com> wrote:

Jagmohan Kaintura <jagmohan@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.

Greetings,

* Jagmohan Kaintura (jagmohan@tecorelabs.com) wrote:

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.

Note that views aren't actually guaranteed to provide the isolation
you're looking for unless you mark them as being a security barrier,
see: https://www.postgresql.org/docs/current/rules-privileges.html

Alternatively, you could use RLS and CREATE POLICY:

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

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 .

You can have PG do encryption by using the pgcrypto extension, perhaps
with some custom GUC and views (which should really also be security
barrier..) to have it be transparent. As mentioned elsewhere, you're
really better off doing it in the application though, so that the DB
server doesn't ever see the plaintext data. You should really be
considering what the attack vector you're concerned about is though-
SQL injection? Insider threat? Improper media disposal? Application
server compromise? DB server compromise? etc.

Thanks,

Stephen

Hi Stephen,

Note that views aren't actually guaranteed to provide the isolation
you're looking for unless you mark them as being a security barrier,
see: https://www.postgresql.org/docs/current/rules-privileges.html

By using Security Barrier we had a huge impact on performance , it was not
considering proper indexes and was doing some filtration with respect to
that User on top of other filtration. So we didn't choose to add a security
barrier with each view we created.

Similar issue we had with ROW level security enabling, tables were always
going for Sequential Scan, when policies were imposed on rows.

From an implementation perspective, I thought I asked questions from bottom
to top.

From a Development perspective we are moving our already multi-tenant
system (achieved at database level with views) to SaaS implementation . In
SaaS we have tried to achieve isolation to bit extend , but now we wanted
to have encryption for multiple tenants .

So as over from all these discussions best would be to achieve encryption
at application level only.

But not sure how we can Limit access of DBA's across tenants. We dont want
DBA's of one customer accessing or viewing data to another customer. Or
Overall DBA's shouldn't be able to access sensitive data from database.

We are mostly looking over Insider Thread... Application Server
Compromise..DB server Compromise.

On Fri, Feb 12, 2021 at 1:29 AM Stephen Frost <sfrost@snowman.net> wrote:

Greetings,

* Jagmohan Kaintura (jagmohan@tecorelabs.com) wrote:

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.

Note that views aren't actually guaranteed to provide the isolation
you're looking for unless you mark them as being a security barrier,
see: https://www.postgresql.org/docs/current/rules-privileges.html

Alternatively, you could use RLS and CREATE POLICY:

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

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 .

You can have PG do encryption by using the pgcrypto extension, perhaps
with some custom GUC and views (which should really also be security
barrier..) to have it be transparent. As mentioned elsewhere, you're
really better off doing it in the application though, so that the DB
server doesn't ever see the plaintext data. You should really be
considering what the attack vector you're concerned about is though-
SQL injection? Insider threat? Improper media disposal? Application
server compromise? DB server compromise? etc.

Thanks,

Stephen

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

DBA=~super user
If your clients have dba privs you need separate (vertical)servers

I have had good luck with security barrier views and performance. The main
thing security barrier does is ensure that where statements are processed
correctly.

The big consideration IMHO is how many tenants are you dealing with. A
couple of tenants, then best to give them separate databases. More than a
couple, but less than 50 or so, then best to give them all separate
schemas. If there are thousands of tenants (like an internet application)
then security barrier views are usually the easiest option to manage.

Martin Ritchie
*Geotab*
Senior DBA
Direct +1 (519) 741-7660
Toll-free +1 (877) 436-8221
Visit www.geotab.com
Twitter <https://twitter.com/geotab&gt; | Facebook
<https://www.facebook.com/Geotab&gt; | YouTube
<https://www.youtube.com/user/MyGeotab&gt; | LinkedIn
<https://www.linkedin.com/company/geotab/&gt;

On Thu, Feb 11, 2021 at 9:33 PM Rob Sargent <robjsargent@gmail.com> wrote: