Problem with function login

[lokize]

Hi guys, i have this problem when i try call endpoint /rpc/login:

{ "message": "Could not find the public.login() function in the schema cache",
"hint": "If a new function was created in the database with this name and parameters, try reloading the schema cache."
}

but i created function login in public schema not in cache, please how to fix that?

[laurenceisla]

Hi! The error mentions that the function cannot be found in the Schema Cache that PostgREST uses to get metadata from the database. What may be happening is that you had PostgREST running when you created the function, causing the cache to be stale. Try reloading it to see if it works.

If it does not help, share the function definition to see how it was created.

[lokize]

thanks for helping

not work, my function login is:

CREATE OR REPLACE FUNCTION cache.login(email text, pass text)
 RETURNS basic_auth.jwt_token
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
declare
  _role name;
  result basic_auth.jwt_token;
begin
  -- check email and password
  select basic_auth.user_role(email, pass) into _role;
  if _role is null then
    raise invalid_password using message = 'invalid user or password';
  end if;

  select sign(
      row_to_json(r), 'reallyreallyreallyreallyreallyverysafe'
    ) as token
    from (
      select _role as role, login.email as email,
         extract(epoch from now())::integer + 60*60 as exp
    ) r
    into result;
  return result;
end;
$function$
;

function is created in my database:

[wolfgangwalther]

The error message is Could not find the public.login() function in the schema cache.

"schema cache" is not meant as the SCHEMA with the name "cache" - but the cache of schemas that is kept by PostgREST internally.

CREATE OR REPLACE FUNCTION cache.login(email text, pass text)

This is wrong. You need to create the function in the public schema.

[lokize]

[wolfgangwalther]

Please stop posting screenshots. It's enough to copy the error message you are getting.

---

I tell you "this is wrong" and the only thing you do is copy paste the wrong snippet into your editor and execute it? How is this supposed to work?

You need to change cache.login to public.login - and then run your full CREATE OR REPLACE FUNCTION statement again.

[lokize]

I'm sorry, I didn't mean to disturb the intention of posting the image, it was to better exemplify the problem, sorry!

[lokize]

i changed function restarted database and reloaded schema the error changed to:

{
"message": "Could not find the public.login() function or the public.login function with a single unnamed json or jsonb parameter in the schema cache",
"hint": "If a new function was created in the database with this name and parameters, try reloading the schema cache."
}

[wolfgangwalther]

alter role anon in database this is wrong. You should not set that on the anon user, but on the authenticator user (the one that postgrest connects with).

[lokize]

in conf file i need to add jwt-secret too? and my db-anon-role = (need to be authenticator?)

[wolfgangwalther]

You need to pass the secret to PostgREST. To do this you can do one of:

• set it in the config file
• set it on the authenticator role

db-anon-role should not be authenticator, but should remain at anon.

[lokize]

i changed only in database, i put secret do default documentation (really...), after generating token i try GET with header Authorization: Bearer mytoken and i have {
"message": "JWSError JWSInvalidSignature"
}

[lokize]

Please close this thread i fixed the problem doing the process from scratch a few times until I understood how it actually worked, thanks to the @wolfgangwalther for the help and patience. thank you!