[zaroth]

One thing I think missing from this write-up is to walk through how the Restore process will work with encrypted data under pgsodium.

Namely what will happen when you first restore some data into a new Postgres instance which booted with its own randomly generated root key (the wrong key) and then how you are supposed to patch in the correct key and be able to start reading secrets again?

Also, how does the decrypted view look if you try to read it with the wrong key loaded?

Do you have to worry about a race condition where you boot an instance with some encrypted data but forget to put the key file in place, and then end up with a new random key, saving some new data, and now you have a mix of rows encrypted with two different keys? Or will the whole subsystem block if there’s data stored that can’t be decrypted with the resident key?

[michelpp]

> Namely what will happen when you first restore some data into a new Postgres instance which booted with its own randomly generated root key (the wrong key) and then how you are supposed to patch in the correct key and be able to start reading secrets again?

We restore you're original key into new projects. There is also WIP on accessing the key through the API and CLI.

> Also, how does the decrypted view look if you try to read it with the wrong key loaded?

The decryption will fail (pgsodium will thrown an error).

> Do you have to worry about a race condition where you boot an instance with some encrypted data but forget to put the key file in place, and then end up with a new random key, saving some new data, and now you have a mix of rows encrypted with two different keys? Or will the whole subsystem block if there’s data stored that can’t be decrypted with the resident key?

There's no race in the system, your key is put in place by us before the server boots.

Thanks for the feedback! I'll put some more thought into your question about authenticating a key is the original before you use it.

[zaroth]

Thank you for the quick reply! I’m not a Supabase customer so apologies if the questions don’t make sense in your context.

But I think it would help to understand if Supabase is fully managing key backup and recovery internally, how exactly is that working?

Ultimately the whole value of TDE at the database layer comes down to two things IMO which are flip sides of the same coin;

1) Being able to store your database backups in less trusted locations,

2) actually keeping the secret data secret, which amounts to keeping that encryption key secured at a much higher level than the database backup itself.

In the end it’s just key vaults all the way down, isn’t it!

[michelpp]

> But I think it would help to understand if Supabase is fully managing key backup and recovery internally, how exactly is that working?

Supabase persists and protects your key and we will provide API and CLI access to retrieve it securely. This is a pre-release so we haven't worked out all the use cases yet but those are the basics for MVP.

> 1) Being able to store your database backups in less trusted locations,

Yes. Using Transparent Column Encryption you control on a column by column basis how your data is stored encrypted so you have more fine grained control over your data.

> 2) actually keeping the secret data secret, which amounts to keeping that encryption key secured at a much higher level than the database backup itself.

Yep, we don't have all the answers there, keeping the root key out of SQL is a big one. Maybe requiring MFA to access the key even with the API, there are a lot of possibilities. Thanks for your feedback these are all going into my notes for an upcoming release.

[jjnoakes]

The article links directly to here, which may answer your question:

https://github.com/michelp/pgsodium#server-key-management

[zaroth]

It doesn’t seem to address the negative test cases either!