Bug #39158
closedExclude last_used_at from PersonalAccessToken auditing to prevent taxable_taxonomies bloat
Description
- Problem
Every API authentication via a Personal Access Token triggers an update to `last_used_at` in `PersonalAccessToken`. Since the model is declared as `audited :except => [:token]`, this change is audited — creating one `Audited::Audit` record per authentication.
Each audit record then inherits the user's taxonomy associations (organizations + locations) via `AuditExtensions#set_taxonomies`, creating ~N rows in `taxable_taxonomies` (where N = number of orgs + locations the user belongs to).
On a production instance with active API usage, this leads to massive `taxable_taxonomies` table bloat. Real-world data from an affected instance:
| taxable_type | row_count | % of table |
| --- | --- | --- |
| Audited::Audit (PersonalAccessToken) | 652,262 | 93% |
| All other types combined | ~46,000 | 7% |
25,087 token authentications generated 652,262 `taxable_taxonomies` rows (avg 26 per authentication). This bloat degrades query performance across all taxonomy-scoped models (Hostgroup, Domain, Subnet, etc.), because they share the same `taxable_taxonomies` table for authorization filtering.
- Root cause
In `app/models/personal_access_token.rb`:
```ruby
audited :except => [:token], :associated_with => :user
```
`last_used_at` is not excluded from auditing, so every call to `authenticate_user` (which does `token.update(last_used_at: Time.current.utc)`) generates an audit entry.
- Proposed fix
Add `last_used_at` to the `:except` list:
```ruby
audited :except => [:token, :last_used_at], :associated_with => :user
```
This is a low-risk change — `last_used_at` is a usage-tracking timestamp with no security or compliance value. Auditing "token was used at 14:03:22 instead of 14:03:21" provides no meaningful information.
- Impact
- Prevents ~N `taxable_taxonomies` rows per API authentication (where N = user's org + location count)
- On the affected instance, this would have prevented 93% of `taxable_taxonomies` growth
- Improves query performance for all taxonomy-scoped index pages (hostgroups, domains, subnets, etc.)
- Cleanup for existing instances
After applying the fix, existing bloat can be cleaned up with:
```sql
DELETE FROM taxable_taxonomies
WHERE taxable_type = 'Audited::Audit'
AND taxable_id IN (
SELECT id FROM audits WHERE auditable_type = 'PersonalAccessToken'
);
DELETE FROM audits WHERE auditable_type = 'PersonalAccessToken';
VACUUM ANALYZE taxable_taxonomies;
VACUUM ANALYZE audits;
```
Updated by Dmytro Shuiskyi 4 months ago
Same text, properly formatted:
Problem¶
Every API authentication via a Personal Access Token triggers an update to last_used_at in PersonalAccessToken. Since the model is declared as audited :except => [:token], this change is audited — creating one Audited::Audit record per authentication.
Each audit record then inherits the user's taxonomy associations (organizations + locations) via AuditExtensions#set_taxonomies, creating ~N rows in taxable_taxonomies (where N = number of orgs + locations the user belongs to).
On a production instance with active API usage, this leads to massive taxable_taxonomies table bloat. Real-world data from an affected instance:
| taxable_type | row_count | % of table |
|---|---|---|
| Audited::Audit (PersonalAccessToken) | 652,262 | 93% |
| All other types combined | ~46,000 | 7% |
25,087 token authentications generated 652,262 taxable_taxonomies rows (avg 26 per authentication). This bloat degrades query performance across all taxonomy-scoped models (Hostgroup, Domain, Subnet, etc.), because they share the same taxable_taxonomies table for authorization filtering.
Root cause¶
In app/models/personal_access_token.rb:
audited :except => [:token], :associated_with => :user
last_used_at is not excluded from auditing, so every call to authenticate_user (which does token.update(last_used_at: Time.current.utc)) generates an audit entry.
Proposed fix¶
Add last_used_at to the :except list:
audited :except => [:token, :last_used_at], :associated_with => :user
This is a low-risk change — last_used_at is a usage-tracking timestamp with no security or compliance value. Auditing "token was used at 14:03:22 instead of 14:03:21" provides no meaningful information.
Impact¶
- Prevents ~N
taxable_taxonomiesrows per API authentication (where N = user's org + location count) - On the affected instance, this would have prevented 93% of
taxable_taxonomiesgrowth - Improves query performance for all taxonomy-scoped index pages (hostgroups, domains, subnets, etc.)
Cleanup for existing instances¶
After applying the fix, existing bloat can be cleaned up with:
DELETE FROM taxable_taxonomies
WHERE taxable_type = 'Audited::Audit'
AND taxable_id IN (
SELECT id FROM audits WHERE auditable_type = 'PersonalAccessToken'
);
DELETE FROM audits WHERE auditable_type = 'PersonalAccessToken';
VACUUM ANALYZE taxable_taxonomies;
VACUUM ANALYZE audits;
Updated by The Foreman Bot 4 months ago
- Status changed from New to Ready For Testing
- Pull request https://github.com/theforeman/foreman/pull/10927 added
Updated by Jakub Duchek 3 months ago
- Status changed from Ready For Testing to Closed
Applied in changeset foreman|e9b2e57c720bc8c22473e9134c56ff44360228c0.