CREATE TABLE AS SELECT copied a whole table: what it leaves behind

VERICTO-084 High In CI (offline CLI): BLOCKED

CREATE TABLE users_copy AS SELECT * FROM users; creates a full copy of users. Nothing is lost, but the copy has none of the original's indexes, constraints, row level security policies or grants: it is owned by whoever ran the statement, and any personal data in it now lives outside the rules that protected the original. SELECT ... INTO and CREATE MATERIALIZED VIEW ... AS SELECT do the same.

How to recover the data

  • Check whether anything reads the copy; if not, drop it: DROP TABLE users_copy;
  • If it must stay, apply the same access rules as the original (grants, RLS) and remove the columns it does not need.
  • If the copy holds sensitive data, remember that backups and replicas taken since then contain it too.

Why it happens

A quick copy is the reflex before a risky change, for a one-off analysis or to hand data to another tool. CREATE TABLE AS is the shortest way to write it, and with SELECT * and no WHERE it copies everything, on a large table also taking a long time and a lot of storage.

How to fix it

Copy only the columns and rows you need:

CREATE TABLE users_recent AS
SELECT id, created_at FROM users WHERE created_at >= '2026-01-01';

To copy only the structure, use CREATE TABLE users_copy (LIKE users INCLUDING ALL);. For a backup, use pg_dump or a snapshot instead of a table in the same database.

How to stop it from happening again

VERICTO-084 · CREATE TABLE AS SELECT · Severity: High · AST node: CreateTableAsStmt

The statement the rule reports

-- copy.sql
CREATE TABLE users_copy AS SELECT * FROM users;

Real CLI output

$ npx @vericto/vericto-cli@2.3.0 check --offline copy.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
✖ copy.sql  BLOCKED  VERICTO-084 high
    CreateTableAsStmt
    fix: Add a WHERE/LIMIT to the source SELECT, or use a reviewed migration
1 blocked · 0 allowed · 0 flagged · 0 monitored   (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))

✖ Vericto blocked 1 statement  (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
  ✖ copy.sql  BLOCKED  VERICTO-084 high
      CreateTableAsStmt
      fix: Add a WHERE/LIMIT to the source SELECT, or use a reviewed migration

This is Vericto enforcing a rule — the exit code is intentional, not a crash.
To allow a finding on purpose, add an inline comment on the statement:
    -- vericto:ignore[VERICTO-084] <reason>   (a reason is required)

The CLI blocks it and exits with code 1, so the CI job fails before the merge.

Try it free, no account

The CLI checks your .sql files on your machine with the 42 standard rules: no sign-up, no API key, and no SQL leaves your computer. It is open source (AGPL-3.0-only).

npx @vericto/vericto-cli@2.3.0 check --offline migrations/

On every pull request

The vericto/check-sql GitHub Action (Apache-2.0) runs the same offline check on the .sql files a PR changes and annotates the findings on the PR. Save it as .github/workflows/vericto.yml:

on: pull_request
jobs:
  vericto:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v7
        with: { fetch-depth: 0 }
      - uses: vericto/check-sql@v1
        with: { offline: true, upload-sarif: false }

At runtime, for AI agents

The SQL an AI agent writes never goes through a PR. The Vericto TCP proxy checks each statement between the application or agent and the database, and the MCP check_sql tool lets the agent check SQL before it runs it. Both use the runtime profile, which also blocks schema changes.

The AST condition in the engine

The rule is a deterministic predicate over the AST, in src/rules/evaluator.rs of the open source engine (AGPL-3.0-only), at this page's commit:

"VERICTO-084" => find(stmts, |s| s.kind == StatementKind::CreateTableAs)
    .map(|s| {
        let mut v = violation(
            rule,
            s,
            Some("Add a WHERE/LIMIT to the source SELECT, or use a reviewed migration".to_string()),
        );
        v.ast_node_path = "CreateTableAsStmt".to_string();
        v
    }),

Frequently asked questions