Project

General

Profile

Actions

Bug #39158

closed

Exclude last_used_at from PersonalAccessToken auditing to prevent taxable_taxonomies bloat

Added by Dmytro Shuiskyi 4 months ago. Updated 3 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 #1

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_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:

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 #2

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
Actions #3

Updated by The Foreman Bot 3 months ago

  • Fixed in Releases 3.19.0 added
Actions #4

Updated by Jakub Duchek 3 months ago

  • Status changed from Ready For Testing to Closed
Actions

Also available in: Atom PDF