Java Auditing: Choosing Between Database RLS and Application-Level Control

Auditing in Java systems: RLS in the database or application-level control?

Java systems managing sensitive data must choose between database-native Row Level Security (RLS) and application-tier interceptors. PostgreSQL RLS filters records via declarative policies, ensuring audit triggers capture every session context regardless of the query source.

Why This Matters

While application-level auditing via Spring AOP or interceptors is common, it relies on developers consistently applying filters across all repositories. In high-stakes environments like finance or healthcare, a single forgotten parameter can lead to massive data leaks; RLS serves as the final, immutable barrier that protects data even if the application layer has bugs.

Key Insights

• PostgreSQL RLS uses declarative policies like USING (company_id = current_setting(‘app.tenant_id’)::uuid) to enforce tenant isolation at the engine level.
• Application-level auditing utilizes Spring Security and ThreadLocal contexts via TenantContext.set() to manage identities, though this is prone to developer error.
• Database triggers automate auditing by capturing TG_TABLE_NAME and TG_OP to log to_jsonb(OLD) and to_jsonb(NEW) states for every transaction.
• Connection pool management tools like HikariCP risk leaking tenant IDs between requests if SET commands are used outside of scoped @Transactional blocks.
• Sensitive sectors such as legal and healthcare require triggers for immutable auditing to ensure logs cannot be bypassed by external database access.

Working Examples

Enabling Row Level Security and creating an audit trigger in PostgreSQL.

ALTER TABLE transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE transactions FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON transactions
USING (company_id = current_setting('app.tenant_id')::uuid);

CREATE OR REPLACE FUNCTION fn_audit()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (table_name, operation, user_id, data_before, data_after)
VALUES (
TG_TABLE_NAME,
TG_OP,
current_setting('app.user_id', true)::uuid,
CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) END,
CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Application-level tenant management and auditing using Spring Interceptors and AOP.

@Component
public class TenantInterceptor implements HandlerInterceptor {
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) {
String tenantId = request.getHeader("X-Tenant-ID");
TenantContext.set(tenantId);
return true;
}
}

@Aspect
@Component
public class AuditAspect {
@AfterReturning(pointcut = "@annotation(Auditable)", returning = "result")
public void audit(JoinPoint jp, Object result) {
auditService.record(jp.getSignature().getName(), TenantContext.get(), SecurityContextHolder.getContext().getAuthentication().getName());
}
}

Correct method to scope session variables within a transaction to prevent connection pool leakage.

@Transactional
public List<Transaction> fetch(String tenantId) {
jdbcTemplate.execute("SET LOCAL app.tenant_id = '" + tenantId + "'");
return transactionRepository.findAll();
}

Practical Applications

• Financial Systems: Use RLS to ensure transactions are only visible to the originating company. Pitfall: Using SET instead of SET LOCAL in HikariCP causes tenant ID leakage between pooled connections.
• Small Teams: Start with application-level control for complex business rules. Pitfall: Forgetting to manually add tenant filters to new repository methods results in unauthorized cross-tenant data access.
• Sensitive Data Compliance: Deploy database triggers as the final barrier. Pitfall: Relying solely on the application layer allows direct database access (DBeaver/psql) to bypass audit logs.

References:

• https://dev.to/reislucaz/auditing-in-java-systems-rls-in-the-database-or-application-level-control-26e