CI/CD integration - pgroles docs

pgroles integrates into CI/CD pipelines as a drift gate, a deployment step, or both.

For Cloud SQL or RDS connectivity setup (auth proxies, VPC access, IAM authentication), see the Google Cloud SQL or AWS RDS guides.

Drift detection

pgroles diff --exit-code returns specific exit codes for automation:

Exit code	Meaning
`0`	Database is in sync with the manifest
`2`	Drift detected — roles, grants, or memberships differ
Other	Command or connectivity failure

GitHub Actions

These examples use the published Docker image, which requires no toolchain installation. Your runner needs network access to the database — see the platform guides linked above if you need to set up a proxy or VPN.

Drift check on PRs

jobs:
  drift-check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Check for drift
        run: |
          docker run --rm \
            -e DATABASE_URL="${{ secrets.DATABASE_URL }}" \
            -v "${{ github.workspace }}:/work" \
            ghcr.io/hardbyte/pgroles:latest \
            diff -f /work/pgroles.yaml --exit-code

Apply on merge

jobs:
  apply-roles:
    if: github.ref == 'refs/heads/main'
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Apply roles
        run: |
          docker run --rm \
            -e DATABASE_URL="${{ secrets.DATABASE_URL }}" \
            -v "${{ github.workspace }}:/work" \
            ghcr.io/hardbyte/pgroles:latest \
            apply -f /work/pgroles.yaml

Diff as a PR comment

Post the planned SQL changes as a PR comment for review:

      - name: Generate diff
        id: diff
        run: |
          OUTPUT=$(docker run --rm \
            -e DATABASE_URL="${{ secrets.DATABASE_URL }}" \
            -v "${{ github.workspace }}:/work" \
            ghcr.io/hardbyte/pgroles:latest \
            diff -f /work/pgroles.yaml 2>&1) || true
          echo "diff<<EOF" >> "$GITHUB_OUTPUT"
          echo "$OUTPUT" >> "$GITHUB_OUTPUT"
          echo "EOF" >> "$GITHUB_OUTPUT"

      - name: Comment on PR
        if: github.event_name == 'pull_request'
        uses: actions/github-script@v7
        with:
          script: |
            const diff = `${{ steps.diff.outputs.diff }}`;
            if (diff.trim()) {
              await github.rest.issues.createComment({
                owner: context.repo.owner,
                repo: context.repo.repo,
                issue_number: context.issue.number,
                body: `### pgroles diff\n\`\`\`sql\n${diff}\n\`\`\``
              });
            }

Using cargo install

If you prefer installing from source instead of Docker, add a Rust toolchain step first:

      - uses: dtolnay/rust-toolchain@stable
      - run: cargo install pgroles-cli
      - run: pgroles diff -f pgroles.yaml --exit-code
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

GitLab CI

The published Docker image is a static binary with no shell, so it can't be used directly as a GitLab CI image: (which requires /bin/sh to run script: blocks). Use a Rust image and install from source:

drift-check:
  image: rust:latest
  script:
    - cargo install pgroles-cli
    - pgroles diff -f pgroles.yaml --exit-code
  variables:
    DATABASE_URL: $DATABASE_URL

Output formats

pgroles diff supports multiple output formats:

# Raw SQL (default) — human-readable, good for PR comments
pgroles diff -f pgroles.yaml

# JSON — machine-readable, good for programmatic processing
pgroles diff -f pgroles.yaml --format json

# Summary — high-level change counts
pgroles diff -f pgroles.yaml --format summary

Multiple environments

Use the same manifest against different databases, or maintain separate manifests:

# Same manifest, different targets
pgroles apply -f pgroles.yaml --database-url "$STAGING_DATABASE_URL"
pgroles apply -f pgroles.yaml --database-url "$PROD_DATABASE_URL"