DEV Community: Gerson Morales

Implementing AWS S3 Cross-Account Replication.

Gerson Morales — Tue, 20 May 2025 15:22:43 +0000

The purpose of this post is to offer a detailed, step-by-step guide to setting up an example of S3 Cross-Account Replication between buckets.

Requirements

1- Two AWS accounts (Account A and Account B).
2- Two Amazon S3 buckets (one in each account).
3- Appropriate IAM policies for access control.
4- IAM roles with necessary trust relationships and permissions.
5- Terraform for infrastructure as code deployment.

Scenario

We have two AWS accounts: Account A (111111111111) and Account B (222222222222). Our objective is to replicate objects from the source bucket, gerx24-source-bucket (in Account A), to the destination bucket, gerx24-destination-bucket (in Account B), using Amazon S3 Cross-Account Replication.

Additionally, we want to apply a lifecycle policy to buckets to automatically expire replicated objects after 10 days.

Folder Structure

Terraform files [Module]

Reusable Terraform module for provisioning Amazon S3 buckets.

main.tf

module "s3_bucket" {
  source  = "terraform-aws-modules/s3-bucket/aws"
  version = "4.9.0"
  bucket  = var.bucket_name

  control_object_ownership = true
  object_ownership         = "BucketOwnerPreferred"

  acl = "private"

  block_public_acls       = true
  block_public_policy     = true
  ignore_public_acls      = true
  restrict_public_buckets = true
  versioning = {
    status = "Enabled"
  }

  lifecycle_rule = var.lifecycle_rules

  server_side_encryption_configuration = {
    rule = {
      apply_server_side_encryption_by_default = {
        sse_algorithm = "AES256"
      }
    }
  }
}

outputs.tf

output "s3_bucket_id" {
  description = "Bucket ID"
  value       = module.s3_bucket.s3_bucket_id
}
output "s3_bucket_arn" {
  description = "Bucket ARN"
  value       = module.s3_bucket.s3_bucket_arn
}

variables.tf

variable "environment" {
  default     = null
  description = "Environment"
  type        = string
}

variable "bucket_name" {
  default     = null
  description = "Bucket name"
  type        = string
}

variable "lifecycle_rules" {
  default     = {}
  description = "Lifecycle rules object"
  type        = any
}

Terraform files [Source Bucket]

S3 Cross-Account Replication source bucket.

1- Creates bucket [gerx24-source-bucket].
2- Creates a Role replication_role [source-replication-role].
3- Creates replication_policy [source-replication-policy].
4- Creates replication_bucket_config and bind it with [gerx24-source-bucket] bucket.
5- Includes in [gerx24-source-bucket] a lifecycle_rules to expire files after 10 days.

main.tf

module "source_replication_bucket" {
  source = "../module/bucket"
  environment        = "dev"
  bucket_name        = "gerx24-source-bucket"
  lifecycle_rules = [
    {
      id      = "Delete files older than 10 days"
      enabled = true
      expiration = {
        days = 10
      }
    }
  ]
}

locals {
  source_replication_bucket_arn  = module.source_replication_bucket.s3_bucket_id
  bucket_name                    = module.source_replication_bucket.s3_bucket_arn
  destination_bucket_arn         = "arn:aws:s3:::gerx24-destination-bucket"
  source_replication_policy_name = "source-replication-policy"
  source_replication_role_name   = "source-replication-role"


  common_tags = {
    environment = "dev"
    maintainer  = "gerx24"
  }
}

resource "aws_iam_role" "replication_role" {
  name = local.source_replication_role_name

  assume_role_policy = jsonencode({
    Version = "2012-10-17",
    Statement = [
      {
        Action = "sts:AssumeRole",
        Principal = {
          Service = "s3.amazonaws.com"
        },
        Effect = "Allow",
        Sid = ""
      }
    ]
  })

  tags = local.common_tags
}

resource "aws_iam_policy" "replication_policy" {
  name = local.source_replication_policy_name

  policy = jsonencode({
    Version = "2012-10-17",
    Statement = [
      {
        Effect = "Allow",
        Action = [
          "s3:GetReplicationConfiguration",
          "s3:ListBucket"
        ],
        Resource = [
          "${local.source_replication_bucket_arn}/*",
          "${local.source_replication_bucket_arn}",
        ]
      },
      {
        Effect = "Allow",
        Action = [
          "s3:GetObjectVersionForReplication",
          "s3:GetObjectVersionAcl",
          "s3:GetObjectVersionTagging"
        ],
        Resource = [
          "${local.source_replication_bucket_arn}/*",
          "${local.source_replication_bucket_arn}"
        ]
      },
      {
        Effect = "Allow",
        Action = [
          "s3:ReplicateObject",
          "s3:ReplicateDelete",
          "s3:ReplicateTags",
          "s3:ObjectOwnerOverrideToBucketOwner"
        ],
        Resource = [
          "${local.destination_bucket_arn}/*",
          "${local.destination_bucket_arn}"
        ]
      }
    ]
  })
}

resource "aws_iam_role_policy_attachment" "replication" {
  role       = aws_iam_role.replication_role.name
  policy_arn = aws_iam_policy.replication_policy.arn
}

resource "aws_s3_bucket_replication_configuration" "replication_bucket_config" {
  depends_on = [module.source_replication_bucket]

  role   = aws_iam_role.replication_role.arn
  bucket = local.bucket_name

  rule {
    id = "cross-replication"
    delete_marker_replication {
      status = "Disabled"
    }
    source_selection_criteria {
      replica_modifications {
        status = "Enabled"
      }

    }
    filter {
      prefix = ""
    }

    status = "Enabled"

    destination {
      bucket        = local.destination_bucket_arn
      storage_class = "STANDARD"
      access_control_translation {
        owner = "Destination"
      }
      account = "222222222222"

      metrics {
        status = "Enabled"

        event_threshold {
          minutes = 15
        }
      }
      replication_time {
        status = "Enabled"

        time {
          minutes = 15
        }
      }
    }
  }
}

provider.tf

provider "aws" {
  region = "us-east-1"
  default_tags {
    tags = {
      terraform   = "true"
      application = "replication"
      environment = "dev"
    }
  }
}

Terraform files [Destination Bucket]

S3 Cross-Account Replication destination bucket.

1- Creates bucket [gerx24-destination-bucket].
2- Creates destination_bucket_replication_policy and attach it to [gerx24-destination-bucket] bucket.

main.tf

module "destination_replication_bucket" {
  source = "../module/bucket"

  bucket_name = "gerx24-destination-bucket"
  environment = "dev"
  lifecycle_rules = [
    {
      id      = "Delete files older than 10 days"
      enabled = true
      expiration = {
        days = 10
      }
    }
  ]
}

locals {
  destination_bucket_name = module.destination_replication_bucket.s3_bucket_id
  source_replication_role = "arn:aws:iam::111111111111:role/source-replication-role"
}

resource "aws_s3_bucket_policy" "destination_bucket_replication_policy" {
  bucket = local.destination_bucket_name

  policy = jsonencode({
    Version = "2012-10-17"
    Id      = ""
    Statement = [
      {
        Sid    = "AllowReplicationfromSourceBucket"
        Effect = "Allow"
        Principal = {
          AWS = local.source_replication_role
        }
        Action = [
          "s3:ReplicateObject",
          "s3:ReplicateDelete",
          "s3:ReplicateTags",
          "s3:ObjectOwnerOverrideToBucketOwner",
          "s3:List*",
          "s3:GetBucketVersioning",
          "s3:PutBucketVersioning"
        ]
        Resource = [
          "arn:aws:s3:::${local.destination_bucket_name}/*",
          "arn:aws:s3:::${local.destination_bucket_name}"
        ]
      }
    ]
  })
}

provider.tf

provider "aws" {
  region = "us-east-1"
  default_tags {
    tags = {
      terraform   = "true"
      application = "replication"
      environment = "dev"
    }
  }
}

Troubleshooting doc

Automated RDS PostgreSQL Restoration Using GitHub Action🐘

Gerson Morales — Fri, 16 May 2025 17:20:09 +0000

This document provides a step-by-step guide for integrating GitHub Actions to automate the restoration of a production AWS RDS PostgreSQL database for use in development environments.

Infrastructure

EKS cluster.
RDS database running in AWS (Just for this case scenario).
Github Repository.
AWS IAM service.
AWS S3 bucket.

Scenario

We have an Amazon RDS cluster running a PostgreSQL database, referred to as gerx_db_prod. Additionally, there are several lower-tier environments—such as gerx_db_dev_1, gerx_db_dev_2, and gerx_db_dev_3 that need to be kept up to date with the latest data from the production database. The goal is to enable on-demand synchronization of these environments by leveraging a GitHub Actions workflow.

AWS IAM configuration

We require the use of AWS IAM to create two roles with an established trust relationship. Additionally, we need to define two IAM policies:

First Policy: Policy granting permission to retrieve a pgdump file from an S3 bucket, which will be used for database restoration.

Second Policy: Policy providing access to an Amazon EKS cluster from github workflow.

I created this document to configure IRSA

https://dev.to/gerson_morales_3e89188d50/configure-irsa-using-eks-to-access-s3-from-a-pod-513a

But good news is that I would make this easy for you by adding how the code would look like for each role.

Role to allow pulling objects from S3 bucket would be like this

Role name: postgres-db-dev-restore-IRSA

Trusted entities

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "",
            "Effect": "Allow",
            "Principal": {
                "Federated": "arn:aws:iam::XXXXXXXXXXXXXXXX:oidc-provider/oidc.eks.us-east-1.amazonaws.com/id/SFAFJFJAFKAFKAFKAFLLSFLAFLAFLAFA"
            },
            "Action": "sts:AssumeRoleWithWebIdentity",
            "Condition": {
                "StringLike": {
                    "oidc.eks.us-east-1.amazonaws.com/id/SFAFJFJAFKAFKAFKAFLLSFLAFLAFLAFA:sub:sub": "system:serviceaccount:<EKS-NAMESPACE>:<EKS-SERVICE_ACCOUNT>",
                    "oidc.eks.us-east-1.amazonaws.com/id/SFAFJFJAFKAFKAFKAFLLSFLAFLAFLAFA:sub:aud": "sts.amazonaws.com"
                }
            }
        }
    ]
}

Policy attached to the role postgres-db-dev-restore-IRSA

{
    "Statement": [
        {
            "Action": [
                "s3:GetObject",
                "s3:ListBucket",
                "s3:PutObject"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3:::pgdump_my_bucket_name",
                "arn:aws:s3:::pgdump_my_bucket_name/*"
            ]
        }
    ],
    "Version": "2012-10-17"
}

Role to allow access from github repo using main branch to the EKS cluster.

Role name: postgres-db-dev-eks-refresh

Trusted entities

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Federated": "arn:aws:iam::xxxxxxxxxxx:oidc-provider/token.actions.githubusercontent.com"
      },
      "Action": "sts:AssumeRoleWithWebIdentity",
      "Condition": {
        "StringLike": {
          "token.actions.githubusercontent.com:aud": "sts.amazonaws.com",
          "token.actions.githubusercontent.com:sub": "repo:myrepo:ref:refs/heads/main"
        }
      }
    }
  ]
}

Policy attached to the role postgres-db-dev-eks-refresh

{
    "Statement": [
        {
            "Action": [
                "eks:DescribeCluster",
                "eks:ListClusters",
                "eks:AccessKubernetesApi"
            ],
            "Effect": "Allow",
            "Resource": "arn:aws:eks:us-east-1:xxxxxxxx:cluster/dev-usva-gerx24-cluster"
        },
        {
            "Action": [
                "sts:AssumeRole"
            ],
            "Effect": "Allow",
            "Resource": "*"
        }
    ],
    "Version": "2012-10-17"
}

With the two new IAM roles created each configured with its respective trust relationship and attached policies you will need to retrieve their ARNs. These ARNs are required for use in two specific areas, which I will outline next.

The role ARN for postgres-db-dev-restore-IRSA

(arn:aws:iam::xxxxxx:role/postgres-db-dev-restore-IRSA) should be associated with the Kubernetes service account responsible for retrieving the pgdump file (postgres_dump_$DATE.sql) from S3. This file will be used to perform the database restoration.

---
apiVersion: v1
kind: ServiceAccount
metadata:
  name: restore-db-sa
  namespace: restore-db
  annotations:
    eks.amazonaws.com/role-arn: arn:aws:iam::xxxxxx:role/postgres-db-dev-restore-IRSA

The role ARN for postgres-db-dev-eks-refresh (arn:aws:iam::xxxxxx:role/postgres-db-dev-eks-refresh) must be added to the EKS cluster by updating the aws-auth ConfigMap in the kube-system namespace. The modification should be as follows:

    - "groups":
      - "github-ci-group"
      "rolearn": "arn:aws:iam::xxxxxx:role/postgres-db-dev-eks-refresh"
      "username": "github:db-restore"

Finally, we need to configure RBAC to grant the role access exclusively to the namespace where the GitHub triggered job responsible for database restoration will be deployed.

---
apiVersion: rbac.authorization.k8s.io/v1
kind: Role
metadata:
  namespace: restore-db
  name: postgres-db-restore-role
rules:
  - apiGroups: [""]
    resources: ["pods", "services"]
    verbs: ["get", "list", "watch", "create", "delete"]
  - apiGroups: ["batch"]
    resources: ["jobs"]
    verbs: ["get", "list", "watch", "create", "delete"]

---
apiVersion: rbac.authorization.k8s.io/v1
kind: RoleBinding
metadata:
  name: postgres-db-restore-rolebinding
  namespace: restore-db
subjects:
  - kind: User
    name: github:db-restore
    apiGroup: rbac.authorization.k8s.io
roleRef:
  kind: Role
  name: restore-db
  apiGroup: rbac.authorization.k8s.io

Configure ConfigMap to run SQL script to grant privileges to postgres_owner_role

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-db-restore-dev
  namespace: restore-db
data:
  privileges.sql: |
    DO $$
    BEGIN
      -- Grant privileges on all tables in all schemas
      EXECUTE (
        SELECT string_agg(
          'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ' || quote_ident(schemaname) || ' TO postgres_owner_role;',
          ' '
        )
        FROM pg_catalog.pg_tables
        WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
      );
    END
    $$;

Configure k8s secret or external secret in this case which contains the password to access database that would be use in variable PGPASSWORD

---
apiVersion: external-secrets.io/v1beta1
kind: ExternalSecret
metadata:
  name: db-refresh-dev
  namespace: restore-db
  annotations:
    argocd.argoproj.io/sync-wave: "-12"
spec:
  dataFrom:
  - extract:
      key: db-refresh-dev/db-refresh-dev-secret
  refreshInterval: 1h
  secretStoreRef:
    kind: ClusterSecretStore
    name: aws-secrets-manager
  target:
    name: db-refresh-dev
    template:
      engineVersion: v2
      type: Opaque

At this stage, we should be ready to configure the GitHub Actions workflow to execute the database restoration process.

Github workflow configuration

Let’s begin by configuring a GitHub Actions workflow in the repository from which the process will be triggered. This workflow should allow the selection of a lower-tier environment e.g. dev that needs to be refreshed with the latest data from the production database.

name: db restore dev [gerx_db_prod]


on:
  workflow_dispatch:
    inputs:
      database:
        description: "gerx_db_dev_x"
        required: true
        type: string
      environment:
        description: "environment"
        default: int
        type: string
      date:
        description: "Backup date format e.g 20250512 yyyymmdd"
        required: true
        type: string

  ## This can be also used as workflow_call ##
  workflow_call:
    inputs:
      database:
        description: "gerx_db_dev_x"
        required: true
        type: string
      environment:
        description: "environment"
        default: dev
        type: string
      date:
        description: "Backup date format e.g 20250515 year/month/day"
        required: true
        type: string

jobs:
  db-restore-int:
    runs-on: ubuntu-latest
    permissions:
      id-token: write
      contents: read

    steps:
      - name: 🔑 Get AWS Creds
        id: aws-creds
        uses: aws-actions/configure-aws-credentials@v4
        with:
          aws-region: us-east-1
          role-to-assume: arn:aws:iam::xxxxxx:role/postgres-db-dev-eks-refresh

      - name: Update kubeconfig for EKS
        run: |
          aws eks update-kubeconfig --name ${{ inputs.environment }}-usva-gerx24-cluster --region us-east-1

      - name: Deploy Job
        run: |
          export DB_NAME=${{ inputs.database }}
          export ENV=${{ inputs.environment }}
          export DATE=${{ inputs.date }}
          export PGPASSWORD=${{ secrets.PGPASSWORD }}

          cat <<EOF | envsubst | kubectl apply -f -
          apiVersion: batch/v1
          kind: Job
          metadata:
            name: db-restore-job-$DB_NAME
            namespace: postgres-db-restore
            labels:
              app: db-restore-job
          spec:
            ttlSecondsAfterFinished: 300
            template:
              metadata:
                name: db-restore-job
                labels:
                  app: db-restore-job
              spec:
                initContainers:
                - name: copying-pgdump
                  image: amazon/aws-cli
                  command:
                    - /bin/sh
                    - -c
                    - |
                      echo "Copying files from my-bucket"
                      aws s3 cp s3://pgdump_my_bucket_name/ /pg-dump --recursive
                  volumeMounts:
                    - name: pg-dump
                      mountPath: /pg-dump
                containers:
                - name: db-restore
                  image: gerx24/centos-tools:3.0.0
                  env:
                    - name: PGPASSWORD
                       valueFrom:
                          secretKeyRef:
                            name: db-refresh-dev
                            key: PGPASSWORD
                    - name: DB_NAME
                      value: "$DB_NAME"
                    - name: ENV
                      value: "$ENV"
                    - name: DATE
                      value: "$DATE"
                  command: ["/bin/bash", "-c"]
                  args:
                    - |
                      echo "Dropping old database..."
                      psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c "ALTER DATABASE $DB_NAME OWNER TO root;"

                      psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_NAME AND pid <> pg_backend_pid(); DROP DATABASE $DB_NAME;"

                      echo "Creating new database..."
                      psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c "CREATE DATABASE $DB_NAME;"

                      echo "Changing ownership..."
                      psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c "ALTER DATABASE $DB_NAME OWNER TO postgres_owner_role;"
                      psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c "GRANT postgres_owner_role TO postgres_owner_green;"
                      psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c "ALTER USER postgres_owner_green SET ROLE postgres_owner_role;"
                      psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c "GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO postgres_owner_role;"
                      echo "Restoring database..."
                      psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d $DB_NAME -f /pg-dump/postgres_dump_$DATE.sql

                      echo "Altering schema ownership..."
                      psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d $DB_NAME -c "ALTER SCHEMA public OWNER TO postgres_owner_role; ALTER SCHEMA client_side OWNER TO postgres_owner_role; ALTER SCHEMA settings OWNER TO postgres_owner_role;"

                      echo "Running script"
                      psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d $DB_NAME -f /script/privileges.sql
                  volumeMounts:
                    - name: pg-dump
                      mountPath: /pg-dump
                    - name: privileges-script
                      mountPath: /script
                volumes:
                  - name: pg-dump
                    emptyDir: {}
                  - name: privileges-script
                    configMap:
                      name: postgres-db-restore-dev
                restartPolicy: OnFailure
                serviceAccountName: restore-db-sa
          EOF

      - name: Wait for Job to Succeed [5 minutes check]
        run: |
          echo "Checking status of job db-restore-job-${{ inputs.database }}"
          for i in {1..30}; do
            STATUS=$(kubectl get job db-restore-job-${{ inputs.database }} -n postgres-db-restore -o jsonpath="{.status.conditions[?(@.type=='Complete')].status}")
            if [[ "$STATUS" == "True" ]]; then
              echo "✅ Job db-restore-job-${{ inputs.database }} completed successfully."
              exit 0
            fi

            FAILED=$(kubectl get job db-restore-job-${{ inputs.database }} -n postgres-db-restore -o jsonpath="{.status.failed}")
            if [[ "$FAILED" -ge 1 ]]; then
              echo "❌ Job db-restore-job-${{ inputs.database }} failed."
              exit 1
            fi

            echo "⏳ Job db-restore-job-${{ inputs.database }} not complete yet... waiting 10 seconds"
            sleep 10
          done

          echo "⏰ Timed out waiting for job to complete."
          exit 1


      - name: Delete Job
        run: |
          kubectl delete job db-restore-job-${{ inputs.database }} -n postgres-db-restore
          echo "Job db-restore-job-${{ inputs.database }} completed"

The job above would do the following:

A. You are expected to include the input parameters for GitHub Actions, using values like below including database_name, environment and date using yyyymmdd format as below.

          export DB_NAME=gerx_db_dev_1
          export ENV=dev
          export DATE=20250515 (Lets assumed you are using a dump created today)
          export PGPASSWORD=${{ secrets.PGPASSWORD }} (This is a secret in the repo with the password you can use to connect to the database)

secrets.PGPASSWORD -> This would be the secret password to access database save in the repo as a secret.

B. The GitHub Action run will initiate a Kubernetes job that begins with an initContainer. This container will download the latest postgres_dump_$DATE.sql backup from s3://pgdump_my_bucket_name/ (At this point we assumed that you have it on your bucket) and place those in a shared volume. The main container will then use this shared volume to execute a series of psql commands that would do the following.

1- Drop the existing database.
2- Recreate the database.
3- Change ownership.
4- Restore database. (gerx_db_dev_1) from (postgres_dump_$DATE.sql)
5- Add schema permissions.
6- Finally run the script to grant privileges on all tables in all schemas.

C. Finally, the GitHub Action will perform a verification check using kubectl within a 5 minute window to confirm the status of the Kubernetes job. Once the job is confirmed to have succeeded, the action will proceed to clean up by deleting the job from the Kubernetes cluster.

Automated RDS PostgreSQL 🐘Restoration Using GitHub Actions Workflow_Dispatch

Gerson Morales — Fri, 16 May 2025 17:04:24 +0000

This document provides a step-by-step guide for integrating GitHub Actions to automate the restoration of a production AWS RDS PostgreSQL database for use in development environments.

Infrastructure

EKS cluster.
Postgres database (I am using AWS RDS in this example.)
Github Repository.
AWS IAM service.
AWS S3 bucket.

Scenario

We have an Amazon RDS cluster running a PostgreSQL database, referred to as gerx_db_prod. Additionally, there are several lower-tier environments—such as gerx_db_dev_1, gerx_db_dev_2, and gerx_db_dev_3 that need to be kept up to date with the latest data from the production database. The goal is to enable on-demand synchronization of these environments by leveraging a GitHub Actions workflow.

To create a backup of your RDS PostgreSQL database using pg_dump, run a command similar to the following from a container that has network access to the RDS cluster: pg_dump -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d gerx_db_prod -f gerx24_dump_$(date +%Y%m%d).sql
This command will generate a SQL dump file named using the current date, for example: gerx24_dump_20250515.sql that you would need to upload to the bucket that you would like to use to restore your database from in the job. Additionally you can include in your bucket a sql script to grant permissions to your database role to all tables in all schemas which job uses in one of the last steps below using privileges.sql file.

DO $$
BEGIN
   -- Grant privileges on all tables in all schemas
   EXECUTE (
     SELECT string_agg('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ' || quote_ident(schemaname) || ' TO postgres_owner_role;', ' ')
     FROM pg_catalog.pg_tables
     WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
   );
END $$;

AWS IAM configuration

We require the use of AWS IAM to create two roles with an established trust relationship. Additionally, we need to define two IAM policies:

A policy granting permission to retrieve a pgdump file from an S3 bucket, which will be used for database restoration.

A policy providing access to an Amazon EKS cluster.

I created this document to configure IRSA Configure IRSA using EKS to access S3 from a POD. but I would make this easy by adding how the code would look like for each role.

Role to allow pulling objects from S3 bucket would be like this

Role name: postgres-db-dev-restore-IRSA

Trusted entities

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "",
            "Effect": "Allow",
            "Principal": {
                "Federated": "arn:aws:iam::XXXXXXXXXXXXXXXX:oidc-provider/oidc.eks.us-east-1.amazonaws.com/id/SFAFJFJAFKAFKAFKAFLLSFLAFLAFLAFA"
            },
            "Action": "sts:AssumeRoleWithWebIdentity",
            "Condition": {
                "StringLike": {
                    "oidc.eks.us-east-1.amazonaws.com/id/SFAFJFJAFKAFKAFKAFLLSFLAFLAFLAFA:sub:sub": "system:serviceaccount:<EKS-NAMESPACE>:<EKS-SERVICE_ACCOUNT>",
                    "oidc.eks.us-east-1.amazonaws.com/id/SFAFJFJAFKAFKAFKAFLLSFLAFLAFLAFA:sub:aud": "sts.amazonaws.com"
                }
            }
        }
    ]
}

Policy attached to the role postgres-db-dev-restore-IRSA

{
    "Statement": [
        {
            "Action": [
                "s3:GetObject",
                "s3:ListBucket",
                "s3:PutObject"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3:::pgdump_my_bucket_name",
                "arn:aws:s3:::pgdump_my_bucket_name/*"
            ]
        }
    ],
    "Version": "2012-10-17"
}

Role to allow access from github repo using main branch to the EKS cluster

Role name: postgres-db-dev-eks-restore

Trusted entities

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Federated": "arn:aws:iam::xxxxxxxxxxx:oidc-provider/token.actions.githubusercontent.com"
      },
      "Action": "sts:AssumeRoleWithWebIdentity",
      "Condition": {
        "StringLike": {
          "token.actions.githubusercontent.com:aud": "sts.amazonaws.com",
          "token.actions.githubusercontent.com:sub": "repo:myrepo:ref:refs/heads/main"
        }
      }
    }
  ]
}

Policy attached to the role postgres-db-dev-eks-restore

{
    "Statement": [
        {
            "Action": [
                "eks:DescribeCluster",
                "eks:ListClusters",
                "eks:AccessKubernetesApi"
            ],
            "Effect": "Allow",
            "Resource": "arn:aws:eks:us-east-1:xxxxxxxx:cluster/dev-usva-gerx24-cluster"
        },
        {
            "Action": [
                "sts:AssumeRole"
            ],
            "Effect": "Allow",
            "Resource": "*"
        }
    ],
    "Version": "2012-10-17"
}

With the two new IAM roles created—each configured with its respective trust relationship and attached policies—you will need to retrieve their ARNs. These ARNs are required for use in two specific areas, which I will outline next.

The role ARN for *postgres-db-dev-restore-IRSA *(arn:aws:iam::xxxxxx:role/postgres-db-dev-restore-IRSA) should be associated with the Kubernetes service account responsible for retrieving the pgdump file from S3. This file will be used to perform the database restoration.

---
apiVersion: v1
kind: ServiceAccount
metadata:
  name: restore-db-sa
  namespace: restore-db
  annotations:
    eks.amazonaws.com/role-arn: arn:aws:iam::xxxxxx:role/postgres-db-dev-restore-IRSA

The role ARN for postgres-db-dev-eks-refresh (arn:aws:iam::xxxxxx:role/postgres-db-dev-eks-restore) must be added to the EKS cluster by updating the aws-auth ConfigMap in the kube-system namespace. The modification should be as follows:

    - "groups":
      - "github-ci-group"
      "rolearn": "arn:aws:iam::xxxxxx:role/postgres-db-dev-eks-refresh"
      "username": "github:db-restore"

Finally, we need to configure RBAC to grant the role access exclusively to the namespace where the GitHub-triggered job responsible for database restoration will be deployed.

---
apiVersion: rbac.authorization.k8s.io/v1
kind: Role
metadata:
  namespace: restore-db
  name: postgres-db-restore-role
rules:
  - apiGroups: [""]
    resources: ["pods", "services"]
    verbs: ["get", "list", "watch", "create", "delete"]
  - apiGroups: ["batch"]
    resources: ["jobs"]
    verbs: ["get", "list", "watch", "create", "delete"]

---
apiVersion: rbac.authorization.k8s.io/v1
kind: RoleBinding
metadata:
  name: postgres-db-restore-rolebinding
  namespace: restore-db
subjects:
  - kind: User
    name: github:db-restore
    apiGroup: rbac.authorization.k8s.io
roleRef:
  kind: Role
  name: restore-db
  apiGroup: rbac.authorization.k8s.io

At this stage, we should be ready to configure the GitHub Actions workflow to execute the database restoration process.

Github workflow configuration

Let’s begin by configuring a GitHub Actions workflow in the repository from which the process will be triggered. This workflow should allow the selection of a lower-tier environment e.g. dev that needs to be refreshed with the latest data from the production database.

name: db restore dev [gerx_db_prod]


on:
  workflow_dispatch:
    inputs:
      database:
        description: "gerx_db_dev_x"
        required: true
        type: string
      environment:
        description: "environment"
        default: int
        type: string
      date:
        description: "Backup date format e.g 20250512 yyyymmdd"
        required: true
        type: string

jobs:
  db-restore-int:
    runs-on: ubuntu-latest
    permissions:
      id-token: write
      contents: read

    steps:
      - name: 🔑 Get AWS Creds
        id: aws-creds
        uses: aws-actions/configure-aws-credentials@v4
        with:
          aws-region: us-east-1
          role-to-assume: arn:aws:iam::xxxxxx:role/postgres-db-dev-eks-restore

      - name: Update kubeconfig for EKS
        run: |
          aws eks update-kubeconfig --name ${{ inputs.environment }}-usva-gerx24-cluster --region us-east-1

      - name: Deploy Job
        run: |
          export DB_NAME=${{ inputs.database }}
          export ENV=${{ inputs.environment }}
          export DATE=${{ inputs.date }}
          export PGPASSWORD=${{ secrets.PGPASSWORD }}

          cat <<EOF | envsubst | kubectl apply -f -
          apiVersion: batch/v1
          kind: Job
          metadata:
            name: db-restore-job-$DB_NAME
            namespace: restore-db
            labels:
              app: db-restore-job
          spec:
            ttlSecondsAfterFinished: 300
            template:
              metadata:
                name: db-restore-job
                labels:
                  app: db-restore-job
              spec:
                initContainers:
                - name: copying-pgdump
                  image: amazon/aws-cli
                  command:
                    - /bin/sh
                    - -c
                    - |
                      echo "Copying files from pgdump_my_bucket_name"
                      aws s3 cp s3://pgdump_my_bucket_name/ /pg-dump --recursive
                  volumeMounts:
                    - name: pg-dump
                      mountPath: /pg-dump
                containers:
                - name: db-restore
                  image: gerx24/centos-tools:3.0.0
                  env:
                    - name: PGPASSWORD
                      value: "$PGPASSWORD"
                    - name: DB_NAME
                      value: "$DB_NAME"
                    - name: ENV
                      value: "$ENV"
                    - name: DATE
                      value: "$DATE"
                  command: ["/bin/bash", "-c"]
                  args:
                    - |
                      echo "Dropping old database..."
                      PGPASSWORD=$PGPASSWORD psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c "ALTER DATABASE $DB_NAME OWNER TO root;"

                      PGPASSWORD=$PGPASSWORD psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_NAME AND pid <> pg_backend_pid(); DROP DATABASE $DB_NAME;"

                      echo "Creating new database..."
                      PGPASSWORD=$PGPASSWORD psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c "CREATE DATABASE $DB_NAME;"

                      echo "Changing ownership..."
                      PGPASSWORD=$PGPASSWORD psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d postgres -c "ALTER DATABASE $DB_NAME OWNER TO postgres_owner_green;"

                      echo "Restoring database..."
                      PGPASSWORD=$PGPASSWORD psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d $DB_NAME -f /pg-dump/postgres_dump_$DATE.sql

                      echo "Altering schema ownership..."
                      PGPASSWORD=$PGPASSWORD psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d $DB_NAME -c "ALTER SCHEMA public OWNER TO postgres_owner_role; ALTER SCHEMA client_side OWNER TO postgres_owner_role; ALTER SCHEMA settings OWNER TO postgres_owner_role;"

                      echo "Running script"
                      PGPASSWORD=$PGPASSWORD psql -h rds.gerx24.usva.$ENV.gersonplace.com -p 5432 -U root -d $DB_NAME -f /pg-dump/privileges.sql
                  volumeMounts:
                    - name: pg-dump
                      mountPath: /pg-dump
                volumes:
                - name: pg-dump
                  emptyDir: {}
                restartPolicy: OnFailure
                serviceAccountName: restore-db-sa
          EOF

      - name: Wait for Job to Succeed [5 minutes check]
        run: |
          echo "Checking status of job db-restore-job-${{ inputs.database }}"
          for i in {1..30}; do
            STATUS=$(kubectl get job db-restore-job-${{ inputs.database }} -n postgres-db-restore -o jsonpath="{.status.conditions[?(@.type=='Complete')].status}")
            if [[ "$STATUS" == "True" ]]; then
              echo "✅ Job db-restore-job-${{ inputs.database }} completed successfully."
              exit 0
            fi

            FAILED=$(kubectl get job db-restore-job-${{ inputs.database }} -n postgres-db-restore -o jsonpath="{.status.failed}")
            if [[ "$FAILED" -ge 1 ]]; then
              echo "❌ Job db-restore-job-${{ inputs.database }} failed."
              exit 1
            fi

            echo "⏳ Job db-restore-job-${{ inputs.database }} not complete yet... waiting 10 seconds"
            sleep 10
          done

          echo "⏰ Timed out waiting for job to complete."
          exit 1


      - name: Delete Job
        run: |
          kubectl delete job db-restore-job-${{ inputs.database }} -n postgres-db-restore
          echo "Job db-restore-job-${{ inputs.database }} completed"

Basically the job above would do the following

A. It is expected that the GitHub Actions workflow includes defined inputs with current default values under the workflow_dispatch trigger to enable environment selection at runtime. e.g

 export DB_NAME=gerx_db_dev_1
 export ENV=dev
 export DATE=20250515 (Lets assumed you are using a dump created today)
 export PGPASSWORD=${{ secrets.PGPASSWORD }} (This is a secret in the repo with the password you can use to connect to the database)

B. A new Kubernetes Job named db-restore-job-gerx_db_dev_1 will be created in the restore-db namespace. This Job will connect to the target database at rds.gerx24.usva.dev.gersonplace.com, execute the necessary psql commands, and restore the database using the latest backup.

The backup file will be retrieved from the S3 bucket my-bucket via an initContainer, which will download the file and mount it to a shared volume. The main container will then use this mounted file during the database restoration step.

C. Job Status Check: A step will continuously monitor the status of the Kubernetes Job using kubectl, ensuring that it completes successfully within a 5-minute window.Once the Job is confirmed to have succeeded, a final step will remove the Job from the cluster by executing kubectl delete job.

Configure IRSA using EKS to access S3 from a POD in terraform

Gerson Morales — Sun, 05 Jan 2025 04:43:04 +0000

This post will provide a detailed, step-by-step guide for configuring IRSA using terraform which would allow a POD running in EKS to connect to S3 service.

Requirements

AWS Account.
S3 bucket.
Terraform.
EKS cluster.

Scope

The final goal would be to allowed a pod to copy or put files to and from an S3 bucket called krakenmoto.

`Initial steps`

💻Install terraform

OSX

brew install hashicorp/tap/terraform

Windows

choco install terraform

Linux

sudo apt-get install terraform

Terraform module configuration

This module would create necessary resources to get IRSA working

S3 Bucket.
IAM Role with trust-relationship for eks service-account and namespace.
IAM Policy for S3 access.
Data resource to get EKS access.

Module would be save into modules/irsa folder and it would contain 3 files data.tf, main.tf, and variables.tf.

data.tf

data "aws_eks_cluster" "eks" {
  count = var.eks_cluster_id == null ? 0 : 1
  name  = var.eks_cluster_id
}

data "aws_partition" "current" {}

main.tf

locals {
  application = "gersonplace-irsa"
  name        = var.project_name

  eks_oidc_issuer                       = var.eks_cluster_id == null ? "" : join("/", slice(split("/", one(data.aws_eks_cluster.eks).identity[0].oidc[0].issuer), 2, 5))
  eks_cluster_oidc_arn                  = "arn:${data.aws_partition.current.partition}:iam::${var.aws_account}:oidc-provider/${local.eks_oidc_issuer}"
  eks_namespace                         = "gersonplace"
  service_account                       = "${local.eks_namespace}-sa"
  common_tags = {
    application = local.application
  }
}

module "s3_bucket" {
  source  = "terraform-aws-modules/s3-bucket/aws"
  version = "4.3.0"
  bucket  = var.bucket_name

  control_object_ownership = true
  object_ownership         = "BucketOwnerPreferred"

  acl = "private"

  block_public_acls       = true
  block_public_policy     = true
  ignore_public_acls      = true
  restrict_public_buckets = true

  server_side_encryption_configuration = {
    rule = {
      apply_server_side_encryption_by_default = {
        sse_algorithm = "AES256"
      }
    }
  }

  tags = local.common_tags
}

resource "aws_iam_role" "irsa" {
  name        = "gersonplace-irsa-role"
  description = "${local.name} EKS IRSA role"

  assume_role_policy = <<-EOT
  {
    "Version": "2012-10-17",
    "Statement": [
      {
        "Action": "sts:AssumeRoleWithWebIdentity",
        "Condition": {
          "StringLike": {
            "${local.eks_oidc_issuer}:sub": "system:serviceaccount:${local.eks_namespace}:${local.service_account}",
            "${local.eks_oidc_issuer}:aud": "sts.amazonaws.com"
          }
        },
        "Principal": {
          "Federated": "${local.eks_cluster_oidc_arn}"
        },
        "Effect": "Allow",
        "Sid": ""
      }
    ]
  }
  EOT

  tags = local.common_tags
}

resource "aws_iam_policy" "irsa" {
  name        = "${local.name}-irsa-policy"
  description = "${local.name}-integration with EKS Pods"
  policy = jsonencode(
    {
      Version = "2012-10-17"
      Statement = [
        {
          Action = [
            "s3:*",
          ]
          Effect = "Allow"
          Resource = [
            "arn:aws:s3:::${var.bucket_name}",
            "arn:aws:s3:::${var.bucket_name}/*"
          ],
        }
      ]
    }
  )
  tags = local.common_tags
}

resource "aws_iam_role_policy_attachment" "irsa" {
  role       = aws_iam_role.irsa.name
  policy_arn = aws_iam_policy.irsa.arn
}

variables.tf

variable "aws_account" {
  description = "AWS account ID"
  type        = string
}

variable "region" {
  description = "AWS Region"
  type        = string
}

variable "bucket_name" {
  type = string
}

variable "project_name" {
  type = string
}

variable "eks_cluster_id" {
  type = string
}

These 3 files are going to create infrastructure necessary to be able to access S3 from EKS pod in namespace gersonplace bucket-name would be configure as an input in main.tf file that would call the module to create the whole infrastructure.

Terraform code that would set variables to call the module

main.tf

module "IRSA" {
  source = "./modules/irsa"
  aws_account    = "112223334445"
  region         = "us-east-1"
  bucket_name    = "krakenmoto"
  project_name   = "gersonplace-irsa"
  eks_cluster_id = "gersonplace-eks-project"
}

Now we are ready to run terraform, place your self in folder where main.tf is located and run the following commands

terraform init -reconfigure -upgrade
terraform validate
terraform plan
terraform apply

At this point we should have a new S3 bucket named krakenmoto and IAM role and Iam policy with a trust-relationship which contains the something like

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "",
            "Effect": "Allow",
            "Principal": {
                "Federated": "arn:aws:iam::112223334445:oidc-provider/oidc.eks.us-east-1.amazonaws.com/id/8DF14F971F8dfdDSJDSJDJSJDJA"
            },
            "Action": "sts:AssumeRoleWithWebIdentity",
            "Condition": {
                "StringLike": {
                    "oidc.eks.us-east-1.amazonaws.com/id/8DF14F971F8dfdDSJDSJDJSJDJA:sub": "system:serviceaccount:gersonplace:gersonplace-sa",
                    "oidc.eks.us-east-1.amazonaws.com/id/8DF14F971F8dfdDSJDSJDJSJDJA:aud": "sts.amazonaws.com"
                }
            }
        }
    ]
}

This configuration enables access for the gersonplace-sa service account in the gersonplace namespace. By associating the service account with the role through the appropriate annotation, any resource using this service account will be granted permissions to access the resources specified in the role’s policy. This approach ensures that the Deployment/Pod can securely access the permitted resources in alignment with the role's policy. I'll explain this next with Kubernetes.

Kubernetes

Now that we have the required infrastructure and permissions in AWS it is time to deploy EKS resources and test connectivity to S3 from pod. For this example I would configure a Deployment running one pod in gersonplace namespace with a service account that includes the annotation for the role with necessary permissions for S3.

`EKS Resources`

Deployment: gersonplace-irsa
Service-Account: gersonplace-sa
Namespace: gersonplace

namespace.yaml

---
apiVersion: v1
kind: Namespace
metadata:
  name: gersonplace

service-account.yaml

---
apiVersion: v1
kind: ServiceAccount
metadata:
  name: gersonplace-sa
  namespace: gersonplace
  annotations:
    eks.amazonaws.com/role-arn: arn:aws:iam::112223334445:role/gersonplace-role

✅ Annotation eks.amazonaws.com/role-arn: with the role arn has been added.

Deployment.yaml

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: gersonplace-irsa
  namespace: gersonplace
  labels:
    app: gersonplace
spec:
  selector:
    matchLabels:
      app: gersonplace
  replicas: 1
  template:
    metadata:
      labels:
        app: gersonplace
    spec:
      containers:
      - name: gersonplace-irsa
        image: amazon/aws-cli:latest
        imagePullPolicy: Always
        command: ["sleep", "infinity"]
      serviceAccountName: gersonplace-sa

✅ Service-Account has been added to the deployment.

⚠Important:
serviceAccountName should be set to service-account set in the trust-policy.

service-account should have the annotation eks.amazonaws.com/role-arn: with the role that contains the policy that allows access to S3.

At this point you can apply k8s manifest using kubectl and login into the pod and test access to S3 bucket krakenmoto and list files inside of it.

💻 ✅## Validation
Shell into the deployment pod and run aws s3 ls s3://krakenmoto you should be able to list files, put and copy files as policy allows to do it.

Setting up IAM Anywhere using terraform

Gerson Morales — Sat, 04 Jan 2025 22:43:02 +0000

This post would walk you through the steps to configured IamAnywhere in AWS using Terraform and custom certificates.

Requirements

AWS Account.
Terraform.
Certificate Authority [PrivateCA.pem, client.key and client.pem].
aws_signing_helper from AWS https://docs.aws.amazon.com/rolesanywhere/latest/userguide/credential-helper.html

Scope

We are going to configure IamAnywhere to allow access to krakenmoto bucket in S3 without access-key or secret-access-key.

`Initial steps`

Configure Certificate Authority. To use AWS IAM anywhere we need an X.509 certificate issued by a CA (Certificate Authority).You can use script below to be able to create Bundle-Certificate including in PrivateCA.pem and client.pem and client.key required to access once we have the infrastructure ready.

certificate.sh

#!/bin/bash

SERVER="${SERVER:-client}"

OUTPUT_PATH=${OUTPUT_PATH:-certificates}
mkdir -p $OUTPUT_PATH

CORPORATION=GERSONPLACE
GROUP="Engineering"
CITY="Cartago"
STATE="Paraiso"
COUNTRY=CR

CERT_AUTH_PASS=`openssl rand -base64 32`
echo $CERT_AUTH_PASS > cert_auth_password
CERT_AUTH_PASS=`cat cert_auth_password`

cat -<<EOF > config.cnf
[ req ]
distinguished_name  = req_distinguished_name
attributes      = req_attributes

[ req_distinguished_name ]
countryName         = Country Name (2 letter code)
countryName_min         = 2
countryName_max         = 2
stateOrProvinceName     = State or Province Name (full name)
localityName            = Locality Name (eg, city)
0.organizationName      = Organization Name (eg, company)
organizationalUnitName      = Organizational Unit Name (eg, section)
commonName          = Common Name (eg, fully qualified host name)
commonName_max          = 64
emailAddress            = Email Address
emailAddress_max        = 64

[ req_attributes ]
challengePassword       = A challenge password
challengePassword_min       = 4
challengePassword_max       = 20

[ v3_ca ]
basicConstraints        = critical, CA:TRUE
subjectKeyIdentifier    = hash
authorityKeyIdentifier  = keyid:always, issuer:always
keyUsage                = critical, cRLSign, digitalSignature, keyCertSign

[SAN]
subjectAltName=DNS:$SERVER"
EOF

echo "Create the certificate authority"
openssl genrsa -out $OUTPUT_PATH/PrivateCA.key 4096
openssl \
  req \
  -subj "/CN=$SERVER.ca/OU=$GROUP/O=$CORPORATION/L=$CITY/ST=$STATE/C=$COUNTRY" \
  -new \
  -x509 \
  -passout pass:$CERT_AUTH_PASS \
  -key $OUTPUT_PATH/PrivateCA.key \
  -out $OUTPUT_PATH/PrivateCA.pem \
  -config config.cnf \
  -extensions v3_ca \
  -days 36500

echo "Create client private key (used to decrypt the cert we get from the CA)"
openssl genrsa -out $OUTPUT_PATH/$SERVER.key 4096

cat -<<EOF > client.ext
basicConstraints = CA:FALSE
authorityKeyIdentifier = keyid,issuer
keyUsage = nonRepudiation, digitalSignature, keyEncipherment, dataEncipherment
EOF

echo "Create the CSR(Certitificate Signing Request)"

openssl req -new -key $OUTPUT_PATH/$SERVER.key -out $SERVER.csr -nodes \
  -subj "/CN=$SERVER/OU=$GROUP/O=$CORPORATION/L=$CITY/ST=$STATE/C=$COUNTRY" \
  -sha256

echo "Sign the certificate with the certificate authority"
openssl x509 -req -in $SERVER.csr -CA $OUTPUT_PATH/PrivateCA.pem -CAkey $OUTPUT_PATH/PrivateCA.key -CAcreateserial -out $OUTPUT_PATH/$SERVER.pem \
  -days 36500 \
  -extfile client.ext \
  -passin pass:$CERT_AUTH_PASS

Run the script ./certificate.sh and it would generate the required certificates inside /certificates folder.

Once you have your custom certificates ready it is time to set up terraform code.

💻## Install terraform

## OSX ##
brew install hashicorp/tap/terraform

## Windows ##
choco install terraform

## Linux ##
sudo apt-get install terraform

🛑Important= If you are using MAC M1/M2 you would probably run into this error when you run the module.

⚠Error: Incompatible provider version
Provider registry.terraform.io/hashicorp/template v2.2.0 does not have a package available for your current platform, darwin_arm64.
Provider releases are separate from Terraform CLI releases, so not all providers are available for all platforms. Other versions
of this provider may have different platforms supported.

🛠No worries lets get it fixed by running these 3 commands:

brew install kreuzwerker/taps/m1-terraform-provider-helper
m1-terraform-provider-helper activate -> [In case you have not activated the helper]
m1-terraform-provider-helper install hashicorp/template -v 2.10.0 --> [Install and compile]

Terraform module configuration

Code would create the following resources in AWS IAM

Trust Anchor: In trust Anchor, we establish trust between AWS IAM Role Anywhere and CA. An application running outside AWS authenticates against a trust anchor with X.509 client certificate to get temporary AWS credentials.

IAM Role: Trust Anchors assumes the AWS IAM role to grant allowed IAM policy permissions. To use a role we must trust the IAM Role Anywhere service principle in the role.

Profile: In the profile, we define an IAM role to be assumed by the client. We can set additional permissions boundaries on active sessions with AWS managed policies and condition blocks.

In this case we are going to make use of a custom module that creates all these resources at once, it starts by creating modules/custom folder including .tf files with the resources that terraform will create in AWS anchor.tf, iam.tf, outputs.tf, tls-crt.tf and variables.tf.

anchor.tf

# Trust anchors
resource "aws_rolesanywhere_trust_anchor" "trust_anchor" {
  name    = "${local.project_name}-trust_anchor"
  enabled = true
  source {
    source_data {
      x509_certificate_data = file("${path.module}/certificates/PrivateCA.pem")
    }
    source_type = "CERTIFICATE_BUNDLE"
  }
}

# Profile
resource "aws_rolesanywhere_profile" "profile" {
  enabled             = true
  name                = "${local.project_name}-profile"
  role_arns           = [aws_iam_role.roles.arn]
  managed_policy_arns = [aws_iam_policy.profile_managed_policies.arn]
}


# Profile policies
#Managed policies limit the permissions granted by the role's permissions policy and are assigned to the role session when the role is assumed.
resource "aws_iam_policy" "profile_managed_policies" {
  name        = "${local.project_name}-user-profile-policies"
  path        = "/"
  description = "Allows access to S3"

  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Action = [
        "s3:*",
      ]
      Resource = [
            "arn:aws:s3:::${var.bucket_name}",
            "arn:aws:s3:::${var.bucket_name}/*"
      ]
      Effect = "Allow"
    }]
  })
}

iam.tf

locals {
  project_name = var.project_name

}
resource "aws_iam_role" "roles" {
  name = "${local.project_name}-iamanywhere-trust-role"
  path = "/"

  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Effect = "Allow",
        Principal = {
          Service = "rolesanywhere.amazonaws.com",
        },
        Action = [
          "sts:AssumeRole",
          "sts:TagSession",
          "sts:SetSourceIdentity"
        ],
        Condition = {
          ArnEquals = {
            "aws:SourceArn" = "arn:aws:rolesanywhere:${var.region}:${var.aws_account}:trust-anchor/${aws_rolesanywhere_trust_anchor.trust_anchor.id}"

          }
        }
      }
    ]
  })
}

# Permission policies in the role of iamanywhere-trust-role
resource "aws_iam_policy" "s3_full_access" {
  name        = "${local.project_name}-iamanywhere-trust-role-policies"
  path        = "/"
  description = "Allows access to S3"

  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Action = [
        "s3:*",
      ]
      Resource = [
        "arn:aws:s3:::${var.bucket_name}",
        "arn:aws:s3:::${var.bucket_name}/*"
      ]
      Effect = "Allow"
    }]
  })
}

resource "aws_iam_role_policy_attachment" "roles_s3_access" {
  role       = aws_iam_role.roles.name
  policy_arn = aws_iam_policy.s3_full_access.arn
}

outputs.tf

output "anchor" {
  value = aws_rolesanywhere_trust_anchor.trust_anchor.arn
}

output "profile" {
  value = aws_rolesanywhere_profile.profile.arn
}

output "awsiam" {
  value = aws_iam_role.roles.arn
}

data "template_file" "aws_export_profile" {
  template = <<-EOT
[profile iam_anywhere]
region=us-east-1
credential_process = aws_signing_helper credential-process --trust-anchor-arn ${aws_rolesanywhere_trust_anchor.trust_anchor.arn} --profile-arn ${aws_rolesanywhere_profile.profile.arn} --role-arn ${aws_iam_role.roles.arn} --certificate /path/client.pem --private-key /path/client.key
EOT
  vars = {
    trust_anchor_arn = aws_rolesanywhere_trust_anchor.trust_anchor.arn
    profile_arn      = aws_rolesanywhere_profile.profile.arn
    role_arn         = aws_iam_role.roles.arn
  }
}

resource "local_file" "aws_export_profile" {
  content  = data.template_file.aws_export_profile.rendered
  filename = "./aws-config.txt"
}

tls-crt.tf

resource "tls_private_key" "roles" {
  algorithm = "RSA"
}

variables.tf

variable "aws_account" {
  description = "AWS account ID"
  type        = string
}

variable "region" {
  description = "AWS Region"
  type        = string
}

variable "bucket_name" {
  type = string
}

variable "project_name" {
  type = string
}

Those 5 files configure the required terraform resources that you would need in order to create AWS infrastructure to access S3 via IAMAnywhere you can modify iam.tf to set additional policies if needed but in this example the access would be only to krakenmoto bucket that would be included in the main.tf as an input.

Below is how the terraform file structure should look like. Notice that /certificates folder from running ./certificates.sh is inside the root module as anchor resource would make use of it to configure certificate bundle.

Terraform code that would set variables to call the module

main.tf

module "Iamanywhere" {
  source = "./modules/custom"
  aws_account  = "112223334445"
  region       = "us-east-1"
  bucket_name  = "krakenmoto"
  project_name = "gersonplace"
}

Now we are ready to run terraform so place your self in folder there main.tf is located and run

terraform init -reconfigure -upgrade
terraform validate
terraform plan
terraform apply

Once you applied the changes to AWS you would see a new file generated by terraform with the name aws-config.txt you can use the data on it in order to create a profile to connect to your AWS account to access bucket krakenmoto in S3.

Save this profile into .aws/credentials and download aws_signing_helper from https://docs.aws.amazon.com/rolesanywhere/latest/userguide/credential-helper.html

Once you have aws_signing_helper run
cp aws_signing_helper /usr/local/bin and
chmod +x /usr/local/bin/aws_signing_helper to set permissions.

At this point you have 2 options to authenticate

Manual

./aws_signing_helper credential-process --trust-anchor-arn arn:aws:rolesanywhere:us-east-1:112223334445:trust-anchor/957dd152-a4e2-4ac8-ab79-ff70ae66cf07 --profile-arn arn:aws:rolesanywhere:us-east-1:286514997612:profile/c25f019c-0234-4905-99cc-6dbeacc65b69 --role-arn arn:aws:iam::112223334445:role/gersonplace-iamanywhere-trust-role --certificate /path/client.pem --private-key /path/client.key

Automatic

export AWS_PROFILE=iam_anywhere

Which would use AWS_PROFILE configure in ~/.aws/credentials e.g

[iam_anywhere]
region=us-east-1
credential_process = aws_signing_helper credential-process --trust-anchor-arn arn:aws:rolesanywhere:us-east-1:112223334445:trust-anchor/957dd152-a4e2-4ac8-ab79-ff70ae66cf07 --profile-arn arn:aws:rolesanywhere:us-east-1:286514997612:profile/c25f019c-0234-4905-99cc-6dbeacc65b69 --role-arn arn:aws:iam::112223334445:role/gersonplace-iamanywhere-trust-role --certificate /path/client.pem --private-key /path/client.key

🛑Important: You need to use client.pem and client.key in order to be able to use the anchor to authenticate with AWS.

At this point in your terminal and after setting up AWS profile to iam_anywhere you would be able to run aws s3 ls s3://krakenmoto and get files inside bucket.

✅💻## Verification
aws sts get-caller-identity

{
    "UserId": "AROAUFNM75FWHT6NA3BGE:3ebe8913d3f56b97407de5e686207ae4f8d99057",
    "Account": "112223334445",
    "Arn": "arn:aws:sts::112223334445:assumed-role/gersonplace-iamanywhere-trust-role/3ebe8913d3f56b97407de5e686207ae4f8d99057"
}

aws s3 ls s3://krakenmoto

Migrate 🪳Coackroach DB into Postgres🐘

Gerson Morales — Tue, 17 Dec 2024 18:27:31 +0000

Migrating from CockroachDB to PostgreSQL involves several steps because CockroachDB and PostgreSQL share some similarities, but they also have important differences, particularly in terms of distributed systems, consistency models, and features.

Infrastructure

Cockroach DB Cluster running in AWS [EC2] with 3 AZ.
Amazon S3 (Simple Storage Service).
Amazon EKS (Elastic Kubernetes Service).
Cockroach DB running local on your computer.

Important Details

Database name 'lynx_core_prod'

CRDB Backup Preparation

1- Login to CockroachDB via Session Manager.

sudo -i
su - cockroach
cockroach cert create-client root --certs-dir=certs/ --ca-key=ca.key
cockroach sql --certs-dir=certs/

2- Create Cockroach Backup.

BACKUP DATABASE lynx_core_prod INTO 'userfile://defaultdb.public.userfiles_$user/lynx_core_prod-backup' AS OF SYSTEM TIME '-10s';

3- Save new backup [lynx_core_prod-backup] to local directory and push it to AWS S3.

cockroach userfile get --certs-dir=certs/ 'userfile://defaultdb.public.userfiles_$user/lynx_core_prod-backup'

aws s3 cp lynx_core_prod-backup s3://gersonsplace-bucket/crdb-backup --recursive

At this point we already have a backup in S3 of the Database we need to migrate to Postgres now it is time to export DDL and make it compatible with Postgres. [This can be tricky and you need to probably make some manual work depending on how big your DB is]

Restore CRDB backup into a local CRDB database on your Computer.

1- Copy your Database from S3 to your Local computer

aws s3 cp s3://gersonsplace-bucket/crdb-backup /crdb_to_postgres --recursive

2- Run a local Cockroach Database local

brew install cockroach

3- Start a single-node Cockroach DB and access SQL Shell

cockroach start-single-node --insecure --listen-addr=localhost:26258 --http-addr=localhost:8081

cockroach sql --insecure --host=localhost:26258

4- Now you need to create a new folder to be able to mount the backup on your local CRDB and then restore backup.

cd /Users/gersonsplace/cockroach-data/

mkdir extern/backup

Note: Copy the backup to cockroach-data/extern/backup folder.

5- Validate backup on your local CRDB Database using cockroach sql.

show backups in 'nodelocal://1/backup';

6- Read and shows files inside the backup using cockroach sql (Optional)

show backup from LATEST in 'nodelocal://1/backup';

7- Restore backup into your local CRDB database with skip_localities_check

RESTORE DATABASE lynx_core_prod FROM LATEST IN 'nodelocal://1/backup' with skip_localities_check;

Congratulations at this point you have your Backup restored on your local CRDB database!!!

Prepare CRDB to be migrated into Postgres

1- Export DDL [Data definition language] create the following script and run it ./ddl.sh

#!/bin/bash

echo "Exporting DDL for all tables to ddl.sql..."

# Query to get all table names
 psql -U root -h localhost -p 26258 -d lynx_core_prod  -Ato ddl.sql -c " show create all tables "

echo "DDL export complete. Check ddl.sql for the results."

Above script is going to create ddl.sql file it would contain all tables from your Database in the CockroachDB style so here comes the tricky part and probably some manual work if someone know a better approach feel free to share it but this process worked for me.

Example of how 2 tables in the ddl.sql

Example of the same 2 tables after making them postgres compatible

As I mentioned this could be tricky but not impossible depending on the size of your CRDB and numbers of tables.

Once you have an idea how your tables are going to look like with the postgres compatible format you can move to the next step.

Run Postgres Database local on your computer

1- Install Postgres
brew install postgresql@14
pg_ctl -D /Users/user/postgressql -l logfile start'
psql postgres

Connect local CRDB and Postgres together

1- Create a foreign server in postgres database and allow it to connect to CRDB

CREATE EXTENSION postgres_fdw;

CREATE SERVER crdb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    host 'localhost',
    port '26258',
    dbname 'lynx_core_prod',
    sslmode 'disable'
);

CREATE USER MAPPING FOR root SERVER crdb OPTIONS (
 user 'root', password ''
);

2- Now you need to create a foreign table in postgres for each table in CRDB example using the 2 tables I mentioned above.

Note: Notice that I use foreign server to be able to access the table on CRDB from postgres.

`SERVER crdb OPTIONS (
    table_name 'table-name'`

3- Now you need to create a local table to be able to insert data from foreign tables I used prefix local_ to create local table and to be able to Alter once I finish with the import. Example using the 2 tables I mentioned above.

4- At this point you can drop foreign server

DROP EXTENSION postgres_fdw;
DROP SERVER crdb CASCADE;
DROP USER MAPPING FOR root SERVER crdb;

5- After importing Data to the local table and dropping foreign server it is time to use Alter to change name to the correct one. Example using the 2 tables I mentioned above.
ALTER TABLE public.local_schema_migrations RENAME TO schema_migrations;
ALTER TABLE client_side.local_mounts RENAME TO mounts;

6- Finally you can now check your local Postgres DB and it should be the same as CRDB Database including all Data.

Lets make things more interesting and do the same process but now in Kubernetes

Requirements

Minikube or EKS with IRSA to access S3 and be able to download backup using initcontainer and emptyDir.
CRDB Backup.
3 files create_local_tables.sql, create_foreign_tables.sql and rename_tables.sql these files are the same files that we explained before in order to be able to make CRDB to run in Postgres but know we would use them to automate process in Kubernetes.

In this example I would use Minikube taking into consideration that we already have our backup file and the 3 files .sql save in the following path cockroach-data/extern/backup

Start Minikube with `--mount-string` flag pointing to where you have the CRDB backup `/Users/user/cockroach-data/extern/backup`

minikube start --mount-string="/Users/user/cockroach-data/extern/backup:/cockroach-backup" --mount

Let's create k8s manifests

RBAC

---
apiVersion: v1
kind: ServiceAccount
metadata:
  name: crdb-to-postgres-migration
  namespace: crdb-to-postgres

---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRole
metadata:
  name: crdb-to-postgres-migration-role
rules:
  - apiGroups: ["*"]
    resources: ["*"]
    verbs: ["*"]

---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRoleBinding
metadata:
  name: crdb-to-postgres-migration-crb
roleRef:
  apiGroup: rbac.authorization.k8s.io
  kind: ClusterRole
  name: crdb-to-postgres-migration-role
subjects:
  - kind: ServiceAccount
    name: crdb-to-postgres-migration
    namespace: crdb-to-postgres

Deployment

I explain deployment so you would find the following

cockroach container (Restore CRDB Backup)
postgres container (Create foreign server, create foreign tables, create local tables, import data from CRDB, drops foreign server and rename local tables to the actual table name.
export-pg-dump (To be able to restore postgres into other DB)

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: migration-demo
  namespace: crdb-to-postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      app: db
  template:
    metadata:
      labels:
        app: db
    spec:
      containers:
      - name: cockroach
        image: cockroachdb/cockroach:v24.2.0
        command: ["/bin/sh", "-c"]
        args:
          - |
            cockroach start-single-node --insecure --http-port=8080 --port=26257 --store=/cockroach-data &

            echo "#### Waiting for CockroachDB to start... ####" &&
            while ! curl -s http://localhost:8080/_status/cluster; do
              sleep 1;
            done &&

            echo "### CockroachDB is up and running! ###" &&
            cockroach sql --insecure --execute "RESTORE DATABASE lynx_core_prod FROM LATEST IN 'nodelocal://1/backup' WITH skip_localities_check;" &&

            echo "### Restore operation completed. ####" &&
            tail -f /dev/null # Keep the container running
        volumeMounts:
        - name: cockroach-backup
          mountPath: /cockroach-data/extern/backup
        ports:
        - containerPort: 8080
        - containerPort: 26257
        resources:
          requests:
            cpu: 50m
            memory: 100Mi
          limits:
            cpu: 1000m
            memory: 2000Mi
      - name: postgres
        image: postgres:latest
        command: ["sh", "-c"]
        args:
          - |
            docker-entrypoint.sh postgres &

            echo " #### Waiting for PostgreSQL to start... #### " &&

            until pg_isready -h localhost; do
              sleep 15;
            done &&

            echo " #### PostgreSQL is up and running! #### " &&

            psql -U root -c "CREATE DATABASE lynx_core_prod;" &&
            psql -U root -d lynx -c "CREATE EXTENSION IF NOT EXISTS postgres_fdw;" &&
            psql -U root -d lynx -c "CREATE SERVER crdb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '26257', dbname 'lynx_core_prod', sslmode 'disable');" &&
            psql -U root -d lynx -c "CREATE USER MAPPING FOR root SERVER crdb OPTIONS (user 'root', password '');" &&
            psql -U root -d lynx -c "CREATE SCHEMA client_side;" &&
            psql -U root -d lynx -c "CREATE SCHEMA settings;" &&
            psql -U root -d lynx -c "IMPORT FOREIGN SCHEMA public FROM SERVER crdb INTO public;" &&
            psql -U root -d lynx -c "IMPORT FOREIGN SCHEMA client_side FROM SERVER crdb INTO client_side;" &&
            psql -U root -d lynx -c "IMPORT FOREIGN SCHEMA settings FROM SERVER crdb INTO settings;" &&

            echo "#### Creating foreign_tables... ####" &&
            sleep 5 &&
            psql -U root -d lynx -f /scripts/create_foreign_tables.sql &&

            echo "#### Creating local_tables... ####" &&
            sleep 5 &&
            psql -U root -d lynx -f /scripts/create_local_tables.sql &&

            echo "#### Dropping  CRDB_Server... ####" &&
            psql -U root -d lynx -c "DROP SERVER crdb CASCADE;" &&
            sleep 2 &&

            echo "#### Renaming_tables... ####" &&
            psql -U root -d lynx -f /scripts/rename_tables.sql &&

            echo "#### All Database commands executed.... ####" &&
            sleep 10 &&

            echo "#### lynx Database dump created at /pg_dump/lynx_dump_$(date +%Y%m%d).sql ####" &&
            pg_dump -U root -d lynx -f /pg_dump/lynx_dump_$(date +%Y%m%d).sql &&

            tail -f /dev/null
        env:
        - name: POSTGRES_DB
          value: root
        - name: POSTGRES_USER
          value: root
        - name: POSTGRES_PASSWORD
          value: root
        ports:
        - containerPort: 5432
        resources:
          requests:
            cpu: 50m
            memory: 100Mi
          limits:
            cpu: 1000m
            memory: 2000Mi
        volumeMounts:
        - name: postgres-data
          mountPath: /var/lib/postgresql/data
        - name: cockroach-backup
          mountPath: /scripts
        - name: pg-dump
          mountPath: /pg_dump
      - name: export-pg-dump
        image: bitnami/kubectl:latest
        env:
          - name: POD_NAME
            valueFrom:
              fieldRef:
                fieldPath: metadata.name
        command:
        - /bin/sh
        - -c
        - |
          sleep 60
          echo "#### Copying "lynx_dump_$(date +%Y%m%d).sql" to /extern/backup ####"

          kubectl cp /pg_dump/lynx_dump_$(date +%Y%m%d).sql /Users/gerson/cockroach-data/extern/backup/
          tail -f /dev/null # Keep the container running
        volumeMounts:
        - name: pg-dump
          mountPath: /pg_dump
        resources:
          requests:
            cpu: 50m
            memory: 100Mi
          limits:
            cpu: 100m
            memory: 200Mi
      volumes:
      - name: postgres-data
        emptyDir: {}
      - name: cockroach-backup
        hostPath:
          path: /cockroach-backup 
          type: Directory
      - name: pg-dump
        emptyDir: {}
      serviceAccountName: crdb-to-postgres-migration

After the whole process finish you can export the pg_dump and run it in another postgres database useful for development

1- Copy pg_dump to local PC
kubectl cp pod-name:/pg_dump/lynx_dump_$(date +%Y%m%d).sql /Users/user/lynx_dump_$(date +%Y%m%d).sql -c export-pg-dump -n crdb-to-postgres

2- Create Database in postgres
createdb lynx_core_prod
psql -U root -d lynx_core_prod < lynx_dump_20241217.sql

Congratulations you were able to migrate CRDB🪳 database into Postgres🐘 DB :)Pura Vida!

Migrate Coackroach DB to Postgres

Gerson Morales — Tue, 17 Dec 2024 18:27:31 +0000

Infrastructure

Cockroach DB Cluster running in AWS [EC2] with 3 AZ.
Amazon S3 (Simple Storage Service).
Amazon EKS (Elastic Kubernetes Service).
Cockroach DB running local on your computer.

Important Details

Database name 'lynx_core_prod'

CRDB Backup Preparation

1- Login to CockroachDB via Session Manager.

sudo -i
su - cockroach
cockroach sql --certs-dir=certs/

2- Create Cockroach Backup.

BACKUP DATABASE lynx_core_prod INTO 'userfile://defaultdb.public.userfiles_$user/lynx_core_prod-backup' AS OF SYSTEM TIME '-10s';

3- Save new backup [lynx_core_prod-backup] to local directory and push it to AWS S3.

cockroach userfile get --certs-dir=certs/ 'userfile://defaultdb.public.userfiles_$user/lynx_core_prod-backup'

aws s3 cp lynx_core_prod-backup s3://gersonsplace-bucket/crdb-backup --recursive

Restore CRDB backup into a local CRDB database on your Computer.

1- Copy your Database from S3 to your Local computer

aws s3 cp s3://gersonsplace-bucket/crdb-backup /crdb_to_postgres --recursive

2- Run a local Cockroach Database local

brew install cockroach

3- Start a single-node Cockroach DB and access SQL Shell

cockroach start-single-node --insecure --listen-addr=localhost:26258 --http-addr=localhost:8081

cockroach sql --insecure --host=localhost:26258

4- Now you need to create a new folder to be able to mount the backup on your local CRDB and then restore backup.

cd /Users/gersonsplace/cockroach-data/

mkdir extern/backup

Note: Copy the backup to cockroach-data/extern/backup folder.

5- Validate backup on your local CRDB Database using cockroach sql.

show backups in 'nodelocal://1/backup';

6- Read and shows files inside the backup using cockroach sql (Optional)

show backup from LATEST in 'nodelocal://1/backup';

7- Restore backup into your local CRDB database with skip_localities_check

RESTORE DATABASE lynx_core_prod FROM LATEST IN 'nodelocal://1/backup' with skip_localities_check;

Congratulations at this point you have your Backup restored on your local CRDB database!!!

Prepare CRDB to be migrated into Postgres

1- Export DDL [Data definition language] create the following script and run it ./ddl.sh

#!/bin/bash

echo "Exporting DDL for all tables to ddl.sql..."

# Query to get all table names
 psql -U root -h localhost -p 26258 -d lynx_core_prod  -Ato ddl.sql -c " show create all tables "

echo "DDL export complete. Check ddl.sql for the results."

Example of how 2 tables in the ddl.sql

Example of the same 2 tables after making them postgres compatible

As I mentioned this could be tricky but not impossible depending on the size of your CRDB and numbers of tables.

Once you have an idea how your tables are going to look like with the postgres compatible format you can move to the next step.

Run Postgres Database local on your computer

1- Install Postgres
brew install postgresql@14
pg_ctl -D /Users/user/postgressql -l logfile start'
psql postgres

Connect local CRDB and Postgres together

1- Create a foreign server in postgres database and allow it to connect to CRDB

CREATE EXTENSION postgres_fdw;

CREATE SERVER crdb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    host 'localhost',
    port '26258',
    dbname 'lynx_core_prod',
    sslmode 'disable'
);

CREATE USER MAPPING FOR root SERVER crdb OPTIONS (
 user 'root', password ''
);

2- Now you need to create a foreign table in postgres for each table in CRDB example using the 2 tables I mentioned above.

Note: Notice that I use foreign server to be able to access the table on CRDB from postgres.

`SERVER crdb OPTIONS (
    table_name 'table-name'`

4- At this point you can drop foreign server

DROP EXTENSION postgres_fdw;
DROP SERVER crdb CASCADE;
DROP USER MAPPING FOR root SERVER crdb;

6- Finally you can now check your local Postgres DB and it should be the same as CRDB Database including all Data.

Lets make things more interesting and do the same process but now in Kubernetes

Requirements

Minikube or EKS with IRSA to access S3 and be able to download backup using initcontainer and emptyDir.
CRDB Backup.
3 files create_local_tables.sql, create_foreign_tables.sql and rename_tables.sql these files are the same files that we explained before in order to be able to make CRDB to run in Postgres but know we would use them to automate process in Kubernetes.

In this example I would use Minikube taking into consideration that we already have our backup file and the 3 files .sql save in the following path cockroach-data/extern/backup

Start Minikube with `--mount-string` flag pointing to where you have the CRDB backup `/Users/user/cockroach-data/extern/backup`

minikube start --mount-string="/Users/user/cockroach-data/extern/backup:/cockroach-backup" --mount

Let's create k8s manifests

RBAC

---
apiVersion: v1
kind: ServiceAccount
metadata:
  name: crdb-to-postgres-migration
  namespace: crdb-to-postgres

---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRole
metadata:
  name: crdb-to-postgres-migration-role
rules:
  - apiGroups: ["*"]
    resources: ["*"]
    verbs: ["*"]

---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRoleBinding
metadata:
  name: crdb-to-postgres-migration-crb
roleRef:
  apiGroup: rbac.authorization.k8s.io
  kind: ClusterRole
  name: crdb-to-postgres-migration-role
subjects:
  - kind: ServiceAccount
    name: crdb-to-postgres-migration
    namespace: crdb-to-postgres

Deployment

I explain deployment so you would find the following

cockroach container (Restore CRDB Backup)
postgres container (Create foreign server, create foreign tables, create local tables, import data from CRDB, drops foreign server and rename local tables to the actual table name.
export-pg-dump (To be able to restore postgres into other DB)

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: migration-demo
  namespace: crdb-to-postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      app: db
  template:
    metadata:
      labels:
        app: db
    spec:
      containers:
      - name: cockroach
        image: cockroachdb/cockroach:v24.2.0
        command: ["/bin/sh", "-c"]
        args:
          - |
            cockroach start-single-node --insecure --http-port=8080 --port=26257 --store=/cockroach-data &

            echo "#### Waiting for CockroachDB to start... ####" &&
            while ! curl -s http://localhost:8080/_status/cluster; do
              sleep 1;
            done &&

            echo "### CockroachDB is up and running! ###" &&
            cockroach sql --insecure --execute "RESTORE DATABASE lynx_core_prod FROM LATEST IN 'nodelocal://1/backup' WITH skip_localities_check;" &&

            echo "### Restore operation completed. ####" &&
            tail -f /dev/null # Keep the container running
        volumeMounts:
        - name: cockroach-backup
          mountPath: /cockroach-data/extern/backup
        ports:
        - containerPort: 8080
        - containerPort: 26257
        resources:
          requests:
            cpu: 50m
            memory: 100Mi
          limits:
            cpu: 1000m
            memory: 2000Mi
      - name: postgres
        image: postgres:latest
        command: ["sh", "-c"]
        args:
          - |
            docker-entrypoint.sh postgres &

            echo " #### Waiting for PostgreSQL to start... #### " &&

            until pg_isready -h localhost; do
              sleep 15;
            done &&

            echo " #### PostgreSQL is up and running! #### " &&

            psql -U root -c "CREATE DATABASE lynx_core_prod;" &&
            psql -U root -d lynx -c "CREATE EXTENSION IF NOT EXISTS postgres_fdw;" &&
            psql -U root -d lynx -c "CREATE SERVER crdb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '26257', dbname 'lynx_core_prod', sslmode 'disable');" &&
            psql -U root -d lynx -c "CREATE USER MAPPING FOR root SERVER crdb OPTIONS (user 'root', password '');" &&
            psql -U root -d lynx -c "CREATE SCHEMA client_side;" &&
            psql -U root -d lynx -c "CREATE SCHEMA settings;" &&
            psql -U root -d lynx -c "IMPORT FOREIGN SCHEMA public FROM SERVER crdb INTO public;" &&
            psql -U root -d lynx -c "IMPORT FOREIGN SCHEMA client_side FROM SERVER crdb INTO client_side;" &&
            psql -U root -d lynx -c "IMPORT FOREIGN SCHEMA settings FROM SERVER crdb INTO settings;" &&

            echo "#### Creating foreign_tables... ####" &&
            sleep 5 &&
            psql -U root -d lynx -f /scripts/create_foreign_tables.sql &&

            echo "#### Creating local_tables... ####" &&
            sleep 5 &&
            psql -U root -d lynx -f /scripts/create_local_tables.sql &&

            echo "#### Dropping  CRDB_Server... ####" &&
            psql -U root -d lynx -c "DROP SERVER crdb CASCADE;" &&
            sleep 2 &&

            echo "#### Renaming_tables... ####" &&
            psql -U root -d lynx -f /scripts/rename_tables.sql &&

            echo "#### All Database commands executed.... ####" &&
            sleep 10 &&

            echo "#### lynx Database dump created at /pg_dump/lynx_dump_$(date +%Y%m%d).sql ####" &&
            pg_dump -U root -d lynx -f /pg_dump/lynx_dump_$(date +%Y%m%d).sql &&

            tail -f /dev/null
        env:
        - name: POSTGRES_DB
          value: root
        - name: POSTGRES_USER
          value: root
        - name: POSTGRES_PASSWORD
          value: root
        ports:
        - containerPort: 5432
        resources:
          requests:
            cpu: 50m
            memory: 100Mi
          limits:
            cpu: 1000m
            memory: 2000Mi
        volumeMounts:
        - name: postgres-data
          mountPath: /var/lib/postgresql/data
        - name: cockroach-backup
          mountPath: /scripts
        - name: pg-dump
          mountPath: /pg_dump
      - name: export-pg-dump
        image: bitnami/kubectl:latest
        env:
          - name: POD_NAME
            valueFrom:
              fieldRef:
                fieldPath: metadata.name
        command:
        - /bin/sh
        - -c
        - |
          sleep 60
          echo "#### Copying "lynx_dump_$(date +%Y%m%d).sql" to /extern/backup ####"

          kubectl cp /pg_dump/lynx_dump_$(date +%Y%m%d).sql /Users/gerson/cockroach-data/extern/backup/
          tail -f /dev/null # Keep the container running
        volumeMounts:
        - name: pg-dump
          mountPath: /pg_dump
        resources:
          requests:
            cpu: 50m
            memory: 100Mi
          limits:
            cpu: 100m
            memory: 200Mi
      volumes:
      - name: postgres-data
        emptyDir: {}
      - name: cockroach-backup
        hostPath:
          path: /cockroach-backup 
          type: Directory
      - name: pg-dump
        emptyDir: {}
      serviceAccountName: crdb-to-postgres-migration

After the whole process finish you can export the pg_dump and run it in another postgres database useful for development

1- Copy pg_dump to local PC
kubectl cp pod-name:/pg_dump/lynx_dump_$(date +%Y%m%d).sql /Users/user/lynx_dump_$(date +%Y%m%d).sql -c export-pg-dump -n crdb-to-postgres

2- Create Database in postgres
createdb lynx_core_prod
psql -U root -d lynx_core_prod < lynx_dump_20241217.sql

DEV Community: Gerson Morales

Implementing AWS S3 Cross-Account Replication.

Requirements

Scenario

Folder Structure

Terraform files [Module]

Terraform files [Source Bucket]

Terraform files [Destination Bucket]

Automated RDS PostgreSQL Restoration Using GitHub Action🐘

Infrastructure

Scenario

AWS IAM configuration

Trusted entities

Trusted entities

Github workflow configuration

Automated RDS PostgreSQL 🐘Restoration Using GitHub Actions Workflow_Dispatch

Infrastructure

Scenario

AWS IAM configuration

Trusted entities

Trusted entities

Github workflow configuration

Configure IRSA using EKS to access S3 from a POD in terraform

Requirements

Scope

Initial steps

OSX

Windows

Linux

Terraform module configuration

Terraform code that would set variables to call the module

Kubernetes

EKS Resources

Setting up IAM Anywhere using terraform

Requirements

Scope

Initial steps

Terraform module configuration

Terraform code that would set variables to call the module

Migrate 🪳Coackroach DB into Postgres🐘

Infrastructure

Important Details

CRDB Backup Preparation

Restore CRDB backup into a local CRDB database on your Computer.

Prepare CRDB to be migrated into Postgres

Run Postgres Database local on your computer

Connect local CRDB and Postgres together

Lets make things more interesting and do the same process but now in Kubernetes

Requirements

Start Minikube with --mount-string flag pointing to where you have the CRDB backup /Users/user/cockroach-data/extern/backup

Let's create k8s manifests

RBAC

Deployment

After the whole process finish you can export the pg_dump and run it in another postgres database useful for development

Congratulations you were able to migrate CRDB🪳 database into Postgres🐘 DB :)Pura Vida!

Migrate Coackroach DB to Postgres

Infrastructure

Important Details

CRDB Backup Preparation

Restore CRDB backup into a local CRDB database on your Computer.

Prepare CRDB to be migrated into Postgres

Run Postgres Database local on your computer

Connect local CRDB and Postgres together

Lets make things more interesting and do the same process but now in Kubernetes

Requirements

Start Minikube with --mount-string flag pointing to where you have the CRDB backup /Users/user/cockroach-data/extern/backup

Let's create k8s manifests

RBAC

Deployment

After the whole process finish you can export the pg_dump and run it in another postgres database useful for development

Congratulations you were able to migrate a Database in CRDB to Postgres! Pura Vida :)

`Initial steps`

`EKS Resources`

`Initial steps`

Start Minikube with `--mount-string` flag pointing to where you have the CRDB backup `/Users/user/cockroach-data/extern/backup`

Start Minikube with `--mount-string` flag pointing to where you have the CRDB backup `/Users/user/cockroach-data/extern/backup`