feat(regulation): ip bans and unbanning (#7230)

This adds several new features to the regulation functionality including IP banning and the ability to unban users.

Signed-off-by: James Elliott <james-d-elliott@users.noreply.github.com>

2 files changed, 31 insertions, 0 deletions

diff --git a/internal/storage/migrations/postgres/V0020.Regulation.down.sql b/internal/storage/migrations/postgres/V0020.Regulation.down.sql
new file mode 100644
index 000000000..86db3f933
--- /dev/null
+++ b/internal/storage/migrations/postgres/V0020.Regulation.down.sql
@@ -0,0 +1,2 @@
+DROP TABLE IF EXISTS banned_user;
+DROP TABLE IF EXISTS banned_ip;
diff --git a/internal/storage/migrations/postgres/V0020.Regulation.up.sql b/internal/storage/migrations/postgres/V0020.Regulation.up.sql
new file mode 100644
index 000000000..f6b79e19f
--- /dev/null
+++ b/internal/storage/migrations/postgres/V0020.Regulation.up.sql
@@ -0,0 +1,29 @@
+CREATE TABLE IF NOT EXISTS banned_user (
+    id SERIAL CONSTRAINT banned_user_pkey PRIMARY KEY,
+    time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    expires TIMESTAMP WITH TIME ZONE NULL DEFAULT NULL,
+    expired TIMESTAMP WITH TIME ZONE NULL DEFAULT NULL,
+    revoked BOOLEAN NOT NULL DEFAULT FALSE,
+    username VARCHAR(100) NOT NULL,
+    source VARCHAR(10) NOT NULL,
+    reason VARCHAR(100) NULL DEFAULT NULL
+);
+
+CREATE INDEX banned_user_username_idx ON banned_user (username);
+CREATE INDEX banned_user_lookup_idx ON banned_user (username, revoked, expires, expired);
+CREATE INDEX banned_user_list_idx ON banned_user (revoked, expires, expired);
+
+CREATE TABLE IF NOT EXISTS banned_ip (
+    id SERIAL CONSTRAINT banned_ip_pkey PRIMARY KEY,
+    time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    expires TIMESTAMP WITH TIME ZONE NULL DEFAULT NULL,
+    expired TIMESTAMP WITH TIME ZONE NULL DEFAULT NULL,
+    revoked BOOLEAN NOT NULL DEFAULT FALSE,
+    ip VARCHAR(39) NOT NULL,
+    source VARCHAR(10) NOT NULL,
+    reason VARCHAR(100) NULL DEFAULT NULL
+);
+
+CREATE INDEX banned_ip_ip_idx ON banned_ip (ip);
+CREATE INDEX banned_ip_lookup_idx ON banned_ip (ip, revoked, expires, expired);
+CREATE INDEX banned_ip_list_idx ON banned_ip (revoked, expires, expired);