A comprehensive solution for automating Google Cloud SQL PostgreSQL database management and IAM user permissions across multiple databases in an organization.

This application provides a complete REST API for managing PostgreSQL databases, schemas, roles, and IAM users. Below you'll find screenshots demonstrating the key features:

• Health Monitoring: Real-time service and database health checks
• Schema Management: Create and manage database schemas with proper ownership
• Role Management: Initialize, assign, and revoke PostgreSQL roles
• IAM Integration: Seamless Google Cloud IAM user management
• Database Operations: List schemas, tables, and perform health checks

Scroll down to see detailed screenshots for each feature!

This project contains comprehensive documentation for the Cloud SQL PostgreSQL Manager, a modular FastAPI service for managing PostgreSQL databases, schemas, roles, and IAM user permissions in Google Cloud SQL.

• md/ARCHITECTURE_OVERVIEW.md - High-level system architecture and design principles
• md/SERVICES.md - Detailed documentation of all services and their responsibilities
• md/COMPONENTS.md - Component system documentation and reusable business logic
• md/PLUGINS.md - Plugin system documentation for extensible role management

• md/API.md - Complete API endpoint documentation with examples
• md/test_endpoints.json - Test endpoints and sample requests

• md/ROLE_TESTING.md - Comprehensive guide for testing standard roles and permissions
• md/QUICK_TEST_GUIDE.md - Quick 5-minute testing guide for role validation
• md/role_test_scripts.sql - Automated SQL test scripts

• md/DEPLOYMENT.md - Comprehensive deployment guide for various environments

Start with md/ARCHITECTURE_OVERVIEW.md to understand the system design and components.

Read md/SERVICES.md to understand each service's responsibilities and interactions.

Check md/API.md for endpoint documentation and examples.

Follow md/DEPLOYMENT.md for deployment instructions.

The system follows a modular microservices architecture with clear separation of concerns:

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│   API Layer     │    │  Service Layer  │    │ Component Layer │
│                 │    │                 │    │                 │
│ • Health Router │    │ • ConnectionMgr │    │ • Validation    │
│ • Database Router│    │ • SchemaMgr    │    │ • ErrorHandler  │
│ • Schema Router │    │ • RoleMgr       │    │ • ServiceOps    │
│ • Role Router   │    │ • UserMgr       │    │ • DatabaseOps   │
└─────────────────┘    └─────────────────┘    └─────────────────┘

• Schema Operations: Create, list, and manage database schemas
• Table Management: List tables with metadata and statistics
• Health Monitoring: Comprehensive database health checks
• Connection Pooling: High-performance connection management

• Plugin System: Extensible role definitions with versioning
• Permission Levels: readonly, readwrite, admin with granular control
• IAM Integration: Seamless Google Cloud IAM user management
• Role Registry: Firestore-based role tracking and history

• Input Validation: Comprehensive request validation
• IAM Validation: Service account and permission verification
• SQL Injection Protection: Parameterized queries and sanitization
• Error Handling: Secure error responses without information leakage

The system includes an extensible plugin architecture for role management:

• StandardRolePlugin: Built-in role definitions
• CustomRolePlugin: Custom role implementations
• PluginRegistry: Plugin management and loading
• Version Control: Role versioning and updates

• GET /health - Service health check

• POST /database/schemas - List database schemas
• POST /database/tables - List schema tables
• POST /database/health - Database health check

• POST /schemas/create - Create database schema

• POST /roles/initialize - Initialize roles
• POST /roles/assign - Assign role to user
• POST /roles/revoke - Revoke role from user
• POST /roles/list - List available roles

• POST /database/postgres-inheritance/grant - Grant IAM user to postgres
• POST /database/postgres-inheritance/revoke - Revoke IAM user from postgres

cd postgres-manager
uvicorn app.main:app --reload --host 0.0.0.0 --port 8080

cd postgres-manager
docker build -t cloudsql-postgres-manager .
docker run -p 8080:8080 cloudsql-postgres-manager

gcloud run deploy cloudsql-postgres-manager \
  --image gcr.io/PROJECT_ID/cloudsql-postgres-manager \
  --platform managed \
  --region europe-west1

For comprehensive testing documentation, see:

• tests/README.md - Complete test suite documentation

For comprehensive role testing, see the testing documentation:

• md/ROLE_TESTING.md - Complete testing guide
• md/QUICK_TEST_GUIDE.md - Quick testing guide
• md/role_test_scripts.sql - Automated test scripts

• Service Health: GET /health
• Database Health: POST /database/health

• Request processing time
• Database connection metrics
• Error rates by endpoint
• Role operation success rates

Structured JSON logging with correlation IDs and performance metrics.

• Google Cloud IAM integration
• Service account validation
• Permission verification

• Secret Manager for credentials
• Parameterized queries
• Input validation and sanitization

• Sanitized error messages
• No sensitive data in logs
• Structured error responses

• High-performance connection management
• Configurable pool sizes
• Automatic connection recovery

• Modular design for independent scaling
• Plugin-based extensibility
• Component reusability

cloudsql-postgres-manager/
├── postgres-manager/           # FastAPI application
│   ├── app/
│   │   ├── main.py            # Main application code
│   │   ├── models.py          # Pydantic models
│   │   ├── services/          # Business logic
│   │   ├── components/        # Reusable components
│   │   ├── routers/           # API endpoints
│   │   ├── plugins/           # Plugin system
│   │   └── utils/             # Utilities
│   ├── requirements.txt       # Python dependencies
│   └── Dockerfile            # Container configuration
├── md/                        # Documentation
│   ├── README.md             # This file
│   ├── API.md                # API documentation
│   ├── SERVICES.md           # Service documentation
│   ├── ROLE_TESTING.md       # Role testing guide
│   └── ...                   # Other documentation
└── README.md                 # This file

1. Read the architecture documentation
2. Understand the service responsibilities
3. Follow the component patterns
4. Add comprehensive tests
5. Update documentation

• Documentation: md/ directory
• API Docs: http://localhost:8080/docs
• Issues: GitHub Issues

This documentation is maintained alongside the codebase. When making changes:

1. Update relevant documentation files
2. Ensure examples are current
3. Test all code examples
4. Update version numbers
5. Review for accuracy and completeness

For questions or suggestions about the documentation, please open an issue or submit a pull request.