[Bug 41310] New: Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Bug ID: 41310 Summary: Add remote_ip (REMOTE_ADDR) to action_logs table to improve security auditing Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: koha-bugs@lists.koha-community.org Reporter: saiful@semanticconsulting.com QA Contact: testopia@bugs.koha-community.org 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #1 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 192147 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=192147&action=edit 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. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au --- Comment #2 from David Cook <dcook@prosentient.com.au> --- 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #3 from Saiful Amin <saiful@semanticconsulting.com> ---
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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |saiful@semanticconsulting.c |ity.org |om Priority|P5 - low |P1 - high -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #4 from David Cook <dcook@prosentient.com.au> --- (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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #5 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #6 from David Cook <dcook@prosentient.com.au> --- (In reply to Katrin Fischer from comment #5)
I think a switch would be helpful.
Like a global system preference? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #7 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 193980 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=193980&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #192147|0 |1 is obsolete| | --- Comment #8 from David Cook <dcook@prosentient.com.au> --- Comment on attachment 192147 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=192147 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #9 from David Cook <dcook@prosentient.com.au> --- Comment on attachment 193980 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=193980 Bug 41310: (follow-up) Add ActionLogsEnableIPLogging system preference Review of attachment 193980: --> (https://bugs.koha-community.org/bugzilla3/page.cgi?id=splinter.html&bug=41310&attachment=193980) ----------------------------------------------------------------- ::: 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_databas... 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 are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Failed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #10 from David Cook <dcook@prosentient.com.au> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #11 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 194151 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=194151&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #193980|0 |1 is obsolete| | --- Comment #12 from David Cook <dcook@prosentient.com.au> --- Comment on attachment 193980 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=193980 Bug 41310: (follow-up) Add ActionLogsEnableIPLogging system preference Please remember to obsolete your old patches when posting new ones -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #194151|0 |1 is obsolete| | --- Comment #13 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 194255 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=194255&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #14 from David Cook <dcook@prosentient.com.au> --- The test plan is missing the steps for upgrading the database and restarting services but that's ok. Not a blocker. I don't know about having "ActionLogsEnableIPLogging" under "Debugging" instead of "Logging", but... I'm not too fussed. If we want to change the description and categorisation later, it's not hard to do, so I think it's fine. I can't recall if we're supposed to include Koha/Schema/Result/ActionLog.pm in the main patch. I think it's supposed to be in a separate patch, but I'm double-checking with QA team on that one. Other than that... I think it's all good. It works as described. I'll mark as Signed Off for now. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Failed QA --- Comment #15 from David Cook <dcook@prosentient.com.au> --- (In reply to David Cook from comment #14)
The test plan is missing the steps for upgrading the database and restarting services but that's ok. Not a blocker.
I don't know about having "ActionLogsEnableIPLogging" under "Debugging" instead of "Logging", but... I'm not too fussed. If we want to change the description and categorisation later, it's not hard to do, so I think it's fine.
I can't recall if we're supposed to include Koha/Schema/Result/ActionLog.pm in the main patch. I think it's supposed to be in a separate patch, but I'm double-checking with QA team on that one.
Other than that... I think it's all good. It works as described. I'll mark as Signed Off for now.
Actually, I was a bit premature there. Checked with the QA team and Koha/Schema/Result/ActionLog.pm should be in a separate patch as the RM will ignore that patch later and generate the schema files later. (It's just included for testing purposes.) The QA tools show a few other problems: Result: [PASS] C4/Log.pm [SKIP] Koha/Schema/Result/ActionLog.pm SKIP pod_coverage No POD exists [FAIL] installer/data/mysql/atomicupdate/bug_41310-add_ip_address_to_action_logs.pl FAIL tidiness File is not tidy, please run `perl misc/devel/tidy.pl installer/data/mysql/atomicupdate/bug_41310-add_ip_address_to_action_logs.pl` [PASS] installer/data/mysql/kohastructure.sql [PASS] installer/data/mysql/mandatory/sysprefs.sql [PASS] koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/logs.pref [FAIL] t/db_dependent/TestIpLogging.t FAIL file_permissions File must have the exec flag FAIL test_no_warnings 'use Test::NoWarnings' must be present in this test file. Processing additional checks PASS! * Was this bug sponsored by anyone? * Have you considered sharing the new syspref to HEA? (see bug 23849 and C4/UsageStats.pm) Running tests (1) * Proving /kohadevbox/koha/t/db_dependent/TestIpLogging.t PASS -- So I should be able to signoff once you move Koha/Schema/Result/ActionLog.pm to a separate patch, and address those FAILs in the QA test results. They should be pretty easy to do. Let me know if you need help with any of those. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #16 from David Cook <dcook@prosentient.com.au> --- Nearly there! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #17 from Saiful Amin <saiful@semanticconsulting.com> --- (In reply to David Cook from comment #14)
The test plan is missing the steps for upgrading the database and restarting services but that's ok. Not a blocker.
Sorry I missed that! I'll add now.
I don't know about having "ActionLogsEnableIPLogging" under "Debugging" instead of "Logging",
I did consider placing it under "Logging", but it seemed the odd one out. Whereas, placing it next to 'ActionLogsTraceDepth' felt more logical.
So I should be able to signoff once you move Koha/Schema/Result/ActionLog.pm to a separate patch, and address those FAILs in the QA test results. They should be pretty easy to do. Let me know if you need help with any of those.
I'll need help with this. Is it two separate commits in same patch or a follow-up patch? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #18 from David Cook <dcook@prosentient.com.au> --- (In reply to Saiful Amin from comment #17)
(In reply to David Cook from comment #14)
The test plan is missing the steps for upgrading the database and restarting services but that's ok. Not a blocker.
Sorry I missed that! I'll add now.
Thanks :)
I don't know about having "ActionLogsEnableIPLogging" under "Debugging" instead of "Logging",
I did consider placing it under "Logging", but it seemed the odd one out. Whereas, placing it next to 'ActionLogsTraceDepth' felt more logical.
Yeah, no big deal either way. All good.
So I should be able to signoff once you move Koha/Schema/Result/ActionLog.pm to a separate patch, and address those FAILs in the QA test results. They should be pretty easy to do. Let me know if you need help with any of those.
I'll need help with this. Is it two separate commits in same patch or a follow-up patch?
I'd say neither. You'll want to split the commit into 2 commits and attach them as 2 separate patches. So the one with Koha/Schema/Result/ActionLog.pm would have a title like "Bug 41310: Schema changes (do not push)". The release manager Lucas explains this a bit more at https://chat.koha-community.org/koha-community/pl/gah7x4txiigbdc15a518kzbnxy For example, look at bug 36506. the final patch there is "Bug 36506: DO NOT PUSH - SCHEMA UPDATES". -- If you wanted to go even further you could separate the "installer/data/mysql/atomicupdate/bug_41310-add_ip_address_to_action_logs.pl", "installer/data/mysql/kohastructure.sql", and "installer/data/mysql/mandatory/sysprefs.sql" into a "Database update" patch, but that's not strictly necessary. The schema updates being separated IS strictly necessary though. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #194255|0 |1 is obsolete| | --- Comment #19 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 194429 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=194429&action=edit 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. Update the database `perl installer/data/mysql/updatedatabase.pl` (Or 'updatedatabase' and 'restart_all', if using ktd) 3. Run the tests: `prove t/db_dependent/TestIpLogging.t` - Verify that all tests pass 4. 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 5. 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 6. Sign off Future improvement: Add the IP Address column to Tools > Log viewer -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #20 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 194430 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=194430&action=edit Bug 41310: Schema changes DO NOT PUSH -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #194429|0 |1 is obsolete| | --- Comment #21 from David Cook <dcook@prosentient.com.au> --- Created attachment 195604 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195604&action=edit 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. Update the database `perl installer/data/mysql/updatedatabase.pl` (Or 'updatedatabase' and 'restart_all', if using ktd) 3. Run the tests: `prove t/db_dependent/TestIpLogging.t` - Verify that all tests pass 4. 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 5. 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 6. Sign off Future improvement: Add the IP Address column to Tools > Log viewer Signed-off-by: David Cook <dcook@prosentient.com.au> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #194430|0 |1 is obsolete| | --- Comment #22 from David Cook <dcook@prosentient.com.au> --- Created attachment 195605 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195605&action=edit Bug 41310: Schema changes DO NOT PUSH Signed-off-by: David Cook <dcook@prosentient.com.au> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #23 from David Cook <dcook@prosentient.com.au> --- Created attachment 195606 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195606&action=edit Bug 41310: (follow-up) fix unit test Signed-off-by: David Cook <dcook@prosentient.com.au> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the| |This change adds a system release notes| |preference | |ActionLogsEnableIPLogging. | |When enabled, it will | |record the IP address of | |the user making the request | |that records an action_logs | |entry. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Elizabeth Hoffman <ehoffman@plumcreeklibrary.net> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |ehoffman@plumcreeklibrary.n | |et -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195604|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195605|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195606|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #24 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 198955 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198955&action=edit Bug 41310: Schema changes DO NOT PUSH Patch from commit a33fe60 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #25 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 198956 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198956&action=edit 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. Update the database `perl installer/data/mysql/updatedatabase.pl` (Or 'updatedatabase' and 'restart_all', if using ktd) 3. Run the tests: `prove t/db_dependent/TestIpLogging.t` - Verify that all tests pass 4. Verification with IP Logging Disabled: - Go to Administration > System preferences > Logs > Debugging - Ensure that 'ActionLogsEnableIPLogging' is set to "Don't" - 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 5. Verification with IP Logging Enabled: - Go to Administration > System preferences > Logs > Debugging - Ensure that 'ActionLogsEnableIPLogging' is set to 'Do' - 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 6. Sign off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #198956|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #26 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 198961 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198961&action=edit 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. Update the database `perl installer/data/mysql/updatedatabase.pl` (Or 'updatedatabase' and 'restart_all', if using ktd) 3. Run the tests: `prove t/db_dependent/TestIpLogging.t` - Verify that all tests pass 4. Verification with IP Logging Disabled: - Go to Administration > System preferences > Logs > Debugging - Ensure that 'ActionLogsEnableIPLogging' is set to "Don't" - 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 5. Verification with IP Logging Enabled: - Go to Administration > System preferences > Logs > Debugging - Ensure that 'ActionLogsEnableIPLogging' is set to 'Do' - 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 6. Sign off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Faheemuddin Syed <faheem@nexuslib.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Faheemuddin Syed <faheem@nexuslib.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #198955|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Faheemuddin Syed <faheem@nexuslib.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #198961|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #27 from Faheemuddin Syed <faheem@nexuslib.com> --- Created attachment 198966 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198966&action=edit Bug 41310: Schema changes DO NOT PUSH Signed-off-by: Syed Faheemuddin <faheem@nexuslib.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #28 from Faheemuddin Syed <faheem@nexuslib.com> --- Created attachment 198967 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198967&action=edit 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. Update the database `perl installer/data/mysql/updatedatabase.pl` (Or 'updatedatabase' and 'restart_all', if using ktd) 3. Run the tests: `prove t/db_dependent/TestIpLogging.t` - Verify that all tests pass 4. Verification with IP Logging Disabled: - Go to Administration > System preferences > Logs > Debugging - Ensure that 'ActionLogsEnableIPLogging' is set to "Don't" - 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 5. Verification with IP Logging Enabled: - Go to Administration > System preferences > Logs > Debugging - Ensure that 'ActionLogsEnableIPLogging' is set to 'Do' - 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 6. Sign off Signed-off-by: Syed Faheemuddin <faheem@nexuslib.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com Status|Signed Off |Failed QA --- Comment #29 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- When I open the logs after applying this patch I get a 500 error: ==> /var/log/koha/kohadev/plack-api-error.log <== [2026/06/05 21:56:17] [WARN] OpenAPI >>> GET api/v1/action_logs [{"message":"Properties not allowed: ip_address.","path":"\/body\/0"},{"message":"Properties not allowed: ip_address.","path":"\/body\/1"},{"message":"Properties not allowed: ip_address.","path":"\/body\/10"},{"message":"Properties not allowed: ip_address.","path":"\/body\/11"},{"message":"Properties not allowed: ip_address.","path":"\/body\/12"},{"message":"Properties not allowed: ip_address.","path":"\/body\/13"},{"message":"Properties not allowed: ip_address.","path":"\/body\/14"},{"message":"Properties not allowed: ip_address.","path":"\/body\/15"},{"message":"Properties not allowed: ip_address.","path":"\/body\/16"},{"message":"Properties not allowed: ip_address.","path":"\/body\/17"},{"message":"Properties not allowed: ip_address.","path":"\/body\/2"},{"message":"Properties not allowed: ip_address.","path":"\/body\/3"},{"message":"Properties not allowed: ip_address.","path":"\/body\/4"},{"message":"Properties not allowed: ip_address.","path":"\/body\/5"},{"message":"Properties not allowed: ip_address.","path":"\/body\/6"},{"message":"Properties not allowed: ip_address.","path":"\/body\/7"},{"message":"Properties not allowed: ip_address.","path":"\/body\/8"},{"message":"Properties not allowed: ip_address.","path":"\/body\/9"}] -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #30 from Saiful Amin <saiful@semanticconsulting.com> --- (In reply to Lucas Gass (lukeg) from comment #29)
When I open the logs after applying this patch I get a 500 error:
==> /var/log/koha/kohadev/plack-api-error.log <== [2026/06/05 21:56:17] [WARN] OpenAPI >>> GET api/v1/action_logs [{"message":"Properties not allowed: ip_address.","path":"\/body\/0"},{"message":"Properties not allowed:
Thanks Lucas. This is interesting and a very good catch. Let me dig into this. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #198966|0 |1 is obsolete| | Attachment #198967|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #31 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 200038 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=200038&action=edit Bug 41310: Schema changes DO NOT PUSH Patch from commit fbe709e -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #32 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 200039 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=200039&action=edit 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 - Also add IP Address column in the log viewer Test Plan: 1. Apply the patch 2. Update the database `perl installer/data/mysql/updatedatabase.pl` (Or 'updatedatabase' and 'restart_all', if using ktd) 3. Run the tests: `prove t/db_dependent/TestIpLogging.t` - Verify that all tests pass 4. Verification with IP Logging Disabled: - Go to Administration > System preferences > Logs > Debugging - Ensure that 'ActionLogsEnableIPLogging' is set to "Don't" - 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 5. Verification with IP Logging Enabled: - Go to Administration > System preferences > Logs > Debugging - Ensure that 'ActionLogsEnableIPLogging' is set to 'Do' - 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 6. Check IP address column in Log viewer: - Go to Tools > Log viewer - In the Log entries table, click Configure button - Deselect 'ip_address' in 'is hidden by default' checkbox and Save - To be safe, open a new private browser window, log in again, then go to Log viewer again - Verify that the IP address column is displayed 7. Sign off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #33 from Saiful Amin <saiful@semanticconsulting.com> --- I forgot mention another test to run in the latest patch: t/db_dependent/api/v1/action_logs.t -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Faheemuddin Syed <faheem@nexuslib.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off --- Comment #34 from Faheemuddin Syed <faheem@nexuslib.com> --- In ktd, after restart_all, flush_memcached is required before the log-viewer shows IP address column. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 Faheemuddin Syed <faheem@nexuslib.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #200038|0 |1 is obsolete| | Attachment #200039|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #35 from Faheemuddin Syed <faheem@nexuslib.com> --- Created attachment 200101 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=200101&action=edit Bug 41310: Schema changes DO NOT PUSH Signed-off-by: Syed Faheemuddin <faheem@nexuslib.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41310 --- Comment #36 from Faheemuddin Syed <faheem@nexuslib.com> --- Created attachment 200102 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=200102&action=edit 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 - Also add IP Address column in the log viewer Test Plan: 1. Apply the patch 2. Update the database `perl installer/data/mysql/updatedatabase.pl` (Or 'updatedatabase' and 'restart_all', if using ktd) 3. Run the tests: `prove t/db_dependent/TestIpLogging.t` - Verify that all tests pass 4. Verification with IP Logging Disabled: - Go to Administration > System preferences > Logs > Debugging - Ensure that 'ActionLogsEnableIPLogging' is set to "Don't" - 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 5. Verification with IP Logging Enabled: - Go to Administration > System preferences > Logs > Debugging - Ensure that 'ActionLogsEnableIPLogging' is set to 'Do' - 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 6. Check IP address column in Log viewer: - Go to Tools > Log viewer - In the Log entries table, click Configure button - Deselect 'ip_address' in 'is hidden by default' checkbox and Save - To be safe, open a new private browser window, log in again, then go to Log viewer again - Verify that the IP address column is displayed 7. Sign off Signed-off-by: Syed Faheemuddin <faheem@nexuslib.com> -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org