使用Postgres的用户和密码进行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 extension ，请参阅。至客户端身份验证。

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

为了能够使用Postgres的SCRAM-SHA-256密码散列，我们还需要PBKDF2密钥派生函数。幸运的是，有 a PL/pgSQL implementation on stackoverflow ：

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 函数定义为 security definer ，匿名用户 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)

在休息级别进行测试#

调用此函数的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"
}

更复杂的休息水平测试#

让我们添加一个表，用于 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数组 [] 是返回的。