Auditing Self-Hosted Services

Auditing Self-Hosted Services

A systematic approach to checking self-hosted services for signups, content, and potential abuse.

Why Audit

When you run public-facing services, you need to periodically check for:

Unauthorized signups: Spammers creating accounts
Problematic content: Spam, hate speech, illegal content
Resource abuse: Excessive storage, bandwidth usage
Security issues: Suspicious activity patterns

Regular audits help maintain service quality and reduce liability.

Audit Strategy

Different services store data differently. Query the database directly rather than relying on web interfaces.

PostgreSQL Services

Most modern self-hosted apps use PostgreSQL. Query directly via docker exec:

# Check user count
docker exec container-db psql -U dbuser -d dbname -c "SELECT COUNT(*) FROM users;"
 
# Check recent signups
docker exec container-db psql -U dbuser -d dbname -c "SELECT id, email, created_at FROM users ORDER BY created_at DESC LIMIT 10;"
 
# Check content count
docker exec container-db psql -U dbuser -d dbname -c "SELECT COUNT(*) FROM posts;"

Use the -t flag for cleaner output on COUNT queries:

docker exec container-db psql -U dbuser -d dbname -A -t -c "SELECT COUNT(*) FROM users;"

SQLite Services

For services using SQLite, mount the volume in a temporary container with sqlite3:

docker run --rm -v volume_name:/data ubuntu:22.04 sh -c '
  apt-get update -qq && apt-get install -y sqlite3 > /dev/null 2>&1
  sqlite3 /data/database.db "SELECT COUNT(*) FROM users;"
'

File-Based Services

Services like PrivateBin store data as files. Count files directly:

docker run --rm -v volume_name:/data alpine sh -c 'find /data/ -type f -name "*.php" | wc -l'

MariaDB/MySQL Services

Similar to PostgreSQL but with mariadb client:

docker exec container-db mariadb -u username -ppassword dbname -e "SELECT COUNT(*) FROM users;"

Note: Requires the actual database password from the service configuration.

Common Service Patterns

Mastodon

# Users
docker exec social-db psql -U mastodon -d mastodon_production -c "SELECT COUNT(*) FROM users;"
 
# Recent signups with emails
docker exec social-db psql -U mastodon -d mastodon_production -c "SELECT account_id, email, created_at FROM users ORDER BY created_at DESC LIMIT 10;"
 
# Statuses
docker exec social-db psql -U mastodon -d mastodon_production -c "SELECT COUNT(*) FROM statuses;"

Forgejo/Gitea

# Users
docker exec git-db psql -U gitea -d gitea -c "SELECT COUNT(*) FROM user;"
 
# Repositories
docker exec git-db psql -U gitea -d gitea -c "SELECT COUNT(*) FROM repository;"

Lemmy

# Users (called "person" in Lemmy schema)
docker exec lemmy-db psql -U lemmy -d lemmy -c "SELECT COUNT(*) FROM person;"
 
# Posts
docker exec lemmy-db psql -U lemmy -d lemmy -c "SELECT COUNT(*) FROM post;"

BookWyrm

# Users
docker exec books-db psql -U bookwyrm -d bookwyrm -c "SELECT COUNT(*) FROM bookwyrm_user;"
 
# Recent signups
docker exec books-db psql -U bookwyrm -d bookwyrm -c "SELECT username, email, created_date FROM bookwyrm_user ORDER BY created_date DESC LIMIT 10;"

Automation

Manual queries are fine for occasional checks, but automation is better for regular audits.

Create a shell script that queries all services and outputs a summary:

#!/bin/bash
# Service audit script
 
echo "=== Mastodon ==="
docker exec social-db psql -U mastodon -d mastodon_production -A -t -c "SELECT COUNT(*) FROM users;"
 
echo "=== Forgejo ==="
docker exec git-db psql -U gitea -d gitea -A -t -c "SELECT COUNT(*) FROM user;"
 
# ... continue for other services

Run via cron for weekly automated audits:

# Add to crontab
0 9 * * 1 /path/to/audit-services.sh >> /var/log/service-audit.log 2>&1

Red Flags

Watch for these patterns during audits:

Sudden user spikes: Could indicate spam signup campaigns
Suspicious email domains: Disposable email services, known spam domains
Content explosions: Thousands of posts in minutes
Storage growth: Rapid database or file size increases
Failed login attempts: Check application logs for brute force patterns

Response Procedures

If you find problematic content:

1. **Document**: Screenshot or export evidence
2. **Contain**: Suspend or delete the offending account
3. **Clean**: Remove the problematic content
4. **Analyze**: Check if it's part of a larger pattern
5. **Prevent**: Adjust registration settings, rate limits, or moderation tools

Table of Contents