PostgreSQL按Schema实现多租户：无Schema注解的实体类异常排查

问题背景 #

我计划为多租户应用采用PostgreSQL的按Schema隔离方案，每个客户对应一个独立Schema。在public Schema中创建tenant表，记录tenant_name和schema_name字段，用于管理和列举所有Schema，同时使用Liquibase对除public外的所有Schema执行迁移。

我已实现MultitenantConnectionProvider、TenantIdentifierResolver、MultitenantContext及租户过滤器，但仅当实体类添加@Table的schema注解时才能正常工作。尝试创建无Schema注解的Product实体类（已确保该表存在于所有租户Schema及public Schema中），却提示“列不存在”。

请问如何实现无需为实体类指定Schema注解即可适配多租户Schema隔离的需求？

我的代码 #

租户表实体类 #

@Table(name = "tenant", schema = "public")
public class Tenant {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String tenant;
    private String schema;
} 

多租户连接提供者 #

@Component
public class MultitenantConnectionProvider implements MultiTenantConnectionProvider, HibernatePropertiesCustomizer {
    private final DataSource dataSource;

    public MultitenantConnectionProvider(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public Connection getAnyConnection() throws SQLException {
        return getConnection("public");
    }

    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {
        connection.close();
    }

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        Connection connection = dataSource.getConnection();
        connection.setSchema(tenantIdentifier);
        return connection;
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
        connection.setSchema("public");
        connection.close();
    }

    @Override
    public boolean supportsAggressiveRelease() {
        return false;
    }

    @Override
    public boolean isUnwrappableAs(Class<?> unwrapType) {
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> unwrapType) {
        return null;
    }

    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(AvailableSettings.MULTI_TENANT_CONNECTION_PROVIDER, this);
    }
}

租户标识符解析器 #

@Component
public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver, HibernatePropertiesCustomizer {
    private final String DEFAULT_TENANT = "public";
    @Override
    public String resolveCurrentTenantIdentifier() {
        String tenant = MultitenantContext.getCurrentTenant();
        if (StringUtils.isBlank(tenant)) {
            return DEFAULT_TENANT;
        }
        return tenant;
    }

    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }

    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(AvailableSettings.MULTI_TENANT_IDENTIFIER_RESOLVER, this);
    }
}

多租户上下文 #

public class MultitenantContext {
    private static final ThreadLocal<String> CURRENT_TENANT = new InheritableThreadLocal<>();

    public static String getCurrentTenant() {
        return CURRENT_TENANT.get();
    }

    public static void setCurrentTenant(String tenant) {
        CURRENT_TENANT.set(tenant);
    }
}

注：我还实现了租户过滤器。

解决方案 #

问题核心是：Hibernate默认会给无@Table(schema)注解的实体生成带public前缀的SQL，即便你通过connection.setSchema()切换了连接的默认Schema，Hibernate依然会显式指定public，导致查询指向错误的Schema。以下是具体解决方法：

1. 自定义PhysicalNamingStrategy动态替换Schema #

实现Hibernate的PhysicalNamingStrategy接口，在生成表名时自动将无指定Schema的实体，替换为当前租户的Schema：

@Component
public class TenantPhysicalNamingStrategy implements PhysicalNamingStrategy {

    private final PhysicalNamingStrategy delegate = new SpringPhysicalNamingStrategy();

    @Override
    public Identifier toPhysicalCatalogName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        return delegate.toPhysicalCatalogName(name, jdbcEnvironment);
    }

    @Override
    public Identifier toPhysicalSchemaName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        // 无Schema注解的实体，替换为当前租户Schema
        String currentTenant = MultitenantContext.getCurrentTenant();
        if (name == null && StringUtils.isNotBlank(currentTenant)) {
            return Identifier.toIdentifier(currentTenant);
        }
        // 保留显式指定Schema的实体（比如Tenant类）的原Schema
        return delegate.toPhysicalSchemaName(name, jdbcEnvironment);
    }

    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        return delegate.toPhysicalTableName(name, jdbcEnvironment);
    }

    @Override
    public Identifier toPhysicalSequenceName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        return delegate.toPhysicalSequenceName(name, jdbcEnvironment);
    }

    @Override
    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        return delegate.toPhysicalColumnName(name, jdbcEnvironment);
    }
}

然后在Hibernate配置中注册该策略，可在已有的HibernatePropertiesCustomizer实现中添加：

@Override
public void customize(Map<String, Object> hibernateProperties) {
    hibernateProperties.put(AvailableSettings.PHYSICAL_NAMING_STRATEGY, new TenantPhysicalNamingStrategy());
}

2. 验证连接Schema切换有效性 #

在MultitenantConnectionProvider的getConnection方法中添加日志，确认连接的Schema确实切换为当前租户：

@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
    Connection connection = dataSource.getConnection();
    connection.setSchema(tenantIdentifier);
    // 打印日志验证Schema切换
    System.out.println("Connection switched to schema: " + tenantIdentifier);
    return connection;
}

关键注意事项 #

• 全局表（比如public下的Tenant）必须显式添加@Table(schema = "public")，避免被动态替换为租户Schema。
• 确保Liquibase在所有租户Schema中正确创建了对应表结构，避免因表不存在引发错误。
• 测试时切换不同租户，验证无Schema注解的实体是否能正确访问对应租户的表。

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