Database Security

Referenced Files in This Document - engine.py - session.py - async_session.py - config.py - payments.py - payment.py - dependencies.py - crypto.py - env.py - alembic.ini - MIGRATIONS.md - requirements.txt

Table of Contents

1. Introduction
2. Project Structure
3. Core Components
4. Architecture Overview
5. Detailed Component Analysis
6. Dependency Analysis
7. Performance Considerations
8. Troubleshooting Guide
9. Conclusion
10. Appendices

Introduction

This document provides comprehensive database security guidance for the cTrip Payment Gateway. It focuses on connection encryption, certificate management, secure connection pooling, access control, privilege management, SQL injection prevention, audit logging, backup encryption, secure retention and deletion, database hardening, vulnerability assessment, and secure administration practices. Where applicable, the analysis references actual code and configuration files from the repository.

Project Structure

The database layer is implemented with SQLAlchemy and Alembic. Connections are configured via environment-driven settings, with separate development and production URLs. Sessions are created using scoped factories, and asynchronous sessions are supported for PostgreSQL. Migrations are managed centrally with Alembic and environment overrides.

Diagram sources - config.py - engine.py - session.py - async_session.py - payments.py - payment.py - dependencies.py - crypto.py - env.py - alembic.ini - MIGRATIONS.md

Section sources - engine.py - session.py - async_session.py - config.py - env.py - alembic.ini - MIGRATIONS.md

Core Components

• Database configuration and environment selection are centralized in settings, which expose production and development URLs and dynamically select the active URL per environment.
• Engines and session factories are created with connection pooling and pre-ping enabled for resilience.
• Alembic manages schema migrations with environment-specific overrides and logging configuration.

Key implementation references: - Settings and dynamic URL selection: config.py - Engine and async engine creation with pooling: engine.py - Sync session factory and dependency: session.py - Async session factory and dependency: async_session.py - Alembic environment override and logging: env.py, alembic.ini

Section sources - config.py - engine.py - session.py - async_session.py - env.py - alembic.ini

Architecture Overview

The database security architecture integrates environment-driven configuration, robust connection pooling, and strict input validation at the API boundary. Alembic enforces schema changes safely, while logging and migration practices support auditability.

Diagram sources - payments.py - dependencies.py - crypto.py - session.py - payment.py

Detailed Component Analysis

Connection Encryption and Certificate Management

• Current configuration uses SQLAlchemy URL schemes for PostgreSQL and SQLite. There is no explicit TLS/SSL parameterization in the engine creation code.
• To enforce encrypted connections:
• Add TLS parameters to the database URL for PostgreSQL (e.g., sslmode parameters) in settings.
• Ensure certificates are managed externally (e.g., CA bundles) and referenced via environment variables.
• Validate TLS handshake failures and configure appropriate error handling.

Implementation references: - URL selection and transformation: engine.py - Settings exposing production and development URLs: config.py

Security recommendations: - Enforce sslmode=require or stricter modes in production URLs. - Integrate certificate verification using trusted CA bundles. - Monitor TLS handshake errors and configure retry/backoff policies.

Section sources - engine.py - config.py

Secure Connection Pooling

• Connection pooling is enabled with pool_pre_ping=True and fixed pool size for synchronous engines.
• Asynchronous engines use pre-ping but no explicit pool size is set in the current code.

Recommendations: - Configure pool_size, max_overflow, pool_recycle, and pool_pre_ping consistently for both sync and async engines. - Use NullPool only for offline/CLI operations (as seen in Alembic); avoid for runtime.

Implementation references: - Synchronous engine pooling: engine.py - Asynchronous engine creation: engine.py

Section sources - engine.py

Access Control, Role-Based Permissions, and Privilege Management

• The codebase does not define database roles or RBAC logic. Privileges are typically managed at the database level.
• Recommended approach:
• Create least-privilege database users per environment.
• Separate read-only users for reporting and write users for application DML.
• Restrict schema modification to dedicated administrative accounts.
• Enforce row-level security or application-level filters where needed.

[No sources needed since this section provides general guidance]

SQL Injection Prevention and Input Validation

• The Payments API validates chain and token existence using ORM queries with equality filters. These are safe as long as the filter arguments are derived from validated Pydantic models.
• Pydantic schemas enforce field constraints (min/max lengths, numeric bounds, types) before reaching the database layer.

Implementation references: - API handler with validation and ORM query: payments.py - Pydantic schema constraints: schemas/payment.py

Additional recommendations: - Avoid dynamic SQL or raw SQL concatenation. - Prefer SQLAlchemy ORM constructs and typed parameters. - Sanitize and validate all external inputs at the API boundary.

Section sources - payments.py - payment.py

Audit Logging, Data Access Monitoring, and Security Event Tracking

• Alembic logging configuration exists and can be extended to capture migration events.
• Application-level audit logs should capture:
• Authentication/authorization failures
• Schema changes and migrations
• High-risk operations (deletes, truncates)
• Database connection errors and retries

Implementation references: - Alembic logging configuration: alembic.ini - Alembic environment logging setup: env.py

Section sources - alembic.ini - env.py

Database Backup Encryption, Secure Retention, and Deletion

• Alembic migration documentation advises backing up the database before applying migrations in production.
• Backup encryption and retention policies are not implemented in the codebase.

Recommendations: - Encrypt backups at rest and in transit. - Define retention windows aligned with compliance. - Implement secure deletion procedures for sensitive data (e.g., GDPR right to erasure) using soft deletes or anonymization.

Section sources - MIGRATIONS.md

Database Hardening, Vulnerability Assessment, and Penetration Testing

• Hardening checklist:
• Disable unnecessary network exposure; prefer private networks and VPNs.
• Enforce strong passwords and rotation policies.
• Limit exposed ports and use bastion hosts.
• Regularly patch database software and OS.
• Vulnerability assessment and penetration testing should be performed by qualified teams with proper authorization and scope.

[No sources needed since this section provides general guidance]

Secure Database Administration Practices

• Use dedicated administrative accounts with MFA and least privilege.
• Apply schema changes only via Alembic migrations.
• Review and test migrations on staging before production.
• Monitor slow queries and resource usage to prevent abuse.

Section sources - MIGRATIONS.md - env.py

Dependency Analysis

The database layer depends on configuration for URLs, SQLAlchemy engines for connections, and session factories for transactions. Alembic depends on the environment to set the database URL and on logging configuration.

Diagram sources - config.py - engine.py - session.py - async_session.py - payments.py - payment.py - env.py - alembic.ini

Section sources - config.py - engine.py - session.py - async_session.py - payments.py - payment.py - env.py - alembic.ini

Performance Considerations

• Connection pooling reduces overhead and improves throughput under load.
• Pre-ping ensures stale connections are recycled.
• For high-throughput scenarios, tune pool_size and overflow based on workload characteristics.
• Monitor slow queries and consider indexing strategies for frequently filtered columns.

[No sources needed since this section provides general guidance]

Troubleshooting Guide

Common areas to investigate: - Connection failures: Verify database URL correctness and TLS parameters. - Migration errors: Confirm Alembic environment URL override and logging configuration. - Slow queries: Profile ORM queries and add appropriate indexes.

References: - Alembic environment URL override: env.py - Alembic logging configuration: alembic.ini

Section sources - env.py - alembic.ini

Conclusion

The cTrip Payment Gateway employs environment-driven database configuration and Alembic-based migrations. To achieve robust database security, integrate TLS enforcement, strengthen connection pooling, implement least-privilege database roles, enforce strict input validation, augment audit logging, and adopt secure backup and deletion practices. Perform regular hardening, assessments, and adhere to secure administration procedures.

[No sources needed since this section summarizes without analyzing specific files]

Appendices

Appendix A: TLS Parameterization Guidance

• Add sslmode and related parameters to PostgreSQL URLs in settings.
• Reference external CA bundles via environment variables.
• Validate TLS handshake outcomes and configure error handling.

Section sources - config.py - engine.py

Appendix B: Alembic Migration Security Checklist

• Back up the database before applying migrations in production.
• Review and test migrations on staging.
• Use descriptive migration messages and keep migrations reversible when possible.

Section sources - MIGRATIONS.md - env.py