Database Auditing: pg_audit vs audited vs paper_trail

Auditing, tracking and logging every change made to data in a system, matters more once an application has to answer “who changed this, and when.” This article compares several popular auditing solutions.

Why Auditing Matters

An auditing system in your database application offers several key benefits:

1. Compliance: Many industries are legally required to maintain an audit trail of changes to sensitive data.
2. Security: Audit logs help detect unauthorized access or potential data breaches.
3. Transparency: A clear record of who did what and when promotes accountability.
4. Troubleshooting: Audit trails aid in understanding the sequence of events leading to issues.
5. Recovery: In case of data corruption or loss, audit logs can help restore the system to a previous state.

Comparing Auditing Solutions

Let’s examine several popular auditing solutions and their key features:

1. pg_audit

pg_audit is a PostgreSQL extension that provides detailed session and object auditing at the database level.

Pros:

• Low performance overhead
• Detailed logging of successful and failed access attempts
• Real-time monitoring capabilities
• Integrates directly with PostgreSQL

Cons:

• Requires PostgreSQL knowledge to set up
• No built-in reversion capabilities

Example Setup:

-- Enable pg_audit extension
CREATE EXTENSION pgaudit;

-- Configure audit logging
ALTER SYSTEM SET pgaudit.log = 'write';
ALTER SYSTEM SET pgaudit.log_catalog = off;
ALTER SYSTEM SET pgaudit.log_client = on;
ALTER SYSTEM SET pgaudit.log_level = notice;
ALTER SYSTEM SET pgaudit.log_statement_once = off;

-- Reload configuration
SELECT pg_reload_conf();

2. audited

audited is a Ruby gem designed for Rails applications, working at the application layer and integrating with ActiveRecord.

Pros:

• Easy setup and configuration
• Flexible auditing strategies
• Tracks user context for changes
• Basic reversion capabilities

Cons:

• May introduce some performance overhead
• Limited to Rails applications

Example Setup:

# Gemfile
gem 'audited'

# In your model
class User < ApplicationRecord
  audited
end

# In your controller
def update
  @user.update(user_params)
  # The audit is automatically created
end

3. papertrail

papertrail is another Ruby gem for Rails that focuses on tracking changes to model instances over time.

Pros:

• Keeps a full history of changes
• Allows reverting to any previous version
• Provides diffing between versions
• Ideal for applications requiring detailed version history

Cons:

• Higher storage requirements due to full version storage
• Limited to Rails applications
• Potential performance impact

Example Setup:

# Gemfile
gem 'paper_trail'

# In your model
class User < ApplicationRecord
  has_paper_trail
end

# In your controller
def update
  @user.update(user_params)
  # The version is automatically created
end

4. Hibernate Envers

Hibernate Envers is an auditing solution for Java applications using Hibernate ORM.

Pros:

• Integrates well with Java and Hibernate ecosystems
• Provides historical data querying
• Supports complex data models and relationships

Cons:

• Specific to Java and Hibernate
• Can add complexity to the application

Example Setup:

import org.hibernate.envers.Audited;

@Entity
@Audited
public class User {
    @Id
    private Long id;
    private String name;
    // other fields and methods
}

5. SQL Server Temporal Tables

For Microsoft SQL Server users, Temporal Tables provide built-in support for auditing and historical data.

Pros:

• Native SQL Server feature (2016 and later)
• Automatic tracking of data changes
• Efficient querying of historical data

Cons:

• Limited to SQL Server databases
• Requires careful design for complex scenarios

Example Setup:

CREATE TABLE Users
(
    UserId INT PRIMARY KEY,
    Name NVARCHAR(100),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

Choosing the Right Solution

When selecting an auditing solution, consider the following factors:

1. Implementation Level: Database-level vs. application-level
2. Performance Impact: Consider the overhead introduced by the auditing system
3. Ease of Setup: Evaluate the complexity of integration and configuration
4. Storage Requirements: Assess the long-term storage impact
5. Reversion Capabilities: Determine if you need to revert to previous states
6. Compliance Needs: Ensure the solution meets regulatory requirements
7. Technology Stack: Choose a solution compatible with your existing infrastructure
8. Scalability: Consider how the solution will perform as your data grows

The principle

pg_audit gives PostgreSQL-level auditing with low overhead. audited and paper_trail are the accessible options if you’re already on Rails. Hibernate Envers and SQL Server temporal tables cover the Java and SQL Server cases. The right choice comes down to your stack, your performance budget, and how much history you actually need to keep.