I spent three days last month tracing a phantom data mutation in our billing system. The Spring Boot application logs showed absolutely nothing. Hibernate Envers was completely silent. Everything looked perfect on the application side.
Actually, let me back up — it turns out a support engineer ran a manual SQL script directly against the production Postgres instance to fix a customer issue. They bypassed our application-level auditing entirely. This is the fundamental problem with handling data auditing and access control purely in Java. It assumes your application is the only thing that ever touches the database.
We usually default to @EntityListeners(AuditingEntityListener.class) because it’s right there in the framework. You add a few annotations, and suddenly your entities track who created or modified them. But — it works fine until you have a data warehouse pulling directly, a background Python worker modifying records, or a DBA doing emergency maintenance.
If you want real security and an audit trail you can actually trust in court, you probably have to push it down to the database layer.
The Database-First Approach
Moving this logic to PostgreSQL involves two things: Row-Level Security (RLS) to restrict what a user can see, and trigger-based functions to record what they do. I recently migrated a core microservice to this pattern.
First, you need a schema that supports this. Here is a stripped-down version of what we deployed.
CREATE TABLE customer_orders (
id UUID PRIMARY KEY,
tenant_id UUID NOT NULL,
amount DECIMAL(10,2),
status VARCHAR(20)
);
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(50),
record_id UUID,
changed_by VARCHAR(100),
old_data JSONB,
new_data JSONB,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- You absolutely need these indices or your audit queries will crawl
CREATE INDEX idx_audit_record_id ON audit_log(record_id);
CREATE INDEX idx_audit_jsonb_data ON audit_log USING GIN (new_data);With the tables in place, you lock down the customer_orders table using RLS. This guarantees that no matter how a query is executed—whether through your Java backend, a BI tool, or a raw terminal—the database enforces the tenant isolation.
ALTER TABLE customer_orders ENABLE ROW LEVEL SECURITY;
-- The policy reads a custom configuration variable that we will set from Java
CREATE POLICY tenant_isolation_policy ON customer_orders
USING (tenant_id = current_setting('app.current_tenant', true)::UUID);The Connection Pool Nightmare
And here is where things usually fall apart. How does Postgres know who the current user or tenant is if your Java application uses a connection pool?
HikariCP reuses connections. If you execute a standard SET app.current_tenant = 'xyz' command, that setting persists on the connection even after HikariCP returns it to the pool. The next user who gets that connection will inherit the previous user’s access rights. I actually exposed cross-tenant data in a staging environment doing exactly this.
The fix is strict transaction management using SET LOCAL. This scopes the variable strictly to the current transaction. When the transaction commits or rolls back, the variable vanishes.
BEGIN;
-- SET LOCAL restricts the variable to the current transaction only
SET LOCAL app.current_tenant = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
SET LOCAL app.current_user = 'dev_ops_jerry';
-- This update will only succeed if the row's tenant_id matches the variable above
-- A database trigger (not shown here) will read app.current_user to write the audit log
UPDATE customer_orders
SET status = 'SHIPPED'
WHERE id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479';
COMMIT;Wiring it up in Spring Boot
You don’t want developers manually writing SET LOCAL before every database call. You need to intercept the transaction right as it starts.
I prefer using a Spring AOP aspect that wraps methods annotated with @Transactional. It grabs the current tenant from your security context (usually a ThreadLocal populated by your JWT filter) and executes the context-setting query.
@Aspect
@Component
public class DatabaseContextAspect {
private final JdbcTemplate jdbcTemplate;
public DatabaseContextAspect(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Before("@annotation(org.springframework.transaction.annotation.Transactional)")
public void setDatabaseContext() {
String tenantId = SecurityContextHolder.getContext().getTenantId();
String userId = SecurityContextHolder.getContext().getUserId();
if (tenantId != null) {
// Execute directly against the connection in the current transaction
jdbcTemplate.execute(String.format(
"SET LOCAL app.current_tenant = '%s'; SET LOCAL app.current_user = '%s';",
tenantId, userId
));
}
}
}If a developer tries to run a query without a transaction, or if the tenant ID is missing, the database policy evaluates to null and returns zero rows. It fails closed. Exactly what you want.
The Performance Reality
I benchmarked this setup extensively before pushing it to production. We were running PostgreSQL 16.2 on an AWS t3.xlarge instance. I wanted to know exactly how much overhead we were adding by forcing the database to evaluate RLS policies and fire JSONB audit triggers on every mutation.
Handling filtering and auditing purely in memory with Java took about 1.2ms of overhead per request. Pushing it down to the database via RLS and triggers added 4.8ms of latency per transaction.
You are paying a roughly 3.6ms penalty for database-level security. For a high-frequency trading platform, that might be a dealbreaker. But for our B2B SaaS application, it was entirely negligible.
The trade-off is absolute certainty. When someone inevitably connects via psql to manually fix a broken record at 2 AM, the database will force them to set their user context before it allows the update. When they do, the trigger will capture their exact changes in the audit log, alongside the application-generated events. No gaps. No phantom data.
Common questions
Why are Hibernate Envers audit logs missing changes made directly in Postgres?
Hibernate Envers and Spring Boot’s @EntityListeners only capture mutations that pass through the Java application. When a support engineer, DBA, Python worker, or data warehouse connects directly to Postgres, those changes bypass application-level auditing entirely, leaving no trace. The article describes a three-day investigation into a phantom mutation that turned out to be a manual SQL script run against production, invisible to Envers.
How do I stop HikariCP from leaking tenant context between Postgres connections?
Avoid plain SET commands, because HikariCP reuses connections and a regular SET app.current_tenant persists after the connection returns to the pool, letting the next user inherit the previous tenant’s access. Use SET LOCAL inside an explicit transaction instead. SET LOCAL scopes the variable to the current transaction only, so it vanishes on COMMIT or ROLLBACK, preventing cross-tenant data exposure.
How much latency does Postgres RLS with JSONB audit triggers actually add?
Benchmarked on PostgreSQL 16.2 running on an AWS t3.xlarge, handling filtering and auditing in Java added roughly 1.2ms of overhead per request, while pushing RLS policies and JSONB audit triggers into the database added 4.8ms per transaction. That is roughly a 3.6ms penalty. Negligible for a B2B SaaS workload, potentially a dealbreaker for high-frequency trading.
How do you set the current tenant for Postgres RLS from Spring Boot without manual SQL calls?
Use a Spring AOP aspect that runs @Before any method annotated with @Transactional. The aspect pulls the tenant and user IDs from the SecurityContextHolder ThreadLocal (populated by your JWT filter) and uses JdbcTemplate to execute SET LOCAL app.current_tenant and SET LOCAL app.current_user on the active transactional connection. If tenant ID is missing, the policy returns zero rows and fails closed.