a093f8787c
Implements comprehensive IP logging for webhook requests to enable IP whitelisting and security monitoring. Features: - Capture source IP from webhook requests (x-forwarded-for, x-real-ip) - Capture user agent for identification - Store in webhook_logs table - New API endpoint: GET /api/webhooks/ips - View unique IPs with request counts and statistics - Identify Autotask IPs for whitelisting Database Changes: - Added source_ip column (VARCHAR 45) to webhook_logs - Added user_agent column (TEXT) to webhook_logs - Added index on source_ip for efficient queries - Migration 005 for existing installations API Endpoints: - GET /api/webhooks/ips?hours=168&entityType=Tickets Returns unique IPs with: * Request counts (total, successful, failed) * First/last seen timestamps * Entity types accessed * User agent strings Use Cases: 1. Identify Autotask webhook IPs 2. Configure IP whitelist in nginx/Pangolin/Cloudflare 3. Monitor for unauthorized webhook attempts 4. Audit webhook sources 5. Detect IP changes from Autotask Security Benefits: - Enable IP whitelisting for webhook endpoint - Block unauthorized webhook attempts - Monitor for suspicious activity - Audit trail of webhook sources Documentation: - Complete IP whitelisting guide (WEBHOOK_IP_WHITELISTING.md) - Configuration examples for nginx, Pangolin, Cloudflare - Monitoring queries and best practices - Troubleshooting guide Files Modified: - migrations/004_webhook_support.sql - Added IP columns - migrations/005_add_webhook_ip_logging.sql - Migration for existing installs - lib/types/webhook.ts - Added IP fields to WebhookLog - lib/services/webhook-service.ts - Capture and log IPs - app/api/webhooks/autotask/route.ts - Extract IP from headers - app/api/webhooks/ips/route.ts - New IP viewing endpoint - docs/WEBHOOK_IP_WHITELISTING.md - Complete guide Next Steps: 1. Run migration (004 for new, 005 for existing) 2. Deploy updated code 3. Receive webhooks from Autotask 4. View IPs via /api/webhooks/ips 5. Configure IP whitelist in proxy/tunnel
35 lines
1.2 KiB
SQL
35 lines
1.2 KiB
SQL
-- Migration: Add IP logging to webhook_logs
|
|
-- Description: Add source_ip and user_agent columns for IP whitelisting
|
|
|
|
-- Add source_ip column if it doesn't exist
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'webhook_logs' AND column_name = 'source_ip'
|
|
) THEN
|
|
ALTER TABLE webhook_logs ADD COLUMN source_ip VARCHAR(45);
|
|
COMMENT ON COLUMN webhook_logs.source_ip IS 'Source IP address of webhook request (IPv4 or IPv6)';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Add user_agent column if it doesn't exist
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'webhook_logs' AND column_name = 'user_agent'
|
|
) THEN
|
|
ALTER TABLE webhook_logs ADD COLUMN user_agent TEXT;
|
|
COMMENT ON COLUMN webhook_logs.user_agent IS 'User agent string from webhook request';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Add index on source_ip for efficient IP-based queries
|
|
CREATE INDEX IF NOT EXISTS idx_webhook_logs_source_ip ON webhook_logs(source_ip);
|
|
|
|
-- Verify columns were added
|
|
SELECT column_name, data_type
|
|
FROM information_schema.columns
|
|
WHERE table_name = 'webhook_logs'
|
|
AND column_name IN ('source_ip', 'user_agent');
|