Clean up user_auth_token_logs?

RGJ · September 13, 2024, 5:30pm

We have a forum where user_auth_token_logs has 61 million rows (and growing).

There are only 25k user_auth_tokens.

Of the 61 million rows, 54 million rows refer to an user_auth_token that does not exist anymore (i.e. a database integrity issue). And of the 61 million rows, around 58 million are older than 2 months (i.e. seemingly useless?)

Questions:

Could we just clean this up without risking further integrity issues?
Would it be an idea to have a job clean this up automatically?

db=# select count(*) from user_auth_tokens;
 count 
-------
 25648

db=# select count(*) from user_auth_token_logs;
  count   
----------
 61415352

db=# select count(*) from user_auth_token_logs where user_auth_token_id not in (select id from user_auth_tokens);
  count   
----------
 54558442

db=# select count(*) from user_auth_token_logs where created_at < '2024-07-13';
  count   
----------
 58565943

pmusaraj · September 13, 2024, 6:22pm

Yes, user_auth_token_logs are there only for debugging purposes. All rows can be emptied, the only consequence will be that you won’t have any logs to debug.

This should be covered by:

github.com

discourse/discourse/blob/main/app/jobs/scheduled/weekly.rb#L13


      
          module Jobs
            # This job will run on a regular basis to update statistics and denormalized data.
            # If it does not run, the site will not function properly.
            class Weekly < ::Jobs::Scheduled
              every 1.week
          
              def execute(args)
                ScoreCalculator.new.calculate
                MiniScheduler::Stat.purge_old
                Draft.cleanup!
                UserAuthToken.cleanup!
                Email::Cleaner.delete_rejected!
                Notification.purge_old!
                Bookmark.cleanup!
              end
            end
          end

RGJ · September 13, 2024, 7:07pm

Thank you for that, I did not realize that was there.

So… the cleanup only runs when verbose_auth_token_logging is true (which is not the case on this instance)

but non-verbose logging always happens regardless of the setting

github.com

discourse/discourse/blob/main/app/models/user_auth_token.rb#L29-L35


      
          def self.log(info)
            UserAuthTokenLog.create!(info)
          end
          
          def self.log_verbose(info)
            log(info) if SiteSetting.verbose_auth_token_logging
          end

per 8fb823c

Moving this to bug

pmusaraj · September 13, 2024, 7:45pm

Ah yes, good catch. Looks like lines 214 to 217 need fixing as well.

I would be comfortable with a global cleanup after a certain timeframe. @osama (since you’re the author of the commit linked above), do you think we can clean up all of these logs after some time (and if so, after how long)? It sounds like we need to keep some of them for detecting suspicious logins.

RGJ · September 13, 2024, 10:16pm

Why does it need fixing? That piece of code is about cleaning up rotated UserAuthTokens, not about the log records?

Update: after enabling SiteSetting.verbose_auth_token_logging, triggering the weekly job and running VACUUM FULL user_auth_token_logs the table went from 16GB to 687MB

Saved some trees today

Osama · September 16, 2024, 8:15pm

Yes, I think we can clean up most of the logs, but some of them have to stay. Specifically, I think any records that have suspicious, generate, or rotate for action will need to be kept around because they’re used for detecting and generating reports for suspicious logins.

github.com

discourse/discourse/blob/501f07ab1fceafcfa56bbfc074951d03d7d30d22/app/models/concerns/reports/suspicious_logins.rb#L38


      
              title: I18n.t("reports.suspicious_logins.labels.login_time"),
            },
          ]
          
          report.data = []
          
          sql = <<~SQL
            SELECT u.id user_id, u.username, u.uploaded_avatar_id, t.client_ip, t.user_agent, t.created_at login_time
            FROM user_auth_token_logs t
            JOIN users u ON u.id = t.user_id
            WHERE t.action = 'suspicious'
              AND t.created_at >= :start_date
              AND t.created_at <= :end_date
            ORDER BY t.created_at DESC
          SQL
          
          DB
            .query(sql, start_date: report.start_date, end_date: report.end_date)
            .each do |row|
              data = {}

github.com

discourse/discourse/blob/501f07ab1fceafcfa56bbfc074951d03d7d30d22/app/models/user_auth_token.rb#L61


      
              Math.sin((lat2_rad - lat1_rad) / 2)**2 +
                Math.cos(lat1_rad) * Math.cos(lat2_rad) * Math.sin((lon2_rad - lon1_rad) / 2)**2
            c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a))
          
            c * EARTH_RADIUS_KM
          end
          
          def self.is_suspicious(user_id, user_ip)
            return false unless User.find_by(id: user_id)&.staff?
          
            ips = UserAuthTokenLog.where(user_id: user_id).pluck(:client_ip)
            ips.delete_at(ips.index(user_ip) || ips.length) # delete one occurrence (current)
            ips.uniq!
            return false if ips.empty? # first login is never suspicious
          
            if user_location = login_location(user_ip)
              ips.none? do |ip|
                if location = login_location(ip)
                  distance(user_location, location) < SiteSetting.max_suspicious_distance_km
                end
              end