Supabase•3y ago

net.http_post permission denied in trigger

I'm trying to use pg_net's net.http_post within an AFTER INSERT trigger on the 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();

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...

5 Replies

garyaustin•3y ago

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•3y ago

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

EykOP•3y ago

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

garyaustin•3y ago

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•3y ago

It works! Thanks so much m8, saved my weekend

Gaming

Programming

net.http_post permission denied in trigger

Did you find this page helpful?