Horton
Menu

Thread

  1. Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Tony Xu <tony.xu@rubrik.com> — 2023-05-17T22:35:39Z

    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?

Thanks
Tony




*https://www.postgresql.org/about/press/faq/
<https://www.postgresql.org/about/press/faq/>*

*Q: What features will PostgreSQL 16 have?A: As always, we can't be certain
what will go in and what won't; the project has strict quality standards
that not all patches can make before deadline. All we can tell you is
what's currently being worked on, which includes native transparent data
encryption support, continued improvements to logical replication,
parallelism, partitioning, and vacuuming, and many more features. By the
time 16 is released, though, this list may have changed considerably.*

  2. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Bruce Momjian <bruce@momjian.us> — 2023-05-18T00:49:01Z

    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.

  3. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Tony Xu <tony.xu@rubrik.com> — 2023-05-18T05:27:54Z

    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:

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

  4. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Andreas Kretschmer <andreas@a-kretschmer.de> — 2023-05-18T09:31:08Z

    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?

On 18 May 2023 00:35:39 CEST, Tony Xu <tony.xu@rubrik.com> 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?
>
>Thanks
>Tony
>
>
>
>
>*https://www.postgresql.org/about/press/faq/
><https://www.postgresql.org/about/press/faq/>*
>
>*Q: What features will PostgreSQL 16 have?A: As always, we can't be certain
>what will go in and what won't; the project has strict quality standards
>that not all patches can make before deadline. All we can tell you is
>what's currently being worked on, which includes native transparent data
>encryption support, continued improvements to logical replication,
>parallelism, partitioning, and vacuuming, and many more features. By the
>time 16 is released, though, this list may have changed considerably.*

  5. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Stephen Frost <sfrost@snowman.net> — 2023-05-18T15:54:31Z

    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

  6. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Tony Xu <tony.xu@rubrik.com> — 2023-05-18T17:00:42Z

    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:

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

  7. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2023-05-18T17:49:20Z

    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.

  8. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Rob Sargent <robjsargent@gmail.com> — 2023-05-18T17:54:05Z

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

  9. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Thorsten Glaser <tg@evolvis.org> — 2023-05-18T18:02:35Z

    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 :-)

  10. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-05-18T18:23:17Z

    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

  11. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2023-05-18T18:51:10Z

    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.

  12. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2023-05-18T18:56:48Z

    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.

  13. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Thorsten Glaser <tg@evolvis.org> — 2023-05-18T19:07:32Z

    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 :-)

  14. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2023-05-18T19:30:23Z

    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

  15. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2023-05-18T19:45:56Z

    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.

  16. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Bruce Momjian <bruce@momjian.us> — 2023-05-18T20:56:39Z

    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.

  17. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2023-05-18T21:10:02Z

    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.

  18. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Stephen Frost <sfrost@snowman.net> — 2023-05-18T22:53:18Z

    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

  19. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Tony Xu <tony.xu@rubrik.com> — 2023-05-18T23:42:13Z

    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,
>
> 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
>

  20. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Stephen Frost <sfrost@snowman.net> — 2023-05-18T23:47:00Z

    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

  21. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Marc Millas <marc.millas@mokadb.com> — 2023-05-21T11:13:44Z

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

  22. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Theodore M Rolle, Jr. <stercor@gmail.com> — 2023-05-21T17:15:38Z

    What about sqlcipher?

On Sun, May 21, 2023, 07:16 Marc Millas <marc.millas@mokadb.com> wrote:

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

  23. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Marc Millas <marc.millas@mokadb.com> — 2023-05-21T19:10:59Z

    ?? the sqlite thing ??

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Sun, May 21, 2023 at 7:15 PM Theodore M Rolle, Jr. <stercor@gmail.com>
wrote:

> What about sqlcipher?
>
> On Sun, May 21, 2023, 07:16 Marc Millas <marc.millas@mokadb.com> wrote:
>
>> 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
>>>
>>>
>>>
>>>

  24. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    shammat@gmx.net — 2023-05-21T19:46:46Z

    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

  25. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2023-05-21T20:01:11Z

    db... schema... cross-db terms get confusing when different systems use the 
same word to mean different things.

On 5/21/23 06:13, Marc Millas wrote:
> 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 <http://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
>
>
>

-- 
Born in Arizona, moved to Babylonia.

  26. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Theodore M Rolle, Jr. <stercor@gmail.com> — 2023-05-21T21:00:29Z

    Yes, the SQLite thing.

On Sun, May 21, 2023, 15:11 Marc Millas <marc.millas@mokadb.com> wrote:

> ?? the sqlite thing ??
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>
>
> On Sun, May 21, 2023 at 7:15 PM Theodore M Rolle, Jr. <stercor@gmail.com>
> wrote:
>
>> What about sqlcipher?
>>
>> On Sun, May 21, 2023, 07:16 Marc Millas <marc.millas@mokadb.com> wrote:
>>
>>> 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
>>>>
>>>>
>>>>
>>>>

  27. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Marc Millas <marc.millas@mokadb.com> — 2023-05-21T21:38:29Z

    Hi Thomas,

to my understanding, there is ONE master key for TDE in 12c.

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Sun, May 21, 2023 at 9:47 PM Thomas Kellerer <shammat@gmx.net> wrote:

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

  28. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Tony Xu <tony.xu@rubrik.com> — 2023-05-22T18:02:04Z

    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.

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?

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?


> In the proposed TDE work, yes, each cluster (which is an entier
>> PostgreSQL system) would be able to have its own KEK.
>>
>> There's a bit of overhead from each cluster and each would have their
>> own shared buffers pool of memory and such.
>>
>
Thanks
Tony

  29. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Christophe Pettus <xof@thebuild.com> — 2023-05-22T18:16:20Z

    
> 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 particular endpoint) is isolated from any other clusters on the same machine.  If the individual clusters have their own underlying volumes, those can be encrypted independently of any other cluster.  This is not quite as elegant as each database in 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-ahead log, and the commit log, just for a start.  The global system catalogs include users and roles.  The effort required to 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, 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.

Best,
-- Christophe

  30. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Tony Xu <tony.xu@rubrik.com> — 2023-05-22T19:22:42Z

    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.

Thanks
Tony

  31. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2023-05-22T19:38:39Z

    On 5/22/23 14:22, Tony Xu wrote:
> 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.

"Cluster" is to Postgres as "Instance" is to SQL Server

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

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.

Azure is probably the same.

-- 
Born in Arizona, moved to Babylonia.

  32. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Thorsten Glaser <tg@evolvis.org> — 2023-05-22T19:53:24Z

    (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 :-)

  33. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2023-05-22T20:06:56Z

    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

  34. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Christophe Pettus <xof@thebuild.com> — 2023-05-22T20:08:58Z

    
> 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 they do 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" using their keystores.

  35. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2023-05-22T20:37:29Z

    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.

  36. Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

    Stephen Frost <sfrost@snowman.net> — 2023-05-23T12:46:57Z

    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