Project

General

Profile

Actions

Bug #39158

closed

Exclude last_used_at from PersonalAccessToken auditing to prevent taxable_taxonomies bloat

Added by Dmytro Shuiskyi 5 months ago. Updated 4 months ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
-
Target version:
-
Difficulty:
Triaged:
No
Fixed in Releases:
Found in Releases:

Description

  1. 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.

  1. 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.

  1. 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.

  1. 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.)

  1. 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;
```

Actions

Also available in: Atom PDF