Find all repositories with unprotected default branches in GitHub

An unprotected default branch on GitHub can be a gateway for accidental or malicious changes, leading to potential vulnerabilities. Using the CloudQuery GitHub plugin to sync your data to a PostgreSQL destination allows us to identify unprotected branches and continuously monitor for any new unprotected branches that may be created. After syncing data by following the instructions here, we can write a SQL query to find all the repositories in our organization that have unprotected default branches:

SELECT
    repo.full_name,
    pushed_at,
    private,
    branch.protected,
    archived
 FROM github_repositories repo
 LEFT JOIN
    github_repository_branches branch
    ON branch.repository_id = repo.id
    AND repo.default_branch = branch.name
 WHERE
    branch.protected = FALSE
    AND archived != true
ORDER BY repo.name ASC;

This query lists all the repositories with unprotected default branches, filtering out any archived repositories. It also outputs when the last push to the repository happened, so we can gauge the importance and activity:

As we change the settings on the affected repositories to enable branch protection, we can re-run the CloudQuery sync and query to track our progress over time, and continuously monitor for any new repositories that have unprotected branches.

This is just one example of what's possible, there's a lot more we can do with all our GitHub data synced to a database. Check out the GitHub source plugin documentation for a full list of all the available tables and more tips!