I am trying to implement SQL User Management with PostgREST and PostgreSQL. Official PostgRest documentation http://postgrest.org/en/v5.2/auth.html#sql-user-management helped me put together the following:
create schema if not exists basic_auth;
create table if not exists
basic_auth.users (
email text primary key check ( email ~* '^.+@.+\..+$' ),
pass text not null check (length(pass) < 512),
role name not null check (length(role) < 512)
);
create table if not exists
basic_auth.jwt_token (
);
create or replace function
basic_auth.check_role_exists() returns trigger as $$
begin
if not exists (select 1 from pg_roles as r where r.rolname = new.role) then
raise foreign_key_violation using message =
'unknown database role: ' || new.role;
return null;
end if;
return new;
end
$$ language plpgsql;
drop trigger if exists ensure_user_role_exists on basic_auth.users;
create constraint trigger ensure_user_role_exists
after insert or update on basic_auth.users
for each row
execute procedure basic_auth.check_role_exists();
create extension if not exists pgcrypto;
create or replace function
basic_auth.encrypt_pass() returns trigger as $$
begin
if tg_op = 'INSERT' or new.pass <> old.pass then
new.pass = crypt(new.pass, gen_salt('bf'));
end if;
return new;
end
$$ language plpgsql;
drop trigger if exists encrypt_pass on basic_auth.users;
create trigger encrypt_pass
before insert or update on basic_auth.users
for each row
execute procedure basic_auth.encrypt_pass();
create or replace function
basic_auth.user_role(email text, pass text) returns name
language plpgsql
as $$
begin
return (
select role from basic_auth.users
where users.email = user_role.email
and users.pass = crypt(user_role.pass, users.pass)
);
end;
$$;
-- login should be on your exposed schema
create or replace function
login(email text, pass text) returns basic_auth.jwt_token as $$
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), 'super_secret'
) 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;
$$ language plpgsql;
-- sacred, we simply choose them for clarity
create role anon;
create role authenticator noinherit login;
grant usage on schema public to authenticator;
grant anon to authenticator;
grant usage on schema public, basic_auth to anon;
grant select on table pg_authid, basic_auth.users to anon;
grant execute on function login(text,text) to anon;
When I try to do a POST request:
localhost:3000/rpc/login { "email": "foo@bar.com", "pass": "foobar" }
{
"hint": "No function matches the given name and argument types. You might need to add explicit type casts.",
"details": null,
"code": "42883",
"message": "function public.login() does not exist"
}
I believe its a permissions issue! I'm not sure what am I missing.
