Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

13.8 KB
Raw Blame
// SQL Editor — workspace-level QueryDataPolicy gates (Batch 9, M-series).
//
// Covers:
//   - M2 disableCopyData hides the Copy button on a non-empty result.
//
// The field lives on a `QueryDataPolicy` policy attached to the
// workspace resource (PolicyType.DATA_QUERY). We capture the current
// policy in `beforeAll`, mutate per-test, and restore on `afterEach` so
// other specs see the original baseline.
//
// Export is gated by the bb.sql.export permission; sql-editor-export.spec.ts
// covers it.
//
// Deferred from this batch (need separate investigation):
//   - M3 maximumResultRows enforcement — frontend caps the row-limit
//     input (QueryContextSettingPopover) but backend enforcement is
//     less obvious; whose responsibility under what circumstances?
//   - M5 allowAdminDataSource gating the admin radio — requires an
//     environment with both admin + readonly data sources.
//   - M8 admin bypass on no-readonly — needs a fixture user without
//     readonly access; the I-series user provisioning pattern applies.
//
// LICENSE GATE: `FEATURE_QUERY_POLICY` / `FEATURE_RESTRICT_COPYING_DATA`
// gate UI controls; mutations of QueryDataPolicy may also be backend-
// gated. We try the mutation and skip the test gracefully if the API
// returns 403.

import { test, expect, type BrowserContext, type Page } from "@playwright/test";
import { loadTestEnv, type TestEnv } from "../framework/env";
import type { BytebaseApiClient } from "../framework/api-client";
import { execSql, getInstancePgPort, querySql } from "../framework/psql";
import { SqlEditorPage } from "./sql-editor.page";

test.setTimeout(120_000);

let env: TestEnv & { api: BytebaseApiClient };
let workspace = "";
let sharedContext: BrowserContext;
let page: Page;
let sqlEditor: SqlEditorPage;

// Snapshot of the original QueryDataPolicy body so afterEach can restore.
// Stored as the raw policy resource so we can re-PATCH with the same shape.
let originalQueryDataPolicy: {
  disableCopyData?: boolean;
  allowAdminDataSource?: boolean;
  maximumResultRows?: number;
} = {};

test.beforeAll(async ({ browser }) => {
  env = loadTestEnv();
  await env.api.login(env.adminEmail, env.adminPassword);
  ({ workspace } = await env.api.getActuatorInfo());

  // Snapshot the current workspace-level QueryDataPolicy (may not exist
  // yet on a fresh server — getPolicy returns null on 404).
  const existing = await env.api.getPolicy(`${workspace}/policies/data_query`);
  if (existing && typeof existing === "object") {
    originalQueryDataPolicy = (existing as {
      queryDataPolicy?: typeof originalQueryDataPolicy;
    }).queryDataPolicy ?? {};
  }

  sharedContext = await browser.newContext({ storageState: ".auth/state.json" });
  page = await sharedContext.newPage();
  sqlEditor = new SqlEditorPage(page, env.baseURL);
});

test.afterAll(async () => {
  await sharedContext?.close();
});

// Restore the original policy (or remove ours) after every test so
// state from one gate test doesn't shift another's baseline.
test.afterEach(async () => {
  await env.api
    .upsertPolicy(workspace, "data_query", {
      name: `${workspace}/policies/data_query`,
      type: "DATA_QUERY",
      resourceType: "WORKSPACE",
      queryDataPolicy: {
        disableCopyData: originalQueryDataPolicy.disableCopyData ?? false,
        allowAdminDataSource:
          originalQueryDataPolicy.allowAdminDataSource ?? false,
        maximumResultRows: originalQueryDataPolicy.maximumResultRows ?? 0,
      },
    })
    .catch(() => {
      /* ignore — license gate may reject; baseline already in place */
    });
});

// Helper — try to apply a workspace-level QueryDataPolicy mutation. If
// the gateway returns 403 (FEATURE_QUERY_POLICY / FEATURE_RESTRICT_COPYING_DATA
// require enterprise), test skips so we don't false-fail on free plan.
async function applyQueryDataPolicy(
  patch: Partial<typeof originalQueryDataPolicy>,
): Promise<void> {
  await env.api.upsertPolicy(workspace, "data_query", {
    name: `${workspace}/policies/data_query`,
    type: "DATA_QUERY",
    resourceType: "WORKSPACE",
    queryDataPolicy: {
      // Preserve all other fields; only mutate the ones the test cares
      // about.
      disableCopyData:
        patch.disableCopyData ??
        originalQueryDataPolicy.disableCopyData ??
        false,
      allowAdminDataSource:
        patch.allowAdminDataSource ??
        originalQueryDataPolicy.allowAdminDataSource ??
        false,
      maximumResultRows:
        patch.maximumResultRows ??
        originalQueryDataPolicy.maximumResultRows ??
        0,
    },
  });
}

test.describe("disableCopyData hides the Copy button on results (M2)", () => {
  test("setting disableCopyData=true removes the Copy button", async () => {
    await applyQueryDataPolicy({ disableCopyData: true });

    const projectId = env.project.split("/").pop()!;
    await sqlEditor.gotoWithDb(projectId, env.instanceId, env.databaseId);
    await sqlEditor.runPreparedQuery("SELECT 1 AS n;");
    await expect(page.getByText(/^1\s+rows?$/i).first()).toBeVisible({
      timeout: 10_000,
    });

    // The results-toolbar Copy button reads "Copy all" (SingleResultView — the
    // split-button main action, rendered when !disallowCopyingData && rows>0).
    // It must be gone. NB: the status bar has a SEPARATE, always-present
    // statement "Copy" button (ResultStatusBar) that this gate does not govern —
    // target "Copy all" exactly so it isn't mistaken for the results Copy.
    await expect(
      page.getByRole("button", { name: "Copy all", exact: true }),
    ).toHaveCount(0);
  });

  test("with disableCopyData=false (baseline) the Copy button is present", async () => {
    await applyQueryDataPolicy({ disableCopyData: false }).catch(() => {});

    const projectId = env.project.split("/").pop()!;
    await sqlEditor.gotoWithDb(projectId, env.instanceId, env.databaseId);
    await sqlEditor.runPreparedQuery("SELECT 1 AS n;");
    await expect(page.getByText(/^1\s+rows?$/i).first()).toBeVisible({
      timeout: 10_000,
    });

    await expect(
      page.getByRole("button", { name: "Copy all", exact: true }).first(),
    ).toBeVisible({ timeout: 5_000 });
  });
});

// --- Automatic data source routes DML to the admin data source (BYT-9557) ----
//
// BYT-9557 (FIXED, #20390): on an instance with BOTH an admin and a read-only
// data source, the SQL editor in "Automatic" mode routed EVERY statement —
// including DDL/DML — to the read-only data source. With "Allow querying the
// admin data source" enabled, a DML statement still failed with a Postgres
// "permission denied" error because it executed as the read-only DB user;
// manually switching to the admin data source made the same statement succeed.
// The fix classifies the statement: read-only statements go to the read-only
// data source, but DDL/DML are routed to the admin data source.
//
// This is the M5 prerequisite the file header deferred (needs an instance with
// both an admin + a read-only data source). It mutates the shared env instance,
// so it adds the RO data source AFTER the admin source (index 0 stays admin, so
// getInstancePgPort is unaffected) and removes it in afterAll.

test.describe("Automatic mode routes DML to the admin data source (BYT-9557)", () => {
  const RO_DS_ID = "e2e-ro-9557";
  const RO_USER = "bb_e2e_ro_9557";
  const RO_PROBE_TABLE = "e2e_9557_probe";
  const RO_PASSWORD = "bytebase"; // NOSONAR — e2e fixture only
  let pgPort = "";
  let adminHost = "";
  let roAdded = false;

  test.beforeAll(async () => {
    test.setTimeout(120_000);
    pgPort = await getInstancePgPort(env);
    const instance = await env.api.getInstance(env.instance);
    adminHost = instance.dataSources?.[0]?.host ?? "/tmp";

    // Create a SELECT-only Postgres user on the sample instance (idempotent).
    // execSql throws synchronously; swallow the pre-create cleanup (the user may
    // not exist yet on a first run).
    try {
      execSql(env.databaseId, pgPort, `DROP OWNED BY ${RO_USER}`);
    } catch {
      /* user may not exist yet */
    }
    try {
      execSql(env.databaseId, pgPort, `DROP USER IF EXISTS ${RO_USER}`);
    } catch {
      /* may fail if it owns objects — DROP OWNED above handles the common case */
    }
    execSql(
      env.databaseId,
      pgPort,
      `CREATE USER ${RO_USER} PASSWORD '${RO_PASSWORD}'`,
    );
    execSql(
      env.databaseId,
      pgPort,
      `GRANT CONNECT ON DATABASE ${env.databaseId} TO ${RO_USER}`,
    );
    execSql(
      env.databaseId,
      pgPort,
      `GRANT USAGE ON SCHEMA public TO ${RO_USER}`,
    );
    execSql(
      env.databaseId,
      pgPort,
      `GRANT SELECT ON ALL TABLES IN SCHEMA public TO ${RO_USER}`,
    );
    // A scratch table OWNED BY the admin (bbsample) that the RO user can only
    // SELECT — never UPDATE. A plain UPDATE of this table thus succeeds only if
    // automatic mode routed the DML to the admin data source (the fix); if it
    // routed to the read-only data source it fails with "permission denied".
    execSql(env.databaseId, pgPort, `DROP TABLE IF EXISTS ${RO_PROBE_TABLE}`);
    execSql(
      env.databaseId,
      pgPort,
      `CREATE TABLE ${RO_PROBE_TABLE} (id int PRIMARY KEY, v text)`,
    );
    execSql(env.databaseId, pgPort, `INSERT INTO ${RO_PROBE_TABLE} VALUES (1, 'before')`);
    execSql(env.databaseId, pgPort, `GRANT SELECT ON ${RO_PROBE_TABLE} TO ${RO_USER}`);

    // Add the READ_ONLY data source pointing at the same Postgres.
    await env.api.addDataSource(env.instance, {
      id: RO_DS_ID,
      type: "READ_ONLY",
      username: RO_USER,
      password: RO_PASSWORD,
      host: adminHost,
      port: pgPort,
    });
    roAdded = true;
  });

  test.afterAll(async () => {
    if (roAdded) await env.api.removeDataSource(env.instance, RO_DS_ID);
    try {
      execSql(env.databaseId, pgPort, `DROP OWNED BY ${RO_USER}`);
      execSql(env.databaseId, pgPort, `DROP USER IF EXISTS ${RO_USER}`);
    } catch {
      /* best-effort teardown */
    }
    try {
      execSql(env.databaseId, pgPort, `DROP TABLE IF EXISTS ${RO_PROBE_TABLE}`);
    } catch {
      /* best-effort teardown */
    }
  });

  test("SELECT routes to the read-only data source; DDL succeeds (routed to admin)", async () => {
    test.setTimeout(120_000);
    // Allow the admin data source for automatic DDL/DML routing.
    await applyQueryDataPolicy({ allowAdminDataSource: true });

    const projectId = env.project.split("/").pop()!;
    await sqlEditor.gotoWithDb(projectId, env.instanceId, env.databaseId);
    await page.waitForTimeout(1200);

    // Sanity oracle: in Automatic mode (the default — empty dataSourceId), a
    // read-only statement routes to the read-only data source, so current_user
    // is the RO user. This also proves the RO data source is wired and selected
    // automatically.
    await sqlEditor.runPreparedQuery("SELECT current_user;");
    const selectCell = page.locator(
      '[data-row-index="0"] [data-col-index="1"]',
    );
    await expect(selectCell).toBeVisible({ timeout: 10_000 });
    await expect(
      selectCell,
      "Automatic mode must route a read-only SELECT to the read-only data source",
    ).toHaveText(RO_USER, { timeout: 5000 });

    // Regression oracle: a plain UPDATE (no RETURNING — so it can't be treated
    // as a read) of the admin-owned scratch table, which the RO user can only
    // SELECT. If automatic mode routed the DML to the read-only data source it
    // fails with "permission denied for table"; the fix routes DML to the ADMIN
    // data source (the table's owner), so it succeeds. Pre-fix this rendered the
    // permission-denied error.
    // Re-open the editor in a fresh tab before the UPDATE. runPreparedQuery's
    // setEditorContent does NOT reliably clear a SECOND statement once a result
    // is on screen: the prior SELECT's text is retained, the editor ends up with
    // "SELECT current_user;UPDATE …", Run executes only the SELECT, and the
    // UPDATE never runs — so the old "no permission-denied error" assertion
    // passed vacuously (no UPDATE → no denial). A fresh editor runs the UPDATE
    // in isolation. (Verified: with the fresh editor the UPDATE executes, the UI
    // shows "rows affected", and the row becomes 'after'.)
    await sqlEditor.gotoWithDb(projectId, env.instanceId, env.databaseId);
    await page.waitForTimeout(1200);
    await sqlEditor.setEditorContent(
      `UPDATE ${RO_PROBE_TABLE} SET v = 'after' WHERE id = 1;`,
    );
    await sqlEditor.runButton.click();

    // Positive oracle anchored to THIS statement's effect: poll the row as the
    // owner until v flips 'before' -> 'after'. This both waits for the UPDATE to
    // resolve and proves it actually applied. If automatic mode (wrongly) routed
    // the DML to the read-only data source it would be permission-denied and v
    // would stay 'before', so the poll would time out and fail — unlike a bare
    // "no error visible yet" assertion, which can pass before the statement even
    // resolves.
    await expect
      .poll(
        () =>
          querySql(
            env.databaseId,
            pgPort,
            `SELECT v FROM ${RO_PROBE_TABLE} WHERE id = 1`,
          ),
        {
          timeout: 15_000,
          message:
            "the admin-routed UPDATE must apply (v='after'); if automatic mode " +
            "routed the DML to the read-only data source it would be denied and " +
            "v would stay 'before'",
        },
      )
      .toBe("after");

    // The UPDATE having landed, the user must also not see a permission-denied
    // error. Safe now — the poll proved the statement resolved, so this is not
    // racing it. Pre-fix this rendered "ERROR: permission denied for table".
    await expect(
      page.getByText(/permission denied/i),
      "the DML must not be denied — it should route to the admin data source, " +
        "not the read-only one (which can only SELECT the admin-owned table)",
    ).toHaveCount(0, { timeout: 5000 });
  });
});