====== 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:

<code bash>
# 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;"
</code>

Use the -t flag for cleaner output on COUNT queries:
<code bash>
docker exec container-db psql -U dbuser -d dbname -A -t -c "SELECT COUNT(*) FROM users;"
</code>

==== SQLite Services ====

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

<code bash>
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;"
'
</code>

==== File-Based Services ====

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

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

==== MariaDB/MySQL Services ===

Similar to PostgreSQL but with mariadb client:

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

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

===== Common Service Patterns =====

==== Mastodon ====

<code bash>
# 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;"
</code>

==== Forgejo/Gitea ====

<code bash>
# 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;"
</code>

==== Lemmy ====

<code bash>
# 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;"
</code>

==== BookWyrm ====

<code bash>
# 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;"
</code>

===== 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:

<code bash>
#!/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
</code>

Run via cron for weekly automated audits:
<code bash>
# Add to crontab
0 9 * * 1 /path/to/audit-services.sh >> /var/log/service-audit.log 2>&1
</code>

===== 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

===== See Also =====

  * [[guides:start|Guides Index]]
  * [[guides:add_new_service|Adding a New Service]]
  * [[folkzone:services:start|folk.zone Services]]

  * [[start|Return to wiki home]]