Supabase本地开发：批量生成可登录测试用户的密码哈希问题

Supabase 使用 bcrypt 算法加密用户密码，你直接硬编码单个哈希值无法登录的原因是加密参数不匹配，系统验证逻辑需要符合标准的动态生成哈希。以下是批量生成可登录测试用户的解决方案：

1. 确认启用pgcrypto扩展 #

Supabase 环境默认已启用 pgcrypto 扩展（内置bcrypt加密函数），本地开发环境若未启用，先执行：

CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. 修改后的批量插入脚本 #

将原脚本中硬编码的encrypted_password替换为动态生成的bcrypt哈希，确保每个用户的密码哈希合法：

BEGIN;

WITH user_values AS (
  SELECT
    uuid_generate_v4() AS id,
    uuid_generate_v4() AS instance_id,
    'authenticated' AS aud,
    'authenticated' AS role,
    (ROW_NUMBER() OVER ()) || '@gmail.com' AS email,
    -- 动态生成密码为'12345678'的bcrypt哈希
    crypt('12345678', gen_salt('bf')) AS encrypted_password,
    now() AS email_confirmed_at,
    NULL::timestamp AS invited_at,
    '' AS confirmation_token,
    NULL::timestamp AS confirmation_sent_at,
    '' AS recovery_token,
    NULL::timestamp AS recovery_sent_at,
    '' AS email_change_token_new,
    '' AS email_change,
    NULL::timestamp AS email_change_sent_at,
    NULL::timestamp AS last_sign_in_at,
    '{"provider":"email","providers":["email"]}'::jsonb AS raw_app_meta_data,
    '{}'::jsonb AS raw_user_meta_data,
    false AS is_super_admin,
    now() AS created_at,
    now() AS updated_at,
    NULL AS phone,
    NULL::timestamp AS phone_confirmed_at,
    '' AS phone_change,
    '' AS phone_change_token,
    NULL::timestamp AS phone_change_sent_at,
    '' AS email_change_token_current,
    0 AS email_change_confirm_status,
    NULL::timestamp AS banned_until,
    '' AS reauthentication_token,
    NULL::timestamp AS reauthentication_sent_at
  FROM generate_series(1, 100) -- 生成100个测试用户
),
inserted_users AS (
INSERT INTO auth.users (
  id,
  instance_id,
  aud,
  role,
  email,
  encrypted_password,
  email_confirmed_at,
  invited_at,
  confirmation_token,
  confirmation_sent_at,
  recovery_token,
  recovery_sent_at,
  email_change_token_new,
  email_change,
  email_change_sent_at,
  last_sign_in_at,
  raw_app_meta_data,
  raw_user_meta_data,
  is_super_admin,
  created_at,
  updated_at,
  phone,
  phone_confirmed_at,
  phone_change,
  phone_change_token,
  phone_change_sent_at,
  email_change_token_current,
  email_change_confirm_status,
  banned_until,
  reauthentication_token,
  reauthentication_sent_at
)

SELECT * FROM user_values RETURNING id, instance_id
)

INSERT INTO public.user_profile (
  id,
  created_at,
  avatar_url,
  username,
  country_id
)
SELECT
  id,
  now(),
  '',
  'USUARIO' || ROW_NUMBER() OVER (),
  20
FROM inserted_users;

COMMIT;

关键说明 #

• crypt('12345678', gen_salt('bf'))：gen_salt('bf')生成bcrypt随机盐，确保每个用户的哈希值唯一且符合Supabase加密标准
• email_confirmed_at设为now()：跳过邮箱验证，直接允许登录
• raw_app_meta_data保持{"provider":"email","providers":["email"]}：匹配邮箱登录的身份验证逻辑

内容的提问来源于stack exchange，提问作者Joyboy