Question

Deploy Changes to Database Cluster

Posted October 16, 2020 577 views
CI/CDDatabasesDigitalOcean Managed PostgreSQL Database

Hello,

I have a CI/CD pipeline running in BitBucket. I want to create a Database Cluster on DigitalOcean for my API, so I can migrate to the API to a Kubernetes Cluster. How can I setup my pipeline to be able to deploy changes to my database? The Database Cluster restricts connections to a specific IP address so I cannot just connect from the pipeline. Is there a way to permit a connection with a specific ssh key maybe? I am not how this is normally done.

Thank you!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
1 answer

I guess I could purchase an additional single droplet dedicated to deploying database changes and approve that one droplet access to the database cluster. I feel like that is a weird route to go though. Is there another method?

  • Hi, did you find a clean solution? Facing the same issue and it looks like it should be possible somehow…

    • No, I have not come up with a cleaner solution. My app is still being developed. We are planning for our first deployment sometime early next year and so I have not really addressed this quite yet. Haven’t even made the switch to Kubernetes yet. As of now, the plan is to utilize a single Digital Ocean node to handle things like this. It will be separate from the cluster, wont host anything, just dedicated to handling database migrations. That is until a cleaner solution comes to light. Please keep me updated if you come up with one.

      • Looks like DO API now can manage DB list (https://developers.digitalocean.com/documentation/changelog/api-v2/managed-databases-firewall-and-sql-mode-management/). I have an idea to add an IP of a CI agent (found an action for GitHub Actions here https://github.com/marketplace/actions/public-ip) via this API and remove it right after the migration is completed.

        Don’t know if it is gonna work and if there any problems from a security perspective but will give it a try.

      • Ok, I come up with the following solution that works. Will keep it for now, probably you (or someone else) can see any problems with it. We use Github Actions so the following config is for it but it doesn’t matter much:

        ...
              - name: Deploy to DigitalOcean K8s cluster
                run: kubectl apply -f ./k8s
        
              - name: Get deployment status
                run: kubectl rollout status deploy/pp-app-auth
        
              - name: Get agent IP address
                id: ip
                uses: haythem/public-ip@v1.2
        
              - name: Get database firewall rules
                run: |
                  INITIAL_FIREWALL_LIST=$(curl -X GET -H "Content-Type: application/json" -H "Authorization: Bearer ${{ secrets.DO_CI_PERSONAL_ACCESS_TOKEN }}" "https://api.digitalocean.com/v2/databases/${{ secrets.DO_DATABASE_ID }}/firewall")
                  echo "INITIAL_FIREWALL_LIST=$INITIAL_FIREWALL_LIST" >> $GITHUB_ENV
        
              - name: Modify IPs list
                run: |
                  UPDATED_FIREWALL_LIST=$(jq -r -c '.rules += [{"type": "ip_addr", "value": "${{ steps.ip.outputs.ipv4 }}"}]' <<< '${{ env.INITIAL_FIREWALL_LIST }}')
                  echo "UPDATED_FIREWALL_LIST=$UPDATED_FIREWALL_LIST" >> $GITHUB_ENV
        
              - name: Submit updated IPs list to DO
                run: |
                  echo ${{ env.UPDATED_FIREWALL_LIST }}
                  curl -X PUT -H "Content-Type: application/json" -H "Authorization: Bearer ${{ secrets.DO_ACCESS_TOKEN }}" -d '${{ env.UPDATED_FIREWALL_LIST }}' "https://api.digitalocean.com/v2/databases/${{ secrets.DO_DATABASE_ID }}/firewall"
        
              - name: Install Python 3.6
                uses: actions/setup-python@v2
                with:
                  python-version: '3.6'
        
              - name: Install requirements
                run: |
                  python -m pip install -r requirements.txt
        
              - name: Run migration
                run: |
                  PYTHONPATH=.:$PYTHONPATH alembic upgrade head
        
              - name: Revert IPs list
                if: ${{ always() }}
                run: |
                  curl -X PUT -H "Content-Type: application/json" -H "Authorization: Bearer ${{ secrets.DO_ACCESS_TOKEN }}" -d '${{ env.INITIAL_FIREWALL_LIST }}' "https://api.digitalocean.com/v2/databases/${{ secrets.DO_DATABASE_ID }}/firewall"
        
        

        Some feedback would be appreciated to improve the solution. One thing I need to check and improve is to cancel the following step if deployment status is “failed” but that’s another story.

        • Wow, this is a very impressive solution! I’m on mobile so I will take another look when I get home, but it looks really clean. I think it’s much better than mine (purchasing an extra droplet and managing/configuring that just for migrations). I wasn’t even aware you could change database configurations via their API. I’ll most likely be taking this solution! Good job and thanks!!

          • Found one potential problem (especially for not self-hosted CI solution). If we run two or more deployments with migrations at the same time we may end up with a situation where the initial list of IPs for the last deployment contains CI agent’s IP that was saved there from the first one. In that case, after the last deployment is finished it will save that IP and it will be stored there forever. Looks like a potential security leak since, I suppose, the cloud CI IPs list is used across everybody. I ended up just saving JSON with allowed IPs in a secret and use that secret on reset rather than getting that list from DO every time.

            P.S. Even in that case we have a period of time during deployment when db can be potentially accessed from shared IP which in theory (e.g. the more deployments we have the more this period is) can be used. I think it’s fine since besides the IP we have db authentication. Self-hosted CI solution with static IP would be perfect :)

        • I can’t reply to your last comment. I think the thread hit max depth so I am replying here.

          That is a good point though. You have two potential issues, it either stores the IP forever, or removes it in the middle of the migration. Not sure how postgre handles that kind of security. Might block the migration from finishing and you end up with part of the update.

          Are you removing the list of authenticated IPs at the end of every deployment? That may break another deployment if it is past the step that grants access to that list.

          I am a bit worried about this solution now. As of now, we only have one deployment pipeline, but plan on migrating to a microservice architecture, which could significantly increase the number of potential concurrent deployments.

          I wish we could just have a dedicated IP for CI. That would solve everything. I am confused how this is not a bigger issue. I feel like this is a common problem people would face.

          I agree though, granting access to that list of IPs is not a big security list due to db authentication. Maybe we just permanently authenticate their list of IPs. Unless that list of IPs change often, which they might, I don’t see that as a bad solution. I have to this about this more. :/

          • Yes, exactly, you are correct. I have already faced that issue while deploying two services with migrations at the same time. Since we are currently in the launch phase as well, that is fine to deploy only one service at a time so I didn’t think about how to solve this issue.

            Personally, I don’t like the idea to add all public CI IPs range to the white list. To have another droplet, as you suggested before, looks like a better alternative to me. The only problem I see here is that we separate that logic from CI process (e.g. how to cancel migration if deployment failed?). Looks like we need an extra layer of abstraction in order to sync these processes, which makes the whole solution more complicated. Probably there is an easy way to do it, don’t know.

            For now, I would stick to the above solution with just one deployment at a time and probably will fix the issue with some outer script that resets IPs white list when no deployments are running. For the future will aim to have a static IP for CI solution which looks the clearest way to deal with it.