Having a few problems with stored procedures.

[timuckun]

I wrote a stored procedure to allow unauthenticated users to login and get a JWT token from the database. This stored proc uses a table called sessions to keep track of logins. The session has an id, user id, start time, end time and jwt as columns.

I then exposed this function as a mutation in hasura. Here are the problems.

1. The mutation does not show up in the browser.
2. If I untrack the function it doesn't show up as untracked. It seems like I can't stop tracking this. I have reloaded the database and the hasura metadata to no avail. I have even restarted hasura.
3. In order to allow the users to be able to call this function I have to also allow them to be able to select on the sessions table which I most definitely do not want.

in postgres I can set the function as security definer. Can something similar be done with hasura. If not what's a good way to deal with this?

I tried returning a setof some_custom_type_thats_not_a_table but that doesn't work either. I would be great if hasura just allowed me to return a JSON from a function or something.

Thanks.

[BenoitRanque]

1. if by in the browser, you mean for unauthenticated users: do not forget to set the anonymous role and grant that role access to the mutation
2. I'm going to need more details on that, may do some testing, it sounds weird and may be a bug?
3. You can work around this by creating an empty table and using that as your return type. I do understand this isn't ideal, but is is mentioned in the documentation

If the SETOF table doesn’t already exist or your function needs to return a custom type i.e. row set, create and track an empty table with the required schema to support the function before executing the above steps.

Alternatives: Computed fields allow you to return scalar values (which may include a json string) but they cannot be mutations.
I would also suggest you take a look at actions, and auth webhooks

[timuckun]

I will redo the code to handle the login using a dummy table as the return type. I wanted to avoid actions and webhooks and just the database which is already there and hasura has already connected to. It seems like hasura should offer this a feature where you can define a stored proc to call instead of a webhook.

Thanks for the help. I will redo the stored proc first and get back to this topic about point 2. I think I'll redo the proc so that it's a select and not a mutation which makes point 1 go away.