PostgreSQL: Record Every Change in Your Database

Losing data can be disastrous for any company. While many rely on frequent backups and live replication, these alone don’t guarantee that no data will be overwritten and lost forever. Even between close backup intervals, information can be written and deleted, and replication merely preserves the latest version of the database — losing the history of changes in between.

TL;DR: Learn how to implement robust database auditing in PostgreSQL to track every change, identify users behind actions using SET LOCAL and current_setting, and prepend custom behavior to transactions for logging, monitoring, or validation. Find the full solution on this GitHub repository.

Enter the Audit Log

Auditing is the technique of recording any change or deletion in a database table. By doing so, it becomes possible to restore the state of any object in the database, effectively creating a history of every modification. There are various ways to implement auditing, the simplest being at the application level. Here, before or after any write or delete operation, the application saves the current state of the data in a separate table, often called “audits” or “versions.”

For example, in the Ruby ecosystem, popular gems like paper_trail and audited handle this by tracking every change/deletions and creating versions of each record. However, there are some limitations.

The Limitations of Application-Level Auditing

While application-level auditing is a good start, it has several downsides:

1. Human Error: If implemented manually, developers may forget to log changes.

2. ORM Limitations: Object-Relational Mappers (ORMs) can miss changes. For instance, in ActiveRecord (used in Ruby on Rails), bulk updates (e.g., update_all) don’t trigger the usual callback mechanisms that single-record updates do.

3. Direct SQL Access: Changes made by code directly with SQL bypass the ORM layer entirely, meaning no audit logs are recorded.

4. Database Access Control: If someone has direct access to the database (eg. psql console) , they can make untracked changes.

To address these issues, it’s best to manage auditing directly within the database.

The Ultimate Solution: Database-Level Auditing

Using PostgreSQL’s built-in functionalities, you can enable the database to handle its own auditing, making it foolproof. The setup involves creating a trigger function that logs every INSERT, UPDATE, or DELETE operation to a dedicated table. Here’s how to implement it.

Step 1: Create an Audit Schema and Table

The audit table can get massive over time, making it long to backup the effective data. It’s a good idea to define it in a separate schema within the same database. This way we can have the choice de backup all the database, audit included, or backup the data and the audit separately.

-- Create a schema named "audit"
CREATE SCHEMA audit;
REVOKE CREATE ON SCHEMA audit FROM public;

CREATE TABLE audit.logged_actions (
    schema_name TEXT NOT NULL,
    table_name TEXT NOT NULL,
    record_id INTEGER NOT NULL,
    user_name TEXT,
    action_tstamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    action TEXT NOT NULL CHECK (action IN ('I', 'D', 'U')),
    original_data TEXT,
    new_data TEXT,
    query TEXT
) WITH (fillfactor=100);

REVOKE ALL ON audit.logged_actions FROM public;
GRANT SELECT ON audit.logged_actions TO public;

This logged_actions table will store the details of each change. The action column will record the type of action (I for insert, D for delete, U for update). You can add indexes on frequently queried columns for optimized performance:

CREATE INDEX logged_actions_schema_table_idx
ON audit.logged_actions(((schema_name || '.' || table_name)::TEXT));

CREATE INDEX logged_actions_action_tstamp_idx 
ON audit.logged_actions(action_tstamp);

CREATE INDEX logged_actions_action_idx 
ON audit.logged_actions(action);

Step 2: Define the Trigger Function

The following trigger function will insert a record into audit.logged_actions every time a row in the target table is modified.

CREATE OR REPLACE FUNCTION audit.log_current_action() RETURNS trigger AS $body$
DECLARE
    v_old_data TEXT;
    v_new_data TEXT;
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        v_old_data := ROW(OLD.*);
        v_new_data := ROW(NEW.*);
        INSERT INTO audit.logged_actions 
        (schema_name, table_name, record_id, user_name, action, original_data, new_data, query)
        VALUES 
        (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, NEW.id, session_user::TEXT, substring(TG_OP,1,1), v_old_data, v_new_data, current_query());
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        v_old_data := ROW(OLD.*);
        INSERT INTO audit.logged_actions 
        (schema_name, table_name, record_id, user_name, action, original_data, query)
        VALUES 
        (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, OLD.id, session_user::TEXT, substring(TG_OP,1,1), v_old_data, current_query());
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        v_new_data := ROW(NEW.*);
        INSERT INTO audit.logged_actions 
        (schema_name, table_name, record_id, user_name, action, new_data, query)
        VALUES 
        (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, NEW.id, session_user::TEXT, substring(TG_OP,1,1), v_new_data, current_query());
        RETURN NEW;
    ELSE
        RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - Other action occurred: %, at %', TG_OP, now();
        RETURN NULL;
    END IF;
EXCEPTION
    WHEN data_exception THEN
        RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - Data Exception';
        RETURN NULL;
    WHEN unique_violation THEN
        RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - Unique Violation';
        RETURN NULL;
    WHEN others THEN
        RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - Other Exception';
        RETURN NULL;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, audit;

Step 3: Attach Triggers to Tables

Add this trigger function to each table you want to audit:

CREATE TRIGGER tablename_audit
AFTER INSERT OR UPDATE OR DELETE ON tablename
FOR EACH ROW EXECUTE FUNCTION audit.log_current_action();

In Ruby on Rails, you can automate this process for every table in your database using the following snippet:

(ActiveRecord::Base.connection.tables - ["schema_migrations", "ar_internal_metadata"]).each do |table_name|
  ActiveRecord::Base.connection.execute(<<-SQL)
    CREATE TRIGGER #{table_name}_audit
    AFTER INSERT OR UPDATE OR DELETE ON #{table_name}
    FOR EACH ROW EXECUTE FUNCTION audit.log_current_action();
  SQL
end

Automate Triggers on Table Creation

To ensure triggers aren’t forgotten for newly created tables, extend ActiveRecord’s create_table and drop_table methods:

module TableWithTrigger
  def create_table(table_name, **options)
    super(table_name, **options) do |t|
      yield(t) if block_given?
    end
    add_trigger(table_name)
  end

  def drop_table(table_name, **options)
    remove_trigger(table_name)
    super(table_name, **options)
  end

  private

  def add_trigger(table_name)
    execute <<-SQL
      CREATE TRIGGER #{table_name}_trigger
      AFTER INSERT OR UPDATE OR DELETE ON #{table_name}
      FOR EACH ROW EXECUTE FUNCTION audit.log_current_action();
    SQL
  end

  def remove_trigger(table_name)
    execute <<-SQL
      DROP TRIGGER IF EXISTS #{table_name}_trigger;
    SQL
  end
end

ActiveSupport.on_load(:active_record) do
  ActiveRecord::ConnectionAdapters::SchemaStatements.prepend(TableWithTrigger)
end

Whodunit: Tracking the Responsible User with SET LOCAL and current_setting

One of the challenges of database-level auditing is tracking which user performed an action. Unlike application-level auditing, where the user’s identity is readily available, database-level auditing often defaults to session_user, which reflects the database user rather than the actual application user.

PostgreSQL’s SET LOCAL and current_setting provide a way to track the application user by setting a session-specific variable during a database connection. This can be implemented seamlessly in applications like Rails.

Using SET LOCAL to Track Application Users

PostgreSQL allows you to set custom session variables within a transaction using SET LOCAL. These variables are available only within the scope of the current transaction, ensuring they don’t persist across unrelated database operations.

Setting the Variable

• Application Context: In a Rails application, the variable can be set in a before_action callback in controllers to track the authenticated user.
• Database Context: The custom variable is accessible in audit triggers using current_setting().

Example Workflow in Rails

1. Set the User Variable in an Around Action: Add a around_action in your ApplicationController to set the Current user for each request.

class ApplicationController < ActionController::Base
  allow_browser versions: :modern

  around_action :set_user

  private

  def set_user
    Current.set(user: current_user) do
      yield
    end
  end
end

2. Set the Current User id in ActiveRecord internals

module CustomTransactionBehavior
  def begin_db_transaction
    super
    internal_execute("SET LOCAL app.current_user_id to '#{Current.user&.id || 'Guest'}';", "TRANSACTION", allow_retry: true, materialize_transactions: false)
  end
end

ActiveSupport.on_load(:active_record) do
  ActiveRecord::ConnectionAdapters::PostgreSQL::DatabaseStatements.prepend(CustomTransactionBehavior)
end

Here, the SET LOCAL command sets a custom variable app.current_user for the duration of the current transaction.

3. Access the Variable in PostgreSQL: Modify the audit trigger function to read the app.current_user variable:

CREATE OR REPLACE FUNCTION audit.log_current_action() RETURNS trigger AS $$
DECLARE
    user_name TEXT;
    v_old_data TEXT;
    v_new_data TEXT;
BEGIN
    user_name := current_setting('app.current_user_id', false);

    IF (TG_OP = 'UPDATE') THEN
        v_old_data := ROW(OLD.*);
        v_new_data := ROW(NEW.*);
        INSERT INTO audit.logged_actions (
            schema_name, table_name, record_id, user_name, action, original_data, new_data, query
        ) VALUES (
            TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id, user_name, 'U', v_old_data, v_new_data, current_query()
        );
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        v_old_data := ROW(OLD.*);
        INSERT INTO audit.logged_actions (
            schema_name, table_name, record_id, user_name, action, original_data, query
        ) VALUES (
            TG_TABLE_SCHEMA, TG_TABLE_NAME, OLD.id, user_name, 'D', v_old_data, current_query()
        );
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        v_new_data := ROW(NEW.*);
        INSERT INTO audit.logged_actions (
            schema_name, table_name, record_id, user_name, action, new_data, query
        ) VALUES (
            TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id, user_name, 'I', v_new_data, current_query()
        );
        RETURN NEW;
    ELSE
        RAISE WARNING 'Unknown operation: %', TG_OP;
        RETURN NULL;
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Using SET LOCAL in PostgreSQL with ActiveRecord published from an application wide controller’saround_action provides granular tracking of application users in database auditing, ensuring that each transaction is associated with the actual user initiating the action. It’s secure, as the variable is scoped to the transaction, and integrates easily into application logic. However, this approach requires consistent implementation to avoid missing user context, and it may not work with connection pooling solutions like pgbouncer that don’t support per-transaction settings.

The second parameter of current_setting, called missing_ok, determines the behavior when the setting is absent. To make it optional rather than mandatory, set missing_ok to true.

user_name := current_setting('app.current_user_id', true);

Performance Impact: Auditing and Write Performance

Adding auditing directly to PostgreSQL impacts write performance in several ways:

1. Increased I/O: Each write operation (INSERT, UPDATE, DELETE) now triggers an additional write to the audit.logged_actions table, which increases disk I/O load.
2. Higher CPU Load: Trigger functions run on every modification, consuming CPU resources and slowing responsiveness, especially in high-traffic tables.
3. Storage Growth: The audit table grows with every logged change, requiring careful disk management and regular maintenance (vacuuming and archiving) to prevent bloat.
4. Transaction Complexity: Auditing is part of each transaction. Rollbacks also revert audit logs, adding complexity and potentially causing delays.

Optimizing Performance

To balance auditing needs with performance:

• Selectively apply triggers to critical tables only.
• Limit logged data to essential columns.
• Archive old logs and regularly vacuum to prevent table bloat.
• Partition the audit table to improve query performance and ease maintenance.

In sum, while auditing impacts write speed, careful configuration, and maintenance help manage these effects, keeping audit trails effective without overloading the database.

Alternatives to Database-Level Auditing

If your app is data-intensive, consider alternatives to reduce the impact of database-level auditing:

1. Application-Level Auditing: Track changes in application code, often with ORM tools (e.g., paper_trail in Ruby). This keeps database triggers out but may miss direct SQL changes.
2. Log-Based Auditing: Use PostgreSQL’s write-ahead log (WAL) to track changes, avoiding trigger overhead. This is efficient but complex to parse and manage.
3. Event Sourcing: Store each change as an “event,” creating a built-in audit trail. This is ideal for high-throughput systems but requires a shift in data management.
4. External Log Services: Use tools like the ELK stack to offload audit logs, handling high volumes without affecting write performance.

Choose based on your app’s performance needs and audit requirements.

Thanks for reading me!