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.

Ok, thanks for the clarification. Any particular reason for the change of
plans? Would it come in 17?

Thanks
Tony

On Wed, May 17, 2023, 5:49 PM Bruce Momjian <bruce@momjian.us> wrote:

It is in epas15, but for the whole cluster. Different keys for each database is not possible, how should it works for instance the wal - stream?

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

Thanks for the information, Andreas, Stephen.

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?

Thanks,
Tony

On Thu, May 18, 2023 at 8:54 AM Stephen Frost <sfrost@snowman.net> 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.

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, 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 12:54, Rob Sargent wrote:

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."

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.

--
Born in Arizona, moved to Babylonia.

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 5/18/23 14:07, Thorsten Glaser wrote:

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.

Better have your roles and pg_hba.conf entries defined correctly!

Fortunately, pg_hba.conf is pretty easy.

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?

A /meta/ postmaster!!!

--
Born in Arizona, moved to Babylonia.

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

Thanks all for the discussions. New to PostgreSQL so don't have much
context here.

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?

Why not using multiple clusters then?

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

On Thu, May 18, 2023 at 3:53 PM Stephen Frost <sfrost@snowman.net> wrote:

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