使用 PostgreSQL 用户和密码的 SQL 用户管理

作者:: fjf2002

这是对章节 SQL 用户管理的替代方案，仅使用 PostgreSQL 内置表 pg_catalog.pg_authid 进行用户管理。这意味着

不需要专门的用户表（除了 pg_authid）
PostgreSQL 的用户和密码（即 pg_authid 中的内容）也在 PostgREST 级别使用。

注意

只支持使用 SCRAM-SHA-256 密码哈希的 PostgreSQL 用户（从 PostgreSQL v14 开始默认）。

警告

这是实验性的。我们无法提供任何保证，特别是关于安全性的保证。使用风险自负。

使用 pg_authid 和 SCRAM-SHA-256 哈希

与 SQL 用户管理中一样，我们创建一个 basic_auth 模式

-- 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 SCHEMA basic_auth;

与 SQL 用户管理中一样，我们创建 pgcrypto 和 pgjwt 扩展。在这里，我们更倾向于将扩展放在自己的模式中

CREATE SCHEMA ext_pgcrypto;
ALTER SCHEMA ext_pgcrypto OWNER TO postgres;
CREATE EXTENSION pgcrypto WITH SCHEMA ext_pgcrypto;

关于 pgjwt 扩展，请参考客户端身份验证。

CREATE SCHEMA ext_pgjwt;
ALTER SCHEMA ext_pgjwt OWNER TO postgres;
CREATE EXTENSION pgjwt WITH SCHEMA ext_pgjwt;

为了能够使用 PostgreSQL 的 SCRAM-SHA-256 密码哈希，我们还需要 PBKDF2 密钥派生函数。幸运的是，在 stackoverflow 上有一个 PL/pgSQL 实现

CREATE FUNCTION basic_auth.pbkdf2(salt bytea, pw text, count integer, desired_length integer, algorithm text) RETURNS bytea
    LANGUAGE plpgsql IMMUTABLE
    AS $$
DECLARE
  hash_length integer;
  block_count integer;
  output bytea;
  the_last bytea;
  xorsum bytea;
  i_as_int32 bytea;
  i integer;
  j integer;
  k integer;
BEGIN
  algorithm := lower(algorithm);
  CASE algorithm
  WHEN 'md5' then
    hash_length := 16;
  WHEN 'sha1' then
    hash_length = 20;
  WHEN 'sha256' then
    hash_length = 32;
  WHEN 'sha512' then
    hash_length = 64;
  ELSE
    RAISE EXCEPTION 'Unknown algorithm "%"', algorithm;
  END CASE;
  --
  block_count := ceil(desired_length::real / hash_length::real);
  --
  FOR i in 1 .. block_count LOOP
    i_as_int32 := E'\\000\\000\\000'::bytea || chr(i)::bytea;
    i_as_int32 := substring(i_as_int32, length(i_as_int32) - 3);
    --
    the_last := salt::bytea || i_as_int32;
    --
    xorsum := ext_pgcrypto.HMAC(the_last, pw::bytea, algorithm);
    the_last := xorsum;
    --
    FOR j IN 2 .. count LOOP
      the_last := ext_pgcrypto.HMAC(the_last, pw::bytea, algorithm);

      -- xor the two
      FOR k IN 1 .. length(xorsum) LOOP
        xorsum := set_byte(xorsum, k - 1, get_byte(xorsum, k - 1) # get_byte(the_last, k - 1));
      END LOOP;
    END LOOP;
    --
    IF output IS NULL THEN
      output := xorsum;
    ELSE
      output := output || xorsum;
    END IF;
  END LOOP;
  --
  RETURN substring(output FROM 1 FOR desired_length);
END $$;

ALTER FUNCTION basic_auth.pbkdf2(salt bytea, pw text, count integer, desired_length integer, algorithm text) OWNER TO postgres;

类似于 SQL 用户管理创建函数 basic_auth.user_role 的方式，我们创建一个辅助函数来检查用户的密码，这里使用另一个名称和签名（因为我们想要用户名，而不是电子邮件地址）。但与 SQL 用户管理不同的是，此函数不使用专门的 users 表来存储密码，而是利用内置表 pg_catalog.pg_authid

CREATE FUNCTION basic_auth.check_user_pass(username text, password text) RETURNS name
    LANGUAGE sql
    AS
$$
  SELECT rolname AS username
  FROM pg_authid
  -- regexp-split scram hash:
  CROSS JOIN LATERAL regexp_match(rolpassword, '^SCRAM-SHA-256\$(.*):(.*)\$(.*):(.*)$') AS rm
  -- identify regexp groups with sane names:
  CROSS JOIN LATERAL (SELECT rm[1]::integer AS iteration_count, decode(rm[2], 'base64') as salt, decode(rm[3], 'base64') AS stored_key, decode(rm[4], 'base64') AS server_key, 32 AS digest_length) AS stored_password_part
  -- calculate pbkdf2-digest:
  CROSS JOIN LATERAL (SELECT basic_auth.pbkdf2(salt, check_user_pass.password, iteration_count, digest_length, 'sha256')) AS digest_key(digest_key)
  -- based on that, calculate hashed passwort part:
  CROSS JOIN LATERAL (SELECT ext_pgcrypto.digest(ext_pgcrypto.hmac('Client Key', digest_key, 'sha256'), 'sha256') AS stored_key, ext_pgcrypto.hmac('Server Key', digest_key, 'sha256') AS server_key) AS check_password_part
  WHERE rolpassword IS NOT NULL
    AND pg_authid.rolname = check_user_pass.username
    -- verify password:
    AND check_password_part.stored_key = stored_password_part.stored_key
    AND check_password_part.server_key = stored_password_part.server_key;
$$;

ALTER FUNCTION basic_auth.check_user_pass(username text, password text) OWNER TO postgres;

公共用户界面

类似于 SQL 用户管理，我们创建一个登录函数，该函数接受用户名和密码，并在凭据与内部表中的用户匹配时返回一个 JWT。这里我们使用用户名而不是电子邮件地址来标识用户。

登录

如客户端身份验证中所述，我们将在登录函数中创建 JWT 令牌。请注意，您需要将此示例中硬编码的密钥调整为您选择的安全密钥（至少 32 个字符）。

-- if you are not using psql, you need to replace :DBNAME with the current database's name.
ALTER DATABASE :DBNAME SET "app.jwt_secret" to 'reallyreallyreallyreallyverysafe';


CREATE FUNCTION public.login(username text, password text, OUT token text)
    LANGUAGE plpgsql security definer
    AS $$
DECLARE
  _role name;
BEGIN
  -- check email and password
  SELECT basic_auth.check_user_pass(username, password) INTO _role;
  IF _role IS NULL THEN
    RAISE invalid_password USING message = 'invalid user or password';
  END IF;
  --
  SELECT ext_pgjwt.sign(
      row_to_json(r), current_setting('app.jwt_secret')
    ) AS token
    FROM (
      SELECT login.username as role,
        extract(epoch FROM now())::integer + 60*60 AS exp
    ) r
    INTO token;
END;
$$;

ALTER FUNCTION public.login(username text, password text) OWNER TO postgres;

权限

类似于 SQL 用户管理：您的数据库角色需要访问模式、表、视图和函数才能服务 HTTP 请求。回想一下角色系统概述，PostgREST 使用特殊角色来处理请求，即身份验证器和匿名角色。以下是一个允许匿名用户尝试登录的权限示例。

CREATE ROLE anon NOINHERIT;
CREATE role authenticator NOINHERIT LOGIN PASSWORD 'secret';
GRANT anon TO authenticator;

GRANT EXECUTE ON FUNCTION public.login(username text, password text) TO anon;

由于上面的 login 函数被定义为安全定义者，匿名用户 anon 不需要权限访问表 pg_catalog.pg_authid 。 grant execute on function 包含在内是为了清晰起见，但它可能不需要，有关更多详细信息，请参阅函数。

为角色 authenticator 选择一个安全的密码。不要忘记配置 PostgREST 以使用 authenticator 用户进行连接，并使用 anon 用户作为匿名用户。

测试

让我们创建一个示例用户

CREATE ROLE foo PASSWORD 'bar';

在 SQL 级进行测试

执行

SELECT * FROM public.login('foo', 'bar');

这应该返回一个类似于以下的单个标量字段

                                                            token
-----------------------------------------------------------------------------------------------------------------------------
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9vIiwiZXhwIjoxNjY4MTg4ODQ3fQ.idBBHuDiQuN_S7JJ2v3pBOr9QypCliYQtCgwYOzAqEk
(1 row)

在 REST 级进行测试

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

curl "http://localhost:3000/rpc/login" \
  -X POST -H "Content-Type: application/json" \
  -d '{ "username": "foo", "password": "bar" }'

响应将类似于以下代码段。尝试在 jwt.io 上解码令牌。（它使用 reallyreallyreallyreallyverysafe 的密钥进行编码，如上面的 SQL 代码中所指定。您需要在您的应用程序中更改此密钥！)

{
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoic2VwcCIsImV4cCI6MTY2ODE4ODQzN30.WSytcouNMQe44ZzOQit2AQsqTKFD5mIvT3z2uHwdoYY"
}

在 REST 级进行更复杂的测试

让我们添加一个表，用于 foo 用户

CREATE TABLE public.foobar(foo int, bar text, baz float);
ALTER TABLE public.foobar owner TO postgres;

现在尝试使用以下方法获取表的內容

curl "http://localhost:3000/foobar"

这将失败——当然，我们还没有指定用户，因此 PostgREST 回退到 anon 用户并拒绝访问。添加一个 Authorization 标头。请使用上面登录函数调用中的令牌值，而不是下面提供的令牌值。

curl "http://localhost:3000/foobar" \
  -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9vIiwiZXhwIjoxNjY4MTkyMjAyfQ.zzdHCBjfkqDQLQ8D7CHO3cIALF6KBCsfPTWgwhCiHCY"

这将再次失败——我们得到 Permission denied to set role。我们忘记了通过执行以下操作来允许身份验证角色切换到此用户

GRANT foo TO authenticator;

重新执行最后一个 REST 请求。我们再次失败——我们也忘记了授予 foo 对该表的权限。执行

GRANT SELECT ON TABLE public.foobar TO foo;

现在 REST 请求应该成功。返回一个空的 JSON 数组 []。