Bulk Exports

{
  "download_url": "https://...",
  "expires_in_seconds": 3600,
  "format": "mmdb",
  "artifact": "ranges"
}

#!/usr/bin/env bash
# wxa-vpn-refresh-mmdb: refresh one MMDB layer.
# Usage: wxa-vpn-refresh-mmdb ranges|points   (reads WXA_API_KEY)
set -euo pipefail

DIR=/var/lib/wxa-vpn
API=https://vpn-proxy-detection.whoisxmlapi.com/api/v1/export/mmdb

case "${1:-}" in
  ranges) DEST="$DIR/wxa_vpn.mmdb" ;;
  points) DEST="$DIR/wxa_vpn-points.mmdb" ;;
  *) echo "usage: $0 ranges|points" >&2; exit 2 ;;
esac
: "${WXA_API_KEY:?is not set}"

# 1. Fetch the JSON. -f turns an HTTP error (401, 403, 503) into a failure.
json=$(curl -fsS --max-time 60 "$API?artifact=$1&apiKey=$WXA_API_KEY")
url=$(printf '%s' "$json" | jq -r '.download_url // empty') || url=
if [ -z "$url" ]; then
  echo "no download_url in the response: ${json:0:300}" >&2
  exit 1
fi

# 2. Download the file to a temp file next to the live one, so the rename in
#    step 4 stays on one filesystem and is atomic. --speed-time gives up on a
#    transfer that stalls (under 1 KB/s for five minutes).
tmp=$(mktemp "$DEST.tmp.XXXXXX")
trap 'rm -f "$tmp"' EXIT
curl -fsS --speed-limit 1024 --speed-time 300 -o "$tmp" "$url"

# 3. Check that it is an MMDB: within its last 128 KiB, every MaxMind DB file
#    has the 14-byte marker that starts its metadata. An error page does not.
if ! LC_ALL=C grep -a -F -q $'\xab\xcd\xefMaxMind.com' <(tail -c 131072 "$tmp"); then
  echo "download is not an MMDB; keeping $DEST" >&2
  exit 1
fi

# 4. Swap it in. mktemp makes the file readable by its owner only.
chmod 644 "$tmp"
mv "$tmp" "$DEST"

# crontab -e: check twice a day
WXA_API_KEY=YOUR_API_KEY
0 0,12 * * * /usr/local/bin/wxa-vpn-refresh-mmdb ranges
5 0,12 * * * /usr/local/bin/wxa-vpn-refresh-mmdb points

import maxminddb

points = maxminddb.open_database("/var/lib/wxa-vpn/wxa_vpn-points.mmdb")
ranges = maxminddb.open_database("/var/lib/wxa-vpn/wxa_vpn.mmdb")

def lookup(ip):
    return points.get(ip) or ranges.get(ip)

data = lookup("1.1.1.1")
# {"classification": "cdn", "provider": "cloudflare", ...}

-- Each file is the full dataset, so this loads it into a fresh table, which
-- stays empty until the COPY finishes. COPY GRANTS keeps the grants made on
-- the table it replaces.
CREATE OR REPLACE TABLE wxa_vpn_ranges (
  network        VARCHAR,
  classification VARCHAR,
  provider       VARCHAR,
  service        VARCHAR,
  confidence     DOUBLE,
  source         VARCHAR,
  first_seen     TIMESTAMP_TZ,
  last_seen      TIMESTAMP_TZ,
  metadata       VARCHAR,
  netblock       VARCHAR,
  netname        VARCHAR,
  netblock_org   VARCHAR
) COPY GRANTS;

COPY INTO wxa_vpn_ranges (network, classification, provider, service, confidence, source,
                          first_seen, last_seen, metadata, netblock, netname, netblock_org)
FROM (
  SELECT $1:network::VARCHAR, $1:classification::VARCHAR, $1:provider::VARCHAR,
         $1:service::VARCHAR, $1:confidence::DOUBLE, $1:source::VARCHAR,
         $1:first_seen::TIMESTAMP_TZ, $1:last_seen::TIMESTAMP_TZ,
         $1:metadata::VARCHAR, $1:netblock::VARCHAR, $1:netname::VARCHAR,
         $1:netblock_org::VARCHAR
  FROM @my_s3_stage/wxa_vpn_ranges.parquet
)
FILE_FORMAT = (TYPE = PARQUET USE_LOGICAL_TYPE = TRUE);