Database Security Standards

This rule applies to files matching the patterns above.

# Database Security Standards

## Rule
All database access MUST use least-privilege roles, parameterized queries, encrypted connections, and externalized credentials.

## Format
Every application connects with a role that has only the permissions it needs. Every query uses parameterized statements.

## Requirements

### 1. Least-Privilege Database Roles
```sql
-- Create role with minimum required permissions
CREATE ROLE app_reader;
GRANT CONNECT ON DATABASE myapp TO app_reader;
GRANT USAGE ON SCHEMA public TO app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;

CREATE ROLE app_writer;
GRANT CONNECT ON DATABASE myapp TO app_writer;
GRANT USAGE ON SCHEMA public TO app_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_writer;

-- Application uses app_writer; reporting tools use app_reader
-- NEVER use superuser/postgres role for application connections
```

### 2. Parameterized Queries Everywhere
```typescript
// BAD: SQL injection vulnerable
const query = `SELECT * FROM users WHERE email = '${userInput}'`;

// GOOD: Parameterized
const result = await pool.query(
  "SELECT id, email, name FROM users WHERE email = $1",
  [userInput]
);

// GOOD: ORM with parameter binding
const user = await prisma.user.findUnique({
  where: { email: userInput },
});
```

### 3. Encrypted Connections (TLS)
```typescript
// Always use SSL/TLS for database connections
const pool = new Pool({
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync("/path/to/ca-cert.pem"),
  },
});

// Or via connection string
// postgresql://user:pass@host:5432/db?sslmode=verify-full
```

### 4. Externalize Credentials
```bash
# NEVER hardcode credentials in source code
# Use environment variables or secret management

# Environment variables
DATABASE_URL=postgresql://user:pass@host:5432/db

# Better: Use a secret manager
# AWS Secrets Manager, HashiCorp Vault, GCP Secret Manager
```

### 5. Audit Logging
```sql
-- Enable query logging for sensitive tables
ALTER TABLE users SET (log_statement = 'all');

-- Or use pg_audit extension for comprehensive audit
CREATE EXTENSION pgaudit;
SET pgaudit.log = 'write';
SET pgaudit.log_relation = on;
```

## Anti-Patterns
- Using superuser credentials for application connections
- String concatenation in SQL queries (SQL injection)
- Database credentials in source code or config files
- Unencrypted connections to remote databases
- No audit trail for data modifications
- Granting ALL PRIVILEGES to application roles

## Enforcement
Use CI checks to scan for hardcoded connection strings. Require TLS for all non-localhost connections. Audit role permissions quarterly.