有人成功地用postgrest和postgresql实现了sql用户管理吗?

v6ylcynt  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(364)

我正在尝试用postgrest和postgresql实现sql用户管理。官方postgrest文档http://postgrest.org/en/v5.2/auth.html#sql-用户管理帮助我整合了以下内容:

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;

当我尝试执行post请求时:

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"
}

我相信这是一个权限问题!我不知道我错过了什么。

nsc4cvqm

nsc4cvqm1#

尝试在公共模式中定义登录函数

-- change this code

-- login should be on your exposed schema
create or replace function
login(email text, pass text) returns basic_auth.jwt_token as $$

-- with 

-- login should be on your exposed schema
create or replace function
public.login(email text, pass text) returns basic_auth.jwt_token as $$

尝试使用发送邮件 curl ```
curl --header "Content-Type: application/json"
--request POST
--data '{ "email": "foo@bar.com", "pass": "foobar" }'
localhost:3000/rpc/login

相关问题