[openstreetmap/openstreetmap-website] Add the ability to rate limit edits (PR #4319)

[Tom Hughes]

Ah I misread the doco last night and I was testing on a user with a block. Just removing `STRICT` from that query should fix it. The next query probably needs a fix to handle users with no changesets as well... Try this one:

```sql
CREATE OR REPLACE FUNCTION check_rate_limit(user_id int8, new_changes int4)
  RETURNS boolean
  AS $$
DECLARE
  min_changes_per_hour int4;
  initial_changes_per_hour int4;
  max_changes_per_hour int4;
  importer_changes_per_hour int4;
  moderator_changes_per_hour int4;
  roles text[];
  last_block timestamp without time zone;
  first_change timestamp without time zone;
  active_reports int4;
  time_since_first_change double precision;
  max_changes double precision;
  recent_changes int4;
BEGIN
  min_changes_per_hour := 100;
  initial_changes_per_hour := 1000;
  max_changes_per_hour := 100000;
  importer_changes_per_hour := 1000000;
  moderator_changes_per_hour := 1000000;

  SELECT ARRAY_AGG(user_roles.role) INTO STRICT roles FROM user_roles WHERE user_roles.user_id = check_rate_limit.user_id;

  IF 'moderator' = ANY(roles) THEN
    max_changes := moderator_changes_per_hour;
  ELSIF 'importer' = ANY(roles) THEN
    max_changes := importer_changes_per_hour;
  ELSE
    SELECT user_blocks.created_at INTO last_block FROM user_blocks WHERE user_blocks.user_id = check_rate_limit.user_id ORDER BY user_blocks.created_at DESC LIMIT 1;

    IF FOUND THEN
      SELECT changesets.created_at INTO first_change FROM changesets WHERE changesets.user_id = check_rate_limit.user_id AND changesets.created_at > last_block ORDER BY changesets.created_at LIMIT 1;
    ELSE
      SELECT changesets.created_at INTO first_change FROM changesets WHERE changesets.user_id = check_rate_limit.user_id ORDER BY changesets.created_at LIMIT 1;
    END IF;

    IF NOT FOUND THEN
      first_change := NOW();
    END IF;

    SELECT COALESCE(SUM(issues.reports_count), 0) INTO STRICT active_reports FROM issues WHERE issues.reported_user_id = check_rate_limit.user_id AND issues.status = 'open';

    time_since_first_change := EXTRACT(EPOCH FROM NOW() - first_change);

    max_changes := max_changes_per_hour * POWER(time_since_first_change, 2) / POWER(7 * 24 * 60 * 60, 2);
    max_changes := GREATEST(initial_changes_per_hour, LEAST(max_changes_per_hour, FLOOR(max_changes)));
    max_changes := max_changes / POWER(2, active_reports);
    max_changes := GREATEST(min_changes_per_hour, LEAST(max_changes_per_hour, max_changes));
  END IF;

  SELECT COALESCE(SUM(changesets.num_changes), 0) INTO STRICT recent_changes FROM changesets WHERE changesets.user_id = check_rate_limit.user_id AND changesets.created_at >= now() - '1 hour'::interval;

  RETURN recent_changes + new_changes < max_changes;
END;
$$ LANGUAGE plpgsql STABLE;
```

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/pull/4319#issuecomment-1788734065
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/openstreetmap-website/pull/4319/c1788734065 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20231101/799a7387/attachment-0001.htm>