From 9a396d3f97c72e5ac8648f115a5c1d8affcd04c1 Mon Sep 17 00:00:00 2001 From: Saiful Amin Date: Thu, 29 Jan 2026 14:14:54 +0000 Subject: [PATCH] Bug 41310: Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing This commit adds an ip_address column to the action_logs table and modifies the logaction function to capture and store the REMOTE_ADDR from the HTTP request environment. The IP address is properly handled by Koha's existing RealIP middleware which accounts for proxy servers and load balancers correctly. The implementation uses VARCHAR(45) to accommodate both IPv4 and IPv6 addresses, providing permanent audit trail linking actions to specific IP addresses regardless of session status. Test Plan: 1. Apply the patch. 2. Run the tests: prove t/db_dependent/TestIpLogging.t - Verify that all tests pass. 3. Verification: - Go to Administration > System preferences > Logs > Logging - Enable few preferences, e.g., AuthFailureLog and AuthSuccessLog - Login to OPAC with wrong and correct credentials - Connect to the Koha database (koha-mysql kohadev) and run this SQL: `SELECT * FROM action_logs WHERE ip_address IS NOT NULL;` It should show the additional column of `ip_address`. 4. Sign-off Future improvement should add this IP Address column in the Tools > Log viewer. --- C4/Log.pm | 24 +++++++------ Koha/Schema/Result/ActionLog.pm | 10 ++++++ installer/data/mysql/db_revs/bug_41310.pl | 37 +++++++++++++++++++ t/db_dependent/TestIpLogging.t | 44 +++++++++++++++++++++++ 4 files changed, 105 insertions(+), 10 deletions(-) create mode 100644 installer/data/mysql/db_revs/bug_41310.pl create mode 100644 t/db_dependent/TestIpLogging.t diff --git a/C4/Log.pm b/C4/Log.pm index 603bc8695a..e7a4a50023 100644 --- a/C4/Log.pm +++ b/C4/Log.pm @@ -140,18 +140,22 @@ sub logaction { $diff //= encode_json( diff( $original, $updated, noU => 1 ) ) if $original && ref $updated eq 'HASH'; + # Get the IP address from the environment + my $ip_address = $ENV{REMOTE_ADDR} || undef; + Koha::ActionLog->new( { - timestamp => \'NOW()', - user => $usernumber, - module => $modulename, - action => $actionname, - object => $objectnumber, - info => $infos, - interface => $interface, - script => $script, - trace => $trace, - diff => $diff, + timestamp => \'NOW()', + user => $usernumber, + module => $modulename, + action => $actionname, + object => $objectnumber, + info => $infos, + interface => $interface, + script => $script, + trace => $trace, + diff => $diff, + ip_address => $ip_address, } )->store(); diff --git a/Koha/Schema/Result/ActionLog.pm b/Koha/Schema/Result/ActionLog.pm index c97ffdcf21..82ff990004 100644 --- a/Koha/Schema/Result/ActionLog.pm +++ b/Koha/Schema/Result/ActionLog.pm @@ -106,6 +106,14 @@ An optional stack trace enabled by ActionLogsTraceDepth Stores a diff of the changed object +=head2 ip_address + + data_type: 'varchar' + is_nullable: 1 + size: 45 + +IP address of the client that performed the action + =cut __PACKAGE__->add_columns( @@ -136,6 +144,8 @@ __PACKAGE__->add_columns( { data_type => "text", is_nullable => 1 }, "diff", { data_type => "longtext", is_nullable => 1 }, + "ip_address", + { data_type => "varchar", is_nullable => 1, size => 45 }, ); =head1 PRIMARY KEY diff --git a/installer/data/mysql/db_revs/bug_41310.pl b/installer/data/mysql/db_revs/bug_41310.pl new file mode 100644 index 0000000000..e40a567ea0 --- /dev/null +++ b/installer/data/mysql/db_revs/bug_41310.pl @@ -0,0 +1,37 @@ +#!/usr/bin/perl + +use Modern::Perl; + +use C4::Context; +use Koha::Database; + +use base 'C4::Installer::DBUpgrade'; + +our $db_rev = '41310'; + +sub db_rev { + return $db_rev; +} + +sub upgrade { + my ($args) = @_; + my $upgrade_context = $args->{upgrade_context}; + my $schema = $upgrade_context->schema; + my $dbh = $upgrade_context->dbh; + + unless ( column_exists( 'action_logs', 'ip_address' ) ) { + $dbh->do( + q{ + ALTER TABLE action_logs + ADD COLUMN ip_address VARCHAR(45) NULL DEFAULT NULL + COMMENT 'IP address of the client that performed the action' + } + ); + + say $upgrade_context->output("Added column 'action_logs.ip_address'"); + } + + return 1; +} + +1; diff --git a/t/db_dependent/TestIpLogging.t b/t/db_dependent/TestIpLogging.t new file mode 100644 index 0000000000..32d4504f69 --- /dev/null +++ b/t/db_dependent/TestIpLogging.t @@ -0,0 +1,44 @@ +#!/usr/bin/perl + +# Test script to verify IP address logging in action_logs +# This script tests that the IP address is properly stored in action_logs + +use Modern::Perl; +use Test::More tests => 5; + +use C4::Context; +use C4::Log qw(logaction); +use Koha::Database; +use Koha::ActionLogs; +use t::lib::TestBuilder; +use t::lib::Mocks; + +my $schema = Koha::Database->new->schema; +$schema->storage->txn_begin; + +my $builder = t::lib::TestBuilder->new; + +# Mock REMOTE_ADDR +$ENV{REMOTE_ADDR} = '192.168.1.100'; + +# Clear any existing action logs for this test +Koha::ActionLogs->search->delete; + +# Perform a test action log +my $patron = $builder->build_object( { class => 'Koha::Patrons' } ); + +# Call logaction which should now capture the IP address +logaction( "TESTING", "ADD", $patron->borrowernumber, "Test IP logging", "intranet" ); + +# Check that the action log was created with the IP address +my $action_log = Koha::ActionLogs->search( { module => 'TESTING', action => 'ADD' } )->next; +ok( $action_log, "Action log was created" ); +is( $action_log->module, "TESTING", "Module is correct" ); +is( $action_log->action, "ADD", "Action is correct" ); +is( $action_log->info, "Test IP logging", "Info is correct" ); +is( $action_log->ip_address, "192.168.1.100", "IP address was captured correctly" ); + +# Clean up +$schema->storage->txn_rollback; + +done_testing(); -- 2.39.5