How to Use PostgreSQL SSL certificates in GitHub Actions
If youāve ever connected to a PostgreSQL database that required an SSL certificate, youāll have probably downloaded the certificate and added it to you key chain, or provided a path to the .pem
file in the connection credentials or string.
In this post iāll explain a scenario where I couldnāt use either of the above methods, because my repository was public, I didnāt want to upload the .pem
file to reference it via an absolute URL, and my PostgreSQL connection was running as part of a GitHub Action.
SSL Root Cert
One way to use an SSL certificate is to download it and then reference the file name at the end of the connection string. E.g.
postgresql://postgres:123@abc.com:5432/postgres?sslrootcert=/my-ssl-cert.pem
This would work if my-ssl-cert.pem
lived at the root of a projectās directory however, as mentioned, for my requirements I couldnāt upload, or store the .pem
file anywhere because my repository was public.
My solution was to convert the SSL certificate into a base64 string and store it as an environment variable / GitHub Secret.
Covert SSL certificate to base64 string
By running the following in my terminal Iām able to convert the SSL certificate into a base64 string.
cat my-ssl-cert.pem | base64
Hereās what my terminal output looks like.
Add SSL certificate to GitHub Secrets
Now that I have a base64 string I can save it as a GitHub Secret by navigating to Settings > Secrets and variables > Actions, then scroll down to Repository secrets in the GitHub UI. From here I added a new environment variable named SSL_CERT_BASE64
and pasted the output from my terminal.
I can now reference the secret using secrets.SSL_CERT_BASE64
from within the GitHub Action. In the below workflow youāll see a step named Decode SSL Cert
which reads the value of secrets.SSL_CERT_BASE64
and decodes the base64 string and temporarily stores the file, my-ssl-cert.pem
, in memory. The name of this ātemporaryā file is what Iāve added on to the end my connection string (as shown above), and also saved that as a GitHub Secret named DATABASE_URL
Hereās an example of a GitHub Action which decodes the SSL certificate, installs PostgreSQL and then logs out (using echo) the result of the following query, SELECT VERSION();
.
// .github/workflows/test-action.yml
name: Test Action
on:
workflow_dispatch: # Trigger workflow manually
env:
DATABASE_URL: ${{ secrets.DATABASE_URL }}
SSL_CERT_BASE64: ${{ secrets.SSL_CERT_BASE64 }}
PG_VERSION: 16
jobs:
dump-and-restore:
runs-on: ubuntu-latest
steps:
- name: Decode SSL Cert
run: |
echo "${{ secrets.SSL_CERT_BASE64 }}" | base64 --decode > my-ssl-cert.pem
- name: Install PostgreSQL
run: |
sudo apt update
yes '' | sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-${{ env.PG_VERSION }}
- name: Database Query
run: |
echo "database_name=$(/usr/lib/postgresql/${{ env.PG_VERSION }}/bin/psql "${{ env.DATABASE_URL }}" -t -c "SELECT VERSION();")"
Finished
Naturally youāll probably want to do more than simply query your database but hopefully this will demonstrates how you can use an SSL certificate stored as a base64 string using GitHub Secrets.
Iām not sure if this is the best, or even the only way to solve this problem but it worked for me. If you know of any other ways to do this, please let me know: @PaulieScanlon.