Delete example

[softmarshmallow]

Improve documentation

Link

https://supabase.com/docs/guides/database/vault

Describe the problem

We don't have a example of deleting a secret, only creating and updating

[ads-silva]

Try do it: DELETE FROM vault.secrets WHERE name = 'secret_name';

[casrar]

I'm also having trouble with this, there's no documentation for deleting a key/secret pair. While your example works, it seems anytime I create a new secret, it creates a new key as well. So if I keep adding and deleting secrets, I end up with a large list of orphaned keys.

[lightstrike]

Agreed that it would be very helpful to have an example of deleting Supabase vault secrets in the documentation.

[Rinzyy]

There seem to be no documentation on that but for me i had to create my own RPC function that can only be called by server via service role keys, works.

CREATE OR REPLACE FUNCTION public.delete_vault_secret_by_id(ref_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = vault
AS $$
BEGIN
    -- Only allow calls using the service role
    IF current_setting('role') != 'service_role' THEN
        RAISE EXCEPTION 'Authentication required: this function can only be called by the service role.';
    END IF;

    -- Perform the deletion
    DELETE FROM vault.secrets
    WHERE id = ref_id;
END;
$$;

[pafa7a]

That's actually a valid point. Every time you're deleting a record from vault.secrets, you left with +1 orphan key. Found out that it's stored in pgsodium.key table, so we can update our function to delete the record from there as well:

  -- Get the vault secret UUID and its associated key_id
  SELECT id, key_id INTO secret_uuid, key_uuid
  FROM vault.secrets
  WHERE name = secret_name
  LIMIT 1;

  -- Delete from vault.secrets
  IF secret_uuid IS NOT NULL THEN
    DELETE FROM vault.secrets WHERE id = secret_uuid;
  END IF;
  
  -- Delete the orphaned key from pgsodium.key
  IF key_uuid IS NOT NULL THEN
    DELETE FROM pgsodium.key WHERE id = key_uuid;
  END IF;

However, it's worth mentioning that in the documentation or potentially create cascade (I prefer the second)