net.http_post permission denied in trigger

I'm trying to use pg_net's

net.http_post

net.http_post

net.http_post

net.http_post within an AFTER INSERT trigger on the

auth.audit_log_entries

auth.audit_log_entries

auth.audit_log_entries

auth.audit_log_entries table, but I keep getting Internal Server Error with the error message "ERROR: permission denied for schema net (SQLSTATE 42501)".

I tried possible fixes like https://github.com/supabase/supabase/issues/4883#issuecomment-1017955034, but nothing works for me.

Does anyone have an idea?

code:

CREATE EXTENSION IF NOT EXISTS pg_net;

CREATE OR REPLACE FUNCTION stream_auth_logs()
RETURNS trigger AS $$
BEGIN
  select net.http_post(
        url:='...',
        body:='{"hello": "world"}'::jsonb
  );

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER stream_auth_logs_trigger on auth.audit_log_entries;

CREATE TRIGGER stream_auth_logs_trigger
AFTER INSERT ON auth.audit_log_entries
FOR EACH ROW
EXECUTE PROCEDURE stream_auth_logs();

CREATE EXTENSION IF NOT EXISTS pg_net;

CREATE OR REPLACE FUNCTION stream_auth_logs()
RETURNS trigger AS $$
BEGIN
  select net.http_post(
        url:='...',
        body:='{"hello": "world"}'::jsonb
  );

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER stream_auth_logs_trigger on auth.audit_log_entries;

CREATE TRIGGER stream_auth_logs_trigger
AFTER INSERT ON auth.audit_log_entries
FOR EACH ROW
EXECUTE PROCEDURE stream_auth_logs();

CREATE EXTENSION IF NOT EXISTS pg_net;

CREATE OR REPLACE FUNCTION stream_auth_logs()
RETURNS trigger AS $$
BEGIN
  select net.http_post(
        url:='...',
        body:='{"hello": "world"}'::jsonb
  );

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER stream_auth_logs_trigger on auth.audit_log_entries;

CREATE TRIGGER stream_auth_logs_trigger
AFTER INSERT ON auth.audit_log_entries
FOR EACH ROW
EXECUTE PROCEDURE stream_auth_logs();

CREATE EXTENSION IF NOT EXISTS pg_net;

CREATE OR REPLACE FUNCTION stream_auth_logs()
RETURNS trigger AS $$
BEGIN
  select net.http_post(
        url:='...',
        body:='{"hello": "world"}'::jsonb
  );

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER stream_auth_logs_trigger on auth.audit_log_entries;

CREATE TRIGGER stream_auth_logs_trigger
AFTER INSERT ON auth.audit_log_entries
FOR EACH ROW
EXECUTE PROCEDURE stream_auth_logs();

GitHub

Permissions denied for table using API but works with SupabaseClien...

Bug report Describe the bug When trying to access data from some API endpoints, like this: /rest/v1/leads?select=* I get this error: { "message": "permission denied for t...

garyaustin•11/5/22, 2:07 PM

I don't think those grants you applied include the supabase_auth_admin role, used by gotrue. Since this is a trigger function you could make it "security definer" type and then it would have permission to run it.

EykOP•11/5/22, 3:08 PM

Okay, we got a bit further. The permission denied issue seems resolved, but the next issue followed:

EykOP•11/5/22, 3:09 PM

The query statement is causing this issue but I cannot imagine why

garyaustin•11/5/22, 3:10 PM

select requires you use the result. You should use "perform" instead of select if you don't want a result. https://stackoverflow.com/questions/10115806/select-or-perform-in-a-pl-pgsql-function

Stack Overflow

SELECT or PERFORM in a PL/pgSQL function

I have this function in my database:

CREATE OR REPLACE FUNCTION "insertarNuevoArticulo"(nombrearticulo character varying, descripcion text, idtipo integer, idfamilia bigint, artstock integer, mins...

EykOP•11/5/22, 3:13 PM

It works! Thanks so much m8, saved my weekend

net.http_post permission denied in trigger

Similar Threads

Similar Threads

Similar Threads