Codify SQL Review Policies with Bytebase API

Nov 12, 2024

Estimated: 40 mins

Bytebase is a database DevSecOps platform designed for developers, security, DBA, and platform engineering teams. While it offers an intuitive GUI for managing database schema changes and access control, some teams may want to integrate Bytebase into their existing DevOps platforms using the Bytebase API.

This tutorial will guide you through configuring SQL review rules using the Bytebase API. This approach allows you to manage SQL review rules as code within your repository, enabling DBAs or platform engineering teams to apply them to Bytebase as needed.

This tutorial code repository is at https://github.com/bytebase/api-example/tree/main/sql-review

Prerequisites#

Docker installed
Node.js >= v18

Start Bytebase#

Make sure your Docker daemon is running. Copy and paste the commands to start Bytebase.

docker run --rm --init \
  --name bytebase \
  --publish 8080:8080 --pull always \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:3.0.1

Bytebase is now running via Docker, and you can access it via localhost:8080. Register the first admin account which will be granted Workspace Admin.

Create Service Account#

Log in as the admin user, and go to Security & Policy > Users & Groups. Click + Add User, fill in with api-example, choose the DBA role that is sufficient for this tutorial and click Confirm.
Find the newly created service account and click on Copy Service Key. We will use this token to authenticate the API calls.

Obtain the Access Token#

Go to Bytebase API Example repo and clone it.

Go to subfolder sql-review, and follow the instructions in the README.md to run the scripts. replace the bytebase_url, bytebase_account, bytebase_password with your own values. Then you will get a bytebase_token looks like ey....9V8s.

export bytebase_url=http://localhost:8080
export bytebase_account=api-sample@service.bytebase.com
export bytebase_password=bbs_xxxxxxxxxxxxxilcLVG
bytebase_token=$(curl -v ${bytebase_url}/v1/auth/login \
   --data-raw '{"email":"'${bytebase_account}'","password":"'${bytebase_password}'","web":true}' \
   --compressed 2>&1 | grep token | grep -o 'access-token=[^;]*;' | grep -o '[^;]*' | sed 's/access-token=//g; s/;//g')
echo $bytebase_token

Configure SQL Review Policies#

Continue following the README.md to run the scripts.

curl --request PATCH ${bytebase_url}/v1/reviewConfigs/basic \
   --header 'Authorization: Bearer '${bytebase_token} \
   --data @policy/basic.json

curl --request PATCH ${bytebase_url}/v1/reviewConfigs/advanced \
   --header 'Authorization: Bearer '${bytebase_token} \
   --data @policy/advanced.json

In the Bytebase console, navigate to CI/CD > SQL Review to see the applied SQL review rules. You may click Edit to change the rules.

To delete the SQL review rules, use the following commands:

curl --request PATCH "${bytebase_url}/v1/reviewConfigs/basic?allow_missing=true&update_mask=rules" \
   --header 'Authorization: Bearer '${bytebase_token} \
   --data @policy/basic.json

curl --request PATCH "${bytebase_url}/v1/reviewConfigs/advanced?allow_missing=true&update_mask=rules" \
   --header 'Authorization: Bearer '${bytebase_token} \
   --data @policy/advanced.json

Attach SQL Review Policies to Resources#

You may notice that the SQL review rules are not applied to any resources yet from the above screenshot. In Bytebase, the SQL review rules can be applied to the environments or projects. Project-level rules take precedence over environment-level rules.

Follow the README.md to run the scripts to apply the SQL review rules to environments.

curl --request PATCH "${bytebase_url}/v1/environments/test/policies/tag?allow_missing=true&update_mask=payload" \
   --header 'Authorization: Bearer '${bytebase_token} \
   --data @binding/environments/test.json

curl --request PATCH "${bytebase_url}/v1/environments/prod/policies/tag?allow_missing=true&update_mask=payload" \
   --header 'Authorization: Bearer '${bytebase_token} \
   --data @binding/environments/prod.json

Continue with the README.md to apply the SQL review rules to projects.

curl --request PATCH "${bytebase_url}/v1/projects/project-sample/policies/tag?allow_missing=true&update_mask=payload" \
   --header 'Authorization: Bearer '${bytebase_token} \
   --data @binding/projects/project-sample.json

On the CI/CD > SQL Review page, you will see the SQL review rules are applied to environments and projects.
Go to Environments page, click Test environment to see the applied SQL review rules.
Go to Sample Project page, click Setting on the left sidebar to see the applied the SQL review rules.

To detach SQL review policies from environments and projects, use the following commands:

curl --request DELETE ${bytebase_url}/v1/environments/test/policies/tag \
   --header 'Authorization: Bearer '${bytebase_token}

curl --request DELETE ${bytebase_url}/v1/environments/prod/policies/tag \
   --header 'Authorization: Bearer '${bytebase_token}

curl --request DELETE ${bytebase_url}/v1/projects/project-sample/policies/tag \
   --header 'Authorization: Bearer '${bytebase_token}

Summary#

Congratulations! You can now codify SQL review rules using the Bytebase API, in addition to the Bytebase GUI, making SQL review policy as code a reality.

Edit this page on GitHub