APEX自定义数据库认证需求：限制特定前缀数据库账号登录

Got it, let's work through this problem together. Since your current database accounts setup lets any valid DB user log in, switching to a custom authentication flow is exactly what you need to restrict access to specific user groups. Here's a step-by-step breakdown to make this happen:

• 第一步：切换到自定义认证模式
  First, navigate to your authentication settings and switch from database accounts to the custom auth option. The exact steps will depend on the platform/tool you're using (e.g., a backend framework, database service like PostgreSQL with custom functions, or an auth service), but this is the foundational switch to take control of the login logic.

• 第二步：编写前缀验证的认证逻辑
  The core idea is to add a check for allowed username prefixes before validating the user's credentials. Here are examples for common scenarios:

  数据库层面的自定义认证（以PostgreSQL为例） #

  If you want to handle this directly in the database, create a custom function that validates both the password and the username prefix:

  CREATE OR REPLACE FUNCTION validate_restricted_login(p_username text, p_password text) RETURNS boolean AS $$
  DECLARE
      allowed_prefixes text[] := ARRAY['dev_', 'qa_']; -- 替换成你需要的前缀
      is_valid boolean;
  BEGIN
      -- 检查用户名是否匹配允许的前缀，同时验证密码哈希
      SELECT EXISTS(
          SELECT 1 FROM accounts
          WHERE username = p_username
          AND password = crypt(p_password, password) -- 假设用crypt存储哈希密码
          AND username LIKE ANY(ARRAY['dev_%', 'qa_%'])
      ) INTO is_valid;
      RETURN is_valid;
  END;
  $$ LANGUAGE plpgsql SECURITY DEFINER;
  

  应用层的自定义认证（以Node.js为例） #

  If you're handling auth in your application code, add the prefix check before querying the database:

  const bcrypt = require('bcrypt');
  const db = require('./db-connection'); // 你的数据库连接
  
  async function authenticateRestrictedUser(username, password) {
      const allowedPrefixes = ['dev_', 'qa_'];
      // 先验证用户名前缀是否合法
      const hasValidPrefix = allowedPrefixes.some(prefix => username.startsWith(prefix));
      if (!hasValidPrefix) return false;
  
      // 查询用户并验证密码
      const result = await db.query('SELECT password FROM accounts WHERE username = $1', [username]);
      if (result.rows.length === 0) return false;
  
      return bcrypt.compare(password, result.rows[0].password);
  }
  

• 第三步：绑定自定义逻辑到登录流程
  Configure your auth system to use this custom function/logic when processing login requests. For example, if you're using an API endpoint, call the authenticateRestrictedUser function before issuing a token or session; if using database-level auth, set the custom function as the authentication handler.

• 额外安全建议

  • Never store plain-text passwords—always use strong hashing algorithms like bcrypt or Argon2.
  • Store allowed prefixes in a database config table instead of hardcoding them, so you can update permissions without modifying code.
  • Add logging for failed login attempts (especially those rejected due to invalid prefixes) to monitor access patterns.

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