| Summary: | Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing | ||
|---|---|---|---|
| Product: | Koha | Reporter: | Saiful Amin <saiful> |
| Component: | Architecture, internals, and plumbing | Assignee: | Saiful Amin <saiful> |
| Status: | Needs Signoff --- | QA Contact: | Testopia <testopia> |
| Severity: | enhancement | ||
| Priority: | P1 - high | CC: | dcook |
| Version: | Main | ||
| Hardware: | All | ||
| OS: | All | ||
| 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
Bug 41310: (follow-up) Add ActionLogsEnableIPLogging system preference Bug 41310: Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing Bug 41310: Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing |
||
|
Description
Saiful Amin
2025-11-26 06:26:05 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. 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 Created attachment 194255 [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 |