单库共享架构多租户场景下Npgsql自动追加TenantID查询方案咨询

我们采用单数据库共享架构实现多租户，希望在基础Npgsql（不依赖EF）中自动为所有查询的WHERE子句追加tenantid = <租户ID>条件，避免开发者手动编写。先分析现有方案的问题，再给出几种推荐实现方式：

现有自定义Command方案的问题 #

你当前基于NpgsqlCommand继承的实现，通过字符串替换和正则处理SQL的方式存在诸多隐患：

• SQL解析不可靠：正则和简单字符串匹配无法处理复杂SQL（比如嵌套子查询、注释中的WHERE、JOIN/UNION语句），很容易出现逻辑错误。
• SQL注入风险：直接拼接租户ID字符串（TenantId = '{_tenantContext.TenantId}'），如果租户ID包含特殊字符，可能引发注入问题。
• 维护成本高：需要不断适配各种SQL语法场景，后续扩展困难。

推荐方案 #

方案1：使用PostgreSQL行级安全策略（RLS）【最推荐】 #

这是数据库层面的强制隔离方案，从根源上保证租户数据隔离，无需修改应用层SQL处理逻辑。

实现步骤：

1. 为需要隔离的租户表启用RLS：

ALTER TABLE "order" ENABLE ROW LEVEL SECURITY;

2. 创建租户隔离策略，限制仅当前租户能访问自己的数据：

CREATE POLICY tenant_isolation_policy ON "order"
FOR ALL USING (tenantid = current_setting('app.tenant_id')::INT);

3. 在Npgsql连接中设置当前租户ID：
   每次连接打开后，执行以下语句设置租户上下文：

using var connection = new NpgsqlConnection("你的连接字符串");
await connection.OpenAsync();
// 设置当前租户ID，用参数化避免注入
using var setTenantCmd = new NpgsqlCommand("SET app.tenant_id = @TenantId", connection);
setTenantCmd.Parameters.AddWithValue("@TenantId", tenantContext.TenantId);
await setTenantCmd.ExecuteNonQueryAsync();

优点：

• 数据库层面强制隔离，无论应用层SQL如何编写，都无法越权访问其他租户数据。
• 无需修改应用层SQL生成逻辑，开发者可以正常编写原始SQL。
• 性能优异，RLS由PostgreSQL内核处理，额外开销极小。

注意事项：

• 确保应用使用的数据库账号没有BYPASSRLS权限，避免绕过安全策略。
• 所有租户表都需要启用RLS并配置对应策略。

方案2：封装SQL构建器 #

通过统一的SQL构建工具，强制生成带租户条件的SQL，同时使用参数化查询避免注入。

示例实现：

public class TenantSqlBuilder
{
    private readonly string _tenantId;

    public TenantSqlBuilder(string tenantId)
    {
        _tenantId = tenantId ?? throw new ArgumentNullException(nameof(tenantId));
    }

    // 生成SELECT语句
    public string BuildSelect(string tableName, string columns = "*", string whereClause = "")
    {
        var tenantCondition = "tenantid = @TenantId";
        var fullWhere = string.IsNullOrEmpty(whereClause) 
            ? $"WHERE {tenantCondition}" 
            : $"WHERE {whereClause} AND {tenantCondition}";
        
        return $"SELECT {columns} FROM {tableName} {fullWhere}";
    }

    // 生成UPDATE语句
    public string BuildUpdate(string tableName, string setClause, string whereClause = "")
    {
        var tenantCondition = "tenantid = @TenantId";
        var fullWhere = string.IsNullOrEmpty(whereClause)
            ? $"WHERE {tenantCondition}"
            : $"WHERE {whereClause} AND {tenantCondition}";
        
        return $"UPDATE {tableName} SET {setClause} {fullWhere}";
    }

    // 同理封装INSERT、DELETE方法，确保插入时自动设置tenantid
    public string BuildInsert(string tableName, string columns, string values)
    {
        // 自动追加tenantid字段和参数
        return $"INSERT INTO {tableName} ({columns}, tenantid) VALUES ({values}, @TenantId)";
    }
}

使用方式：

var builder = new TenantSqlBuilder(tenantContext.TenantId);
var sql = builder.BuildSelect("order");

using var cmd = new NpgsqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@TenantId", tenantContext.TenantId);
// 执行查询
using var reader = await cmd.ExecuteReaderAsync();

优点：

• 可控性强，强制规范SQL生成逻辑，避免开发者遗漏租户条件。
• 全程使用参数化查询，彻底避免SQL注入风险。
• 代码简洁，维护成本低，适合团队统一规范。

方案3：基于SQL AST解析的自定义Command（不推荐，仅作备选） #

如果必须通过修改现有SQL的方式实现，不要用正则，而是使用SQL抽象语法树（AST）解析库来修改SQL，比如Npgsql官方的Npgsql.SqlParser或第三方库如SqlKata。

核心思路：

1. 解析原始SQL为AST结构。
2. 遍历AST节点，为每个租户表的查询添加tenantid = @TenantId条件。
3. 将修改后的AST重新生成为SQL字符串。
4. 添加租户ID参数，避免注入。

伪代码示例：

public override async Task<NpgsqlDataReader> ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
{
    ModifySqlWithTenantCondition();
    return await base.ExecuteReaderAsync(behavior, cancellationToken);
}

private void ModifySqlWithTenantCondition()
{
    if (string.IsNullOrEmpty(_tenantContext.TenantId))
        throw new InvalidOperationException("Tenant ID未设置");

    // 使用Npgsql.SqlParser解析SQL（需引入Npgsql.SqlParser包）
    var parser = new SqlParser();
    var ast = parser.Parse(CommandText);

    // 遍历所有SELECT节点，添加租户条件
    foreach (var selectStmt in ast.Statements.OfType<SelectStatement>())
    {
        var tenantCondition = new BooleanExpression(
            new ColumnReference("tenantid"),
            BooleanOperator.Equals,
            new Parameter("@TenantId")
        );

        if (selectStmt.WhereClause == null)
        {
            selectStmt.WhereClause = new WhereClause(tenantCondition);
        }
        else
        {
            // 与原有条件用AND拼接
            selectStmt.WhereClause.Expression = new BooleanExpression(
                selectStmt.WhereClause.Expression,
                BooleanOperator.And,
                tenantCondition
            );
        }
    }

    // 生成修改后的SQL
    CommandText = ast.ToSqlString();
    // 添加参数
    Parameters.AddWithValue("@TenantId", _tenantContext.TenantId);
}

缺点：

• 引入额外依赖，增加项目复杂度。
• SQL解析和AST修改逻辑需要维护，适配不同SQL语法场景成本高。

总结 #

优先选择PostgreSQL行级安全策略（RLS），它是最安全、最省心的多租户隔离方案；如果无法使用RLS，推荐封装SQL构建器，规范团队SQL编写方式；基于SQL AST的方案仅作为最后备选，尽量避免使用字符串替换/正则的方式处理SQL。

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