From 8e84ebf0cb211c1d94145399b05c9f2ad0e4d4b0 Mon Sep 17 00:00:00 2001
From: Eugen Rochko <eugen@zeonfederated.com>
Date: Sun, 16 Jan 2022 13:23:50 +0100
Subject: Remove IP tracking columns from users table (#16409)

---
 db/migrate/20210616214526_create_user_ips.rb       |  5 +++++
 ...6214135_remove_current_sign_in_ip_from_users.rb | 12 ++++++++++
 db/schema.rb                                       | 24 ++++++++++++++++++--
 db/views/user_ips_v01.sql                          | 26 ++++++++++++++++++++++
 4 files changed, 65 insertions(+), 2 deletions(-)
 create mode 100644 db/migrate/20210616214526_create_user_ips.rb
 create mode 100644 db/post_migrate/20210616214135_remove_current_sign_in_ip_from_users.rb
 create mode 100644 db/views/user_ips_v01.sql

(limited to 'db')

diff --git a/db/migrate/20210616214526_create_user_ips.rb b/db/migrate/20210616214526_create_user_ips.rb
new file mode 100644
index 000000000..68e81a9d8
--- /dev/null
+++ b/db/migrate/20210616214526_create_user_ips.rb
@@ -0,0 +1,5 @@
+class CreateUserIps < ActiveRecord::Migration[6.1]
+  def change
+    create_view :user_ips
+  end
+end
diff --git a/db/post_migrate/20210616214135_remove_current_sign_in_ip_from_users.rb b/db/post_migrate/20210616214135_remove_current_sign_in_ip_from_users.rb
new file mode 100644
index 000000000..b53b247f2
--- /dev/null
+++ b/db/post_migrate/20210616214135_remove_current_sign_in_ip_from_users.rb
@@ -0,0 +1,12 @@
+# frozen_string_literal: true
+
+class RemoveCurrentSignInIpFromUsers < ActiveRecord::Migration[5.2]
+  disable_ddl_transaction!
+
+  def change
+    safety_assured do
+      remove_column :users, :current_sign_in_ip, :inet
+      remove_column :users, :last_sign_in_ip, :inet
+    end
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index a1d169b23..d1446c652 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -923,8 +923,6 @@ ActiveRecord::Schema.define(version: 2021_12_13_040746) do
     t.integer "sign_in_count", default: 0, null: false
     t.datetime "current_sign_in_at"
     t.datetime "last_sign_in_at"
-    t.inet "current_sign_in_ip"
-    t.inet "last_sign_in_ip"
     t.boolean "admin", default: false, null: false
     t.string "confirmation_token"
     t.datetime "confirmed_at"
@@ -1120,6 +1118,28 @@ ActiveRecord::Schema.define(version: 2021_12_13_040746) do
   SQL
   add_index "instances", ["domain"], name: "index_instances_on_domain", unique: true
 
+  create_view "user_ips", sql_definition: <<-SQL
+      SELECT t0.user_id,
+      t0.ip,
+      max(t0.used_at) AS used_at
+     FROM ( SELECT users.id AS user_id,
+              users.sign_up_ip AS ip,
+              users.created_at AS used_at
+             FROM users
+            WHERE (users.sign_up_ip IS NOT NULL)
+          UNION ALL
+           SELECT session_activations.user_id,
+              session_activations.ip,
+              session_activations.updated_at
+             FROM session_activations
+          UNION ALL
+           SELECT login_activities.user_id,
+              login_activities.ip,
+              login_activities.created_at
+             FROM login_activities
+            WHERE (login_activities.success = true)) t0
+    GROUP BY t0.user_id, t0.ip;
+  SQL
   create_view "account_summaries", materialized: true, sql_definition: <<-SQL
       SELECT accounts.id AS account_id,
       mode() WITHIN GROUP (ORDER BY t0.language) AS language,
diff --git a/db/views/user_ips_v01.sql b/db/views/user_ips_v01.sql
new file mode 100644
index 000000000..50a8201cd
--- /dev/null
+++ b/db/views/user_ips_v01.sql
@@ -0,0 +1,26 @@
+SELECT
+  user_id,
+  ip,
+  max(used_at) AS used_at
+FROM (
+  SELECT
+    id AS user_id,
+    sign_up_ip AS ip,
+    created_at AS used_at
+  FROM users
+  WHERE sign_up_ip IS NOT NULL
+  UNION ALL
+  SELECT
+    user_id,
+    ip,
+    updated_at
+  FROM session_activations
+  UNION ALL
+  SELECT
+    user_id,
+    ip,
+    created_at
+  FROM login_activities
+  WHERE success = 't'
+) AS t0
+GROUP BY user_id, ip
-- 
cgit