How to manage column encryptions in migrations?

[ianschmitz]

Link https://supabase.com/docs/guides/database/column-encryption

Describe the problem I'm confused how encrypted columns should be managed using Supabase db migrations. Specifically configuring a new encrypted column in local development and then applying the migration to the remote Supabase project.

I can add a new encrypted column using the UI in local development which works well, and i can use supabase db pull --local to get the diff as a migration, however the migration includes reference to a unique pgsodium key UUID which was created when adding the column via the UI. For example this is what is produced during a diff after adding the column:

set check_function_bodies = off;
create or replace view "public"."decrypted_organization_vendor_api_key" as  SELECT organization_vendor_api_key.id,
    organization_vendor_api_key.created_at,
    organization_vendor_api_key.created_by,
    organization_vendor_api_key.updated_at,
    organization_vendor_api_key.updated_by,
    organization_vendor_api_key.vendor,
    organization_vendor_api_key.organization_id,
    organization_vendor_api_key.value,
        CASE
            WHEN (organization_vendor_api_key.value IS NULL) THEN NULL::text
            ELSE
            CASE
                WHEN ('857431a9-def5-4e4e-87c6-d8b2714d67c4' IS NULL) THEN NULL::text
                ELSE convert_from(pgsodium.crypto_aead_det_decrypt(decode(organization_vendor_api_key.value, 'base64'::text), convert_to(''::text, 'utf8'::name), '857431a9-def5-4e4e-87c6-d8b2714d67c4'::uuid, NULL::bytea), 'utf8'::name)
            END
        END AS decrypted_value
   FROM organization_vendor_api_key;

CREATE OR REPLACE FUNCTION public.organization_vendor_api_key_encrypt_secret_value()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
		BEGIN
		        new.value = CASE WHEN new.value IS NULL THEN NULL ELSE
			CASE WHEN '857431a9-def5-4e4e-87c6-d8b2714d67c4' IS NULL THEN NULL ELSE pg_catalog.encode(
			  pgsodium.crypto_aead_det_encrypt(
				pg_catalog.convert_to(new.value, 'utf8'),
				pg_catalog.convert_to(('')::text, 'utf8'),
				'857431a9-def5-4e4e-87c6-d8b2714d67c4'::uuid,
				NULL
			  ),
				'base64') END END;
		RETURN new;
		END;
		$function$
;

CREATE TRIGGER organization_vendor_api_key_encrypt_secret_trigger_value BEFORE INSERT OR UPDATE OF value ON public.organization_vendor_api_key FOR EACH ROW EXECUTE FUNCTION organization_vendor_api_key_encrypt_secret_value();

Notice the hard coded 857431a9-def5-4e4e-87c6-d8b2714d67c4 in the SQL. This is a reference to an encryption key that was created locally automatically when adding the column in the local UI.

Describe the improvement Migrating an encrypted column from local development to a production/remote Supabase instance should be easy and seamless.

[ianschmitz]

I also notice the security_invoker is not in the diff which is a big security issue.

I've come up with a way to generate this via migration and have it be consistent across multiple environments (local, multiple supabase projects). I would love some feedback if there's anything here that is insecure.

I definitely think there's a lot of room for improvement in the docs around this issue as it is a security risk if implemented improperly.

-- First create the default encryption key
SELECT pgsodium.create_key(name => 'default');

-- Update the created key to have a known UUID that will be consistent across environments.
UPDATE pgsodium.key SET id = '857431a9-def5-4e4e-87c6-d8b2714d67c4' WHERE name = 'default';

-- Next enable encryption on a particular column
SECURITY LABEL FOR pgsodium ON COLUMN public.organization_vendor_api_key.value IS 'ENCRYPT WITH KEY ID 857431a9-def5-4e4e-87c6-d8b2714d67c4';

-- Finally enable `security_invoker` on the generated view to enable RLS on the decrypted view.
-- Note if you encrypt another column on the same table, using the above `SECURITY LABEL` command,
-- it will re-generate the view and thus you will have to re-enable `security_invoker` on the view.
ALTER VIEW public.decrypted_organization_vendor_api_key SET (security_invoker = on);

I see a related Discord post so it seems i'm not the only one confused 😄

https://discord.com/channels/839993398554656828/1150494627372216320

[sweatybridge]

I believe what you are doing is correct, ie. with both the initial schema diff and the manual edits to alter view. The one last step here is to sync your local pgsodium root key to the hosted project.

$ docker exec -it supabase_db_<project> cat /etc/postgresql-custom/pgsodium_root.key
d4dc5b...
$ npx supabase@beta encryption update-root-key
Enter a new root key:

~This would ensure that the encrypted columns in your local and hosted project can be decrypted using the same key.~

EDIT: I probably misread your intention here as wanting to sync the rows within organization_vendor_api_key table between local and hosted project. If you want separately encrypted values, then ignore the last step of syncing pgsodium root key. That would be safer as your local dev environment won't be able to decrypt values stored in hosted project.

We are still working on making these steps easier and documenting them. Apologies for any inconvenience in the meantime.

[ianschmitz]

EDIT: I probably misread your intention here as wanting to sync the rows within organization_vendor_api_key table between local and hosted project. If you want separately encrypted values, then ignore the last step of syncing pgsodium root key. That would be safer as your local dev environment won't be able to decrypt values stored in hosted project.

Correct. I don't have any desire (at this time) to sync encrypted values from production to my local dev machine, which is where your suggestion would come in to play as I understand. I am simply looking for a way to create migrations to add encrypted columns where the migrations will work end-to-end as I push them from local -> staging -> production.

Thanks for the help!