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

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.

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

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;"

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;"
'

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'

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.

# 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;"

# 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;"

# 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;"

# 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;"

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

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

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

• Guides Index
• Adding a New Service
• folk.zone Services

• Return to wiki home