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.
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.
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.
> 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.
(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.
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.
(In reply to Katrin Fischer from comment #5) > I think a switch would be helpful. Like a global system preference?
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 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 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
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.
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 on attachment 193980 [details] [review] Bug 41310: (follow-up) Add ActionLogsEnableIPLogging system preference Please remember to obsolete your old patches when posting new ones