Column Encryption is SQL-only now

[kiwicopple]

tldr:

Support for column encryption in the table editor has been removed. You can still use it, but you must use SQL. Your data is already encrypted-at-rest, so this is an advanced feature that should be used sparingly.

How it was previously

Previously, the Table Editor in the Supabase dashboard supported encrypting newly created columns using pgsodium’s Transparent Column Encryption (TCE).

Why we’re changing it

While this makes it easy to use, we found that the easiness has led to a lot of “mis-use” of Encryption. We’ve decided to remove it from the UI for now because TCE has a few sharp edges and the dashboard makes it too easy to encrypt columns without considering trade-offs.

This mis-use led to multiple users frequently running into unrecoverable issues with encryption. A non-exhaustive list of issues which we observed users running into when using TCE through the dashboard includes the following:

• TCE is prone to inappropriate usage - we’ve seen users encrypting all kinds of stuff that does not need to be encrypted (e.g email address of sender/receivers). This incurs a performance penalty and results in a bad experience.
• TCE makes migrating between projects (or local to hosted) a problem as you’d also have to copy the root encryption key separately, although this is nonetheless by design. Developers should be aware that “just works” and “advanced encryption” are very difficult goals to align.
• Triggers (which are used by TCE) are executed in alphabetical order. When users add their own triggers on encrypted tables, they are frequently unaware if they are dealing with encrypted or unencrypted contents which has been a source of confusion.
• Upserting into an encrypted column could produce doubly encrypted content.
• Since TCE uses a view into an encrypted table, RLS rules that are applied on the underlying table do not apply to the views as views use the permissions of the creator rather than the query-er, leading to another source of confusion. There is a fix for this which is to add a security label to pg_sodium to make the view a security invoker.

If you want TCE, use SQL instead

As of now, you can use TCE in SQL by following the pg_sodium documentation so users who already are using TCE can continue doing so via the SQL editor on the dashboard, while new users will have to learn the nuts and bolts of what they are doing before trying to use the feature.

[vinch]

@kiwicopple Thanks for the update and I understand the reasons even though it makes life more complicated for people who were using the feature as intended.

From the pgsodium documentation, I found this snippet and I think that's what Supabase was doing behind the scenes:

SECURITY LABEL FOR pgsodium	ON COLUMN private.users.secret
  IS 'ENCRYPT WITH KEY ID dfc44293-fa78-4a1a-9ef9-7e600e63e101';

Do you confirm that it is the case?

Thank you!

[EduardMe]

Append SECURITY INVOKER at the end if you want the view to use the RLS policies setup for the table, as in

SECURITY LABEL FOR pgsodium	ON COLUMN public.table_name.column_name
  IS 'ENCRYPT WITH KEY ID <your key id> SECURITY INVOKER';

[AlexIsMaking]

Thanks for the heads up, I can imagine plenty of people getting caught out by that 😅

[vinch]

Nevermind, I confirm that's the way it works, so it's fairly easy to implement directly with SQL.

[lunchpaillola]

Any one know where to look for the encryption key location? The documentation just redirects back to the top of the page https://supabase.com/docs/guides/database/extensions/pgsodium#encryption-key-location

[EduardMe]

The key is in the vault. But you don't need the location to use it as far as I know.

[lunchpaillola]

Thanks a ton @EduardMe!

[jstdk]

What is meant by "Your data is already encrypted-at-rest"? Does Supabase already encrypt the whole DB on creation?

[Parfyonator]

@kiwicopple @EduardMe can you confirm or refute this? Does this mean volume-level encryption or something else?

[EduardMe]

I'm not 100% sure, I think it means the data is encrypted on disk, but once it's loaded, i.e. accessed it's definitely decrypted. I have no way to test this though. You would need some admin access to the database "from outside" and look at the data.

[Parfyonator]

@EduardMe thanks for response. Connecting using psql gives me the same data as Supabase dashboard does. So I'm not sure if it is the valid way to test if it refutes the data-at-rest encryption.
If it's volume-level encryption then probably AWS EBS takes care of it and not Supabase itself. I don't know how I should test in this case. I'm using one service that requires answers to questions like this and I need to know what is going on under the hood.

[EduardMe]

I guess it's a question for the Supabase team, I'm also curious, though not necessarily need it.

[williamneves]

Oh no!

That was a shock for me. Could you guys show me the documentation to know how to this, step by step. Also the view table, was created automatic.

I have a entire project build on top of this feature :-(.

[EduardMe]

If you still have the view table and the triggers / functions, you can rebuild it with this by copy paste. There was also info on Supabase pages I checked last how to set this up manually.

[erezhod]

I was able to apply encryption on a specific table column as @vinch and @EduardMe suggested using

SECURITY LABEL FOR pgsodium ON COLUMN public.table_name.column_name IS 'ENCRYPT WITH KEY ID <your key id> SECURITY INVOKER';

But this only encrypts new data coming in. How do I go about encrypting already existing column data?

🎯 Edit: I have found a temporary solution - export the existing table data and re-import it. It will encrypt all of the incoming data.

[EduardMe]

As a basis, you can use something like this to encrypt data manually and overwrite what you got:

select * from pgsodium.crypto_aead_det_encrypt (
	'this is the message',  -- a message to encrypt
	'this is associated data',  -- some authenticated associated data
	'd45ggcd8-1851-4c5c-8avv-8dd20291c699'::uuid -- key ID
);