Обсуждение: Would PostgreSQL 16 native transparent data encryption support database level encryption?

On Wed, May 17, 2023 at 03:35:39PM -0700, Tony Xu wrote:
> Hi There,
> 
> The FAQ (copied below) mentioned that native transparent data encryption might
> be included in 16. Is it fair to assume that it will support database level
> encryption, that is, we can use two encryption keys for two databases in the
> same server, respectively? How can one verify that? 

It will not be in PG 16.

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

  Embrace your flaws.  They make you human, rather than perfect,
  which you will never be.

Greetings,

* Tony Xu (tony.xu@rubrik.com) wrote:
> The FAQ (copied below) mentioned that native transparent data encryption
> might be included in 16. Is it fair to assume that it will support database
> level encryption, that is, we can use two encryption keys for two databases
> in the same server, respectively? How can one verify that?

The current work to include TDE in PG isn't contemplating a per-database
key option.  What's the use-case for that?  Why do you feel that you'd
need two independent keys?  Also, the general idea currently is that
we'll have one key provided by the user which will be a KEK and then
multiple DEKs (different ones for relation data vs. temporary data vs.
the WAL).

If you're interested in TDE in PG, we could certainly use more folks
being involved and working to push it forward.

Thanks,

Stephen

Greetings,

* Tony Xu (tony.xu@rubrik.com) wrote:

The FAQ (copied below) mentioned that native transparent data encryption
might be included in 16. Is it fair to assume that it will support database
level encryption, that is, we can use two encryption keys for two databases
in the same server, respectively? How can one verify that?

The current work to include TDE in PG isn't contemplating a per-database
key option.  What's the use-case for that?  Why do you feel that you'd
need two independent keys?

On 5/18/23 10:54, Stephen Frost wrote:

Greetings,

* Tony Xu (tony.xu@rubrik.com) wrote:

The FAQ (copied below) mentioned that native transparent data encryption
might be included in 16. Is it fair to assume that it will support database
level encryption, that is, we can use two encryption keys for two databases
in the same server, respectively? How can one verify that?

The current work to include TDE in PG isn't contemplating a per-database
key option.  What's the use-case for that?  Why do you feel that you'd
need two independent keys?

I don't feel that key-per-database us useful; I know that key-per-database is useful, since the databases can be different projects for different companies.  Each wants it's own encryption key so that no one else can get to their at-rest data.

(pg_dump files will automatically be encrypted, right?)

--
Born in Arizona, moved to Babylonia.

On Thu, 18 May 2023, Tony Xu wrote:

>Our use-case is for a multi-tenancy scenario - we are considering using
>different databases to store different customer's data, however, for

Why not using multiple clusters then?

Better isolation of the customers, but still on one server.

bye,
//mirabilos
--
15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)

Thorsten Glaser <tg@evolvis.org> writes:
> On Thu, 18 May 2023, Tony Xu wrote:
>> Our use-case is for a multi-tenancy scenario - we are considering using
>> different databases to store different customer's data, however, for

> Why not using multiple clusters then?

Yeah.  The problem with key-per-database is what are you going to do
with the shared catalogs?  It's a lot simpler, and probably better
performing, to use one key per cluster.

            regards, tom lane

On 5/18/23 13:02, Thorsten Glaser wrote:
> On Thu, 18 May 2023, Tony Xu wrote:
>
>> Our use-case is for a multi-tenancy scenario - we are considering using
>> different databases to store different customer's data, however, for
> Why not using multiple clusters then?

Yet More Firewall Rules to get approved by the Security Team.  And then they 
balk at port 5433 because they've never heard of it.

And from a technical point of view, one Postgresql system can better manage 
the memory on a VM than two which don't know about each other.

-- 
Born in Arizona, moved to Babylonia.

On 5/18/23 11:49, Ron wrote:

On 5/18/23 10:54, Stephen Frost wrote:

Greetings,

* Tony Xu (tony.xu@rubrik.com) wrote:

The FAQ (copied below) mentioned that native transparent data encryption
might be included in 16. Is it fair to assume that it will support database
level encryption, that is, we can use two encryption keys for two databases
in the same server, respectively? How can one verify that?

The current work to include TDE in PG isn't contemplating a per-database
key option.  What's the use-case for that?  Why do you feel that you'd
need two independent keys?

I don't feel that key-per-database us useful; I know that key-per-database is useful, since the databases can be different projects for different companies.  Each wants it's own encryption key so that no one else can get to their at-rest data.

(pg_dump files will automatically be encrypted, right?)

--
Born in Arizona, moved to Babylonia.

Ron, this sounds like a revenue opportunity:  "Oh you want your own key, well then we'll have to spin up another server just for you so you're all separate and special-like.  Way more secure that way."

On Thu, 18 May 2023, Ron wrote:

>> Why not using multiple clusters then?
>
> Yet More Firewall Rules to get approved by the Security Team.  And then they
> balk at port 5433 because they've never heard of it.

But mixing multiple customers on one cluster is much more of a risk.

> And from a technical point of view, one Postgresql system can better manage the
> memory on a VM than two which don't know about each other.

Probably true. Is there something with which multiple clusters running
on the same server can communicate to do that better?

bye,
//mirabilos
--
15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)

On 5/18/23 11:56, Ron wrote:
> On 5/18/23 12:54, Rob Sargent wrote:
>> On 5/18/23 11:49, Ron wrote:

> We need to keep costs down, too.
> 
> Oracle (I think) does it at the DB level, and so does SQL Server. Upper 
> Management hears us say "sorry, no can do" and wonders what bunch of 
> amateurs are developing PostgreSQL.

Looks like you will be migrating to Oracle or SQL Server.

Good luck on keeping costs down.

> 
> -- 
> Born in Arizona, moved to Babylonia.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 18 May 2023, Ron wrote:

Why not using multiple clusters then?

Yet More Firewall Rules to get approved by the Security Team.  And then they
balk at port 5433 because they've never heard of it.

But mixing multiple customers on one cluster is much more of a risk.

And from a technical point of view, one Postgresql system can better manage the
memory on a VM than two which don't know about each other.

Probably true. Is there something with which multiple clusters running
on the same server can communicate to do that better?

On Thu, May 18, 2023 at 01:56:48PM -0500, Ron wrote:
> We need to keep costs down, too.
> 
> Oracle (I think) does it at the DB level, and so does SQL Server.  Upper
> Management hears us say "sorry, no can do" and wonders what bunch of amateurs
> are developing PostgreSQL.

I have found it is hard to protect against the judgment of the ignorant,
so I usually don't bother.

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

  Only you can decide what is important to you.

On 5/18/23 15:56, Bruce Momjian wrote:
> On Thu, May 18, 2023 at 01:56:48PM -0500, Ron wrote:
>> We need to keep costs down, too.
>>
>> Oracle (I think) does it at the DB level, and so does SQL Server.  Upper
>> Management hears us say "sorry, no can do" and wonders what bunch of amateurs
>> are developing PostgreSQL.
> I have found it is hard to protect against the judgment of the ignorant,
> so I usually don't bother.

It's perfectly valid to ask "why can't they do X when our previous vendor 
could?"

I, of course, know that it's because WAL records are global.  "They" then 
quite reasonably ask why the developers haven't fixed that, yet.  The answer 
(I think) is that "global WAL"is deeply embedded in the product, and would 
require a major rewrite; adding new features is a higher priority.

-- 
Born in Arizona, moved to Babylonia.

Greetings,

Please don't top-post on these lists.

* Tony Xu (tony.xu@rubrik.com) wrote:
> Our use-case is for a multi-tenancy scenario - we are considering using
> different databases to store different customer's data, however, for
> cost-efficiency, we want to host them in the same server (to reduce the
> CPU/mem idle time and to reduce the server management efforts). Now there
> is a compliance related feature that we need to let our customer control
> the KEK for their databases so they can rotate their KEKs independently, so
> we cannot use one KEK for the whole PG server. Conceptually, different
> databases are independent of each other, it also makes sense to allow them
> to have completely independent encryption facilities?

This really isn't currently in the plans and while it might be something
added later, as pointed out farther down on this thread, it wouldn't be
possible for the shared catalogs or the WAL to have separate keys for
those things which are relevant to a database, so it's not like each
tenant would actually have control over the key for "all" of their data
(consider that roles are stored in a shared PG catalog and then shared
among databases...).

To meet this compliance requirement, you'd certainly be much more able
to blanket claim that everything is independent by having a separate PG
instance for each client.  This would also allow rather useful things
like being able to do a file-based restore on a per-client basis in the
event something happens, rather than having to roll back an entire
cluster to some point in time just because one client did something
bad..  You'd also be able to scale the number of systems supporting a
given client independently.

Thanks,

Stephen

Greetings,

Really, please don't top-post on these lists.

* Tony Xu (tony.xu@rubrik.com) wrote:
> Regarding the multiple clusters idea, how does that work? Assume we can
> store one customer's data in one cluster, is it possible to have separate
> KEK for different clusters?

In the proposed TDE work, yes, each cluster (which is an entier
PostgreSQL system) would be able to have its own KEK.

> Why not using multiple clusters then?

There's a bit of overhead from each cluster and each would have their
own shared buffers pool of memory and such.

> Better isolation of the customers, but still on one server.

Depending on the OS, multi-cluster management on a given system is
easier or harder.  In my view, at least, Debian systems make having
multiple clusters on a given server a lot easier as they have
pg_createcluster, pg_lsclusters, etc, commands and management tools.

Another alternative would be to use container technology and Kubernetes
or OpenShift and a PG Operator to manage all the clusters across
whatever systems you're running on top of.

Of course, there are trade-offs to consider between all of these
different approaches.

Thanks,

Stephen

Marc Millas schrieb am 21.05.2023 um 13:13:
> Oracle have One (1) DB per instance (in Oracle its not named a cluster as...there is only one !). So ...

Oracle can have multiple (pluggable) databases per instance since Oracle 12

Oracle have One (1) DB per instance (in Oracle its not named a cluster as...there is only one !). So ...

Marc MILLAS

Senior Architect

+33607850334

www.mokadb.com

On Thu, May 18, 2023 at 9:30 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 5/18/23 11:56, Ron wrote:
> On 5/18/23 12:54, Rob Sargent wrote:
>> On 5/18/23 11:49, Ron wrote:

> We need to keep costs down, too.
>
> Oracle (I think) does it at the DB level, and so does SQL Server. Upper
> Management hears us say "sorry, no can do" and wonders what bunch of
> amateurs are developing PostgreSQL.

Looks like you will be migrating to Oracle or SQL Server.

Good luck on keeping costs down.

>
> --
> Born in Arizona, moved to Babylonia.

--
Adrian Klaver
adrian.klaver@aklaver.com

> On May 22, 2023, at 11:02, Tony Xu <tony.xu@rubrik.com> wrote:
> there are still some shared area between clusters.

That's not quite right.  A PostgreSQL cluster (in the traditional sense, which means one PostgreSQL server handling a
particularendpoint) is isolated from any other clusters on the same machine.  If the individual clusters have their own
underlyingvolumes, those can be encrypted independently of any other cluster.  This is not quite as elegant as each
databasein a cluster having its own encryption key, but it does have the advantage that it can be deployed right now. 

A single cluster does have shared areas between databases, and those are not trivial: The global system catalogs, the
write-aheadlog, and the commit log, just for a start.  The global system catalogs include users and roles.  The effort
requiredto split those up is very significant. 

And, to be somewhat blunt, is Rubrik prepared to pay for it?  This engineering effort needs to be funded by someone,
sinceengineers have to eat.  This is not to say that any feature someone is willing to pay for will make it into
PostgreSQL,but an effort of this size, to have any hope of reaching the point of being accepted, will need someone to
paythe salaries of the people working on it. 

Best,
-- Christophe

Thanks Christophe for the clarification. 

That's not quite right.  A PostgreSQL cluster (in the traditional sense, which means one PostgreSQL server handling a particular endpoint) is isolated from any other clusters on the same machine.  

Thanks. I think I had a misunderstanding over the "cluster" eariler. So a PostgreSQL cluster is a PostgreSQL server that we're talking about in general. This means per-DB KEY within a cluster/server is impossible without splitting the whole shared storage among databases, which is indeed a significant amount of work.

 

And, to be somewhat blunt, is Rubrik prepared to pay for it?  This engineering effort needs to be funded by someone, since engineers have to eat.  This is not to say that any feature someone is willing to pay for will make it into PostgreSQL, but an effort of this size, to have any hope of reaching the point of being accepted, will need someone to pay the salaries of the people working on it.

I cannot speak on behalf of Rubrik - I'm just exploring PostgreSQL as a solution for one of my team's projects thus the questions. Nothing is finalized yet but we prefer a managed solution provided by some cloud infra providers so the context I'm getting here will allow me to better understand our provider's solution and their road map.

(please read http://deb.li/quote and don’t top-post)

On Mon, 22 May 2023, Tony Xu wrote:

>First question - do we, as a community, see the value of the proposal and
>do we believe that value is big enough for us to make any necessary changes

I’d rather like to see the energy, if there’s some invested in the
first place, pointed towards creating something like a “super
postmaster” that helps multiple clusters running on the same
machine (even different versions, starting at the first to support
such) manage the entire-machine resources (CPUs, RAM, …) better.

That has use cases beyond encryption (incidentally, multi-tenant,
but perhaps also keeping an older cluster around for stuff that
cannot yet be dangerlessly migrated to a newer PostgreSQL version).

Just my 2¢, uninformed wondering.

bye,
//mirabilos
--
15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)

On 5/22/23 12:38, Ron wrote:
> On 5/22/23 14:22, Tony Xu wrote:

> RDS Postgresql would do the job just fine.  And since you can't get to 
> the files (only access it via port 5432 and aws cli/web, there's no need 
> for TDE.

As I understand TDE whether you can get to the files is not really the 
point. It is that someone/thing can and if they do the files are 
encrypted. Pretty sure RDS is not magical enough to have no access from 
any source to the file system.

> 
> Azure is probably the same.
> 
> -- 
> Born in Arizona, moved to Babylonia.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

> On May 22, 2023, at 13:06, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> As I understand TDE whether you can get to the files is not really the point. It is that someone/thing can and if
theydo the files are encrypted. Pretty sure RDS is not magical enough to have no access from any source to the file
system.

That is true.  (One of the reasons that TDE in cloud hosting is generally a regulatory issue, not really a technical
one.) That being said, RDS does allow the underlying EBS volume to be encrypted, and you can do "bring your own key"
usingtheir keystores. 

On 5/22/23 15:06, Adrian Klaver wrote:
> On 5/22/23 12:38, Ron wrote:
>> On 5/22/23 14:22, Tony Xu wrote:
>
>> RDS Postgresql would do the job just fine.  And since you can't get to 
>> the files (only access it via port 5432 and aws cli/web, there's no need 
>> for TDE.
>
> As I understand TDE whether you can get to the files is not really the 
> point. It is that someone/thing can and if they do the files are 
> encrypted. Pretty sure RDS is not magical enough to have no access from 
> any source to the file system.

They sure hide the raw files well, only allowing access from very restricted 
paths (port

Or maybe we just locked it down tight.

-- 
Born in Arizona, moved to Babylonia.

Greetings,

There seems to be a bit of confusion here, so I'll try to clear it up.

* Tony Xu (tony.xu@rubrik.com) wrote:
> Thanks all for the discussions. It sounds like there are different
> questions to clear before we can get to a conclusion on if per-database KEK
> is possible or not.

It's not possible in PG's design today to ensure that all user data is
segregated by database, due to the fact that there's a shared WAL which
includes data from all databases running under a given postmaster
(typically considered a 'cluster' or 'instance').  There are also shared
catalogs which aren't per-database to begin with (as for if that's
considered user data or not depends on your definition of user data..
are users/roles considered user data, or not?).

> First question - do we, as a community, see the value of the proposal and
> do we believe that value is big enough for us to make any necessary changes
> to PostgreSQL?  Another way to ask is, do we want/need to keep the parity
> with other database solutions that already have such ability?

TDE is clearly valuable to this community and multiple organizations
have implemented it already and there's an ongoing effort to bring TDE
into core, with patches already posted and many of these design
decisions made.  Given the point made above, it seems very unlikely that
the basics of the design are going to change, at least not unless PG
changes in some fundamental way when it comes to WAL and how shared
catalogs and databases work (note: I'm not aware of anyone currently
working on this..).

> If the answer to the first question is no, then I will stop here.
> However, if the answer is yes or "it depends", then the second question is
> on how - It seems that per-cluster KEK can be a a good alternative to
> achieve the same as per-db KEK, however, there are still some shared area
> between clusters. Is it possible to further split this shared area? Are
> there more shared memory/storage between clusters?

There isn't anything shared between PG clusters, to be clear.  A
"cluster" in PG is all data and processes running under one
"postmaster", there is nothing shared between two PG clusters.
Splitting up the WAL to be per-database has been contemplated before but
there's no active work trying to achieve that and there's a lot of
complications associated with trying to do that (such as- what to do
about the shared catalogs...).

Thanks,

Stephen