Storage

RDS DBs

RDS DB Consumption and Authentication

In order to create and migrate a DB from RDS include the following manifests & kustomize patches:

First, you will need a Job that creates a DB instance using the RDS Master Credentials and also assign your Application specific DB credentials proper permissions:

# applications/internal/passport/base/db-migrator.yaml
apiVersion: batch/v1
kind: Job
metadata:
  name: db-bootstrap
  annotations:
    argocd.argoproj.io/hook: PreSync
    argocd.argoproj.io/hook-delete-policy: HookSucceeded
spec:
  backoffLimit: 1
  ttlSecondsAfterFinished: 300
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: bootstrap
          image: postgres:16
          env:
            - name: PGDATABASE
              value: postgres
          envFrom:
            - secretRef:
                name: secrets
          command:
            - bash
            - -ceu
            - |
              echo "Bootstrapping $DB_NAME for user $DB_USER..."

              # Check if database exists
              if ! psql -h "$PGHOST" -U "$PGUSER" -d "$PGDATABASE" -tAc "SELECT 1 FROM pg_database WHERE datname='$DB_NAME'" | grep -q 1; then
                echo "Creating database $DB_NAME..."
                psql -h "$PGHOST" -U "$PGUSER" -d "$PGDATABASE" -c "CREATE DATABASE \"$DB_NAME\""
              else
                echo "Database $DB_NAME already exists."
              fi

              # Now create or update the user and grant privileges
              psql -h "$PGHOST" -U "$PGUSER" -d "$DB_NAME" <<SQL
              DO \$\$
              BEGIN
                IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '$DB_USER') THEN
                  EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', '$DB_USER', '$DB_PASSWORD');
                ELSE
                  EXECUTE format('ALTER ROLE %I WITH LOGIN PASSWORD %L', '$DB_USER', '$DB_PASSWORD');
                END IF;
              END
              \$\$;

              GRANT ALL PRIVILEGES ON DATABASE "$DB_NAME" TO "$DB_USER";

              -- Set up default privileges BEFORE ownership transfer
              ALTER DEFAULT PRIVILEGES FOR ROLE "$DB_USER" IN SCHEMA public
                GRANT ALL ON TABLES TO "$DB_USER";
              ALTER DEFAULT PRIVILEGES FOR ROLE "$DB_USER" IN SCHEMA public
                GRANT ALL ON SEQUENCES TO "$DB_USER";

              -- Now hand over ownership
              ALTER DATABASE "$DB_NAME" OWNER TO "$DB_USER";

              SQL

              echo "Bootstrap for $DB_NAME completed successfully."

Then, in your main kustomization.yam include the following patches:

patches:
  # db-master-creds are used to create the DB instance
  - target:
      kind: ExternalSecret
      name: secrets
    patch: |
      - op: add
        path: /spec/dataFrom
        value:
          - extract:
              key: constellation/<env>/rds_credentials
          - extract:
              key: constellation/<app-name>/<env>