PostgreSQL Multi-Deployment Management with Terraform

Assignment

Rationale: Currently we have multiple deployments that span across multiple cloud accounts. Each deployment has a separate terraform codebase, with some TF modules shared. As our deployments grow, managing each terraform configuration separately becomes less and less viable. We need to move to a more scalable solution and the goal of this assignment is to create a simple demo of some approach for the solution.

Please publish your solution in a publicly available git repository. All code, scripts and possibly documentation should be committed into this repository. If manual steps are performed (eg. start a docker-compose or terraform commands), they should be documented so everybody familiar with the technologies is able to reproduce the steps. Each task should be represented as one or more commits in the repository.

Key Requirements:

• Everything must run locally using Docker/Docker Compose
• No cloud services should be used
• Solution must be in a public git repository
• All manual steps must be documented
• Each task should be separate commits

My improvements

• GitHub action for Terraform validation
• [IN PROGRESS] Use Terragrunt for 2nd task
• branch: terragrung
  • Currently problems with downloading providers from Hashicorp
    Details

    ``` 23:24:10.579 STDOUT terraform: Initializing the backend... 23:24:10.615 STDOUT terraform: Initializing provider plugins... 23:24:10.615 STDOUT terraform: - Finding cyrilgdn/postgresql versions matching "~> 1.21.0"... 23:24:10.803 STDOUT terraform: - Finding hashicorp/random versions matching "3.6.3"... 23:24:35.602 STDOUT terraform: - Installing hashicorp/random v3.6.3... 23:24:36.087 STDOUT terraform: - Installed hashicorp/random v3.6.3 (signed by HashiCorp) 23:24:36.087 STDOUT terraform: ╷ 23:24:36.087 STDOUT terraform: │ Error: Failed to install provider 23:24:36.087 STDOUT terraform: │ 23:24:36.087 STDOUT terraform: │ Error while installing cyrilgdn/postgresql v1.21.0: could not query 23:24:36.087 STDOUT terraform: │ provider registry for registry.terraform.io/cyrilgdn/postgresql: failed to 23:24:36.087 STDOUT terraform: │ retrieve cryptographic signature for provider: the request failed after 2 23:24:36.087 STDOUT terraform: │ attempts, please try again later: Get 23:24:36.087 STDOUT terraform: │ "https://github.com/cyrilgdn/terraform-provider-postgresql/releases/download/v1.21.0/terraform-provider-postgresql_1.21.0_SHA256SUMS.sig": 23:24:36.087 STDOUT terraform: │ context deadline exceeded 23:24:36.087 STDOUT terraform: ╵ 23:24:36.090 ERROR terraform invocation failed in /home/waldauf/__JOB/__ROSSUM/rossum-homework/grunt-02/clusters/dev/.terragrunt-cache/rzHbUrxhpsx1alZf4xU4KDor6YU/mhGmks4mczjF3JZlKaF8nz364B4 error=[/home/waldauf/__JOB/__ROSSUM/rossum-homework/grunt-02/clusters/dev/.terragrunt-cache/rzHbUrxhpsx1alZf4xU4KDor6YU/mhGmks4mczjF3JZlKaF8nz36 4B4] exit status 1 23:24:36.090 ERROR 1 error occurred: * [/home/waldauf/__JOB/__ROSSUM/rossum-homework/grunt-02/clusters/dev/.terragrunt-cache/rzHbUrxhpsx1alZf4xU4KDor6YU/mhGmks4mczjF3JZlKaF8nz364B4] exit status 1 ```

Task 1 - Single Deployment Setup

• Create a docker-compose.yml file that:
  • Runs a PostgreSQL database
  • Makes it accessible only from localhost
  • Sets up root password (can be visible in git)
• Create a Terraform module that:
  • Creates 3 databases
  • Creates 3 pairs of users (each with full access to one database)
  • Creates 1 read-only user with access to all databases
  • Manages secure passwords (must not be visible in git)
  • Can use local terraform state

Task 2 - Multiple Deployments

• Extend docker-compose.yml to:
  • Run multiple PostgreSQL instances (suggested 3)
  • Each instance on different ports
  • Each instance simulates a separate deployment/AWS account
• Apply the same Terraform configuration to each instance:
  • Same database and user structure
  • Unique passwords for each instance
  • Should be easily scalable to add more instances

General information

Required tools

• terraform (version ~> 1.7)
• docker
• docker-compose

Task 1: Single Deployment Setup

Steps for reproducing:

1. Switch to the task 1 directory:

cd ./terraform-01

2. Run PostgreSQL instance as docker container:

cd docker
docker-compose up -d
docker-compose ps -a
cd ..

3. Run terraform for applying PostgreSQL configuration:

# Initialize Terraform (load all necessary providers)
terraform init

# Check the output if everything is alright
terraform plan -out postgres_configuration.tfplan

# Apply changes
terraform apply postgres_configuration.tfplan

4. View all users and their passwords from the terraform.tfstate file:

jq -r '.outputs.all_users_passwords.value | to_entries[] | "\(.key) => \(.value)"' terraform.tfstate

5. Verify PostgreSQL configuration:

# List all databases
PGPASSWORD="root" psql -h localhost -p 5432 -U root -d postgres -c "\l"

# List all users
PGPASSWORD="root" psql -h localhost -p 5432 -U root -d postgres -c "\du"

6. Clean up (when done):

terraform apply -destroy
cd docker
docker-compose down
sudo rm -rf postgres_data
cd ..

Task 2: Multiple Deployments

Steps for reproducing:

1. Switch to the task 2 directory:

cd ./terraform-02

2. Run all PostgreSQL instances as docker containers:

cd docker
docker-compose up -d
docker-compose ps -a
cd ..

Each instance is running on different ports:

• DEV: 5432
• TEST: 5433
• PROD: 5434

3. Configure each environment (example for DEV):

# Switch to DEV configuration
cd clusters/dev

# Initialize Terraform
terraform init

# Plan changes
terraform plan -out postgres_configuration.tfplan

# Apply changes
terraform apply postgres_configuration.tfplan

Repeat for TEST and PROD environments by changing to the appropriate directory.

4. View passwords for a specific environment:

jq -r '.outputs.all_users_passwords.value | to_entries[] | "\(.key) => \(.value)"' terraform.tfstate

5. Verify PostgreSQL configuration (adjust port based on environment):

# For DEV environment (port 5432)
PGPASSWORD="root" psql -h localhost -p 5432 -U root -d postgres -c "\du"
PGPASSWORD="root" psql -h localhost -p 5432 -U root -d postgres -c "\l"

# For TEST environment (port 5433)
PGPASSWORD="root" psql -h localhost -p 5433 -U root -d postgres -c "\du"
PGPASSWORD="root" psql -h localhost -p 5433 -U root -d postgres -c "\l"

# For PROD environment (port 5434)
PGPASSWORD="root" psql -h localhost -p 5434 -U root -d postgres -c "\du"
PGPASSWORD="root" psql -h localhost -p 5434 -U root -d postgres -c "\l"

6. Clean up all environments:

# For each environment
cd clusters/dev
terraform apply -destroy
cd ../test
terraform apply -destroy
cd ../prod
terraform apply -destroy

# Remove containers and volumes
cd ../../docker
docker-compose down
sudo rm -rf postgres_data*

To consideration

Used providers in Terraform

• Use mainly official or supported providers by Hashicorp

Password Storage

• All passwords should be loaded during CD pipeline to:
  • Prevent possible leakage
  • Avoid manual manipulation
  • In this implementation, we prevent sharing by adding terraform.tfstate to .gitignore file

Terraform State Management

• State should be stored in a remote backend to support team collaboration
• Remote state enables locking to prevent concurrent modifications

CI/CD Pipeline Integration

CI

• Run formatting, validation, and security scans on each pull request
• Implement linting and policy checks to ensure best practices

CD

• Manage sensitive data like passwords as part of the CD pipeline
• Consider whether the apply process should be automated or require manual approval