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

您可以使用 pgjwt 扩展 在 SQL 中创建 JWT 令牌。它很简单，只需要 pgcrypto。如果您使用的是像 Amazon RDS 这样的环境，它不支持安装新扩展，您仍然可以手动运行 pgjwt 中的 SQL（您需要将 @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 通过对 /rpc/jwt_test 的 POST 请求向客户端公开此函数。

注意

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

-- 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 ...

登录

如 来自 SQL 的 JWT 中所述，我们将在登录函数中创建 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 函数被定义为 安全定义者，匿名用户 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 表。