• fix(coderd/database): remove hardcoded public schema from migration 000401 #21493
• chore: add lint/migrations to detect hardcoded public schema #21496
• fix: remove unreachable exit after error call in check_pg_schema.sh #21530

NB these commits were originally authored by Blink on behalf of @dannykopping, so amended to reflect actual authorship.

Repro/Verification Steps:

• Created a Coder deployment with a non-public schema via Docker compose on v2.28.6:

  • Created a DB init script under db-init/01-create-schema.sql with the following:

    CREATE SCHEMA IF NOT EXISTS coder AUTHORIZATION coder;
    GRANT ALL PRIVILEGES ON SCHEMA coder TO coder;
    ALTER ROLE coder SET search_path TO coder;

  • Mounted above inside the postgres container:

         volumes:
           - coder_data:/var/lib/postgresql/data
    +      - ./db-init:/docker-entrypoint-initdb.d:ro

  • Edited CODER_PG_CONNECTION_URL to update the search path:

    environment:
    -      CODER_PG_CONNECTION_URL: "postgresql://${POSTGRES_USER:-username}:${POSTGRES_PASSWORD:-password}@database/${POSTGRES_DB:-coder}?sslmode=disable"
    +      CODER_PG_CONNECTION_URL: "postgresql://${POSTGRES_USER:-username}:${POSTGRES_PASSWORD:-password}@database/${POSTGRES_DB:-coder}?sslmode=disable&search_path=coder"

  • Brought up the deployment:

    CODER_VERSION=v2.28.6 CODER_ACCESS_URL=http://localhost:7080 POSTGRES_USER=coder POSTGRES_PASSWORD=coder docker compose up`

  • Created user / template / workspace

• Updated to v2.29.1:

  • CODER_VERSION=v2.29.1 CODER_ACCESS_URL=http://localhost:7080 POSTGRES_USER=coder POSTGRES_PASSWORD=coder docker compose up`

  • Observed following error:

    database-1  | 2026-01-21 15:07:17.629 UTC [102] ERROR:  relation "public.workspace_agents" does not exist
    coder-1     | Encountered an error running "coder server", see "coder server --help" for more information
    database-1  | 2026-01-21 15:07:17.629 UTC [102] STATEMENT:  CREATE INDEX IF NOT EXISTS workspace_agents_auth_instance_id_deleted_idx ON public.workspace_agents (auth_instance_id, deleted);
    coder-1     | error: connect to postgres: connect to postgres: migrate up: up: 2 errors occurred:
    coder-1     | 	* run statement: migration failed: relation "public.workspace_agents" does not exist in line 0: CREATE INDEX IF NOT EXISTS workspace_agents_auth_instance_id_deleted_idx ON public.workspace_agents (auth_instance_id, deleted);
    coder-1     |  (details: pq: relation "public.workspace_agents" does not exist)
    coder-1     | 	* commit tx on unlock: pq: Could not complete operation in a failed transaction
    coder-1 exited with code 1
    

  • Built image locally:

    $ make build/coder_$(./scripts/version.sh)_linux_amd64.tag
    ...
    ghcr.io/coder/coder:v2.29.1-devel-e8c482a98a67-amd64

  • Started with new image:

    CODER_VERSION=v2.29.1-devel-e8c482a98a67-amd64 CODER_ACCESS_URL=http://localhost:7080 POSTGRES_USER=coder POSTGRES_PASSWORD=coder docker compose up

  • Observed migrations ran successfully and Coder came up successfully