PostgREST, mettre en place une API RestFull depuis n'importe quelle base de données PostgreSQL,

1
2
3
4
5
6
7
8
ALTER FUNCTION brain.login(text, text)
    OWNER TO postgres;

GRANT EXECUTE ON FUNCTION brain.login(text, text) TO postgres;

GRANT EXECUTE ON FUNCTION brain.login(text, text) TO PUBLIC;

GRANT EXECUTE ON FUNCTION brain.login(text, text) TO anonymous;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE OR REPLACE FUNCTION public.login(
    pseudo text,
    pass text)
    RETURNS basic_auth.jwt_token
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE SECURITY DEFINER 
AS $BODY$
declare
  _role name;
  result basic_auth.jwt_token;
begin
  -- check email and password
  select basic_auth.user_role(pseudo, 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), current_setting('public.jwt_secret')
    ) as token
    from (
      select _role as role, login.pseudo as pseudo,
         extract(epoch from now())::integer + 60*60 as exp
    ) r
    into result;
  return result;
end;
$BODY$;

1
2
3
4
5
6
7
8
ALTER FUNCTION public.login(text, text)
    OWNER TO postgres;

GRANT EXECUTE ON FUNCTION public.login(text, text) TO postgres;

GRANT EXECUTE ON FUNCTION public.login(text, text) TO PUBLIC;

GRANT EXECUTE ON FUNCTION public.login(text, text) TO anonymous;

 GRANT readall TO anonymous;