Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package migrator_test
import (
"context"
"database/sql"
"testing"
"time"
"github.com/stretchr/testify/require"
"github.com/bytebase/bytebase/backend/common/testcontainer"
"github.com/bytebase/bytebase/backend/migrator"
)
func TestMain(m *testing.M) { testcontainer.Main(m) }
func TestMigration3_24_1_MSSQLVerifyTLSOff(t *testing.T) {
ctx := context.Background()
_, db := testcontainer.NewPgDatabase(t)
_, err := db.ExecContext(ctx, `
CREATE TABLE instance (
resource_id TEXT PRIMARY KEY,
metadata JSONB NOT NULL DEFAULT '{}'
);
INSERT INTO instance (resource_id, metadata) VALUES
('mssql-verify', '{"engine":"MSSQL","dataSources":[{"id":"admin","useSsl":true,"verifyTlsCertificate":true,"sslCa":"ca"},{"id":"ro","type":"READ_ONLY","useSsl":true,"verifyTlsCertificate":true}]}'),
('mssql-verify-without-tls', '{"engine":"MSSQL","dataSources":[{"id":"admin","useSsl":true,"verifyTlsCertificate":true},{"id":"ro","type":"READ_ONLY","verifyTlsCertificate":true}]}'),
('mssql-tls', '{"engine":"MSSQL","dataSources":[{"id":"admin","useSsl":true}]}'),
('mssql-no-data-sources', '{"engine":"MSSQL","dataSources":[]}'),
('postgres-verify', '{"engine":"POSTGRES","dataSources":[{"id":"admin","useSsl":true,"verifyTlsCertificate":true}]}');
`)
require.NoError(t, err)
statement, err := migrator.MigrationFS.ReadFile("migration/3.24/0001##mssql_verify_tls_off.sql")
require.NoError(t, err)
_, err = db.ExecContext(ctx, string(statement))
require.NoError(t, err)
for resourceID, want := range map[string]string{
"mssql-verify": `{"engine":"MSSQL","dataSources":[{"id":"admin","useSsl":true,"sslCa":"ca"},{"id":"ro","type":"READ_ONLY","useSsl":true}]}`,
"mssql-verify-without-tls": `{"engine":"MSSQL","dataSources":[{"id":"admin","useSsl":true},{"id":"ro","type":"READ_ONLY","verifyTlsCertificate":true}]}`,
"mssql-tls": `{"engine":"MSSQL","dataSources":[{"id":"admin","useSsl":true}]}`,
"mssql-no-data-sources": `{"engine":"MSSQL","dataSources":[]}`,
"postgres-verify": `{"engine":"POSTGRES","dataSources":[{"id":"admin","useSsl":true,"verifyTlsCertificate":true}]}`,
} {
var got string
require.NoError(t, db.QueryRowContext(ctx, `SELECT metadata::text FROM instance WHERE resource_id = $1`, resourceID).Scan(&got))
require.JSONEq(t, want, got, resourceID)
}
}
func TestMigration3_24_2_OracleTLSOff(t *testing.T) {
ctx := context.Background()
_, db := testcontainer.NewPgDatabase(t)
_, err := db.ExecContext(ctx, `
CREATE TABLE instance (
resource_id TEXT PRIMARY KEY,
metadata JSONB NOT NULL DEFAULT '{}'
);
INSERT INTO instance (resource_id, metadata) VALUES
('oracle-tls', '{"engine":"ORACLE","dataSources":[{"id":"admin","useSsl":true,"verifyTlsCertificate":true,"obfuscatedSslCa":"ca","obfuscatedSslCert":"cert","obfuscatedSslKey":"key"},{"id":"ro","type":"READ_ONLY","useSsl":true,"obfuscatedSslCaPath":"ca-path","obfuscatedSslCertPath":"cert-path","obfuscatedSslKeyPath":"key-path","extraConnectionParameters":{"SSL":"TRUE","WALLET":"/opt/oracle/wallet"}}]}'),
('oracle-tls-on-one', '{"engine":"ORACLE","dataSources":[{"id":"admin","useSsl":true},{"id":"ro","type":"READ_ONLY","verifyTlsCertificate":true,"obfuscatedSslCa":"ca"}]}'),
('oracle-tls-off-with-ca', '{"engine":"ORACLE","dataSources":[{"id":"admin","verifyTlsCertificate":true,"obfuscatedSslCa":"ca"}]}'),
('oracle-no-tls', '{"engine":"ORACLE","dataSources":[{"id":"admin","verifyTlsCertificate":true}]}'),
('oracle-no-data-sources', '{"engine":"ORACLE","dataSources":[]}'),
('oracle-without-data-sources', '{"engine":"ORACLE"}'),
('oracle-null-data-sources', '{"engine":"ORACLE","dataSources":null}'),
('postgres-tls', '{"engine":"POSTGRES","dataSources":[{"id":"admin","useSsl":true,"obfuscatedSslCa":"ca"}]}'),
('mssql-tls', '{"engine":"MSSQL","dataSources":[{"id":"admin","useSsl":true}]}');
`)
require.NoError(t, err)
statement, err := migrator.MigrationFS.ReadFile("migration/3.24/0002##oracle_tls_off.sql")
require.NoError(t, err)
_, err = db.ExecContext(ctx, string(statement))
require.NoError(t, err)
for resourceID, want := range map[string]string{
"oracle-tls": `{"engine":"ORACLE","dataSources":[{"id":"admin","verifyTlsCertificate":true},{"id":"ro","type":"READ_ONLY","extraConnectionParameters":{"SSL":"TRUE","WALLET":"/opt/oracle/wallet"}}]}`,
"oracle-tls-on-one": `{"engine":"ORACLE","dataSources":[{"id":"admin"},{"id":"ro","type":"READ_ONLY","verifyTlsCertificate":true}]}`,
"oracle-tls-off-with-ca": `{"engine":"ORACLE","dataSources":[{"id":"admin","verifyTlsCertificate":true}]}`,
"oracle-no-tls": `{"engine":"ORACLE","dataSources":[{"id":"admin","verifyTlsCertificate":true}]}`,
"oracle-no-data-sources": `{"engine":"ORACLE","dataSources":[]}`,
"oracle-without-data-sources": `{"engine":"ORACLE"}`,
"oracle-null-data-sources": `{"engine":"ORACLE","dataSources":null}`,
"postgres-tls": `{"engine":"POSTGRES","dataSources":[{"id":"admin","useSsl":true,"obfuscatedSslCa":"ca"}]}`,
"mssql-tls": `{"engine":"MSSQL","dataSources":[{"id":"admin","useSsl":true}]}`,
} {
var got string
require.NoError(t, db.QueryRowContext(ctx, `SELECT metadata::text FROM instance WHERE resource_id = $1`, resourceID).Scan(&got))
require.JSONEq(t, want, got, resourceID)
}
}
func TestMigration3_25_1_DBSchemaSyncedAtBackfill(t *testing.T) {
ctx := context.Background()
_, db := testcontainer.NewPgDatabase(t)
_, err := db.ExecContext(ctx, `
CREATE TABLE db (
instance TEXT NOT NULL,
name TEXT NOT NULL,
metadata JSONB NOT NULL DEFAULT '{}',
PRIMARY KEY (instance, name)
);
CREATE TABLE db_schema (
instance TEXT NOT NULL,
db_name TEXT NOT NULL,
raw_dump TEXT NOT NULL DEFAULT '',
PRIMARY KEY (instance, db_name)
);
INSERT INTO db (instance, name, metadata) VALUES
-- protojson writes a Timestamp with nanosecond precision as nine
-- fractional digits, more than timestamptz keeps.
('prod', 'synced', '{"lastSyncTime":"2026-10-28T04:05:06.123456789Z","syncStatus":"SYNC_STATUS_OK"}'),
('prod', 'whole-second', '{"lastSyncTime":"2026-10-28T04:05:06Z"}'),
('prod', 'never-synced', '{"syncStatus":"SYNC_STATUS_OK"}'),
-- A clock that ran ahead, so the recorded time is in the future.
('prod', 'clock-ahead', '{"lastSyncTime":"2099-01-01T00:00:00Z"}'),
-- Same database name in another instance: db_schema is keyed by
-- (instance, db_name) and the backfill joins on the whole key.
('staging', 'synced', '{"lastSyncTime":"2026-10-20T01:02:03Z"}');
INSERT INTO db_schema (instance, db_name, raw_dump) VALUES
('prod', 'synced', 'prod dump'),
('prod', 'whole-second', 'whole second dump'),
('prod', 'never-synced', 'unstamped dump'),
('prod', 'clock-ahead', 'clock ahead dump'),
('staging', 'synced', 'staging dump');
`)
require.NoError(t, err)
statement, err := migrator.MigrationFS.ReadFile("migration/3.25/0001##db_schema_synced_at.sql")
require.NoError(t, err)
_, err = db.ExecContext(ctx, string(statement))
require.NoError(t, err)
for _, tc := range []struct {
instance string
databaseName string
want string
wantClamped bool
}{
{instance: "prod", databaseName: "synced", want: "2026-10-28T04:05:06.123457Z"},
{instance: "prod", databaseName: "whole-second", want: "2026-10-28T04:05:06Z"},
{instance: "prod", databaseName: "never-synced"},
{instance: "prod", databaseName: "clock-ahead", wantClamped: true},
{instance: "staging", databaseName: "synced", want: "2026-10-20T01:02:03Z"},
} {
var syncedAt sql.NullTime
require.NoError(t, db.QueryRowContext(ctx,
`SELECT synced_at FROM db_schema WHERE instance = $1 AND db_name = $2`,
tc.instance, tc.databaseName).Scan(&syncedAt))
switch {
case tc.wantClamped:
// A stamp in the future would refuse every sync until wall clock passed it.
require.True(t, syncedAt.Valid, "%s/%s", tc.instance, tc.databaseName)
require.WithinDuration(t, time.Now(), syncedAt.Time, time.Hour, "%s/%s", tc.instance, tc.databaseName)
case tc.want == "":
require.False(t, syncedAt.Valid, "%s/%s", tc.instance, tc.databaseName)
default:
require.True(t, syncedAt.Valid, "%s/%s", tc.instance, tc.databaseName)
require.Equal(t, tc.want, syncedAt.Time.UTC().Format(time.RFC3339Nano), "%s/%s", tc.instance, tc.databaseName)
}
}
}
func TestMigration3_25_0_SQLExportPermission(t *testing.T) {
ctx := context.Background()
_, db := testcontainer.NewPgDatabase(t)
createMigration3_25_0Tables(ctx, t, db)
_, err := db.ExecContext(ctx, `
INSERT INTO role (resource_id, workspace, permissions) VALUES
('export-select', 'ws-export', '{"permissions":["bb.databases.get","bb.sql.select"]}'),
('export-no-select', 'ws-export', '{"permissions":["bb.databases.get","bb.sql.info"]}'),
('export-already', 'ws-export', '{"permissions":["bb.sql.select","bb.sql.export"]}'),
('approval-select', 'ws-approval', '{"permissions":["bb.databases.get","bb.sql.select"]}'),
('approval-no-select', 'ws-approval', '{"permissions":["bb.databases.get"]}'),
('unenforced-select', 'ws-unenforced', '{"permissions":["bb.sql.select"]}'),
('unchanged-no-select', 'ws-unchanged', '{"permissions":["bb.databases.get"]}');
`)
require.NoError(t, err)
_, err = db.ExecContext(ctx, `
INSERT INTO policy (workspace, resource_type, resource, type, enforce, payload) VALUES
('ws-export', 'WORKSPACE', 'workspaces/ws-export', 'IAM', true, $1),
('ws-export', 'PROJECT', 'projects/export-project', 'IAM', true, $2),
('ws-export', 'WORKSPACE', 'workspaces/ws-export', 'QUERY_DATA', true, '{"maximumResultRows":100,"disableCopyData":true}'),
('ws-export', 'PROJECT', 'projects/export-project', 'QUERY_DATA', true, '{"maximumResultRows":50,"disableExport":true}'),
('ws-approval', 'WORKSPACE', 'workspaces/ws-approval', 'IAM', true, $3),
('ws-approval', 'PROJECT', 'projects/approval-project', 'IAM', true, $4),
('ws-approval', 'WORKSPACE', 'workspaces/ws-approval', 'QUERY_DATA', true, '{"disableExport":true,"maximumResultRows":10,"allowAdminDataSource":true}'),
('ws-unenforced', 'PROJECT', 'projects/unenforced-project', 'IAM', true, $5),
('ws-unenforced', 'WORKSPACE', 'workspaces/ws-unenforced', 'QUERY_DATA', false, '{"disableExport":true,"disableCopyData":true}'),
('ws-unchanged', 'WORKSPACE', 'workspaces/ws-unchanged', 'IAM', true, $6),
('ws-unchanged', 'WORKSPACE', 'workspaces/ws-unchanged', 'QUERY_DATA', true, '{"maximumResultRows":5}');
`,
migration3_25_0WorkspaceIAM,
migration3_25_0ProjectIAM,
migration3_25_0WorkspaceIAM,
migration3_25_0ProjectIAM,
`{"bindings":[{"role":"roles/sqlEditorReadUser","members":["groups/[email protected]"]}]}`,
`{"bindings":[{"role":"roles/workspaceMember","members":["allUsers"]}]}`,
)
require.NoError(t, err)
rolePermissions := func() map[string]string {
rows, err := db.QueryContext(ctx, `SELECT resource_id, permissions::text FROM role`)
require.NoError(t, err)
defer rows.Close()
permissions := map[string]string{}
for rows.Next() {
var role, text string
require.NoError(t, rows.Scan(&role, &text))
permissions[role] = text
}
require.NoError(t, rows.Err())
return permissions
}
rolesBefore := rolePermissions()
require.Len(t, rolesBefore, 7)
require.NoError(t, runMigration3_25_0SQL(ctx, db))
require.Equal(t, rolesBefore, rolePermissions(), "the migration does not change custom roles")
type policyKey struct{ workspace, resource, policyType string }
type policyWant struct {
enforce bool
payload string
}
for key, want := range map[policyKey]policyWant{
{"ws-export", "workspaces/ws-export", "IAM"}: {true, `{"bindings":[
{"role":"roles/workspaceMember","members":["allUsers"]},
{"role":"roles/sqlEditorUser","members":["users/[email protected]"],"condition":{"title":"prod app","expression":"resource.database in [\"instances/prod/databases/app\"] && request.time < timestamp(\"2027-01-01T00:00:00Z\")"}},
{"role":"roles/sqlEditorReadUser","members":["groups/[email protected]"]},
{"role":"roles/sqlEditorExporter","members":["users/[email protected]"],"condition":{"title":"prod app","expression":"resource.database in [\"instances/prod/databases/app\"] && request.time < timestamp(\"2027-01-01T00:00:00Z\")"}},
{"role":"roles/sqlEditorExporter","members":["groups/[email protected]"]}]}`},
{"ws-export", "projects/export-project", "IAM"}: {true, `{"bindings":[
{"role":"roles/projectDeveloper","members":["users/[email protected]"]},
{"role":"roles/sqlEditorReadUser","members":["groups/[email protected]"]},
{"role":"roles/sqlEditorUser","members":["users/[email protected]","allUsers"],"condition":{"expression":"resource.database == \"instances/prod/databases/app\" && resource.schema_name == \"public\" && resource.table_name in [\"orders\"]"}},
{"role":"roles/sqlEditorExporter","members":["groups/[email protected]"]},
{"role":"roles/sqlEditorExporter","members":["users/[email protected]","allUsers"],"condition":{"expression":"resource.database == \"instances/prod/databases/app\" && resource.schema_name == \"public\" && resource.table_name in [\"orders\"]"}}]}`},
{"ws-export", "workspaces/ws-export", "QUERY_DATA"}: {true, `{"maximumResultRows":100,"disableCopyData":true}`},
{"ws-export", "projects/export-project", "QUERY_DATA"}: {true, `{"maximumResultRows":50}`},
{"ws-approval", "workspaces/ws-approval", "IAM"}: {true, migration3_25_0WorkspaceIAM},
{"ws-approval", "projects/approval-project", "IAM"}: {true, migration3_25_0ProjectIAM},
{"ws-approval", "workspaces/ws-approval", "QUERY_DATA"}: {true, `{"maximumResultRows":10,"allowAdminDataSource":true}`},
{"ws-unenforced", "projects/unenforced-project", "IAM"}: {true, `{"bindings":[{"role":"roles/sqlEditorReadUser","members":["groups/[email protected]"]}]}`},
{"ws-unenforced", "workspaces/ws-unenforced", "QUERY_DATA"}: {false, `{"disableCopyData":true}`},
{"ws-unchanged", "workspaces/ws-unchanged", "IAM"}: {true, `{"bindings":[{"role":"roles/workspaceMember","members":["allUsers"]}]}`},
{"ws-unchanged", "workspaces/ws-unchanged", "QUERY_DATA"}: {true, `{"maximumResultRows":5}`},
} {
var enforce bool
var payload string
require.NoError(t, db.QueryRowContext(ctx,
`SELECT enforce, payload::text FROM policy WHERE workspace = $1 AND resource = $2 AND type = $3`,
key.workspace, key.resource, key.policyType,
).Scan(&enforce, &payload))
require.Equal(t, want.enforce, enforce, key)
require.JSONEq(t, want.payload, payload, key)
}
}
func TestMigration3_25_0_SQLEditorExporterIDCollision(t *testing.T) {
ctx := context.Background()
_, db := testcontainer.NewPgDatabase(t)
createMigration3_25_0Tables(ctx, t, db)
_, err := db.ExecContext(ctx, `
INSERT INTO role (resource_id, workspace, permissions) VALUES
('sqlEditorExporter', 'ws-export', '{"permissions":["bb.sql.select"]}');
`)
require.NoError(t, err)
err = runMigration3_25_0SQL(ctx, db)
require.ErrorContains(t, err, `custom role "sqlEditorExporter" in workspace "ws-export" conflicts with a new built-in role`)
var got string
require.NoError(t, db.QueryRowContext(ctx, `SELECT permissions::text FROM role WHERE resource_id = 'sqlEditorExporter'`).Scan(&got))
require.JSONEq(t, `{"permissions":["bb.sql.select"]}`, got)
}
const (
migration3_25_0WorkspaceIAM = `{"bindings":[
{"role":"roles/workspaceMember","members":["allUsers"]},
{"role":"roles/sqlEditorUser","members":["users/[email protected]"],"condition":{"title":"prod app","expression":"resource.database in [\"instances/prod/databases/app\"] && request.time < timestamp(\"2027-01-01T00:00:00Z\")"}},
{"role":"roles/sqlEditorReadUser","members":["groups/[email protected]"]}]}`
migration3_25_0ProjectIAM = `{"bindings":[
{"role":"roles/projectDeveloper","members":["users/[email protected]"]},
{"role":"roles/sqlEditorReadUser","members":["groups/[email protected]"]},
{"role":"roles/sqlEditorUser","members":["users/[email protected]","allUsers"],"condition":{"expression":"resource.database == \"instances/prod/databases/app\" && resource.schema_name == \"public\" && resource.table_name in [\"orders\"]"}}]}`
)
func createMigration3_25_0Tables(ctx context.Context, t *testing.T, db *sql.DB) {
t.Helper()
_, err := db.ExecContext(ctx, `
CREATE TABLE role (
resource_id text NOT NULL PRIMARY KEY,
workspace text NOT NULL,
permissions jsonb NOT NULL DEFAULT '{}'
);
CREATE TABLE policy (
enforce boolean NOT NULL DEFAULT TRUE,
workspace text NOT NULL,
resource_type text NOT NULL,
resource text NOT NULL,
type text NOT NULL,
payload jsonb NOT NULL DEFAULT '{}',
PRIMARY KEY (workspace, resource_type, resource, type)
);
`)
require.NoError(t, err)
}
// runMigration3_25_0SQL runs the file in one transaction, as the migrator does.
func runMigration3_25_0SQL(ctx context.Context, db *sql.DB) error {
statement, err := migrator.MigrationFS.ReadFile("migration/3.25/0000##sql_export_permission.sql")
if err != nil {
return err
}
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
if _, err := tx.ExecContext(ctx, string(statement)); err != nil {
return err
}
return tx.Commit()
}