Dev.to

Automated RDS PostgreSQL Restoration Using GitHub Action

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. I created this document to configure IRSAhttps://dev.to/gerson_morales_3e89188d50/configure-irsa-using-eks-to-access-s3-from-a-pod-513a but 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::", "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"

May 16, 2025 - 18:44
 0
Automated RDS PostgreSQL Restoration Using GitHub Action

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

  1. EKS cluster.
  2. RDS database running in AWS (Just for this case scenario).
  3. Github Repository.
  4. AWS IAM service.
  5. 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.

I created this document to configure IRSAhttps://dev.to/gerson_morales_3e89188d50/configure-irsa-using-eks-to-access-s3-from-a-pod-513a but 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::",
                    "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

  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:
Read More

Tags:

Previous Article

Pattern presence verification using strings

Next Article

Closures and the Scope Chain in JavaScript

Related Posts

Hackers Love Lazy People — Here’s Why!

Hackers Love Lazy People — Here’s Why!

May 12, 2025  0

Kotlin Multiplatform: From Early Challenges to Stability in Cross-Platform Development

Kotlin Multiplatform: From Early Challenges to Stabilit...

May 10, 2025  0

Understanding Nested Describe Blocks in Unit Tests

Understanding Nested Describe Blocks in Unit Tests

May 16, 2025  0