S
Supabase•4w ago
PeanutBuddha

How to access secrets inside triggers in local Docker

I have a simple trigger (with security definer) which calls a function (with security definer) which then uses the recommended way to get a decrypted secret from the vault. This works in my client DBeaver when connected as postgres but when I try via my React app (which has role authenticated ) it gives a decryption error: 
failed: pgsodium_crypto_aead_det_decrypt_by_id: invalid ciphertext
failed: pgsodium_crypto_aead_det_decrypt_by_id: invalid ciphertext
my function: 
CREATE OR REPLACE FUNCTION callEdgeFunctionInsert(
    function_name TEXT,
    new_record JSONB
)
RETURNS VOID
SECURITY DEFINER
AS $$
DECLARE
    api_url TEXT;
    service_key TEXT;
    payload JSONB;
BEGIN
    SELECT decrypted_secret INTO api_url 
    FROM vault.decrypted_secrets 
    WHERE name = 'api_url' 
    LIMIT 1;
    
    SELECT decrypted_secret INTO service_key 
    FROM vault.decrypted_secrets 
    WHERE name = 'service_key' 
    LIMIT 1;

    payload := jsonb_build_object('record', new_record);

    PERFORM ...
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION callEdgeFunctionInsert(
    function_name TEXT,
    new_record JSONB
)
RETURNS VOID
SECURITY DEFINER
AS $$
DECLARE
    api_url TEXT;
    service_key TEXT;
    payload JSONB;
BEGIN
    SELECT decrypted_secret INTO api_url 
    FROM vault.decrypted_secrets 
    WHERE name = 'api_url' 
    LIMIT 1;
    
    SELECT decrypted_secret INTO service_key 
    FROM vault.decrypted_secrets 
    WHERE name = 'service_key' 
    LIMIT 1;

    payload := jsonb_build_object('record', new_record);

    PERFORM ...
END;
$$ LANGUAGE plpgsql;
When I INSERT from my DB client I can see the trigger logging: 
NOTICE:  session_user=postgres, current_user=postgres
NOTICE:  session_user=postgres, current_user=postgres
But from postgrest I can see the role is different: 
authenticator@postgres ERROR:  pgsodium_crypto_aead_det_decrypt_by_id: invalid ciphertext
authenticator@postgres ERROR:  pgsodium_crypto_aead_det_decrypt_by_id: invalid ciphertext
I'm using the basic docker/compose setup with latest image and SDK It doesnt seem to be an issue in my production/cloud/supabase version of my project
3 Replies
ihm40
ihm40•4w ago
Could i ask what you mean when you say your trigger has security definer? I couldn't find anything related to setting such privileges for triggers?
PeanutBuddha
PeanutBuddhaOP•4w ago
Sorry I mean the func the trigger calls
ihm40
ihm40•4w ago
How have you set up the vault secrets, was it via the UI? Is there any chance you have done any schema dump recently from remote?

Did you find this page helpful?