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

postgres

but when I try via my React app (which has role

authenticated

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

How to access secrets inside triggers in local Docker

postgres

postgres

but when I try via my React app (which has role

authenticated

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

How to access secrets inside triggers in local Docker

Similar Threads

How to access secrets inside triggers in local Docker

Similar Threads

Similar Threads

Similar Threads