Обсуждение: Encryption with customer provided key in a multi tenant Postgres JSONB DB

Поиск
Список
Период
Сортировка

Encryption with customer provided key in a multi tenant Postgres JSONB DB

От
Saurav Sarkar
Дата:

Hi All,

We use Postgres's JSONB structure for NoSQL usage.

We have a multi tenant application where for each tenant we create separate tables . So for e.g. if i have 100 tenants then i have 100 tables.

Now we want to have encryption for the data in the tables with the tenant provided key. Is it possible to encrypt tables in the same database with different keys. ?

I learnt that PostgreSQL itself does not support encryption at database level or other finer granular levels . May i please know if in future can this be supported ? or is it not possible technically at all ?

I have posted a similar question on SF

https://stackoverflow.com/questions/64719415/encryption-with-customer-provided-key-in-a-multi-tenant-postgres-jsonb-db


Best Regards,

Saurav

Re: Encryption with customer provided key in a multi tenant Postgres JSONB DB

От
"David G. Johnston"
Дата:
On Wed, Nov 11, 2020 at 10:49 PM Saurav Sarkar <saurav.sarkar1@gmail.com> wrote:

We have a multi tenant application where for each tenant we create separate tables . So for e.g. if i have 100 tenants then i have 100 tables.

Now we want to have encryption for the data in the tables with the tenant provided key. Is it possible to encrypt tables in the same database with different keys. ?

I learnt that PostgreSQL itself does not support encryption at database level or other finer granular levels . May i please know if in future can this be supported ? or is it not possible technically at all ?

So you answered your own question...though you can encrypt the data being stored within the table by supplying it pre-encrypted and letting the client deal with encryption and decryption - which is probably your best bet anyway.

Almost everything is possible if you throw enough time and effort at it so I'm not sure how to constructively answer the last two questions.  Why do you ask?

David J.

Re: Encryption with customer provided key in a multi tenant Postgres JSONB DB

От
Saurav Sarkar
Дата:
Hi David,

Thanks for the reply.

I just wanted to check if there is any possibility or any activity ongoing which can enable database or fine granular level encryption in future.

Probably then i can wait otherwise i have to move towards Client Side encryption as you mentioned.

Best Regards,
Saurav

On Thu, Nov 12, 2020 at 11:44 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Nov 11, 2020 at 10:49 PM Saurav Sarkar <saurav.sarkar1@gmail.com> wrote:

We have a multi tenant application where for each tenant we create separate tables . So for e.g. if i have 100 tenants then i have 100 tables.

Now we want to have encryption for the data in the tables with the tenant provided key. Is it possible to encrypt tables in the same database with different keys. ?

I learnt that PostgreSQL itself does not support encryption at database level or other finer granular levels . May i please know if in future can this be supported ? or is it not possible technically at all ?

So you answered your own question...though you can encrypt the data being stored within the table by supplying it pre-encrypted and letting the client deal with encryption and decryption - which is probably your best bet anyway.

Almost everything is possible if you throw enough time and effort at it so I'm not sure how to constructively answer the last two questions.  Why do you ask?

David J.

Re: Encryption with customer provided key in a multi tenant Postgres JSONB DB

От
Bruce Momjian
Дата:
On Thu, Nov 12, 2020 at 11:57:27AM +0530, Saurav Sarkar wrote:
> Hi David,
> 
> Thanks for the reply.
> 
> I just wanted to check if there is any possibility or any activity ongoing
> which can enable database or fine granular level encryption in future.
> 
> Probably then i can wait otherwise i have to move towards Client Side
> encryption as you mentioned.

Postgres shared the same WAL files for all databases in a cluster, so
the idea of having multiple keys for different users is very hard or
impossible to implement.  Client-side is much better for this use-case.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Re: Encryption with customer provided key in a multi tenant Postgres JSONB DB

От
Michael Lewis
Дата:
On Wed, Nov 11, 2020 at 10:49 PM Saurav Sarkar <saurav.sarkar1@gmail.com> wrote:

We have a multi tenant application where for each tenant we create separate tables . So for e.g. if i have 100 tenants then i have 100 tables.


Depending how many tables each client gets and what you expect your tenant growth may be like, you will likely need to consider separate databases at some point for better stats and to ensure the system handles catalog table lookups and such.