Audit Table column length

[Tom Martin]

Hi folks,

We're recently implemented PWM using the default schema for mySQL (RDS), and one of our test accounts hit the character limit for USER_AUDIT/value, which is currently of type TEXT.  It looks like each password change event writes logs to this field, and it runs out of space at roughly 136 events.  In this scenario, the test account is effectively bricked.

I was wondering if anyone has ever encountered this, and what is the recommended solution?  I've considered increasing the column type to MEDIUMTEXT, which has a 16MB limit vs the 64k limit of TEXT fields.  Before doing this, I would like to understand if there are any side effects of other implications worth considering?  Or perhaps, there is some better way of managing the audit log growth?

As of right now, the issue only affects this one test user, but this raised concern as we require periodic password changes, and 136 events seems like a relatively low ceiling.

Thanks in advance,

--Tom

[Jason Rivard]

The USER_AUDIT table stores only recent history for the user, which is shown in the user's account info page or to the helpdesk.  By default there is only 20 records stored there set with the setting: 'Settings ⇨ User History ⇨ User History Maximum Events'.  I suspect you increased this value else there may be a bug.

There is a more useful audit record stored per server in the LocalDB, that can be viewed at admin -> user activity -> audit.  This is a per server record storage.

For true audit records, you will need to provide your own audit record server that accepts events via syslog.  See 'Settings ⇨ Auditing ⇨ Audit Forwarding'.

[Jason Everling]

We forward to our SIEM, but we also still had issues many years ago, we changed the ‘value’ column to ‘longtext’ type and no issues since.

--
You received this message because you are subscribed to the Google Groups "pwm-general" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pwm-general...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pwm-general/90f0b30a-6e9b-4b30-8959-934bf4809b91n%40googlegroups.com.

 

[Tom Martin]

Thanks Jason, we didn't change the default value.  It's still set to 20, so maybe it's a bug? (screenshot attached)

From your perspective, does increasing the column type to MEDIUMTEXT or LONGTEXT seem like an effective workaround? 

Or would you recommend waiting for a code fix?  The column change seemed to work for Jason E (thanks Jason E), but I wanted to get your thoughts on that as well.

Naturally, I understand this is open source software and you are donating your time out of the goodness of your heart, so I certainly don't expect every minor bug to get zapped.

Just trying to make an informed decision.

Also, I appreciate the hard work! :)

Thanks,

--Tom

[Jason Everling]

Jason R. can respond about bug, I forgot to you need to change settings in PWM to match what you change column type to, you can see my previous response back in April about same issue from someone else

 

https://groups.google.com/g/pwm-general/c/Xcn6oNfVrJU/m/B64bra97BQAJ

 

 

From: Tom Martin
Sent: Tuesday, January 12, 2021 3:12 PM
To: pwm-general
Subject: [pwm-general] Re: Audit Table column length

 

--

You received this message because you are subscribed to the Google Groups "pwm-general" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pwm-general...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/pwm-general/b7369dae-4225-4305-883e-4e895e11c438n%40googlegroups.com.

 

[Jason Everling]

I also don’t think it has anything to do with amount of audit entries, it has to do with the “value” column, it stores everything you select under “User Audit Event Types” in json

[Tom Martin]

I appreciate that advice, Jason E.  Thank you for that.

And to your last point, the value column for that user is one big json record with 136 entries, totally about 64k in overall size (the max size for the column).