DEV Community: Adela

From Zero to First Migration: How Much Setup Do Flyway, Liquibase, and Bytebase Require?

Adela — Mon, 24 Aug 2026 09:46:59 +0000

Starting with a fresh PostgreSQL database, what does it actually take a beginner to complete their first managed schema migration?

Short answer: Flyway Desktop and Bytebase Cloud got there in about half an hour each; Liquibase Community CLI took about an hour and a half, not because of its migration engine but because its beginner guide sent me to the wrong Java version and skipped a required PostgreSQL driver.

For this experiment I started as a new user with Flyway, Liquibase, and Bytebase, applied the same SQL change to three fresh databases hosted on Neon, and recorded what happened along the way.

One methodology choice shapes everything below: I let each vendor pick the on-ramp. Rather than forcing the three products into the same shape, I took whatever path their own beginner documentation recommends.

This is Flyway Desktop rather than the Flyway CLI — Redgate's Get Started guide steers new users to the desktop app.
This is Bytebase Cloud rather than self-hosted Docker — the Bytebase quickstart presents Cloud first, as the path with no installation.
Liquibase is the Community CLI, the easiest entry point its documentation offers.

	Flyway Desktop	Liquibase Community CLI	Bytebase Cloud
Vendor's recommended on-ramp	Desktop app	CLI	Cloud, no install
Installed locally	Desktop app, 663 MB	JDK 17+, CLI, JDBC driver	nothing
Account required	Redgate ID + email confirmation	none	Bytebase Cloud sign-in
Times the guide did not match reality	0	3	0
Times I left the guide to find an answer	0	2	0
Rough time to first migration	~30 minutes	~1.5 hours	~30 minutes

A disclosure: I work at Bytebase, so it's the one product here I already knew going in — for Flyway and Liquibase I was starting from zero. I controlled for that by following each vendor's own quickstart to the letter rather than improvising. The times above are approximate, not stopwatch measurements — read them as orders of magnitude, not a benchmark; the countable rows are the more reliable comparison.

Official guides followed

Rather than design an idealized workflow for each product, I started from the official beginner documentation available at the time of the test and adapted only the connection details and the SQL, so all three runs used Neon PostgreSQL and the same schema change.

Tool	Official getting-started documentation followed	How it was adapted for this test
Flyway Desktop	Quickstart — Flyway Desktop	The guide's sample database and SQL were replaced with the Neon `flyway_demo` database and the shared `CREATE TABLE users` migration.
Liquibase Community CLI	Install Liquibase and Introduction to Liquibase	I followed the installation and introductory project flow, then connected the generated project to the Neon `liquibase_demo` database and used a formatted SQL changeset.
Bytebase Cloud	Step 1: Start Bytebase, which opens the step-by-step quickstart by offering Bytebase Cloud and self-hosted Docker side by side	I took the Cloud path the page presents first, connected the Neon instance, and applied the shared SQL to `bytebase_demo`.

Worth flagging: Flyway also ships a free CLI that needs no Redgate account, and Bytebase also self-hosts with Docker, which would have added deployment time. I followed each vendor's own beginner path either way.

Liquibase also sent me to two supplemental pages: Connect Liquibase with PostgreSQL when the driver turned out to be missing, and the 5.0.3 system requirements when the Java runtime became an issue.

The setup

Each tool got its own empty Neon PostgreSQL database — flyway_demo, liquibase_demo, and bytebase_demo — with the host, port, credentials, and database name saved in advance. Neon requires SSL, so every connection had to be configured for it: a small tax paid equally by all three, and one a local PostgreSQL instance would not have charged.

Each database then received exactly the same migration:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The finish line is the same too: the migration succeeds, the users table exists, and the tool shows a record of the applied change.

Flyway Desktop: install, connect, migrate

I followed Redgate's Quickstart — Flyway Desktop, replacing its sample database and SQL with Neon and my shared migration.

Installing and creating the project

I downloaded Flyway Desktop — 663 MB for the macOS Arm64 build, the single largest download in this experiment — and installed it. On first launch, the application asked me to sign in with a Redgate ID. Because I was starting from zero, this also meant registering an account, confirming my email address, signing in again, and choosing between Community and an Enterprise trial. I selected Flyway Community — free to use.

Redgate asking me to confirm my email address
Flyway Desktop asking me to choose between Community and an Enterprise trial

After signing in, I landed on the project screen. I selected New project, chose PostgreSQL as the database type, and created a local flyway-demo project.

Flyway Desktop new-project screen

Connecting Neon

I added the Neon PostgreSQL database as the target environment, entered the connection details for flyway_demo, and tested the connection.

Flyway Desktop successfully connecting to the Neon target database, with credentials redacted

Adding the migration

I created a versioned migration and pasted in the shared SQL. Flyway Desktop generated a timestamp-based version, and I used create_table_users as the description.

Creating the versioned migration in Flyway Desktop

The version prefix gives Flyway the order in which migrations should run, while the description makes the change recognizable in history.

Running Migrate

After I saved the file, Flyway showed one pending migration against the Neon Flyway Demo target.

The new Flyway migration in Pending state

I selected Migrate and clicked Run migrate. Flyway applied the migration and its state moved from Pending to Success (466 ms recorded for the SQL itself).

What I saw at the finish line

The migration was marked successful and appeared in the migration history, with a managed record of the timestamp-based version being applied.

Flyway Desktop showing the migration as successful

I then opened Neon to verify the database state independently. The users table was present, and flyway_schema_history contained the versioned migration with the description create table users.

Neon showing the users table and Flyway schema history

The flyway_schema_history table stores the migration version, description, script, checksum, installation time, execution time, and success status.

The structure of the Flyway schema history table in Neon

Rough time from download to visible history: about 30 minutes. Nothing in the guide contradicted what I saw, and I never had to leave it to find an answer. Redgate's quickstart estimates five minutes, and the configure-and-migrate steps it actually covers are about that fast. The rest of my half hour went to the 663 MB download, the account registration, and the email confirmation — none of which the estimate counts.

Beginner takeaway

Flyway Desktop keeps the main workflow visual: project, database target, migration file, and execution history. The user still works with a conventional versioned SQL file, but the desktop interface makes the target and result easy to inspect.

Liquibase Community CLI: three detours before the first update

I started with Liquibase's Install Liquibase and Introduction to Liquibase guides. On paper the path looked straightforward: install Liquibase, initialize a project, configure the connection, run the first update. My 5.0.3 run was not that linear — it became the most troubleshooting-heavy of the three.

The documentation summarizes the standard workflow as changesets inside a changelog, deployed with update, with DATABASECHANGELOG and DATABASECHANGELOGLOCK tracking the result. Here is the sequence I actually experienced:

Point in the run	What I expected	What happened
Java setup	Follow the Java link and continue	The getting-started path led to Java 8, but Liquibase 5.0.3 requires Java 17 or later — so I installed a JDK twice.
Selecting the runtime	The newly installed JDK 17 would be used automatically	My shell still used the older Java installation, so I had to set `JAVA_HOME`.
Project initialization	The generated files would match the documented example	My 5.0.3 run produced only `example-changelog.sql` and `liquibase.properties`.
First PostgreSQL connection	Liquibase would connect after I supplied the JDBC URL and credentials	The PostgreSQL driver was missing.
Driver installation	—	I found the PostgreSQL-specific guide and ran `liquibase lpm add postgresql`.
Migration	`liquibase update` would apply the changeset	After resolving the earlier issues, the update succeeded.

First obstacle: which Java version did I need?

The getting-started path pointed me to a Java 8 download — jre-8u501-macosx-aarch64.dmg, 93.2 MB. After installing it, I found it was not compatible with the Liquibase version I was running: the 5.0.3 system requirements put the minimum at Java 17.

So the first Java install was wasted. I went back and downloaded a second JDK, this time 17. Even after installing it, java -version still reported the old runtime — my shell had not picked up the new installation. I had to point JAVA_HOME at the JDK 17 location and put its bin directory first on PATH — on macOS:

export JAVA_HOME=$(/usr/libexec/java_home -v 17)
export PATH="$JAVA_HOME/bin:$PATH"

With that, Liquibase could finally start on the expected runtime.

Second surprise: the generated project looked different

I downloaded the Liquibase archive — liquibase-5.0.3.tar.gz, 8.3 MB — installed the Community CLI, and ran the project initialization flow from the introduction guide.

Initialization produced only two relevant files:

example-changelog.sql
liquibase.properties

That is a smaller project structure than the documentation showed, and I initially wondered whether initialization had failed. It had not: one file holds the SQL changelog, the other the connection and changelog configuration.

Configuring the Neon connection

I updated liquibase.properties with the changelog path and the JDBC connection for liquibase_demo:

changelog-file=example-changelog.sql
url=jdbc:postgresql://YOUR_NEON_HOST/liquibase_demo?sslmode=require
username=YOUR_NEON_USER
password=YOUR_NEON_PASSWORD

Third obstacle: the PostgreSQL driver was missing

My first connection attempt failed because Liquibase could not find the PostgreSQL JDBC driver. I left the introductory guide and found the database-specific Connect Liquibase with PostgreSQL page, which adds the driver through Liquibase Package Manager:

liquibase lpm add postgresql

That gave Liquibase what it needed to reach Neon — installing Liquibase itself had not installed what it takes to talk to PostgreSQL.

Adding the SQL changeset

I replaced the example content in example-changelog.sql with a formatted SQL changeset:

--liquibase formatted sql

--changeset demo:1
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The --changeset demo:1 line gives the change an author and identifier. Liquibase uses that identity, together with the changelog file path, to determine whether the changeset has already run.

The update finally succeeded

I checked the current state, inspected the SQL Liquibase planned to run, then applied it:

liquibase status
liquibase update-sql
liquibase update

This time it worked.

What I saw at the finish line

In Neon I could see all three tables together: users, plus the two management tables Liquibase creates, databasechangelog and databasechangeloglock. Inspecting databasechangelog showed the applied changeset and the fields Liquibase uses to track it — id, author, filename, dateexecuted, orderexecuted, and md5sum.

Neon showing the Liquibase tracking tables and the DATABASECHANGELOG structure

Rough time from download to confirmed changeset: about an hour and a half. Almost none of that was spent on migration work. The update command itself was instant; the time went into the Java version mismatch, the JAVA_HOME switch, the unexpected project layout, and the missing PostgreSQL driver.

Beginner takeaway

Liquibase exposes the moving parts clearly: the Java runtime, CLI, project files, JDBC driver, connection properties, and changeset metadata are all visible. But the beginner path did not prepare me for any of it — the guide sent me to Java 8 for a release that needs Java 17, and installing Liquibase did not install what it needed to talk to PostgreSQL. That's an onboarding problem, not an engine problem — though a new user has no way to tell the difference.

Bytebase Cloud: sign in, connect, plan, deploy

I followed Bytebase's step-by-step quickstart, whose first page offers Bytebase Cloud and self-hosted Docker side by side, and took the Cloud path it presents first. Because Neon was publicly reachable, I did not need to install Bytebase locally.

Signing in and connecting Neon

I signed in to Bytebase Cloud and created a workspace, then added a PostgreSQL instance using the Neon connection details, enabled SSL, and tested and saved the connection.

Connecting Bytebase Cloud to Neon and selecting bytebase_demo, with credentials redacted

Creating a project and transferring the database

I created a project, found bytebase_demo under the connected instance, and transferred it in.

Creating the plan

I created a new database-change plan, selected bytebase_demo, and entered the same SQL used for the other tools.

Selecting bytebase_demo as the target of the new plan

On save, Bytebase showed the target, the SQL statement, and the results of its built-in checks on the same plan page. Both checks passed, and the plan became ready for review.

The Bytebase plan containing the SQL migration and two successful checks

Reviewing and deploying

In my fresh workspace I had not configured a custom approval flow, so there was no manual approval step: the plan showed Review: Skipped, followed by the deployment task for bytebase_demo. I selected Run immediately, and it finished successfully.

Running the Bytebase deployment task immediately after review was skipped

What I saw at the finish line

I opened the database Changelog and found the recorded schema change. The users table existed in bytebase_demo, with the execution linked to the plan that introduced it.

The create table users entry in the Bytebase Changelog

I also verified the result directly in Neon, where bytebase_demo now contained the users table.

The users table in the Neon bytebase_demo database

Rough time from sign-in to visible changelog entry: about 30 minutes — effectively the same as Flyway Desktop. Skipping the install saved time, but the workspace, instance, project, and database-transfer steps spent it again.

Beginner takeaway

Bytebase Cloud skips local installation and presents the migration as a managed change request rather than a file plus a command. That means more vocabulary up front — projects, plans, review, deployment — in exchange for a review and deployment path that already exists by the time you need it. In a fresh workspace the review stage stays lightweight; the same structure supports stricter controls later.

Comparing the first PostgreSQL migration

Tool	Interface	Main artifacts	Setup encountered	Managed history	Rough time
Flyway Desktop	Desktop GUI	Versioned SQL migration	663 MB desktop installation, Redgate account, project, target connection	Migration history	~30 minutes
Liquibase Community	CLI	Formatted SQL changelog and properties	Java 17+, `JAVA_HOME`, CLI, PostgreSQL driver, JDBC configuration	`DATABASECHANGELOG`	~1.5 hours
Bytebase Cloud	Browser	Plan containing SQL	Account, workspace, instance connection, project, database transfer	Changelog	~30 minutes

Read these as onboarding observations, not speed rankings — a second attempt would be faster for every tool.

Flyway Desktop and Bytebase Cloud landed in the same range by different routes: Flyway spent its time on installation and an account, Bytebase on workspace and project setup. Neither had a step where the documentation misled me. Liquibase's gap is a different kind — the update command worked the first time I could actually run it, and the time went almost entirely into getting a runtime, a driver, and a project layout into the state its own guide assumed.

The more useful distinction is what each tool teaches during the first migration:

Flyway Desktop introduces versioned migration files through a visual project and target-database workflow.
Liquibase Community introduces changesets and database tracking through an explicit local CLI toolchain.
Bytebase Cloud introduces the change as a plan that moves through review, deployment, and centralized history.

All three apply and track the same change. What differs is where configuration lives, which dependencies the user has to manage, and how early review and collaboration appear.

What this exercise does not compare

This walkthrough does not evaluate:

migration performance or throughput;
rollback strategies;
drift detection;
complex or long-running schema changes;
team collaboration and permissions;
CI/CD integration;
policy enforcement across environments;
licensing or total cost of ownership.

Those topics require a broader scenario than a single CREATE TABLE statement.

Next up: from test to production

A first migration is a poor proxy for daily use. The interesting comparison is what happens when a change has to move from test to production: pull requests or approvals, automated checks, and a deployment history someone else can audit. That is where migration tooling stops being a quickstart exercise and becomes a team practice, and it is what I plan to look at next.

If you want a feature-by-feature look instead of a first-run diary, see Bytebase vs. Flyway, Bytebase vs. Liquibase, and Flyway vs. Liquibase.

Originally published on the Bytebase blog.

DBHub vs Bytebase MCP: which one to point your agent at

Adela — Thu, 13 Aug 2026 13:16:33 +0000

We maintain two database MCP servers, DBHub and the one built into Bytebase. People keep asking which to use, so: DBHub when you're the only one affected, Bytebase MCP when other people are.

Note that's not the same as dev vs prod. A read-only replica only you touch is fine on DBHub; a staging database loaded with a copy of real customer data isn't. What matters is who pays if the agent does the worst thing it can do.

The rest is one difference, and everything follows from it.

Shared account vs your account

DBHub takes a connection string. In practice that's a shared service account, usually the one your app already uses, and every agent pointed at it connects as that same account.

Bytebase MCP takes an OAuth login. The agent connects as you.

So when your agent runs a DELETE at 2am, DBHub's database logs app_user, same as every other query from everyone else sharing it. You know it happened, not who it happened for, and you can't revoke it for one person without revoking it for everybody. Bytebase logs it under your account.

DBHub

One command, no server:

npx @bytebase/dbhub@latest --transport stdio --dsn "postgres://user:pass@localhost:5432/mydb"

Two tools by default, run SQL and search the schema, which keeps your context window free for the actual problem. Postgres, MySQL, MariaDB, SQL Server and SQLite, several connections in one process. You can start it read-only, cap the rows, set a query timeout.

No identity, though. Read-only is a flag on the process, so it protects you from the agent, not from whoever started the process.

Bytebase MCP

Nothing to install. If you run Bytebase it's at /mcp, and your agent inherits your account, which already has rules on it: masked columns come back ******, schema changes open a review instead of running, and every call is logged under your name.

Worth saying plainly: it inherits all your permissions. Connecting as yourself only helps if yourself isn't an admin.

	DBHub	Bytebase MCP
Connects as	A shared account	You
Reads	As stored	Masked per policy
Writes	Run immediately	Open a review
Audit	Database login records	Every call, under your account
Needs	Nothing	A running Bytebase

The catches

DBHub has no per-person access to grant or revoke, no masking, and nothing between the agent and the table if writes are on.

Bytebase MCP needs Bytebase running with an external URL, and it's HTTP only. You can't dry-run SQL, since the checks happen when the change is created. Results cap at 100 rows by default, 1,000 max, 30 second timeout.

Picking

Local Postgres, a scratch copy, a replica, read-only poking at prod: DBHub. Real customer data, masked columns, writes someone should look at first: Bytebase MCP.

Both, honestly, is the normal answer. DBHub in your client config for local work, Bytebase MCP for anything shared. What to avoid is the middle, where a connection string that happens to reach production ends up in an MCP config because it was the fastest thing to paste.

🐘🤖 Top 3 Open Source Postgres MCP Servers in 2026

Adela — Tue, 28 Jul 2026 12:35:02 +0000

At Bytebase we work on database access control all day, so pointing an AI agent at a real database is not a hypothetical for me. Picking a Postgres MCP server is easy right up until the database on the other end holds something you actually care about. What runs fine against a throwaway local database behaves very differently in production. That split, trusted local development vs. production, is the lens I use here. Most of these servers are great at the first and quietly dangerous at the second.

Building one safely is harder than it looks. Anthropic's own reference Postgres server got archived after people noticed its "read-only" mode happily accepted COMMIT; DROP SCHEMA public CASCADE;. Read-only, except when it wasn't. So I care less about the feature list and more about two questions: what can the agent actually do, and can you prove afterward what it did. The three servers below answer those very differently.

pgconsole

pgconsole is the newest of the three. It is a self-hosted Postgres console, a web SQL editor first, that also hands its connections to agents over a built-in MCP endpoint. The important part: the agent never sees a connection string. It gets a token, and every call runs through the same access rules and audit log as a human clicking around the console. The agent talks to pgconsole, and pgconsole talks to the database.

Most database tools land at one of two extremes. Heavyweight platforms that need real infrastructure to stand up, or lightweight editors with no access controls at all. pgconsole sits in the awkward, useful middle: a Postgres editor with team controls built in, shipped as a single binary you run yourself.

The access control is the heart of it, and it treats agents as first-class principals instead of bolting them on after the fact with built-in delegation and attenuation.

Key Features:

One self-hosted binary. Connections, users, and access rules live in a versioned pgconsole.toml, GitOps-style, and you run it with npx or Docker. No separate database to babysit.
Fine-grained, default-deny access. Seven independent rights (read, write, DDL, admin, and more), scoped per connection, with nothing allowed unless a rule grants it. pgconsole parses every statement before it runs, so a destructive query cannot sneak through a read-only tool.
Agents as first-class identities. An agent authenticates as its own principal, or acts on behalf of a user. In delegated mode it inherits that person's permissions, capped lower and never higher, and it loses access the moment the user does.

The alice-claude agent acts for Alice, capped to read-only on a single connection:

[[agents]]
id = "alice-claude"
on_behalf_of = "alice@example.com"
permissions = ["read"]   # capped at or below Alice's own grants
connections = ["prod"]   # and scoped to a single connection

Best For: teams that want a self-hosted Postgres editor and a genuinely governed agent path (distinct identities, delegation, least-privilege caps) without standing up a whole platform.

One honest caveat: it is young. Around 120 stars, Apache-2.0, recent first releases.

Verdict: the access model is the reason to run it. Distinct agent identity, on-behalf-of delegation, and attenuation that holds an agent at or below the human it acts for, all enforced by a SQL parser rather than by trust. If you remember one design from this post, make it this one.

Supabase MCP

Supabase MCP manages a whole Supabase project: auth, storage, edge functions, branches, not just the Postgres database underneath. That breadth is both the appeal and the cage. It only works on Supabase.

It is a first-party server from Supabase, the open-source Firebase alternative, and it inherits the company's instinct to treat everything as one managed surface. That is why it reaches well past plain SQL into auth, storage, and functions.

Key Features:

Wide surface: 29 tools across eight feature groups (Account, Database, Debugging, Development, Edge Functions, Branching, Docs, Storage), covering migrations, TypeScript type generation, and serverless functions beyond plain SQL.
Hosted, with OAuth 2.1 and automatic token refresh, so no local server, binaries, or hardcoded credentials.
Stronger security defaults than most: project scoping, feature-group restriction, and a dedicated read-only role (supabase_read_only_user).

Best For: teams already on Supabase who want their agent to manage the project, not just query it.

Verdict: excellent if Supabase is your platform, irrelevant otherwise. Two things to know: the default tool set is heavy (~19.3k tokens, though selective loading cuts it to ~4.2k), and the Management API endpoint behind execute_sql is still beta with no row limits. More guardrails than most. But like every server here, it mitigates prompt injection, it does not solve it.

Postgres MCP Pro

Postgres MCP Pro, from Crystal DBA, is usually the first result when you search for a Postgres MCP server. It is host-neutral, point it at any Postgres, and it goes further than most. Where the others stop at "run this query," it brings a DBA's tuning toolkit.

Crystal DBA open-sourced it as the on-ramp to its AI-driven Postgres tooling. Then Temporal acquired the company in September 2025, and active development of the server has mostly stalled since. That matters, and I will come back to it.

Key Features:

Performance analysis: index tuning, EXPLAIN plan inspection, and database health checks. This is the real differentiator.
Two access modes: unrestricted (full read/write, arbitrary SQL) and restricted (read-only, with query-time limits).

Best For: developers and DBAs who want the agent to diagnose a slow query or propose an index, not just read rows.

Two things to weigh before reaching for it:

It defaults to unrestricted, and every README example uses it. Out of the box the agent gets full write access and arbitrary SQL. Set --access-mode=restricted for anything past a dev sandbox. Shipping with the safety off by default is a choice I disagree with.
Development has stalled since the acquisition. The last tagged release (v0.3.0) was May 2025, and commits trailed off after the September 2025 Temporal deal (the last one landed in January 2026). Merged work like streamable-HTTP transport never made it into a release or the Docker image, people are asking for one, and 63 issues sit open, including a connection-pool leak under SSE that is the most-reacted of the bunch.

Verdict: the tuning features are genuinely good, and still the reason to keep it around for hands-on work. But change the unrestricted default on day one, pin a known-good version, and factor in the stalled maintenance before you let it anywhere near production.

Postgres MCP Server Comparison Table

Server	Language	Stars	Neutrality	Governance	Best for
pgconsole	TypeScript	120	Any Postgres	Default-deny IAM, per-connection rights; agent identity, on-behalf-of, attenuation, audit	Self-hosted teams wanting control
Supabase MCP	TypeScript	2.7k	Supabase-locked	Read-only by default, scoped token; project scoping, read-only role	Teams already on Supabase
Postgres MCP Pro	Python	2.9k	Any Postgres	Read/write by default, opt-in read-only; none (forwards the connection)	DBA-style tuning + dev

Star counts are from the time of writing and move fast. Treat them as a rough adoption signal, not a ranking.

Summary

None of these three is finished. Pick by where you sit:

pgconsole has the strongest access model (separate agent identity, on-behalf-of, attenuation) but it is the youngest. Right for a self-hosted editor with a governed agent path.
Supabase MCP is the most polished, as long as you live on Supabase. Right when you are already on Supabase.
Postgres MCP Pro has the richest toolkit but lost its momentum to an acquisition. Right for diagnosing a slow query in dev, once you switch off the unrestricted default.

All three are great on a laptop and get scarier in production, for the same structural reason. Simon Willison named it the lethal trifecta: an agent with access to private data, exposure to untrusted input, and a way to send data back out. A correctly scoped credential does not save you, because the attack arrives through the prompt, not the permissions. So the real test is not "can the agent query the database." It is "what happens when the prompt tells it to do something it shouldn't."

🪄🐘 Postgres best practices I wish every app developer knew 🧑‍💻

Adela — Sun, 14 Jun 2026 20:52:27 +0000

At Bytebase we work with PostgreSQL every day, both inside our own product and across the databases our customers point us at. The same handful of mistakes shows up over and over, and almost none of them are exotic. They are small things an application developer controls and skips anyway.

So this is not a database administration checklist. These are the five things I wish every app developer turned on by default. Each takes minutes, and each one buys you something concrete: a connection you can actually debug, a query that fails fast instead of hanging, a deploy that doesn't lock prod. Do them and your DBA stops paging you at 2am. There's a bonus index trick at the end.

1. Set application_name for Better Debugging

This is the cheapest win on the list, and the one people skip most. By default every connection from your fleet looks identical. When something melts down and you're staring at a wall of pg_stat_activity rows, all the same user from the same pooler IP, you have no idea which service is the culprit. application_name is a free label. Set it once per service and the noise turns into a name.

How to Implement

PostgreSQL connection string:

postgresql://user:password@localhost:5432/mydb?application_name=user-service

Node.js with pg:

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  database: 'mydb',
  user: 'myuser',
  password: 'mypassword',
  application_name: 'user-service',
});

Python with psycopg2:

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="myuser",
    password="mypassword",
    application_name="user-service"
)

Java with JDBC:

String url = "jdbc:postgresql://localhost:5432/mydb?ApplicationName=user-service";
Connection conn = DriverManager.getConnection(url, "myuser", "mypassword");

Go with lib/pq:

import (
    "database/sql"
    _ "github.com/lib/pq"
)

connStr := "host=localhost dbname=mydb user=myuser password=mypassword application_name=user-service"
db, err := sql.Open("postgres", connStr)

What You Get

Now pg_stat_activity actually tells you something:

-- See all active connections from your service
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE application_name = 'user-service';

-- Monitor long-running queries from specific services
SELECT application_name, query, now() - query_start as duration
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '30 seconds';

2. Configure PostgreSQL Statement and Lock Timeouts

Postgres will wait forever if you let it. With no statement timeout, one bad query sits there holding a connection until a human notices. Do that across a pool and you don't get a slow app, you get an outage: every slot taken, nothing left to serve real traffic. Four timeouts decide how patient Postgres is willing to be. Set all four.

The Four Critical Timeouts

statement_timeout. The ceiling on any single statement. This is the one to set first; it kills runaway queries before they take the pool down with them.
lock_timeout. How long a statement waits for a lock before giving up, instead of blocking your whole request path behind someone else's transaction.
idle_in_transaction_timeout. Reaps a transaction that opened, grabbed locks, and then went quiet because the app forgot to commit.
transaction_timeout. The ceiling on an entire transaction, not just one statement (PostgreSQL 17+). Stops a long transaction from sitting on locks the whole time.

How to Configure

In connection string:

postgresql://user:pass@localhost/db?options=-c%20statement_timeout=30s%20-c%20lock_timeout=10s%20-c%20transaction_timeout=60s

At transaction level:

BEGIN;
SET LOCAL statement_timeout = '30s';
SET LOCAL lock_timeout = '10s';
SET LOCAL transaction_timeout = '60s'; -- PostgreSQL 17+
-- Your transaction operations here
COMMIT;

Recommended Values by Use Case

Use Case	statement_timeout	lock_timeout	idle_in_transaction_timeout	transaction_timeout
Web Applications	30s	10s	60s	60s
Background Jobs	300s+	30s	300s	600s
Reporting/Analytics	0 (disabled)	60s	600s	0 (disabled)

The logic is just patience matched to purpose. A user is staring at a spinner, so web requests should fail fast. Background jobs run unattended and can afford to wait a little longer for a lock. Analytics queries legitimately run for hours, so you disable the statement cap there and accept that those sessions are the exception, not the rule.

To learn more, please refer Postgres Timeout Explained.

3. Run Online Schema Migrations Without Downtime

This is where a one-line migration becomes an incident. A statement that runs in 20ms on your laptop can lock a 50-million-row table for ten minutes in production, and for those ten minutes every read and write behind it queues up. The thing that actually takes prod down isn't the migration. It's the pile of normal traffic stuck waiting on its lock.

The Problem with Standard Migrations

Traditional migrations acquire exclusive locks that block all reads and writes to your tables:

-- DON'T: This blocks ALL SELECT, INSERT, UPDATE, DELETE on users table
ALTER TABLE users ADD COLUMN email_verified boolean DEFAULT false;

-- DON'T: This blocks ALL writes (INSERT, UPDATE, DELETE) on orders table for hours
CREATE INDEX ON orders (user_id, created_at);

On a small table you'll never notice. On a large one, the same statement blocks every query for minutes, the connection pool drains as traffic backs up behind it, and the stall cascades into every service that touches that table. One ALTER takes down half the stack.

Online Migration Strategies

Use CREATE INDEX CONCURRENTLY

   -- Safe: Builds index without blocking reads/writes
   -- Note: Cannot be run inside a transaction
   CREATE INDEX CONCURRENTLY idx_orders_user_created
   ON orders (user_id, created_at);

To learn more production tips, please refer Postgres CREATE INDEX CONCURRENTLY.

Add columns and constraints safely

   -- Step 1: Add constraint without validation (fast)
   ALTER TABLE users ADD CONSTRAINT email_verified_not_null
   CHECK (email_verified IS NOT NULL) NOT VALID;

   -- Step 2: Validate constraint (can be done later, non-blocking)
   ALTER TABLE users VALIDATE CONSTRAINT email_verified_not_null;

   -- Step 3: Convert to NOT NULL constraint
   ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

4. Split PostgreSQL Roles for Different Operations

Most apps connect to Postgres as one user that can do everything, because that's what the quickstart told you to do. It works right up until a bug in your code runs a DROP TABLE it should never have been allowed to run. Least privilege isn't bureaucracy here. It's the difference between a bug that returns a permission error and a bug that drops prod by accident.

The Problem with Single "Super User" Approach

Many applications use a single database user with broad permissions:

-- DON'T: Single user with excessive privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

You inherit four problems at once. The app carries DDL rights it never uses, so a single bug can reshape the schema. Every connection is the same identity, so in pg_stat_activity you can't tell a runaway report apart from your write path. You can't tune connection settings per workload. And your audit trail says "the app did it", which tells you nothing. One identity, every risk.

The Three-Role Strategy

Three roles cover almost every app. Think three keys, not one master key:

Read-Only Role - For analytics, reporting, read replicas
Application Role - For normal CRUD operations
Migration Role - For schema changes and DDL operations

Implementation Example

Create the roles:

-- 1. Read-only role for analytics/reporting
CREATE ROLE app_reader;
GRANT CONNECT ON DATABASE myapp TO app_reader;
GRANT USAGE ON SCHEMA public TO app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
-- Automatically grant SELECT on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_reader;

-- 2. Application role for normal operations
CREATE ROLE app_writer;
GRANT CONNECT ON DATABASE myapp TO app_writer;
GRANT USAGE ON SCHEMA public TO app_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_writer;
-- Auto-grant on future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

-- 3. Migration role for schema changes
CREATE ROLE app_migrator;
GRANT CONNECT ON DATABASE myapp TO app_migrator;
GRANT ALL PRIVILEGES ON SCHEMA public TO app_migrator;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_migrator;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_migrator;

Create actual users:

-- Create users and assign roles
CREATE USER analytics_user PASSWORD 'secure_password';
GRANT app_reader TO analytics_user;

CREATE USER application_user PASSWORD 'secure_password';
GRANT app_writer TO application_user;

CREATE USER migration_user PASSWORD 'secure_password';
GRANT app_migrator TO migration_user;

5. Use UPSERT for Idempotent Writes

Networks drop, clients retry, two requests race. In production this isn't an edge case, it's Tuesday. UPSERT (INSERT ... ON CONFLICT) lets you write code that doesn't care whether the row already exists. A retry can't create a duplicate, a race resolves itself, and the "create or update" branching you'd otherwise write in the app disappears. The database settles the conflict atomically, which is exactly where you want that decision made.

Ignore Duplicates

-- Pattern 1: Ignore duplicates
INSERT INTO users (email, name, created_at)
VALUES ('john@example.com', 'John Doe', NOW())
ON CONFLICT (email) DO NOTHING;

Update on Conflict

-- Pattern 2: Update on conflict
INSERT INTO users (email, name, updated_at)
VALUES ('john@example.com', 'John Smith', NOW())
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

Conditional Update

-- Pattern 3: Conditional update
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES ($1, 1, NOW())
ON CONFLICT (user_id)
DO UPDATE SET
  login_count = user_stats.login_count + 1,
  last_login = EXCLUDED.last_login
WHERE user_stats.last_login < EXCLUDED.last_login;

Bonus: Implement "Invisible" Indexes

PostgreSQL doesn't ship invisible indexes the way MySQL 8 does. But you can fake it by flipping the indisvalid flag in the pg_index catalog, which takes an index out of the planner's consideration without dropping it. Handy when you suspect an index isn't earning its keep and want to confirm that before you actually remove it. Just know you're poking a system catalog directly, so keep it to staging or a maintenance window.

Making an Index "Invisible"

-- Disable an existing index (make it invisible to the query planner)
UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = 'idx_orders_status'::regclass;

Making the Index "Visible" Again

-- Re-enable the index (make it visible to the query planner)
UPDATE pg_index
SET indisvalid = true
WHERE indexrelid = 'idx_orders_status'::regclass;

Checking Index Status

-- See which indexes are currently disabled
SELECT
  schemaname,
  tablename,
  indexname,
  indisvalid AS is_valid
FROM pg_indexes
JOIN pg_index ON pg_indexes.indexname = pg_class.relname
JOIN pg_class ON pg_index.indexrelid = pg_class.oid
WHERE NOT indisvalid;

Note: PostgreSQL itself uses the indisvalid flag internally. When you run CREATE INDEX CONCURRENTLY, PostgreSQL automatically sets indisvalid = false until the index is fully built.

Conclusion

None of this is database administration. It's five things an application developer controls and usually leaves on the floor: name your connections, set your timeouts, migrate online, split your roles, write idempotent inserts. Start with application_name and timeouts. They take ten minutes and pay you back the first time something breaks at 2am.

None of these will win you praise in a design review. They just quietly keep you out of the incident channel, which is the whole point.

⚙️🔥 Automating Database Schema Change workflow Using GitHub Actions 🐙🚀

Adela — Wed, 20 Mar 2024 10:20:04 +0000

Tutorial repository https://github.com/bytebase/ci-example

Developers like to keep their schema migration scripts in Git along with the application code. Thus the migration scripts will be reviewed and version-controlled in the same way as the application code.
However, developers still have to manually paste the migration script into their SQL client or ask the DBAs to run it against the target database. This is inefficient and error-prone:

What if you paste the wrong script / miss the script?
What if you run the script against the wrong database?

This tutorial teaches how to get rid of this manual process and fully automate database schema change
using GitHub Actions and Bytebase API.

Above shows a typical workflow:

Developer creates a PR containing the migration script. After PR is created, it triggers a GitHub
Action to lint the SQL by calling Bytebase SQL Review API.
TL approves the PR.
Upon PR approval, it triggers a GitHub Action to create a Bytebase rollout issue.
The issue contains the migration script changes.
Depending on the configured approval and rollout policy, it may require manual approval and rollout from DBA. Another GitHub Action is configured to block the PR merge until Bytebase rolls out the schema migration. Sometimes, a PR contains both code and schema changes. This setup guarantees the schema migration is applied before the code deployment.
Bytebase deploys the schema change and marks the issue as Done.
PR re-runs the migration status check and now it turns green.
Now PR can be merged.

Prepare Bytebase

Suppose Bytebase is running at https://bytebase.example.com/. To begin, we'll first set up the necessary data to support our API interactions.

Service account: As an admin, we add a service account ci@service.bytebase.com with the Workspace DBA role, which will be used to authenticate the API calls.

To limit the service account permissions, you can choose to grant Workspace Member instead of Workspace DBA.
And then in the particular project, grant the account the permission to create an issue.

A database in a project: We have a project called Example, and a database: example.

Prepare GitHubs Actions

Go and check the example on GitHub https://github.com/bytebase/ci-example.

The repository contains several GitHub Action workflows, you may go to .github/workflows to view.

We will use the following workflows:

bytebase-sql-review.yml: Triggered on PR change. Thus any SQL review violation will block the PR.
bytebase-upsert-migration.yml Triggered on PR approval. Creates the Bytebase migration issue after approval. And whenever the migration scripts change afterwards, the migration issue will also be updated accordingly.
bytebase-check-migration-status.yml: Triggered on PR change. Thus PR will be blocked until migration completes.

Sample Workflow, Four Phases

To illustrate the workflow, we have divided it into four phases to showcase the database schema change process.

Phase 1: Not passing SQL review on GitHub

Before we delve into the workflow, let's set up the SQL Review policy in Bytebase. The example database is on the Prod environment, where we will configure SQL review policy. Here we have a policy that checks for NOT NULL constraints, which we will violate in the PR.

Returning to GitHub Actions and digging into the code, the bytebase-sql-review.yml workflow is triggered on PR change. It scans the SQL files named following the pattern **.up.sql in the PR and reports any SQL review policy violations.

Configure the environment.

   bytebase-sql-review:
      runs-on: ubuntu-latest
      env:
         BYTEBASE_URL: "https://bytebase-ci.zeabur.app"
         BYTEBASE_SERVICE_ACCOUNT: "ci@service.bytebase.com"
         DATABASE: "instances/prod-instance/databases/example"
   ...

After authentication, we call the Bytebase API /sql/check to lint the migration files. We parse the response
and emit GitHub inline annotations for each advice and mark the check as failed if any ERROR or WARNING is found.

     name: SQL Review
    steps:
      - name: Checkout
        uses: actions/checkout@v4
      - name: Login to Bytebase
        ...
      - name: Review
        id: review
        uses: ./.github/actions/sql-review
        with:
          github-token: ${{ secrets.GITHUB_TOKEN }}
          pattern: "**/*.up.sql"
          url: ${{ env.BYTEBASE_URL }}
          token: ${{ steps.login.outputs.token }}
          headers: '{"Accept-Encoding": "deflate, gzip"}'
          database: ${{ env.DATABASE }}
      ...

We create a PR with several SQL files, and it triggers both bytebase-sql-review.yml and bytebase-check-migration-status.yml. After these checks are completed, the PR is blocked due to failures.

Click on the Details for SQL Review.

You may also go to Files changed to view the annotations.

Phase 2: Passing SQL review and waiting for TL's approval on GitHub

We then fix the SQL files and pushes. After completing these checks, the PR is still blocked due to failures, but this time SQL review has passed.

In real-life scenarios, the PR also encompasses application code. Because the SQL migration has passed the basic SQL review checks, it is now time for a tech leader to approve this PR.

The developer who creates the PR assigns the tech leader to review on GitHub.

Phase 3: TL approves on GitHub and migration issue is created in Bytebase

The assigned tech leader approves the PR, and another workflow bytebase-upsert-migration.yml is triggered.

It checks the SQL files named like **.up.sql within the pull request and creates a rollout issue in Bytebase.

   bytebase-upsert-migration:
    runs-on: ubuntu-latest
    # Runs only if PR is approved and target branch is main
    if: github.event.review.state == 'approved' && github.event.pull_request.base.ref == 'main'
    env:
      BYTEBASE_URL: "https://bytebase-ci.zeabur.app"
      BYTEBASE_SERVICE_ACCOUNT: "ci@service.bytebase.com"
      PROJECT: "example"
      DATABASE: "instances/prod-instance/databases/example"
      ISSUE_TITLE: "[${{ github.repository }}#${{ github.event.pull_request.number }}] ${{ github.event.pull_request.title }}"
      DESCRIPTION: "Triggered by ${{ github.event.repository.html_url }}/pull/${{ github.event.pull_request.number }} ${{ github.event.pull_request.title }}"
    name: Upsert Migration
    steps:
    ...

Go to Bytebase and view the created issue, which consists of two tasks corresponding to the presence of two **.up.sql files in the PR.

You may notice there is an approval flow attached to the created issue, that's because we set up a default custom approval flow for DDL.

Phase 4: Migration completed and PR is mergable on GitHub

After the DBA approves and rolls out the migrations, the issue status will become Done.

Go back to GitHub, click Details for the failed bytebase-check-migration-status.yml workflow, and then click Re-run all jobs.

It checks the migration status in Bytebase and return pass if it's Done, indicating the database migration has been completed. The PR is now ready to be merged, which means the application code is ready to be deployed.

Summary

Keep in mind that workflows can be tuned according to your organization's needs:

You can attach the workflow to different branches depending on your branching strategy (e.g. trunk-based or not).
You can use different migration file formats and different migration file structures.
You can determine when to create the migration issue, upon PR approval or creation.

Whatever workflow you choose, with the help of GitHub Actions and Bytebase API, you can now keep your migration scripts in the repository, let the migration scripts go through the same code review process and automate the schema migration deployment.

🔄 How to Synchronize Database Schemas🔥🚀

Adela — Fri, 01 Mar 2024 10:21:45 +0000

This tutorial will walk you through the typical use cases for database schema synchronization in Bytebase with pure UI operations.

The Sync Schema feature in Bytebase supports copying a specific schema version from one database to multiple others. Without it, developers have to write SQL statements cautiously and apply them manually. Additionally, this feature may also be used for rollback purpose.

Preparation

Make sure you installed Docker.

Copy and paste the commands to start one Bytebase via Docker.

   docker run --init \
   --name bytebase \
   --publish 8080:8080 \
   --volume ~/.bytebase/data:/var/opt/bytebase \
   bytebase/bytebase:%%bb_version%%

Case 1 - Sync Schema to a New Database

When you set up a new environment or find the current database is nearing capacity, it becomes necessary to establish a new database and synchronize the schema from the original database.

Go into Sample Project, there are two databases hr_test on Test environment and hr_prod on Prod environment. Let's create another one on Prod.
Click New DB, fill in the form as follows and click Create:
- Name: hr_prod_2
- Environment: Prod
- Instance: Prod Sample Instance
- Database owner name: bbsample
An issue will be created and executed automatically, and the database hr_prod_2 will be ready when the issue is Done.
Within the project, click Sync Schema, choose hr_prod as the source schema, and click Next.
You may select as many target databases as you like to sync to. Here we select all (including the source) and click Select.
Bytebase will calculate the schema differences between the source and target databases, and generate the suggested DDL statements. Click Preview Issue.
An issue with the generated DDL is created automatically. Click Rollout ignoring the SQL review warning. After the issue execution is Done, the schema synchronization is completed.

Case 2 - Sync Schema from One to Many

During the product release process, for different environments, such as dev, staging and prod, you'll need to do schema synchronization. Furthermore, for the prod environment, databases with identical schemas, such as SaaS, and multi-region game deployment, you may also need to sync the schema to multiple databases at the same time.

Within the project, select hr_test and click Edit Schema. With the help of Schema Editor, add a new column email as follows and click Preview issue:
- Name: email
- Type: text
- Default: Empty string
- Not Null: checked

An issue is created and executed automatically. After it's Done, the new column email is added.
Within the project, click Sync Schema, choose hr_test as the source schema and click Next.
Select hr_prod and hr_prod_2 as the target databases. Schema differences are calculated and the suggested DDL statements are generated. Click Preview issue.
An issue with the generated DDL is created automatically. Because two databases need to be synced, there're two task blocks. After they execute one by one, the issue is Done, and email is added.

Case 3 - Rollback to Previous Schema Versions

In Bytebase Community Plan, you can choose the latest history version; In Pro Plan or Enterprise Plan, you can choose an arbitrary schema version from the full migration history. Which means you can roll back to any previous schema version.

After upgrading to Enterprise Plan, go into Sample Project, click Sync Schema and select hr_test as the source schema. When you choose the schema version, you will see the following prompt. That's because we haven't assigned the enterprise license to database instances.
Click on the lock sign, select both two instances, and click Confirm.
After the license is assigned, you can choose the schema version freely, we'll choose the previous version and click Next.
Choose the same database hr_test as the target, and click Select.
Bytebase will calculate the schema differences between the source and target databases, and generate the suggested DDL statements. Here means if you want to roll back to the previous version, you'll need to delete this line. Click Preview Issue.
Click Create, an issue with the generated DDL is created and rollout automatically. After the issue execution is Done, the schema rolls back to the previous version.

Summary

Is the experience smooth? Or do you encounter any problems? Feel free to join our Discord Group to talk about it!

🛡️4 Top Database Security Tools in 2024 🏆🔥

Adela — Thu, 01 Feb 2024 10:04:03 +0000

In today's digital age, where data is as valuable as oil, protecting this crucial asset is essential for every organization. Threats arise from external sources like hackers and malware, as well as internal ones, including human errors and malicious insiders. This article will explore leading database security tools designed to shield your data from these varied risks.

Networking - Tailscale
Database credential management - Infisical
On-demand database access - Indent
All-in-one human-to-db operation - Bytebase

Tailscale

Tailscale is a VPN service that makes the devices and applications you own accessible anywhere in the world, securely and effortlessly. It enables encrypted point-to-point connections using the open source WireGuard® protocol, which means only devices on your private network can communicate with each other.

When it comes to databases, Tailscale can be used to connect to your database from any location globally, without having to expose your database to the public internet or setting up extra SSH tunnels.

Its free tier accommodates up to 3 users and 100 devices, which is for individuals or small businesses. For additional capabilities like Access control and Audit logs, paid plans start at $6 per user per month.

Infisical

Infisical is an open-source, end-to-end encrypted secret management platform for storing, managing, and syncing application configuration and secrets like API keys, database credentials, and environment variables across applications and infrastructure. It is an alternative to HashiCorp Vault and AWS Secrets Manager.

You have the option to host Infisical on your premises or utilize their cloud offering.

With its help, your database credentials can be stored in a secure vault and be accessed by your application only when needed. In this way, you can avoid storing your database credentials in your application code or configuration files. Access is facilitated through CLI, SDK, Docker, Kubernetes, or REST API.

The free tier supports up to 5 developers and 3 environments, catering to hobbyists. For features such as Access Controls and Audit Logs, paid plans start at $6 per developer per month.

Indent

Indent provides team members with on-demand access to cloud apps and infrastructure in seconds. They aptly describe this innovation as the request access button for any software or service utilized by the company.

In the context of database access management, Indent provides you the capability to grant time-bound, granular access to systems that handle customer data. It offers three methods of integration:

Zero Trust Network (ZTN) - e.g. Tailscale
Identity Group - e.g. Okta
Custom Integration

Indent provides a secure-by-default free tier suitable for small teams on a trial basis. For those requiring more, the paid plans begin at $8 per user per month.

Bytebase

Bytebase is an open-source database DevOps tool, it's the GitLab/GitHub for managing databases throughout the application development lifecycle. It offers a web-based collaboration workspace for DBAs, Developers and platform engineers. It consolidates disparate DB tools
such as DBeaver, Liquibase, Flyway into a single place.

It seeks to encompass all interactions between humans and databases across every database, including managing changes, querying data, handling access, and beyond. Thanks to its features such as SQL Lint, Database CI/CD, Data Access Control, Data Masking, and more, you can be confident that all your database activities are executed via standardized procedures and completely auditable.

With Community Plan, you can have up to 10 users and up to 5 databases for free. For medium to large organizations, the Enterprise plan offers advanced options like Data Access Control, Audit Log, and additional features.

Summary

In this article, we have explored four database security tools that can help you protect your database from external and internal threats. Each tool offers its unique strengths; by understanding your specific needs, you can choose the one that most aligns with your requirements.

🐣Your First Database Schema Change in 5 Minutes with Bytebase

Adela — Thu, 18 Jan 2024 06:42:04 +0000

In this tutorial, you'll use the sample databases Bytebase provides by default to get familiar with the product in the quickest way.

Step 1 - Run via Docker

Install and start Docker.
Open Terminal to run the command:

  docker run --init \
    --name bytebase \
    --publish 8080:8080 \
    --volume ~/.bytebase/data:/var/opt/bytebase \
    bytebase/bytebase:2.13.1

When the Terminal shows the following message, the execution is successful.

██████╗ ██╗   ██╗████████╗███████╗██████╗  █████╗ ███████╗███████╗
██╔══██╗╚██╗ ██╔╝╚══██╔══╝██╔════╝██╔══██╗██╔══██╗██╔════╝██╔════╝
██████╔╝ ╚████╔╝    ██║   █████╗  ██████╔╝███████║███████╗█████╗
██╔══██╗  ╚██╔╝     ██║   ██╔══╝  ██╔══██╗██╔══██║╚════██║██╔══╝
██████╔╝   ██║      ██║   ███████╗██████╔╝██║  ██║███████║███████╗
╚═════╝    ╚═╝      ╚═╝   ╚══════╝╚═════╝ ╚═╝  ╚═╝╚══════╝╚══════╝

  Version 2.13.1 has started on port 8080 🚀

Now you have Bytebase running in Docker.

Open Bytebase in localhost:8080, fill in the fields and click Create admin account. You'll be redirected to the workspace.

Follow the Quikstart guide on the bottom to click around or dismiss it by now. You can click your avatar on top right and click Quickstart on the dropdown menu to reopen it later.

Step 2 - One Issue with Two Stages

Click My Issues on the left sidebar, and click the issue SAM-101 which is created by default.

The issue is waiting to rollout. There's a pipeline consisting of two stages:
1. Test Stage: apply to database hr_test on Test Sample instance
2. Prod Stage: apply to database hr_prod on Prod Sample instance

Test stage is active by default.

Click Prod Stage to switch to it, and you will see the two stages share the same SQL but to different databases. You may also notice there's a warning sign for SQL review on the Prod stage. That's because when the issue is created, Bytebase will run task checks automatically. SQL review is one of them.

Click the warning sign to see the details. If you wonder why only Prod Stage has the warning sign, it's because by default SQL Review is only configured for Prod environment. You can click the Settings (gear) on the top right, and click Security & Policy > SQL Review to have a look.

Step 3 - Roll out on Test Stage

Switch back to Test Stage and click Rollout. Click Rollout on the confirmation dialog.

When the SQL is applied, there will be a checkmark on the Test Stage. Click View change and you'll see the diff.

Step 4 - Roll out on Prod Stage

There are two ways to roll out on Prod Stage regarding the SQL review result.

If you are confident with the SQL, you can click Rollout directly. Check the Rollout anyway, and click Rollout on the confirmation dialog.

Another way is to edit the SQL. Click Edit on top of the SQL, and add the NOT NULL. It will look like this:

   ALTER TABLE employee ADD COLUMN IF NOT EXISTS email TEXT NOT NULL DEFAULT '';

Click Save, the checks will be run again. This time the SQL review will pass and it will roll out automatically. The issue will become Done as well.

You may ask why it's rolling out automatically, it's because for Community Plan, the rollout policy is automatic if the SQL review passes. You may go to Environments to check.

Next Step

Now you have successfully performed your first schema change in Bytebase. It's the core part of Bytebase. You can continue to dig deeper by following Deploy Schema Migration with Rollout Policy.

🔥Top psql Commands with Examples🐘

Adela — Wed, 22 Nov 2023 03:10:10 +0000

PostgreSQL, or Postgres, is a free and open-source relational database management system (RDBMS) known for its reliability, feature robustness, and performance. It has been actively developed for over 35 years and is considered one of the most advanced open-source RDBMSs available.

psql is the official CLI client to command the PostgreSQL database. It supports interactive and noninteractive use.

Below list of the 10 most commonly used psql commands with examples.

1. Connect to a database - `psql -U xxx -p -h xxx -d xxx`

If you are using the default PostgreSQL username postgres and have not set a password, you can connect to the database using the following command. You will be prompted to enter the password for the postgres user.

psql

If you are using a different username or have set a password for the postgres user, you can connect to the database using the following command. You will be prompted to enter the password for the specified user.

psql -U your_username

If the PostgreSQL server is running on a different port than the default (5432), you can connect to the database using the following command. You will be prompted to enter the password for the specified user.

psql -p 5433

If the PostgreSQL server is running on a remote host, you can connect to the database using the following command. You will be prompted to enter the password for the specified user.

psql -h remote_host -p 5432

In all of these examples, you can also specify the name of the database you want to connect to using the -d option. For example, to connect to the database named my_database, you would use the following command:

psql -d my_database

2. Run a single command - `-c`

To run a single command in PostgreSQL using the psql CLI, you can use the -c option followed by the SQL command you want to execute.

psql -d my_database -c "SELECT * FROM my_table;"

List available databases - \l This command will all of the databases that are currently available on the server. This can be useful for getting an overview of the databases that are available to you.

postgres-# \l

   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
(3 rows)

4. Switch to a specified database - `\c`

The \c command switches to the specified database. This is useful for working with a specific database after you have listed the available databases.

postgres-# \c postgres


psql (16.0, server 16.1 (Debian 16.1-1.pgdg120+1))
You are now connected to database "postgres" as user "postgres".

5. List tables - `\dt`

The \dt command lists all of the tables that are currently available in the current database. This can be useful for getting an overview of the tables that are available to you in the current database.

postgres-# \dt

List of relations
Schema | Name | Type | Owner
-------+---------+-------+--------
public | mytable | table | postgres
public | users | table | postgres
public | orders | table | postgres

6. Describe a specified table - `\d`

The \d command describes the specified table. This provides information about the table's columns, data types, and constraints.

postgres-# \d my_table

Table: my_table

Column | Data Type | Constraints
-------+------------+-------------------
id | SERIAL | PRIMARY KEY
name | VARCHAR(50) | NOT NULL
email | VARCHAR(100) | UNIQUE

7. Listing users - `\du`

The \du command lists all of the users that exist in the current database.

postgres-# \du

List of roles
Role name | Attributes
---------+------------
postgres | SUPERUSER, CREATEDB, CREATEROLE, INHERIT, REPLICATION
myuser | CREATEDB

The \du username command is used to list the roles (users) in the current database that have the specified username.

postgres-# \du myuser

List of roles
Role name | Attributes
---------+------------
myuser | CREATEDB

If the specified username does not match any roles in the current database, the output will be an empty table.

postgres-# \du youruser

List of roles
Role name | Attributes
---------+------------
 |

8. Show connections - `pg_stat_activity`

To list all active connections, execute the following SQL query.

postgres-# SELECT * FROM pg_stat_activity;

pid | datname | username | client_addr | client_port | backend_start | query_start | query | state
-------+---------+---------+-------------+-------------+---------------+---------------+-----------------+----------
1234 | mydb1   | postgres | 192.168.1.100 | 5432 | 2023-10-04 15:04:00 | 2023-10-04 15:04:05 | SELECT * FROM mytable; | active
5678 | mydb2   | user1    | 192.168.1.101 | 5432 | 2023-10-04 15:05:00 | 2023-10-04 15:05:03 | UPDATE mytable SET name = 'John Doe' WHERE id = 123; | idle in transaction
9012 | postgres | 192.168.1.102 | 5432 | 2023-10-04 15:06:00 |                |                | idle |

You can also filter the results to specific connections based on criteria such as username, database name, or state. For example, to list only active connections to the database mydb, use the following query:

postgres-# SELECT * FROM pg_stat_activity WHERE datname = 'mydb' AND state = 'active';

Similarly, to list connections currently executing queries, use the following query:

postgres-# SELECT * FROM pg_stat_activity WHERE state IN ('active', 'idle in transaction');

9. Kill connection - `pg_terminate_backend(), kill`

Once you have identified the PID of the connection you want to kill, you can use the pg_terminate_backend() function to terminate it. For example, the following command will kill the connection with PID 1234:

postgres-# SELECT pg_terminate_backend(1234);

Using the kill command is a less common method for killing PostgreSQL connections, as it can be more dangerous and less reliable than using the pg_terminate_backend() function.

However, it may be necessary in certain situations, such as when the connection is unresponsive or the pg_terminate_backend() function fails.

kill -9 1234




## 10. Quit - `\q`,Ctrl+D/Ctrl+Z
Using the `\q` command or the keyboard shortcut Ctrl+D(or Ctrl+Z on Windows). This will immediately exit the `psql `CLI.👋

🚛 Deploy Database Schema Migrations with Bytebase

Adela — Fri, 10 Nov 2023 07:09:31 +0000

Bytebase offers a powerful GUI for schema migration deployments. This tutorial will show you how to use Bytebase to deploy schema migrations with features like SQL Review, custom approval, time scheduling, and more.

Bytebase offers Community, Pro and Enterprise Plans. Advanced plans
unlock new capabilities of deploying schema migrations and this tutorial will walk you through them progressively.

Features covered

Level 1 - Automatic SQL Review (available in all plans)
Level 2 - Rollout Policy (available in Pro and Enterprise)
Level 3 - Custom Approval (available in Enterprise)

Preparation

Make sure your Docker is running, and start the Bytebase Docker container with the following command:

docker run --init \
  --name bytebase \
  --restart always \
  --publish 5678:8080 \
  --health-cmd "curl --fail http://localhost:5678/healthz || exit 1" \
  --health-interval 5m \
  --health-timeout 60s \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:%%bb_version%% \
  --data /var/opt/bytebase \
  --port 8080

Bytebase is running successfully in Docker, and you can visit it via localhost:5678. Register an admin account and it will be granted the workspace owner role automatically.

Level 1: Automatic rollout with SQL review (Community Plan)

Go to Environments > Test and Environments > Prod, you'll see
1. The Rollout policy is automatic. Unless there's some warning or error, the rollout will be automatically executed after the issue is created.
2. SQL Review is enabled on Prod with a sample policy.

Click SQL Review Sample Policy to go to SQL Review policy, there're three rules activated. Let's pay attention to Enforce NOT NULL constraints on columns rule, and we'll try to violate it.

Go to Sample Project, click Edit Schema, choose both Test and Prod databases, and click Next. Paste the following SQL statements into Raw SQL, and click Preview issue.

ALTER TABLE "public"."employee"
    ADD COLUMN "country" text;

SQL Review checks will dry run before the issue is created. Here let's create the issue regardless of the dry run result.
After the issue is created, SQL Review will run automatically along with some other checks. You'll see there's a warning for the task on Prod.

Click Edit, paste this SQL statement and click Save. Apply this change to all tasks. The SQL checks will run again, and you'll see the warning disappear and it will roll out automatically.

ALTER TABLE "public"."employee"
ADD COLUMN "country" text NOT NULL DEFAULT '';

Click View change to see the diff or go to Change History to view all changes.

Level 2: Manual rollout with dedicated roles and scheduled time (Pro Plan)

With Pro Plan, you'll get two additional features:

Manual rollout policy. You can specify multiple pre-defined roles to manually roll out the change.
Time scheduling. You can specify a particular time to roll out the change.

To simplify the process, we'll use 14-day enterprise trial here. Click the Start free trial to upgrade.

Go to Environments > Prod, choose Manual rollout by dedicated roles and check all the roles. Click Update.

Go to Sample Project, click Edit Schema, choose both Test and Prod databases, and click Next. Paste the following SQL statements into Raw SQL, and click Preview issue.

ALTER TABLE "public"."employee"
    ADD COLUMN "city" text NOT NULL DEFAULT '';

Click Create, and after Task checks runs, you'll see the SQL running on Test automatically but waiting to run on Prod.

Click Rollout to trigger directly or set a Rollout time.

Level 3: Manual rollout with custom approval (Enterprise Plan)

If you want the approval flow to be more dynamic based on the context like the type of SQL statements, the affected rows and etc,
then you can configure custom approval flow.

Go to Instances and click Assign License for both instances. Without doing this, the enterprise plan required for custom approval won't be enabled on instances.

Click Settings (the gear icon) > Security & Policy > Custom Approval. Choose Project Owner -> DBA as High Risk for DDL.

Click the related risk rules or Settings (the gear icon) > Security & Policy > Risk Center. Click Add rule. Set High Risk and DDL as The risk for the production environment is considered to be high.

Click Settings (the gear icon) and add a DBA account. Click it in the Active members list, and edit its password. You'll need this account later to do the approval.
Go to Environments > Prod, you now unlock the third option for rollout policy Manual rollout by the last approver from the custom approval flow. Choose it.

Go to Sample Project, click Edit Schema, choose both Test and Prod databases, and click Next. Paste the following SQL statements into Raw SQL, and click Preview issue.

ALTER TABLE "public"."employee"
    ADD COLUMN "district" text NOT NULL DEFAULT '';

Create the issue and the approval flow is matched. Since it's in the pipeline, it will be brought forward to the Test stage to review earlier. Follow its order to approve. DBA will be the one to do the rollout.

Summary

You have now learned how to use Bytebase to deploy schema migration in a basic way. Bytebase also provides other advanced features for your interests:

GitOps - Observe Git code push events and trigger schema migration;
Batch changes - Change multiple databases in a single workflow;
Branching - Collaborate on schema changes using Git-like branching;
Changelist - Organize and apply changes sequentially, or export them for offline execution.

Join our Discord channel to discuss.

🐣 Step-by-Step Guide to Data Masking 🎭

Adela — Fri, 13 Oct 2023 09:50:51 +0000

Bytebase Dynamic Data Masking can mask sensitive data in the query result based on the context on the fly.
It helps organizations to protect sensitive data from being exposed to unauthorized users.

This tutorial will teach you how to set up and try out data masking in Bytebase within 30 mins.

Prerequisites

Before starting, make sure you have installed Docker.

Preparation

Make sure your Docker is running, and start the Bytebase Docker container with following command:

docker run --init \
  --name bytebase \
  --restart always \
  --publish 5678:8080 \
  --health-cmd "curl --fail http://localhost:5678/healthz || exit 1" \
  --health-interval 5m \
  --health-timeout 60s \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:%%bb_version%% \
  --data /var/opt/bytebase \
  --port 8080

Bytebase is running successfully in Docker, and you can visit it via localhost:5678. Register an admin account and it will be granted the workspace owner role automatically.
Go back to the Bytebase console, click Start free trial on the left bottom and upgrade to Enterprise plan.
Click Instances, and click Assign License. Select both instances, and click Confirm. Without doing this, the enterprise plan required for data masking won't be enabled on instances.

No Masking

Login to Bytebase,click SQL Editor, you'll be redirected to SQL Editor page. Choose (Prod) employee > public > employee, and then run SELECT * FROM employee;, you'll see the following result. Run the same query against (Test) employee, the result is the same.

Global Masking Rule

You may want to batch apply masking settings, for example, you want to mask all the birth_date columns in all the tables in the employee database. You can use Global Masking Rule to achieve this.

Click the Setting icon on the top right. Click Security & Policy > Data Masking. Click Global Masking Rule and then Add rule.
Name the rule as birth_date should be masked, and select Column name, ==. Fill birth_date in the input box, and click Confirm.
Go back to the SQL Editor page, Choose (Prod) employee > public > employee and run SELECT * FROM employee; again. You'll see the birth_date is masked. Choose (Test) employee, the result is the same.

Column Masking Rule

If you want to mask a specific column in a specific table, you can use Column Masking Rule.

Click Databases and choose employee on Prod, and select salary table.
Click the edit(pen) icon on the amount row, and click Full.
Go back to the SQL Editor page, Choose (Prod) employee > public > salary and run SELECT * FROM salary; again. You'll see the amount is masked. Choose (Test) employee, it's not.

Grant unmasked access to a user

What if you want to reveal the masked data to a specific user? You can grant unmasked access.

Click Settings icon on the top right, and click Members. Add a DBA user and click + Add. Click its link in the Active members section, click Edit to set a password, click Save.
Click Databases and choose employee on Prod, and select salary table.
Click the edit(pen) icon on the amount row, and click Grant Access. Select the DBA user, and click Confirm.
Login as the DBA user, go to SQL Editor, choose (Prod)Employee > public > salary and run SELECT * FROM salary; again. You'll see the amount is not masked.

Summary

You can now mask data in Bytebase using two methods: use Global Masking Rule to batch apply masking settings, and use Column Masking Rule to mask a specific column in a specific table. You can also grant access to a specific user to reveal the masked data.

Bytebase supports Data Masking for different databases.

If you have any questions, please join our discord.

How to Manage Data Access for Developers

Adela — Tue, 05 Sep 2023 03:15:22 +0000

When you have a team of developers, it is critical to restrict their access to data to only what they need to do their job. This ensures to protect sensitive data and prevent unauthorized access.

Bytebase provides several powerful features to help you achieve this:

You can restrict users to only view data in a specific environment.
Developer can require approval to access a specific table.
You can mask sensitive data, such as salary.
You can track who has accessed which data.
You can add watermarks to your data to deter unauthorized copying.

This tutorial will walk you through how to use these features in Bytebase.

All are Enterprise Plan only features. However, you can start a 14-day trial of the Enterprise Plan with one click without providing additional information (no credit card required).

Preparation

Make sure you have Docker installed, and if you don’t have important existing Bytebase data locally, you can start over from scratch by rm -rf ~/.bytebase/data.

You’ll need two Bytebase accounts – one Owner and one Developer.

Owner is the one who configures the settings.
Developer is the one who should only see information based on the configuration.

Step 1 - Run Bytebase via Docker

Make sure your docker daemon is running, and then start the Bytebase docker container by typing the following command in the terminal.

docker run --init \
--name bytebase \
--restart always \
--publish 5678:8080 \
--volume ~/.bytebase/data:/var/opt/bytebase \
bytebase/bytebase:2.7.0 \
--data /var/opt/bytebase \
--port 8080

Step 2 - Register accounts Owner and Developer

Visit localhost:5678 in the browser. Register an admin account, we’ll refer to it as Owner. This account will be granted Workspace Owner role. To keep it simple, we use this Owner instead of registerating another DBA in this tutorial. Check Roles and Permissions (RBAC).
Log out and register another normal account - we’ll refer to it as Developer. This account will be granted Workspace Developer role.
Switch back to Owner, now you can see there's a Sample Project with two embedded databases both named employee mapping to Test and Prod environments respectively.
Click Members and Grant Access to add Developer as Developer. Only after this, can he see the project.

Step 3 - Start Enterprise Plan trial and assign license to instances

Click Start free trial on the left bottom. Click Start 14 days trial (no credit card required).
Click Instances on the top navigation bar. You can see there are two instances. Click them one by one, and turn on Assign License for both. You may check Manage License for more details.

Data Access Control - Query Data

Log in as Developer, and you can see Sample Project. Click SQL Editor on the left bar. Within SQL Editor, you can't see anything. It's because you don't have any database access permission yet.

Step 1 - Developer can skip approvals to query data

Log in as Owner, and go to Settings > Data Access Control. Check Skip approval for Query Data in Test environment.
Log in as Developer and go into SQL Editor again. Now you can see database under Test environment. Select (Prod)employee, input SELECT * FROM salary; and run and you can see the result. Change salary to any other tables and run, you can see data as well.

Step 2 - Developer can request to query data

In practice, developers can access any database on Test environment, but they need to obtain permission to access Prod environment. In this step, we'll show you how to implement this.

Log in as Owner, and go to Settings > Custom Approval. Scroll down to Request Query, and choose Workspace Owner as Approval flow.
Go to Settings > Risk Center. Click Add Rule. Choose Request Query as Type. Choose High as Risk. Click Load for the first template on the right. Click Add.
Log in as Developer, and go to Sample Project, click Request Query. Choose Manual select and then employee table under Prod environment. Click OK.
A request issue is created with the approval flow Workspace Owner we just defined.
Log in as Owner, go to this issue and click Approve.
Log in as Developer, and go to SQL Editor. You can see salary table under Prod environment. Select (Prod)employee, input SELECT * FROM salary; and run, you'll see the result.
Input SELECT * FROM employee; and run, it'll show permission denied. You can click Request Query to request permission.

Step 3 - Owner can grant access to query data directly

Log in as Owner, and go to Sample Project. Click Members and then Grant Access. Choose Developer and assign the role Querier, All for Databases. Click Confirm.
Log in as Developer, and go to SQL Editor. You can see all databases under Test and Prod environment. Select (Prod)employee, input SELECT * FROM employee; and run, you can see the result. Change employee to any other tables and run, you can see data as well. ## Data Access Control - Export Data Export Data is similar to Query Data. Developer can skip approvals, request approvals to export data or be granted Exporter role directly by Owner.

Data Masking

Log in as Developer, and go to SQL Editor. Select (Prod)employee, input SELECT * FROM salary; and run, you can see all the information. We want to mask the amount column.
Login in as Owner, click Databases on the top navigation bar and then choose employee under Prod environment. Scroll down to Tables, click salary table, and check amount row as Sensitive.
Log in as Developer, and go to SQL Editor. Select (Prod)employee, input SELECT * FROM salary; and run, you can see the amount column is masked.

Audit Log

Bytebase records everything happened within the system. You may check the audit log.
Log in as Owner, and go to Settings > Audit Log. You can see all the queries you've run.

Watermark

Bytebase provides watermark feature to help you identify the data source. You can click Settings > General, and scroll to Security to enable it.

Summary

With Bytebase, you have now tried out the basic management of data access for developers. If you want to know more about database change as well, you can check DevOps: Database Change Management with PostgreSQL.