S
Supabase•2mo ago
PeanutBuddha

Can't use vault inside trigger locally

I'm using Docker on Windows WSL2 I'm using the Supabase JS SDK to call a function which reads from the vault: 
Object { code: "22000", details: null, hint: null, message: "pgsodium_crypto_aead_det_decrypt_by_id: invalid ciphertext" }
Object { code: "22000", details: null, hint: null, message: "pgsodium_crypto_aead_det_decrypt_by_id: invalid ciphertext" }
When I call it from my SQL client (DBeaver) it works properly. Other function calls and SQL queries work correctly. My simple func: 
CREATE OR REPLACE FUNCTION public.get_api_url()
 RETURNS text
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public', 'vault', 'pg_temp'
AS $function$
DECLARE
    api_url TEXT;
BEGIN
    SELECT decrypted_secret
    INTO api_url
    FROM vault.decrypted_secrets
    WHERE name = 'api_url'
    LIMIT 1;

    RETURN api_url;
END;
$function$
;
CREATE OR REPLACE FUNCTION public.get_api_url()
 RETURNS text
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public', 'vault', 'pg_temp'
AS $function$
DECLARE
    api_url TEXT;
BEGIN
    SELECT decrypted_secret
    INTO api_url
    FROM vault.decrypted_secrets
    WHERE name = 'api_url'
    LIMIT 1;

    RETURN api_url;
END;
$function$
;
3 Replies
ihm40
ihm40•2mo ago
are your other functions that you are calling using vault?
PeanutBuddha
PeanutBuddhaOP•2mo ago
They arent being called at all. This is an issue with using the REST API to call a SQL func
ihm40
ihm40•2mo ago
one thing i'm wondering is why pg_temp and public are being set as search paths. Mainly because you only read from vault. i don't think that is the issue and that it's something to do with vault but possible worth investigating if you need those search paths? otherwise a support ticket is your best bet

Did you find this page helpful?