[Feature]: Support AWS RDS PostgreSQL with IAM Authentication and Separate Connection Configuration

[vivekkrishna]

Problem Statement

Problem Statement

Currently, the Directory Server assumes a single PostgreSQL instance and hardcodes connection configuration to Kubernetes internal DNS (dir-postgres.dir-server.svc.cluster.local). This design prevents deployment in cloud-native environments like AWS RDS, where:

1. Database endpoint is external (not K8s internal DNS)
2. Credentials need cloud-native management (AWS Secrets Manager, not hardcoded)
3. IAM-based authentication is required (no password needed if using IAM roles)
4. Connection pooling must be configurable for serverless environments (ECS/Fargate)

Current Limitation

# Current: Only supports hardcoded K8s internal service
DIRECTORY_SERVER_OASF_API_VALIDATION_SCHEMA_URL="http://dir-postgres.dir-server.svc.cluster.local:5432"
# OR external Postgres, but with manual host/port via Helm values.yaml

# Needed: AWS RDS with separate config options
DIRECTORY_SERVER_POSTGRES_HOST="prod-db-instance.c123456789.us-east-1.rds.amazonaws.com"
DIRECTORY_SERVER_POSTGRES_PORT="5432"
DIRECTORY_SERVER_POSTGRES_USERNAME="postgres"  # Or IAM role
DIRECTORY_SERVER_POSTGRES_PASSWORD="<from-secrets-manager>"  # Or absent if using IAM
DIRECTORY_SERVER_POSTGRES_DATABASE="directory"
DIRECTORY_SERVER_POSTGRES_SSL_MODE="require"
DIRECTORY_SERVER_POSTGRES_AUTH_MODE="iam"  # New: support IAM auth

### Proposed Solution


# server.config.yml (YAML alternative)
database:
  type: postgres
  postgres:
    host: "prod-db-instance.c123456789.us-east-1.rds.amazonaws.com"
    port: 5432
    username: "postgres"
    password: ""  # Will use IAM if auth_mode=iam
    database: "directory"
    ssl_mode: "require"
    auth_mode: "iam"
    aws_region: "us-east-1"
    max_connections: 100
    connection_timeout: "30s"
    idle_timeout: "10m"
    max_lifetime: "1h"

### Alternatives Considered

_No response_

### Additional Context

_No response_

### Checklist

- [x] I have read the [contributing guidelines](/agntcy/repo-template/blob/main/CONTRIBUTING.md)
- [x] I have verified this does not duplicate an existing feature request