Security, Backups, and Maintenance

🔄 Quick Recall: In the last lesson, you built reporting and dashboard queries — views, time intelligence, and automated report distribution. Now let’s protect all that data with proper security, backup strategies, and ongoing maintenance.

The Security Checklist

Database security isn’t optional, even for small projects. AI can generate a comprehensive security setup:

Database: PostgreSQL
Environment: [development / staging / production]
Application: [describe what connects to the database]
Users: [list who needs access and what they do]

Generate a security hardening checklist and SQL scripts for:
1. Role-based access control (RBAC) — define roles with minimum necessary permissions
2. Separate roles for: application (read/write), reporting (read-only), admin (full)
3. Row-level security policies (if applicable)
4. Connection restrictions (IP allowlists, SSL required)
5. Audit logging setup
6. Password policies

Role-Based Access Control

Never give applications or users the default superuser role. Create specific roles:

-- Read-only role for reporting
CREATE ROLE reporting_role;
GRANT CONNECT ON DATABASE myapp TO reporting_role;
GRANT USAGE ON SCHEMA public TO reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role;

-- Application role (read + write specific tables)
CREATE ROLE app_role;
GRANT CONNECT ON DATABASE myapp TO app_role;
GRANT USAGE ON SCHEMA public TO app_role;
GRANT SELECT, INSERT, UPDATE ON customers, orders, order_items TO app_role;
-- No DELETE permission — application uses soft deletes

-- Admin role (schema changes, all data)
CREATE ROLE admin_role;
GRANT ALL PRIVILEGES ON DATABASE myapp TO admin_role;

✅ Quick Check: Why does the application role exclude DELETE permission in this example?

Because most applications should use soft deletes (setting a deleted_at timestamp) instead of actually removing rows. Hard deletes are irreversible at the database level. By removing DELETE permission from the app role, even a bug in the application code can’t accidentally wipe data. If hard deletes are truly needed, they happen through an admin role with proper approval.

SQL Injection Prevention

Even with good database permissions, SQL injection can bypass controls. AI can review your code:

Review this code for SQL injection vulnerabilities:

[paste your application code that builds SQL queries]

1. Identify any places where user input is concatenated into SQL strings
2. Show the safe version using parameterized queries / prepared statements
3. List any other injection risks (column names, table names, ORDER BY clauses)

The rule is simple: Never concatenate user input into SQL. Always use parameterized queries.

# DANGEROUS — SQL injection risk
cursor.execute(f"SELECT * FROM users WHERE email = '{user_email}'")

# SAFE — parameterized query
cursor.execute("SELECT * FROM users WHERE email = %s", (user_email,))

Backup Strategy

AI can design a backup plan for your specific needs:

Database: PostgreSQL (500GB, production)
RPO (maximum acceptable data loss): [e.g., 1 hour]
RTO (maximum acceptable downtime): [e.g., 4 hours]
Budget constraints: [if any]

Design a backup strategy including:
1. Backup types and schedule (full, incremental, WAL archiving)
2. Backup storage locations (local + offsite/cloud)
3. Retention policy (how long to keep backups)
4. Restore procedures (step-by-step for each scenario)
5. Backup verification (how to confirm backups actually work)
6. The exact commands/cron jobs to implement this

Critical rule: Untested backups aren’t backups. Schedule monthly restore tests on a non-production server.

Common backup schedule:

• Continuous: WAL (write-ahead log) archiving for point-in-time recovery
• Daily: Full logical backup (pg_dump) at low-traffic time
• Weekly: Full physical backup to offsite storage
• Monthly: Restore test to verify backup integrity

Ongoing Maintenance

Databases degrade without maintenance. Set up automated health checks:

Database: PostgreSQL

Create maintenance scripts for:
1. VACUUM ANALYZE schedule (prevent bloat, update statistics)
2. Index health check (bloated indexes, unused indexes)
3. Table bloat monitoring (identify tables needing VACUUM FULL)
4. Connection monitoring (are we approaching max_connections?)
5. Disk space alerting (warn at 80% capacity)
6. Long-running query detection (queries over 5 minutes)
7. Replication lag monitoring (if applicable)

Output as a maintenance runbook with cron schedules.

✅ Quick Check: Why is VACUUM ANALYZE important for PostgreSQL performance?

Because PostgreSQL doesn’t immediately reclaim space from deleted or updated rows — they become “dead tuples” that bloat the table and slow scans. VACUUM reclaims that space. ANALYZE updates the statistics the query planner uses to choose execution strategies. Without regular VACUUM ANALYZE, tables grow larger than necessary and the query planner makes poor decisions based on stale statistics.

Monitoring and Alerting

Set up proactive alerts so problems don’t surprise you:

Design a monitoring and alerting setup for my PostgreSQL database:

Alert me when:
1. Any query runs longer than [X] seconds
2. Connection count exceeds [Y]% of max_connections
3. Disk usage exceeds 80%
4. Replication lag exceeds [Z] seconds
5. A backup hasn't completed in the last 24 hours
6. Table bloat exceeds a threshold

For each alert, provide:
- The monitoring query or command
- Suggested threshold values for a production database
- How to integrate with [Slack/email/PagerDuty]

Exercise: Secure and Maintain Your Database

Apply these practices to your own database:

1. Audit your current permissions — who has access to what? Use AI to identify overly broad grants
2. Create proper roles (reporting, application, admin) with least-privilege permissions
3. Set up a backup schedule with automated scripts
4. Run a VACUUM ANALYZE and check for table bloat
5. Create one monitoring query that checks for long-running queries

Key Takeaways

• Principle of least privilege: every role gets minimum necessary permissions — never grant more than needed
• SQL injection prevention means parameterized queries everywhere — never concatenate user input into SQL
• A backup strategy needs both logical (pg_dump) and physical backups with regular restore testing
• Untested backups are not backups — schedule monthly restore verification
• VACUUM ANALYZE prevents performance degradation from dead tuples and stale statistics
• Proactive monitoring (connection counts, disk usage, query duration) catches problems before they cause outages

Up Next: In the final lesson, you’ll build a complete database project — combining everything you’ve learned into a real-world system from schema design to production deployment.