search

mailcow / mailcow-dockerized

mailcow: dockerized - 🐮 + 🐋 = 💕
https://mailcow.email
GNU General Public License v3.0
8.65k stars 1.16k forks source link

provide blocklists/allowlists as list to enable firewalls to take on blocking #4678

Open LU-386 opened 2 years ago

LU-386 commented 2 years ago

Summary

I've got pretty many mail from the watchdog about blocked ip's recently, I add those ip's to a blocklist on my firewall. I would love to add a list of those blocked ip's to my firewall to allow blocking at network level.

This would only need a published file/page from mailcow with text/plain content which includes the ip's like in a blocklist from spamhaus, for example "https://www.spamhaus.org/drop/drop.txt"

Motivation

allow advanced security for hole networks and keep load off of the mailcow instance

Additional context

No response

LU-386 commented 1 year ago

We used the open-source middleware n8n to create a blocklist temporary.

If someone is interested in this as well, reply here I will publish the workflow

Tundra-bit commented 1 year ago

Hey LU-386, I'm interested in this, Thanks

LU-386 commented 1 year ago

hey @Tundra-bit, I've put the mailcow-blocklist.json to import into n8n below.

The current version listens for the notification-mails, parses them and writes them into a mssql-database. It should be pretty easy to migrate to mysql/mariadb or to consume a rest-api with that information.

To use the workflow, just import it and add the credentials in the e-mail-node and the mssql-nodes.

I will probably put this into a git-repo anytime soon. Just tell me if you need any further help!

{
  "name": "mailcow-blocklist",
  "nodes": [
    {
      "parameters": {},
      "id": "38457dfd-bd6b-42be-b1ed-861011fad1d9",
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -340,
        620
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=INSERT INTO mailcow_blocked_ips (ip, dt) VALUES('{{$json[\"ip\"]}}', '{{$json[\"date_formatted\"]}}');"
      },
      "id": "22762bfe-91a3-4874-89ad-c6c1b48fddfb",
      "name": "write ips to table",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        380,
        280
      ],
      "credentials": {
        "microsoftSql": {
          "id": "3",
          "name": "mssql"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "select ip from mailcow_blocked_ips;"
      },
      "id": "20d0d37c-30dd-4869-a624-c3e0dc6621a4",
      "name": "read ips from table",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        -160,
        440
      ],
      "alwaysOutputData": true,
      "credentials": {
        "microsoftSql": {
          "id": "3",
          "name": "mssql"
        }
      },
      "continueOnFail": true
    },
    {
      "parameters": {
        "functionCode": "// Code here will run once per input item.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.functionItem\n// Tip: You can use luxon for dates and $jmespath for querying JSON structures\n\n// Add a new field called 'myNewField' to the JSON of the item\nvar obj = {}\n\nobj.date = item.date\nobj.ip = item.subject.match(/([0-9]{1,3}\\.){3}[0-9]{1,3}(\\/([0-9]|[1-2][0-9]|3[0-2]))?($| )/igm)[0]\n\nreturn obj;"
      },
      "id": "8450eb21-4512-48a2-87de-787ac9755736",
      "name": "get only ips",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        20,
        280
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"subject\"]}}",
              "operation": "contains",
              "value2": "- IP ban:"
            }
          ]
        }
      },
      "id": "808b6527-45db-4ee9-b330-4d1ace8b7127",
      "name": "if mail from mailcow",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        -160,
        280
      ]
    },
    {
      "parameters": {
        "postProcessAction": "nothing",
        "options": {}
      },
      "id": "6db75903-dfdf-4d6e-8845-dfcb8ac3828b",
      "name": "get mails",
      "type": "n8n-nodes-base.emailReadImap",
      "typeVersion": 1,
      "position": [
        -340,
        280
      ],
      "credentials": {
        "imap": {
          "id": "2",
          "name": "receiver@example.com"
        }
      }
    },
    {
      "parameters": {
        "value": "={{ $json[\"date\"] }}",
        "dataPropertyName": "date_formatted",
        "custom": true,
        "toFormat": "YYYY-MM-DD HH:MM:SS",
        "options": {
          "toTimezone": "Europe/Berlin"
        }
      },
      "id": "ff780583-41e3-4d63-af5f-c32136c6e9cf",
      "name": "Date & Time",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        200,
        280
      ]
    },
    {
      "parameters": {
        "path": "ec20c713-598b-4b76-90df-200ecac1d69a",
        "responseMode": "responseNode",
        "options": {
          "rawBody": true,
          "responseHeaders": {
            "entries": [
              {
                "name": "Content-Type",
                "value": "text/plain"
              }
            ]
          }
        }
      },
      "id": "9580215e-589f-40b1-93e2-caa51bbbd242",
      "name": "Webhook",
      "type": "n8n-nodes-base.webhook",
      "typeVersion": 1,
      "position": [
        -340,
        440
      ],
      "webhookId": "ec20c713-598b-4b76-90df-200ecac1d69a",
      "alwaysOutputData": false
    },
    {
      "parameters": {
        "respondWith": "text",
        "responseBody": "={{$json[\"str\"]}}",
        "options": {
          "responseCode": 200,
          "responseHeaders": {
            "entries": [
              {
                "name": "Content-Type",
                "value": "text/plain"
              }
            ]
          }
        }
      },
      "id": "993f4e07-af3d-4d70-8872-54f2324ecbfc",
      "name": "Respond to Webhook",
      "type": "n8n-nodes-base.respondToWebhook",
      "typeVersion": 1,
      "position": [
        200,
        440
      ],
      "alwaysOutputData": false
    },
    {
      "parameters": {
        "functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n// Tip: You can use luxon for dates and $jmespath for querying JSON structures\n\n// Loop over inputs and add a new field called 'myNewField' to the JSON of each one\nvar str = \"\"\n\nif( items.length > 0 ) {\n \n  for (item of items) {\n    ip = item.json.ip.trim()\n    if(str.indexOf(ip) == -1) {\n      str += ip + \"\\n\";\n    }\n  }\n\n}\n\nreturn { str };"
      },
      "id": "d0686b42-1656-481c-a8e8-472b35885731",
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        20,
        440
      ],
      "alwaysOutputData": true,
      "continueOnFail": true
    },
    {
      "parameters": {
        "content": "## Code for SQL-Table creation\n\nSQL-Server 2022\n```\nIF object_id('master.dbo.mailcow_blocked_ips', 'U') is null\n\tCREATE TABLE master.dbo.mailcow_blocked_ips (\n\t\t[id] int not null identity,\n\t\t[ip] nvarchar(255),\n\t\t[dt] datetime,\n\t\tPRIMARY KEY( [id] )\n\t);\n```\n\nPostgres\n```\nCREATE TABLE IF NOT EXISTS \"mailcow_blocked_ips\" (\n\t\"id\" serial,\n\t\"ip\" text,\n\t\"dt\" timestamp,\n\tPRIMARY KEY( id )\n);\n```",
        "height": 337.74905422446403,
        "width": 640
      },
      "id": "b9e95983-f1a6-4c8b-b590-841b8af3f880",
      "name": "Note",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        -160,
        620
      ]
    }
  ],
  "pinData": {},
  "connections": {
    "get only ips": {
      "main": [
        [
          {
            "node": "Date & Time",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "if mail from mailcow": {
      "main": [
        [
          {
            "node": "get only ips",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "get mails": {
      "main": [
        [
          {
            "node": "if mail from mailcow",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Date & Time": {
      "main": [
        [
          {
            "node": "write ips to table",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "read ips from table": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Respond to Webhook",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Webhook": {
      "main": [
        [
          {
            "node": "read ips from table",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": true,
  "settings": {},
  "id": 4,
  "meta": {
    "instanceId": "3ff46003c30f3c31f9f344ceae3287321be4403471dba3850704bd55558cce69"
  },
  "tags": []
}
Tundra-bit commented 1 year ago

@LU-386

Thanks!!