Stored procedures and private schema

[shorn]

Context: I'm trying to store errors in my DB and I don't want that table to be public.

So I thought I'd try to make a stored procedure that stores the data into a table in a private schema.

Here's my setup SQL:

create schema private;

-- drop table private.cabbage_error;

create table private.cabbage_error (
  id integer generated by default as identity
    constraint cabbage_error_pkey primary key,
  content json not null
);

grant insert, select, update, delete, truncate, references, trigger on private.cabbage_error to supabase_auth_admin;


-- drop procedure public.store_error;

-- adding "security definer" didn't make it able to be called
create or replace procedure public.store_error(json_content jsonb)
  language sql
as $$
insert into private.cabbage_error (content) values (json_content);
$$;

grant execute on procedure public.store_error(jsonb) to anon;

-- works
-- call public.store_error('{"test": "test2"}'::jsonb);

When I try to call this stored procedure with the following code:

function StoreSbErrorCard(){
  const {db} = useSupabase();

  return <ContainerCard title={<>Store supabase error</>} >
    <SecondaryButton
      onClick={async (e)=>{
        stopClick(e);
        log.debug("storing error to supabase")
        const result = await db.rpc('store_error', {clientError: 'some value'});
        log.debug("store result", result);
      }}
    >Store error</SecondaryButton>
  </ContainerCard>
}

The POST request fails with a 404 status.

What am I doing wrong?

[steve-chavez]

Can you try creating a function instead of a procedure? Do create or replace function instead of create or replace procedure.

Procedures have some limitations for now(can't do nested transactions, for one) and they're not supported.

Also, I'd recommend sticking to functions, they have more flexible RETURN types.

[shorn]

Same problem.

grant execute on function public.store_error_v2(jsonb) to anon;

select public.store_error_v2('{"test": "test2"}'::jsonb);

create or replace function public.store_error_v3(json_content jsonb)
returns text
  language plpgsql
as $$
  declare
  begin
    return 'test';
  end
$$;

grant execute on function public.store_error_v3(jsonb) to anon;

select public.store_error_v3('{"test": "test2"}'::jsonb);

Both the stored procedure and the functions are visible in the "/ API / stored procedures" section of the UI.

[steve-chavez]

Make sure your payload matches the function arguments names. In your previous example:

const result = await db.rpc('store_error', {clientError: 'some value'});

clientError should be json_content.

[shorn]

Yes, I didn't realise the parameters were named, I just assumed they were positional.

This appears to work for invoking the function:

        const result = await db.rpc('store_error_v3', {json_content: {'someerror': 'somevalue'}});

Given a function definition of:

create or replace function public.store_error_v3(json_content jsonb)
returns void security definer
  language plpgsql
as $$
declare
begin
  insert into private.cabbage_error (content) values(json_content);
end
$$;

Though now I get a 403 error, presumably something to do with permissions related to having the table in a separate schema?

EDIT: To resolve the 403 error, I just needed to add security definer to make it work across schemas. Thankyou for your help.