Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-93033

Increase the character limit for the clientIP column in the AUDIT_AUDITEVENT table from 75 to 255

    Details

      Description

      *Description: *
      Our built-in Audit Portlet can be used to track user logon events.
      Customers reported, that if a user is connecting to the Liferay server through many network hops/proxies, then the AUDIT_AUDITEVENT table's CLIENTIP column can no longer hold the IP address values in the Liferay database, because it is limited to 75 characters. They contacted Liferay Support to have the AUDIT_AUDITEVENT table's CLIENTIP column expanded.

      The CLIENTIP is the X_FORWARDED_FOR attribute from the request's httpheader.
      According to this article, the X_FORWARDED_FOR attribute basically contains the path from the client's computer through all proxies and loadbalancers to the server:
      https://en.wikipedia.org/wiki/X-Forwarded-For

      Therefore it can contain more ip addresses when there are more proxies, so the 75 character is sometimes not enough:
      https://github.com/liferay/liferay-portal-ee/blob/7.1.x/modules/apps/portal-security-audit/portal-security-audit-storage-service/src/main/resources/META-INF/sql/tables.sql#L12

      Reproduction Steps:
      (tested on: today's master: 5031d8ccf3ba2c39134c60abc6c2cea84d6c3f66)

      1. Start the Liferay Portal with a MySQL 5.7 db and log on as the administrator (with the user: "test")
      2. Go to Control Panel > Configuration > System Settings > Audit > Logging Message Audit Message Processor and click: "Enabled"
      3. Log out of the portal
      4. Connect to the portal with an Eclipse debugger
      5. Set up a breakpoint at:
      LoggingAuditMessageProcessor.java line 88 where the auditMessage is processed
      6. Log on again as administrator
      7. When the debugger catches the breakpoint, right-click on the _clientIP variable within Eclipse and click "change value", and enter this value:
      127.0.0.1, 82.141.143.146, 82.141.143.146, 2.16.60.108, 104.108.71.29, 10.63.128.153, 10.63.128.154
      Note: if the debugger is not catching the breakpoint, try another audit event scenario like changing a portal user's password
      8. Press F8 so the server can continue running

      Result:
      The console will show the following error message:

      2019-03-29 15:04:45.067 ERROR [liferay/audit-1][JDBCExceptionReporter:234] Data truncation: Data too long for column 'clientIP' at row 1
      2019-03-29 15:04:45.069 FATAL [liferay/audit-1][PersistentAuditMessageProcessor:49] Unable to process audit message com.liferay.portal.kernel.audit.AuditMessage@3289ae25
      org.hibernate.exception.DataException: Could not execute JDBC batch update
      

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  24 weeks, 3 days ago

                  Packages

                  Version Package
                  7.1.X
                  7.2.X
                  Master