Bug 41310 - Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing
Summary: Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P1 - high enhancement
Assignee: Saiful Amin
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2025-11-26 06:26 UTC by Saiful Amin
Modified: 2026-03-01 23:23 UTC (History)
1 user (show)

See Also:
GIT URL:
Initiative type: ---
Sponsorship status: ---
Comma delimited list of Sponsors:
Crowdfunding goal: 0
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments
Bug 41310: Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing (6.25 KB, patch)
2026-01-29 14:27 UTC, Saiful Amin
Details | Diff | Splinter Review
Bug 41310: (follow-up) Add ActionLogsEnableIPLogging system preference (10.98 KB, patch)
2026-02-26 11:44 UTC, Saiful Amin
Details | Diff | Splinter Review
Bug 41310: Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing (11.31 KB, patch)
2026-02-27 18:51 UTC, Saiful Amin
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Saiful Amin 2025-11-26 06:26:05 UTC
Context:
Currently, the action_logs table records what happened, when it happened, and who performed the action. However, it does not record the network context (source IP) of the request.

The Problem:
For security auditing, it is necessary to trace an action back to a specific IP address. This is critical when:
1. A staff account is compromised (distinguishing the attacker from the real user).
2. Multiple concurrent sessions exist for the same user (e.g., logged in at the desk and on a mobile device).

The Session data:
The sessions table in Koha is temporary, which may even be stored cache or filesystem. If we store the session_id in action_logs, we can only link it to an IP address while the session is still active. Once the session is cleared, we will lose the IP address history.

Proposed Solution:
We should capture the REMOTE_ADDR at the time the action occurs and store it directly into a new column in the action_logs table. This ensures the audit trail remains complete permanently, regardless of the session status.
Comment 1 Saiful Amin 2026-01-29 14:27:00 UTC
Created attachment 192147 [details] [review]
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.
Comment 2 David Cook 2026-01-30 06:18:30 UTC
I'm not sure what people will think about this one.

But, if it were to go ahead, it would need the matching change in kohastructure.sql as well.
Comment 3 Saiful Amin 2026-01-30 11:14:11 UTC
> But, if it were to go ahead, it would need the matching change in
> kohastructure.sql as well.

I was under the impression that that is something done during release management. Let me check the developer docs again and I'll add submit another patch if needed.

Nonetheless, I feel this is an important improvement from the security audit perspective.
Comment 4 David Cook 2026-02-02 00:21:33 UTC
(In reply to Saiful Amin from comment #3)
> > But, if it were to go ahead, it would need the matching change in
> > kohastructure.sql as well.
> 
> I was under the impression that that is something done during release
> management. Let me check the developer docs again and I'll add submit
> another patch if needed.

Nope, it's done by the developer. It's your db_revs one that gets changed during release management.

> Nonetheless, I feel this is an important improvement from the security audit
> perspective.

Yeah, I think there's merit to it. There might be some data privacy issues, so it might need to be governed by a system preference, but the Europeans on here will have a better idea about that.
Comment 5 Katrin Fischer 2026-02-02 07:26:58 UTC
To my knowledge IP addresses are considered personal data, so keeping/storing them would require changes to your data privacy documentation. I think a switch would be helpful.
Comment 6 David Cook 2026-02-03 00:33:14 UTC
(In reply to Katrin Fischer from comment #5)
> I think a switch would be helpful.

Like a global system preference?
Comment 7 Saiful Amin 2026-02-26 11:44:03 UTC
Created attachment 193980 [details] [review]
Bug 41310: (follow-up) Add ActionLogsEnableIPLogging system preference

Update to previous patch:
- Add ActionLogsEnableIPLogging system preference with GDPR compliance notice
- Add ActionLogsEnableIPLogging toggle in System preferences > Logs > Debugging
- Log IP address only if ActionLogsEnableIPLogging is set to 1
- Update kohastructure.sql: add `ip_address` column to action_logs table

Test Plan:
1. Apply the patch
2. Run the tests:
   `prove t/db_dependent/TestIpLogging.t`
   - Verify that all tests pass
3. Verification with IP Logging Disabled:
   - Go to Administration > System preferences > Logs > Debugging
   - Ensure that 'ActionLogsEnableIPLogging' is set to 'No'
   - Go to Administration > System preferences > Logs > Logging
   - Toggle any preference, e.g., AcquisitionLog
   - Connect to the Koha database (koha-mysql kohadev) and run:
   `SELECT * FROM action_logs ORDER BY action_id DESC LIMIT 1;`
   - The 'ip_address' column should be NULL
4. Verification with IP Logging Enabled:
   - Go to Administration > System preferences > Logs > Debugging
   - Ensure that 'ActionLogsEnableIPLogging' is set to 'Yes'
   - Go to Administration > System preferences > Logs > Logging
   - Toggle any preference, e.g., AcquisitionLog
   - Connect to the Koha database (koha-mysql kohadev) and run:
   `SELECT * FROM action_logs ORDER BY action_id DESC LIMIT 1;`
   - The 'ip_address' column should contain a valid IP address
5. Sign off

Future improvement: Add the IP Address column to Tools > Log viewer
Comment 8 David Cook 2026-02-26 22:38:50 UTC
Comment on attachment 192147 [details] [review]
Bug 41310: Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing

The second patch isn't a follow-up - it's a replacement so obsoleting this one
Comment 9 David Cook 2026-02-26 22:42:55 UTC
Comment on attachment 193980 [details] [review]
Bug 41310: (follow-up) Add ActionLogsEnableIPLogging system preference

Review of attachment 193980 [details] [review]:
-----------------------------------------------------------------

::: installer/data/mysql/db_revs/bug_41310.pl
@@ +1,1 @@
> +#!/usr/bin/perl

This file format is invalid. Did you use AI to create this? Please see https://wiki.koha-community.org/wiki/Database_updates#How_to_write_a_database_update for the way to write a database update

::: installer/data/mysql/kohastructure.sql
@@ +180,4 @@
>    `script` varchar(255) DEFAULT NULL COMMENT 'the name of the cron script that caused this change',
>    `trace` text DEFAULT NULL COMMENT 'An optional stack trace enabled by ActionLogsTraceDepth',
>    `diff` longtext DEFAULT NULL COMMENT 'Stores a diff of the changed object',
> +  `ip_address` varchar(45) DEFAULT NULL COMMENT 'IP address of the client that performed the action',

Good call on 45 characters for the IP address because of IPv6
Comment 10 David Cook 2026-02-26 22:43:57 UTC
You'll need to remove "(follow-up)" from the patch title as well since it's not a follow-up in the sense of adding onto an existing patch.
Comment 11 Saiful Amin 2026-02-27 18:51:45 UTC
Created attachment 194151 [details] [review]
Bug 41310: Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing

What this patch does:
- Add an ip_address column to the action_logs table
- Uses VARCHAR(45) to accommodate both IPv4 and IPv6 addresses
- Modify the logaction function to capture and store the REMOTE_ADDR into ip_address column
- Add ActionLogsEnableIPLogging system preference with GDPR compliance notice
- Add ActionLogsEnableIPLogging toggle in System preferences > Logs > Debugging
- Log IP address only if ActionLogsEnableIPLogging is set to 1
- Update kohastructure.sql: add `ip_address` column to action_logs table

Test Plan:
1. Apply the patch
2. Run the tests:
   `prove t/db_dependent/TestIpLogging.t`
   - Verify that all tests pass
3. Verification with IP Logging Disabled:
   - Go to Administration > System preferences > Logs > Debugging
   - Ensure that 'ActionLogsEnableIPLogging' is set to 'No'
   - Go to Administration > System preferences > Logs > Logging
   - Toggle any preference, e.g., AcquisitionLog
   - Connect to the Koha database (koha-mysql kohadev) and run:
   `SELECT * FROM action_logs ORDER BY action_id DESC LIMIT 1;`
   - The 'ip_address' column should be NULL
4. Verification with IP Logging Enabled:
   - Go to Administration > System preferences > Logs > Debugging
   - Ensure that 'ActionLogsEnableIPLogging' is set to 'Yes'
   - Go to Administration > System preferences > Logs > Logging
   - Toggle any preference, e.g., AcquisitionLog
   - Connect to the Koha database (koha-mysql kohadev) and run:
   `SELECT * FROM action_logs ORDER BY action_id DESC LIMIT 1;`
   - The 'ip_address' column should contain a valid IP address
5. Sign off

Future improvement: Add the IP Address column to Tools > Log viewer
Comment 12 David Cook 2026-03-01 23:23:56 UTC
Comment on attachment 193980 [details] [review]
Bug 41310: (follow-up) Add ActionLogsEnableIPLogging system preference

Please remember to obsolete your old patches when posting new ones