SQL用户管理

如上所述 JWT一代 ，外部服务可以提供用户管理，并使用JWT与PostgREST服务器进行协调。也可以完全通过SQL支持登录。这是一个相当大的工作量，所以做好准备。

存储用户和密码

下面的表、函数和触发器将位于 basic_auth 不应在API中公开的架构。公共视图和函数将位于不同的架构中，该架构在内部引用此内部信息。

首先，我们需要一个表来跟踪我们的用户：

-- We put things inside the basic_auth schema to hide
-- them from public view. Certain public procs/views will
-- refer to helpers and tables inside.

create table
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)
);

我们希望该角色是实际数据库角色的外键，但是PostgreSQL不支持对 pg_roles 桌子。我们将使用触发器手动执行它。

create 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;

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();

接下来，我们将使用pgcrypto扩展和触发器来确保密码在 users 桌子。

create extension pgcrypto;

create 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;

create trigger encrypt_pass
  before insert or update on basic_auth.users
  for each row
  execute procedure basic_auth.encrypt_pass();

有了表，我们就可以创建一个帮助器来根据加密列检查密码。如果电子邮件和密码正确，它将返回用户的数据库角色。

create 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;
$$;

公共用户界面

在前一节中，我们创建了一个内部表来存储用户信息。在这里，我们创建了一个登录函数，该函数接受电子邮件地址和密码，如果凭据与内部表中的用户匹配，则返回JWT。

权限

您的数据库角色需要访问架构、表、视图和函数才能服务于HTTP请求。召回从 角色系统概述 该PostgREST使用特殊角色来处理请求，即验证者角色和匿名角色。下面是允许匿名用户创建帐户并尝试登录的权限示例。

create role anon noinherit;
create role authenticator noinherit;
grant anon to authenticator;

然后，添加 db-anon-role 添加到配置文件以允许匿名请求。

db-anon-role = "anon"

来自SQL的JWT

属性在SQL中创建JWT令牌 pgjwt extension 。它很简单，只需要pgcrypto。如果您所在的环境(如Amazon RDS)不支持安装新的扩展模块，您仍然可以手动运行 SQL inside pgjwt (您需要更换 @extschema@ 使用另一个模式或直接删除它)，这将创建您将需要的函数。

接下来，编写一个返回令牌的函数。下面的令牌返回一个具有硬编码角色的令牌，该令牌在颁发后五分钟过期。请注意，该函数也有一个硬编码的秘密。

CREATE FUNCTION jwt_test(OUT token text) AS $$
  SELECT public.sign(
    row_to_json(r), 'reallyreallyreallyreallyverysafe'
  ) AS token
  FROM (
    SELECT
      'my_role'::text as role,
      extract(epoch from now())::integer + 300 AS exp
  ) r;
$$ LANGUAGE sql;

PostgREST通过POST请求向客户端公开此函数 /rpc/jwt_test 。

备注

为了避免在函数中对秘密进行硬编码，请将其保存为数据库的属性。

-- run this once
ALTER DATABASE mydb SET "app.jwt_secret" TO 'reallyreallyreallyreallyverysafe';

-- then all functions can refer to app.jwt_secret
SELECT sign(
  row_to_json(r), current_setting('app.jwt_secret')
) AS token
FROM ...

登录名

如中所述 JWT from SQL ，我们将在登录函数中创建一个JWT。请注意，您需要将本例中硬编码的密钥调整为您选择的安全(至少32个字符)密钥。

-- login should be on your exposed schema
create function
login(email text, pass text, out token text) as $$
declare
  _role name;
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), 'reallyreallyreallyreallyverysafe'
    ) as token
    from (
      select _role as role, login.email as email,
         extract(epoch from now())::integer + 60*60 as exp
    ) r
    into token;
end;
$$ language plpgsql security definer;

grant execute on function login(text,text) to anon;

由于上述原因， login 函数定义为 security definer ，匿名用户 anon 不需要权限即可读取 basic_auth.users 桌子。它甚至不需要权限就可以访问 basic_auth 架构。 grant execute on function 是为了清楚起见而包括的，但可能不需要，请参见 功能 了解更多详细信息。

调用此函数的API请求将如下所示：

curl "http://localhost:3000/rpc/login" \
  -X POST -H "Content-Type: application/json" \
  -d '{ "email": "foo@bar.com", "pass": "foobar" }'

响应将类似于下面的代码片段。尝试在以下位置解码令牌 jwt.io 。(它是用秘密编码的 reallyreallyreallyreallyverysafe 如上面的SQL代码中所指定的。你会想要在你的应用程序中更改这个秘密！)

{
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJlbWFpbCI6ImZvb0BiYXIuY29tIiwicGFzcyI6ImZvb2JhciJ9.37066TTRlh-1hXhnA9oO9Pj6lgL6zFuJU0iCHhuCFno"
}

替代方案

请参阅操作指南 使用Postgres的用户和密码进行SQL用户管理 以类似的方式完全避免了表 basic_auth.users 。