Back to Details

enforcing-password-policies

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Enforcing Password Policies

强制执行密码策略

Configures and enforces password policies on CockroachDB clusters by setting minimum password length, bcrypt hash cost, and login throttling. Ensures password strength meets organizational and compliance requirements.
通过设置最小密码长度、bcrypt哈希成本和登录限流，配置并强制执行CockroachDB集群的密码策略，确保密码强度符合组织及合规要求。

When to Use This Skill

适用场景

  • Strengthening password requirements to meet compliance standards (SOC 2, HIPAA, NIST 800-63B)
  • Setting up password policies for a new production cluster
  • Responding to a security audit finding about weak password policies
  • Increasing bcrypt hash cost to improve resistance against brute-force attacks
  • Configuring login throttling to mitigate credential stuffing
  • 强化密码要求以满足合规标准（SOC 2、HIPAA、NIST 800-63B）
  • 为新生产集群设置密码策略
  • 响应安全审计中关于弱密码策略的发现
  • 提高bcrypt哈希成本以增强抵御暴力攻击的能力
  • 配置登录限流以缓解凭证填充攻击

Prerequisites

前提条件

  • SQL access with admin role (required to modify cluster settings)
  • Understanding of impact: Password policy changes affect new passwords only, not existing passwords
Check your access:
sql
SELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();
  • 具备管理员角色的SQL访问权限（修改集群设置所需）
  • 了解影响范围：密码策略变更仅作用于新密码，不会影响现有密码
检查访问权限：
sql
SELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();

Steps

操作步骤

1. Check Current Password Policy Settings

1. 查看当前密码策略设置

sql
-- Minimum password length
SHOW CLUSTER SETTING server.user_login.min_password_length;

-- Password hash cost (bcrypt rounds)
SHOW CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt;

-- Login attempt throttling
SHOW CLUSTER SETTING server.user_login.password.min_delay;
SHOW CLUSTER SETTING server.user_login.password.max_delay;
See SQL queries reference for additional password-related queries.
sql
-- 最小密码长度
SHOW CLUSTER SETTING server.user_login.min_password_length;

-- 密码哈希成本（bcrypt轮数）
SHOW CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt;

-- 登录尝试限流
SHOW CLUSTER SETTING server.user_login.password.min_delay;
SHOW CLUSTER SETTING server.user_login.password.max_delay;
更多密码相关查询请查看SQL查询参考。

2. Set Minimum Password Length

2. 设置最小密码长度

sql
-- Set minimum password length to 12 characters (recommended)
SET CLUSTER SETTING server.user_login.min_password_length = 12;
Recommended minimums by compliance framework:
FrameworkMinimum LengthRecommendation
NIST 800-63B8 characters12+ recommended
SOC 28 characters12+ recommended
HIPAA8 characters12+ recommended
PCI DSS7 characters12+ recommended
Internal best practice—14+ for admin accounts
sql
-- 将最小密码长度设置为12个字符（推荐值）
SET CLUSTER SETTING server.user_login.min_password_length = 12;
各合规框架推荐的最小长度：
合规框架最小长度推荐值
NIST 800-63B8个字符推荐12个及以上
SOC 28个字符推荐12个及以上
HIPAA8个字符推荐12个及以上
PCI DSS7个字符推荐12个及以上
内部最佳实践—管理员账户推荐14个及以上

3. Configure Hash Cost

3. 配置哈希成本

The bcrypt hash cost controls how computationally expensive password hashing is. Higher values make brute-force attacks slower but increase CPU during authentication.
sql
-- Set bcrypt hash cost (default: 10, recommended: 12)
SET CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt = 12;
Hash cost guidance:
CostTime per Hash (approx.)Recommendation
10~100msDefault, acceptable for most
12~400msRecommended for production
14~1.5sHigh security, slower logins
Trade-off: Higher cost means slower password verification, which affects login latency. Cost 12 is a good balance.
bcrypt哈希成本控制密码哈希计算的复杂度。值越高，暴力攻击的难度越大，但会增加身份验证时的CPU消耗。
sql
-- 设置bcrypt哈希成本（默认值：10，推荐值：12）
SET CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt = 12;
哈希成本指南：
成本值单次哈希耗时（约）推荐场景
10~100ms默认值，适用于大多数场景
12~400ms生产环境推荐
14~1.5s高安全要求场景，登录速度较慢
权衡点： 哈希成本越高，密码验证速度越慢，会影响登录延迟。成本值12是较好的平衡点。

4. Configure Login Throttling

4. 配置登录限流

Login throttling introduces delays after failed authentication attempts to slow down brute-force attacks.
sql
-- Minimum delay after failed login attempt
SET CLUSTER SETTING server.user_login.password.min_delay = '0.5s';

-- Maximum delay after repeated failures
SET CLUSTER SETTING server.user_login.password.max_delay = '10s';
The delay increases exponentially between 
min_delay
and 
max_delay
with each consecutive failed attempt.
登录限流会在身份验证失败后引入延迟，以减缓暴力攻击的速度。
sql
-- 登录失败后的最小延迟
SET CLUSTER SETTING server.user_login.password.min_delay = '0.5s';

-- 多次失败后的最大延迟
SET CLUSTER SETTING server.user_login.password.max_delay = '10s';
每次连续失败尝试后，延迟会在
min_delay
和
max_delay
之间呈指数增长。

5. Verify Enforcement

5. 验证策略生效

sql
-- Confirm settings
SHOW CLUSTER SETTING server.user_login.min_password_length;
SHOW CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt;
SHOW CLUSTER SETTING server.user_login.password.min_delay;
SHOW CLUSTER SETTING server.user_login.password.max_delay;
Test enforcement:
sql
-- This should fail if min_password_length is 12
CREATE USER test_weak_password WITH PASSWORD 'short';
-- Expected: ERROR: password too short

-- This should succeed
CREATE USER test_strong_password WITH PASSWORD 'a-secure-password-123';
DROP USER test_strong_password;
sql
-- 确认设置
SHOW CLUSTER SETTING server.user_login.min_password_length;
SHOW CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt;
SHOW CLUSTER SETTING server.user_login.password.min_delay;
SHOW CLUSTER SETTING server.user_login.password.max_delay;
测试策略执行：
sql
-- 如果最小密码长度设为12，此操作应失败
CREATE USER test_weak_password WITH PASSWORD 'short';
-- 预期结果：ERROR: password too short

-- 此操作应成功
CREATE USER test_strong_password WITH PASSWORD 'a-secure-password-123';
DROP USER test_strong_password;

6. Address Existing Users with Weak Passwords

6. 处理现有弱密码用户

Password policy changes only apply to new passwords. Existing users retain their old passwords until they change them.
Options for enforcing password rotation:
  1. Communicate the policy change and ask users to update their passwords
  2. Expire existing passwords by requiring a password change on next login (if supported by your application layer)
  3. Reset passwords administratively for critical accounts:
sql
-- Reset a user's password (forces them to use a new, policy-compliant password)
ALTER USER <username> WITH PASSWORD '<new-strong-password>';
密码策略变更仅适用于新密码。现有用户在修改密码前仍保留旧密码。
强制执行密码轮换的选项：
  1. 通知用户策略变更，要求他们更新密码
  2. 过期现有密码，要求用户下次登录时更改密码（如果应用层支持）
  3. 管理员重置密码（针对关键账户）：
sql
-- 重置用户密码（强制其使用符合策略的新密码）
ALTER USER <username> WITH PASSWORD '<new-strong-password>';

7. Manage Password Changes and Rotation

7. 管理密码修改与轮换

User Self-Service Password Changes

用户自助修改密码

SQL users can change their own passwords:
sql
-- User changes their own password
ALTER USER current_user() WITH PASSWORD '<new-password>';
Note: Non-admin users can change their own passwords by default. If users report they cannot change their password, verify they are connected as the correct user and that there are no HBA rules blocking password-based authentication.
SQL用户可自行修改密码：
sql
-- 用户修改自身密码
ALTER USER current_user() WITH PASSWORD '<new-password>';
注意： 默认情况下，非管理员用户可自行修改密码。若用户反馈无法修改密码，请确认他们以正确用户身份连接，且没有HBA规则阻止基于密码的身份验证。

Administrative Password Reset

管理员重置密码

sql
-- Admin resets another user's password
ALTER USER <username> WITH PASSWORD '<new-strong-password>';

-- Verify the user exists before resetting
SELECT username FROM [SHOW USERS] WHERE username = '<username>';
sql
-- 管理员重置其他用户的密码
ALTER USER <username> WITH PASSWORD '<new-strong-password>';

-- 重置前验证用户是否存在
SELECT username FROM [SHOW USERS] WHERE username = '<username>';

Cloud Console vs SQL Passwords

云控制台密码 vs SQL密码

CockroachDB Cloud has two separate password domains:
  • Cloud Console password — Managed via Cloud Console UI or SSO. Reset via the Cloud Console login page "Forgot password" flow.
  • SQL user password — Managed via SQL 
    ALTER USER
    . Independent of the Cloud Console password.
Changing one does not affect the other. Users must manage both if they use both access methods.
CockroachDB Cloud有两个独立的密码域：
  • 云控制台密码 — 通过云控制台UI或SSO管理，可通过云控制台登录页面的“忘记密码”流程重置。
  • SQL用户密码 — 通过SQL 
    ALTER USER
    命令管理，与云控制台密码相互独立。
修改其中一个不会影响另一个。若用户同时使用两种访问方式，需分别管理这两个密码。

Password Rotation Best Practices

密码轮换最佳实践

  • Rotate service account passwords on a regular schedule (e.g., every 90 days)
  • Use certificate-based authentication for service accounts to avoid password rotation entirely
  • Coordinate password rotation with application deployment cycles to avoid downtime
  • After changing a password, verify the application can connect with the new credentials before decommissioning the old password
  • 定期轮换服务账户密码（例如每90天）
  • 为服务账户使用基于证书的身份验证，避免密码轮换
  • 协调密码轮换与应用部署周期，避免停机
  • 修改密码后，在停用旧密码前验证应用能否使用新凭证连接

8. Troubleshoot Common Password Errors

8. 排查常见密码错误

"password too short"

"password too short"（密码过短）

The password does not meet the 
min_password_length
setting.
sql
-- Check the current minimum
SHOW CLUSTER SETTING server.user_login.min_password_length;
Fix: Use a longer password that meets or exceeds the minimum length.
密码未满足
min_password_length
设置要求。
sql
-- 查看当前最小长度设置
SHOW CLUSTER SETTING server.user_login.min_password_length;
解决方法：使用符合或超过最小长度的更长密码。

"bcrypt password hash too long"

"bcrypt password hash too long"（bcrypt密码哈希过长）

The password exceeds the bcrypt input limit of 72 bytes. This can occur with very long passwords or multi-byte Unicode characters.
sql
-- Workaround: use a shorter password (under 72 bytes)
ALTER USER <username> WITH PASSWORD '<shorter-password>';
This is a bcrypt limitation, not a CockroachDB-specific issue.
密码超过了bcrypt的72字节输入限制。这种情况可能发生在超长密码或多字节Unicode字符的场景下。
sql
-- 解决方法：使用更短的密码（少于72字节）
ALTER USER <username> WITH PASSWORD '<shorter-password>';
这是bcrypt的限制，并非CockroachDB特有的问题。

Authentication failures after password change

修改密码后身份验证失败

If users report authentication failures immediately after changing their password:
  1. Verify the password was set correctly (no copy-paste whitespace)
  2. Check for connection pool caching of old credentials — restart the connection pool
  3. Verify the HBA configuration allows password authentication:
    sql
    SHOW CLUSTER SETTING server.host_based_authentication.configuration;
  4. Check login throttling delays if there were failed attempts:
    sql
    SHOW CLUSTER SETTING server.user_login.password.min_delay;
SHOW CLUSTER SETTING server.user_login.password.max_delay;
若用户反馈修改密码后立即出现身份验证失败：
  1. 确认密码设置正确（无复制粘贴带来的空格）
  2. 检查连接池是否缓存了旧凭证 — 重启连接池
  3. 验证HBA配置是否允许基于密码的身份验证：
    sql
    SHOW CLUSTER SETTING server.host_based_authentication.configuration;
  4. 若存在失败尝试，检查登录限流延迟设置：
    sql
    SHOW CLUSTER SETTING server.user_login.password.min_delay;
SHOW CLUSTER SETTING server.user_login.password.max_delay;

Safety Considerations

安全注意事项

  • New passwords only: Changing 
    min_password_length
    does not invalidate existing passwords. Users with short passwords can still log in until they change their password.
  • Hash cost latency: Increasing 
    crdb_bcrypt
    cost increases login time. Test with realistic connection pools before setting cost above 12.
  • Throttling impact: Login throttling delays affect all users after failed attempts, including legitimate users who mistype their password.
  • Service accounts: Ensure service accounts use strong passwords or certificate-based authentication (certificates bypass password policy).
  • 仅作用于新密码：修改
    min_password_length
    不会使现有密码失效。使用短密码的用户在修改密码前仍可登录。
  • 哈希成本带来的延迟：提高
    crdb_bcrypt
    成本会增加登录时间。在将成本设置为12以上前，需使用真实连接池进行测试。
  • 限流影响：登录限流延迟会影响所有登录失败后的用户，包括输入错误密码的合法用户。
  • 服务账户：确保服务账户使用强密码或基于证书的身份验证（证书不受密码策略限制）。

Rollback

回滚设置

sql
-- Reset minimum password length to default (1 = no minimum)
SET CLUSTER SETTING server.user_login.min_password_length = 1;

-- Reset hash cost to default
RESET CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt;

-- Reset login throttling to defaults
RESET CLUSTER SETTING server.user_login.password.min_delay;
RESET CLUSTER SETTING server.user_login.password.max_delay;
sql
-- 将最小密码长度重置为默认值（1表示无限制）
SET CLUSTER SETTING server.user_login.min_password_length = 1;

-- 将哈希成本重置为默认值
RESET CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt;

-- 将登录限流重置为默认值
RESET CLUSTER SETTING server.user_login.password.min_delay;
RESET CLUSTER SETTING server.user_login.password.max_delay;

References

参考资料

Skill references:
  • SQL queries for password policies
Related skills:
  • auditing-cloud-cluster-security — Run a full security posture audit
  • configuring-sso-and-scim — Use SSO to eliminate password-based authentication
  • managing-tls-certificates — Use certificate-based authentication instead of passwords
Official CockroachDB Documentation:
技能参考：
  • 密码策略SQL查询
相关技能：
  • auditing-cloud-cluster-security — 执行完整的安全态势审计
  • configuring-sso-and-scim — 使用SSO消除基于密码的身份验证
  • managing-tls-certificates — 使用基于证书的身份验证替代密码
官方CockroachDB文档：