Does net.http_post (from pg_net) no longer accept jsonb_build_object() as a valid body?

[nate-oo]

Hi everyone,

For the life of me I can't figure this one out. Does net.http_post no longer accept jsonb_build_object() as a valid body?

My function is not sending the body in the request for whatever reason when I use jsonb_build_object() as seen in definitions.

1) I have checked that my bodyPayload variable is getting populated in JSON format by raising a log...everything looks good there I believe? but for some reason it apparently won't accept what I have defined as the body.
{"type": "UPDATE", "table": "users", "record": {"id": "832sl....etc", "email": "some.new.email@gmail.com"}, "schema": "auth", "old_record": {"id": "832sl....etc", "email": "some.old.email@gmail.com"}}

2) I tried simplifying the jsonb_build_object() for a test and this does not work:
jsonb_build_object( 'type', tg_op, 'table', tg_table_name, 'schema', tg_table_schema ),

3) I tried simplifying the body even more as a test and this does work:
body:='{"name": "test"}'::jsonb

Here are my trigger and function sql definitions:

-- create the function to securely send the webhook event to our API route
create function auth.webhook_event()
returns trigger 
security definer 
as $$
declare
  url text;
  secret text;
  bodyPayload jsonb;
  request_id bigint;
  signature text;
begin

    url='our endpoint';
    secret='our secret';

    -- Generate the body payload (only sending the columns needed for security)
    bodyPayload = jsonb_build_object(
            'type', tg_op,
            'table', tg_table_name,
            'schema', tg_table_schema,
            'record', jsonb_build_object('id', new.id, 'email', new.email),
            'old_record', jsonb_build_object('id', old.id, 'email', old.email)
              );
              raise log 'payload = %', bodyPayload;

    -- Generate the signature with custom postgres function of ours
    signature = auth.our_sig_generate_function(secret, bodyPayload::text);

    
    -- Send the webhook request (comma separated order matters)
    select http_post
    into request_id
    from
        net.http_post(
                url:=url,
                body:=jsonb_build_object(
                        'type', tg_op,
                        'table', tg_table_name,
                        'schema', tg_table_schema,
                        'record', jsonb_build_object('id', new.id, 'email', new.email),
                        'old_record', jsonb_build_object('id', old.id, 'email', old.email)
                ), 
                params:=jsonb_build_object(
                        'event_name', 'updated_email' 
                ),
                headers:=jsonb_build_object(
                        'Content-Type', 'application/json',
                        'X-Supabase-Signature', signature
                ),
                timeout_milliseconds:=1000
        );

    -- Insert the request ID into the Supabase hooks table
    insert into supabase_functions.hooks
        (hook_table_id, hook_name, request_id)
    values (tg_relid, tg_name, request_id);

    return new;
end;
$$ language plpgsql;


-- create the webhook trigger to call the function (only on update of the email field)
create trigger on_auth_users_email_address_update
after update of email on auth.users
for each row 
when (old.email is distinct from new.email)
execute procedure auth.webhook_event();

Any help is appreciated!
Thanks, Nate

[nate-oo]

I figured it out. My timeout of 1000ms was too low. I bumped it up to 5000ms and that solved my issue.