qABsBnxk

1
2CREATE TABLE IF NOT EXISTS "wtdwtf_real_ip" (
3        "ip" INET NOT NULL UNIQUE,
4        "hash" BYTEA NOT NULL PRIMARY KEY CHECK(OCTET_LENGTH("hash") = 20)
5);
6
7CREATE EXTENSION IF NOT EXISTS pgcrypto;
8
9CREATE TEMPORARY TABLE "secret" ( "s" TEXT );
10INSERT INTO "secret" ("s") VALUES (:'secret');
11
12CREATE TEMPORARY TABLE "recent_ips" (
13        "zhash" BYTEA
14);
15INSERT INTO "recent_ips"
16SELECT DECODE(z."value", 'hex') "zhash"
17  FROM "legacy_object_live" o
18 INNER JOIN "legacy_zset" z
19    ON o."_key" = z."_key"
20   AND o."type" = z."type"
21 WHERE z."_key" = 'ip:recent'
22   AND z."value" SIMILAR TO '[0-9a-f]{40}';
23
24DO $$
25DECLARE
26        secret TEXT;
27        inc BIGINT;
28        addr INET;
29        hash BYTEA;
30BEGIN
31
32SELECT "s" INTO secret FROM "secret";
33
34LOOP
35
36        addr := inet '0.0.0.0' + inc;
37        hash := DIGEST(CONVERT_TO(addr::TEXT || secret, 'SQL_ASCII'), 'sha1');
38
39        IF EXISTS(SELECT 1 FROM "recent_ips" WHERE "zhash" = hash) THEN
40                INSERT INTO "wtdwtf_real_ip" ("ip", "hash") VALUES (addr, hash) ON CONFLICT DO NOTHING;
41        END IF;
42
43        inc := inc + 1;
44        EXIT WHEN inc = 2^32;
45
46END LOOP;
47
48END;
49$$ LANGUAGE plpgsql;
50
51CLUSTER VERBOSE "wtdwtf_real_ip" USING "wtdwtf_real_ip_pkey";