http_post not found when using http extension in trigger

[j-mendez]

A basic example creating a POST with HTTP extension does not work.

``
supabase_auth_admin@postgres WARNING: Failed to notify webhook: function net.http_post(unknown, text[], bytea) does not exist

``
 supabase_auth_admin@postgres WARNING:  Failed to notify webhook: function http_post(unknown, text[], bytea) does not exist

The extension is not installed correctly.

[j-mendez]

A basic example creating a POST with HTTP extension does not work.

`` supabase_auth_admin@postgres WARNING: Failed to notify webhook: function net.http_post(unknown, text[], bytea) does not exist

``
 supabase_auth_admin@postgres WARNING:  Failed to notify webhook: function http_post(unknown, text[], bytea) does not exist

The extension is not installed correctly.

https://supabase.com/docs/guides/database/extensions/http?queryGroups=database-method&database-method=sql this guide was referenced with issues.

[j-mendez]

CREATE EXTENSION IF NOT EXISTS http;

CREATE OR REPLACE FUNCTION notify_user_webhook()
RETURNS trigger AS $$
DECLARE
  response text;
BEGIN
  PERFORM
    http_post(
      'https://myexamplewebsite/new-user',
      json_build_object(
        'data', NEW
      )::text::bytea,
      'Content-Type', 'application/json'
    );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER new_user_notify_trigger
AFTER INSERT ON profiles
FOR EACH ROW
EXECUTE FUNCTION notify_user_webhook();

[olirice]

The issue that you're running into is this

The "search_path" of the of where postgres looks for functions is different depending on how a function is executed. If the postgres role executes it, it may look in public and extensions. If its executed by supabase_auth_admin or authenticated by an API user, as in this case, that path may be different.

Our security and performance advisor flags would flag this. The solution is to set a the search_path in the function explicitly to an empty string and then fully qualify the function call http_post

CREATE OR REPLACE FUNCTION notify_user_webhook()
RETURNS trigger
  SET search_path = '' -- NEW LINE
AS $$
DECLARE
  response text;
BEGIN
  PERFORM
    extensions.http_post(       -- LINE EDITED
      'https://myexamplewebsite/new-user',
      json_build_object(
        'data', NEW
      )::text::bytea,
      'Content-Type', 'application/json'
    );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

I'm going to close this, but feel free to follow up