密码验证钩子

你们公司希望在默认密码实现的要求之外提高安全性，以满足安全或合规要求。你们计划跟踪密码登录尝试的状态，并在必要时通过电子邮件或限制登录来采取措施。

🌐 Your company wishes to increase security beyond the requirements of the default password implementation in order to fulfill security or compliance requirements. You plan to track the status of a password sign-in attempt and take action via an email or a restriction on logins where necessary.

由于这个钩子会在未认证的请求上运行，恶意用户可能会通过多次调用它来滥用。使用这个钩子时要特别小心，否则可能会无意中阻止合法用户访问你的应用。

🌐 As this hook runs on unauthenticated requests, malicious users can abuse the hook by calling it multiple times. Pay extra care when using the hook as you can unintentionally block legitimate users from accessing your application.

在采取任何额外措施确保用户合法之前，先检查密码是否有效。如果可能的话，尽量发送电子邮件或通知，而不是直接屏蔽用户。

🌐 Check if a password is valid prior to taking any additional action to ensure the user is legitimate. Where possible, send an email or notification instead of blocking the user.

输入

字段	类型	描述
`user_id`	`string`	尝试登录的用户的唯一标识。与 `auth.users` 表关联。
`valid`	`boolean`	密码验证尝试是否有效。

1
{
2
  "user_id": "3919cb6e-4215-4478-a960-6d3454326cec",
3
  "valid": true
4
}

输出

只有在你的钩子处理输入没有错误时才返回这些。

🌐 Return these only if your hook processed the input without errors.

字段	类型	描述
`decision`	`string`	决定是否允许认证继续进行。使用 `reject` 拒绝验证尝试并将用户从所有活动会话中登出。使用 `continue` 则采用默认的 Supabase Auth 行为。
`message`	`string`	如果决策是 `reject`，要显示给用户的消息。
`should_logout_user`	`boolean`	如果发出 `reject` 决策，是否注销用户。在发出 `continue` 决策时没有效果。

1
{
2
  "decision": "reject",
3
  "message": "You have exceeded maximum number of password sign-in attempts.",
4
  "should_logout_user": "false"
5
}

作为公司新安全措施的一部分，用户每10秒只能输入一次错误的密码，不能超过这个频率。你想写一个钩子来强制执行这个规则。

🌐 As part of new security measures within the company, users can only input an incorrect password every 10 seconds and not more than that. You want to write a hook to enforce this.

创建一个表格来记录每个用户最后一次密码验证失败的尝试。

🌐 Create a table to record each user's last incorrect password verification attempt.

1
create table public.password_failed_verification_attempts (
2
  user_id uuid not null,
3
  last_failed_at timestamp not null default now(),
4
  primary key (user_id)
5
);

创建一个钩子来读取和写入这个表的信息。例如：

🌐 Create a hook to read and write information to this table. For example:

1
create function public.hook_password_verification_attempt(event jsonb)
2
returns jsonb
3
language plpgsql
4
as $$
5
  declare
6
    last_failed_at timestamp;
7
  begin
8
    if event->'valid' is true then
9
      -- password is valid, accept it
10
      return jsonb_build_object('decision', 'continue');
11
    end if;
12
13
    select last_failed_at into last_failed_at
14
      from public.password_failed_verification_attempts
15
      where
16
        user_id = event->'user_id';
17
18
    if last_failed_at is not null and now() - last_failed_at < interval '10 seconds' then
19
      -- last attempt was done too quickly
20
      return jsonb_build_object(
21
        'error', jsonb_build_object(
22
          'http_code', 429,
23
          'message',   'Please wait a moment before trying again.'
24
        )
25
      );
26
    end if;
27
28
    -- record this failed attempt
29
    insert into public.password_failed_verification_attempts
30
      (
31
        user_id,
32
        last_failed_at
33
      )
34
      values
35
      (
36
        event->'user_id',
37
        now()
38
      )
39
      on conflict do update
40
        set last_failed_at = now();
41
42
    -- finally let Supabase Auth do the default behavior for a failed attempt
43
    return jsonb_build_object('decision', 'continue');
44
  end;
45
$$;
46
47
-- Assign appropriate permissions
48
grant all
49
  on table public.password_failed_verification_attempts
50
  to supabase_auth_admin;
51
52
revoke all
53
  on table public.password_failed_verification_attempts
54
  from authenticated, anon, public;