github
solutions

Find all repositories with unprotected default branches in GitHub

Herman Schaaf

Herman Schaaf

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:
full_namepushed_atprivateprotectedarchived
cq-demo/unicorn-rainbow-pizza2024-06-06 13:20:21FalseFalseFalse
cq-demo/dancing-platypus2023-11-16 14:46:37TrueFalseFalse
cq-demo/invisible-taco2023-07-19 08:00:47TrueFalseFalse
cq-demo/sparkly-donut-ui2024-08-02 14:53:51FalseFalseFalse
cq-demo/ninja-kitten-analytics2024-05-22 17:15:08TrueFalseFalse
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!
Herman Schaaf

Written by Herman Schaaf

Herman is the Director of Engineering at CloudQuery and an Apache Arrow contributor. A polyglot with a preference for Go and Python, he has spoken at QCon London and Data Council New York.

Turn cloud chaos into clarity

Find out how CloudQuery can help you get clarity from a chaotic cloud environment with a personalized conversation and demo.

Join our mailing list

Subscribe to our newsletter to make sure you don't miss any updates.

Legal

© 2024 CloudQuery, Inc. All rights reserved.

We use tracking cookies to understand how you use the product and help us improve it. Please accept cookies to help us improve. You can always opt out later via the link in the footer.