From: Tom Hughes Date: Sun, 29 Oct 2023 15:58:03 +0000 (+0000) Subject: Add support for per-user limits on the rate changes can be made X-Git-Tag: live~421^2~3 X-Git-Url: https://git.openstreetmap.org/rails.git/commitdiff_plain/2f11b77309ee18aafb1bcce9dbe5bfc38a298bbb Add support for per-user limits on the rate changes can be made --- diff --git a/config/settings.yml b/config/settings.yml index cffd3bd31..87c467c88 100644 --- a/config/settings.yml +++ b/config/settings.yml @@ -62,6 +62,13 @@ min_changeset_comments_per_hour: 1 initial_changeset_comments_per_hour: 6 max_changeset_comments_per_hour: 60 moderator_changeset_comments_per_hour: 36000 +# Rate limit for changes +min_changes_per_hour: 100 +initial_changes_per_hour: 1000 +max_changes_per_hour: 100000 +days_to_max_changes: 7 +importer_changes_per_hour: 1000000 +moderator_changes_per_hour: 1000000 # Domain for handling message replies #messages_domain: "messages.openstreetmap.org" # MaxMind GeoIPv2 database diff --git a/db/migrate/20231101222146_api_rate_limit.rb b/db/migrate/20231101222146_api_rate_limit.rb new file mode 100644 index 000000000..9790629ee --- /dev/null +++ b/db/migrate/20231101222146_api_rate_limit.rb @@ -0,0 +1,13 @@ +class ApiRateLimit < ActiveRecord::Migration[7.1] + def up + safety_assured do + execute DatabaseFunctions::API_RATE_LIMIT + end + end + + def down + safety_assured do + execute "DROP FUNCTION api_rate_limit(bigint)" + end + end +end diff --git a/db/structure.sql b/db/structure.sql index f7f329326..56e778523 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -108,6 +108,67 @@ CREATE TYPE public.user_status_enum AS ENUM ( 'deleted' ); + +-- +-- Name: api_rate_limit(bigint); Type: FUNCTION; Schema: public; Owner: - +-- + +CREATE FUNCTION public.api_rate_limit(user_id bigint) RETURNS integer + LANGUAGE plpgsql STABLE + AS $$ + DECLARE + min_changes_per_hour int4 := 100; + initial_changes_per_hour int4 := 1000; + max_changes_per_hour int4 := 100000; + days_to_max_changes int4 := 7; + importer_changes_per_hour int4 := 1000000; + moderator_changes_per_hour int4 := 1000000; + 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 + SELECT ARRAY_AGG(user_roles.role) INTO STRICT roles FROM user_roles WHERE user_roles.user_id = api_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 = api_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 = api_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 = api_rate_limit.user_id ORDER BY changesets.created_at LIMIT 1; + END IF; + + IF NOT FOUND THEN + first_change := CURRENT_TIMESTAMP AT TIME ZONE 'UTC'; + END IF; + + SELECT COUNT(*) INTO STRICT active_reports + FROM issues INNER JOIN reports ON reports.issue_id = issues.id + WHERE issues.reported_user_id = api_rate_limit.user_id AND issues.status = 'open' AND reports.updated_at >= COALESCE(issues.resolved_at, '1970-01-01'); + + time_since_first_change := EXTRACT(EPOCH FROM CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - first_change); + + max_changes := max_changes_per_hour * POWER(time_since_first_change, 2) / POWER(days_to_max_changes * 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 = api_rate_limit.user_id AND changesets.created_at >= CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - '1 hour'::interval; + + RETURN max_changes - recent_changes; + END; + $$; + + SET default_tablespace = ''; SET default_table_access_method = heap; @@ -3438,6 +3499,7 @@ INSERT INTO "schema_migrations" (version) VALUES ('23'), ('22'), ('21'), +('20231101222146'), ('20231029151516'), ('20231010194809'), ('20231007141103'), diff --git a/lib/database_functions.rb b/lib/database_functions.rb new file mode 100644 index 000000000..f9e09ac70 --- /dev/null +++ b/lib/database_functions.rb @@ -0,0 +1,58 @@ +module DatabaseFunctions + API_RATE_LIMIT = %( + CREATE OR REPLACE FUNCTION api_rate_limit(user_id int8) + RETURNS int4 + AS $$ + DECLARE + min_changes_per_hour int4 := #{Settings.min_changes_per_hour}; + initial_changes_per_hour int4 := #{Settings.initial_changes_per_hour}; + max_changes_per_hour int4 := #{Settings.max_changes_per_hour}; + days_to_max_changes int4 := #{Settings.days_to_max_changes}; + importer_changes_per_hour int4 := #{Settings.importer_changes_per_hour}; + moderator_changes_per_hour int4 := #{Settings.moderator_changes_per_hour}; + 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 + SELECT ARRAY_AGG(user_roles.role) INTO STRICT roles FROM user_roles WHERE user_roles.user_id = api_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 = api_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 = api_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 = api_rate_limit.user_id ORDER BY changesets.created_at LIMIT 1; + END IF; + + IF NOT FOUND THEN + first_change := CURRENT_TIMESTAMP AT TIME ZONE 'UTC'; + END IF; + + SELECT COUNT(*) INTO STRICT active_reports + FROM issues INNER JOIN reports ON reports.issue_id = issues.id + WHERE issues.reported_user_id = api_rate_limit.user_id AND issues.status = 'open' AND reports.updated_at >= COALESCE(issues.resolved_at, '1970-01-01'); + + time_since_first_change := EXTRACT(EPOCH FROM CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - first_change); + + max_changes := max_changes_per_hour * POWER(time_since_first_change, 2) / POWER(days_to_max_changes * 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 = api_rate_limit.user_id AND changesets.created_at >= CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - '1 hour'::interval; + + RETURN max_changes - recent_changes; + END; + $$ LANGUAGE plpgsql STABLE; + ).freeze +end diff --git a/lib/tasks/update_functions.rake b/lib/tasks/update_functions.rake new file mode 100644 index 000000000..605d3c9ad --- /dev/null +++ b/lib/tasks/update_functions.rake @@ -0,0 +1,6 @@ +namespace :db do + desc "Update database function definitions" + task :update_functions => :environment do + ActiveRecord::Base.connection.execute DatabaseFunctions::API_RATE_LIMIT + end +end