Issue: PostgreSQL migration fails with `syntax error at or near "binary"` in gateway (v0.0.7)

[AdnanSattar]

Agent Diagnostic

Agent Diagnostic

Investigation Summary

• Reproduced the issue consistently on a clean environment using Docker Compose with PostgreSQL 15
• Observed failure during SQLx migration execution (migration 3)
• Verified that the error is deterministic and not related to database state or connectivity

Signals Collected

• Gateway logs show:
  • SQLx migration execution failure
  • PostgreSQL error: syntax error at or near "binary"
• PostgreSQL logs confirm failure occurs during execution of:
  • 003_create_policy_recommendations.sql

Schema Inspection

• Located failing SQL segment:

CREATE UNIQUE INDEX IF NOT EXISTS idx_draft_chunks_endpoint
ON draft_policy_chunks (sandbox_id, host, port, binary)
WHERE status IN ('pending', 'approved', 'rejected');

• Column definition:

binary TEXT NOT NULL DEFAULT ''

Findings

• BYTEA usage in migration is valid → not a datatype issue

• Failure is triggered specifically by usage of binary in index definition

• PostgreSQL parser rejects this usage, indicating:

• identifier conflict

• or parsing ambiguity requiring quoting

• SQLite version of the migration does not fail, indicating dialect difference

What Was Tested

• Clean environment:
• docker compose down -v
• full restart
• Verified correct DB connection string
• Tested with stable gateway image 0.0.7
• Confirmed issue persists across runs (not transient)

Hypothesis

• PostgreSQL migration contains an identifier (binary) that is not safely parsed
• Requires quoting ("binary") or renaming to be PostgreSQL-compliant

Conclusion

• Issue is isolated to PostgreSQL migration logic

• Not related to:

• container architecture

• database configuration

• network setup

• Root cause is likely migration-level incompatibility across database dialects

Description

Issue: PostgreSQL migration fails with syntax error at or near "binary" in gateway (v0.0.7)

Environment

• OpenShell Gateway: 0.0.7
• Database: PostgreSQL 15
• Deployment: Docker Compose
• Architecture: amd64

---

Issue

The gateway fails to start due to a migration error during initialization:

Error: migration error: while executing migration 3
returned from database: syntax error at or near "binary"

This results in a restart loop and prevents the gateway from becoming operational.

---

Logs

openclaw@staging:~/openshell-gateway$ docker compose up
[+] up 29/29
✔ Image ghcr.io/nvidia/openshell/gateway:0.0.7 Pulled 83.5ss
✔ Image postgres:15 Pulled 143.4s
✔ Network openshell-gateway_openshell-net Created 0.0s
✔ Volume openshell-gateway_openshell-data Created 0.0s
✔ Volume openshell-gateway_openshell-workspace Created 0.0s
✔ Volume openshell-gateway_postgres-data Created 0.0s
✔ Container openshell-postgres Created 0.2s
✔ Container openshell-gateway Created 0.0s
Attaching to openshell-gateway, openshell-postgres
openshell-postgres | The files belonging to this database system will be owned by user "postgres".
openshell-postgres | This user must also own the server process.
openshell-postgres |
openshell-postgres | The database cluster will be initialized with locale "en_US.utf8".
openshell-postgres | The default database encoding has accordingly been set to "UTF8".
openshell-postgres | The default text search configuration will be set to "english".
openshell-postgres |
openshell-postgres | Data page checksums are disabled.
openshell-postgres |
openshell-postgres | fixing permissions on existing directory /var/lib/postgresql/data ... ok
openshell-postgres | creating subdirectories ... ok
openshell-postgres | selecting dynamic shared memory implementation ... posix
openshell-postgres | selecting default max_connections ... 100
openshell-postgres | selecting default shared_buffers ... 128MB
openshell-postgres | selecting default time zone ... Etc/UTC
openshell-postgres | creating configuration files ... ok
openshell-gateway | 2026-04-01T10:55:46.522430Z INFO openshell_server: TLS disabled — listening on plaintext HTTP
openshell-gateway | 2026-04-01T10:55:46.522469Z INFO openshell_server: Starting OpenShell server bind=0.0.0.0:8080
openshell-postgres | running bootstrap script ... ok
openshell-postgres | performing post-bootstrap initialization ... ok
openshell-postgres | syncing data to disk ... ok
openshell-postgres |
openshell-postgres |
openshell-postgres | Success. You can now start the database server using:
openshell-postgres |
openshell-postgres | pg_ctl -D /var/lib/postgresql/data -l logfile start
openshell-postgres |
openshell-postgres | initdb: warning: enabling "trust" authentication for local connections
openshell-postgres | initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
openshell-postgres | waiting for server to start....2026-04-01 10:55:47.060 UTC [48] LOG: starting PostgreSQL 15.17 (Debian 15.17-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
openshell-postgres | 2026-04-01 10:55:47.062 UTC [48] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
openshell-postgres | 2026-04-01 10:55:47.070 UTC [51] LOG: database system was shut down at 2026-04-01 10:55:46 UTC
openshell-postgres | 2026-04-01 10:55:47.076 UTC [48] LOG: database system is ready to accept connections
openshell-postgres | done
openshell-postgres | server started
openshell-postgres | CREATE DATABASE
openshell-postgres |
openshell-postgres |
openshell-postgres | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
openshell-postgres |
openshell-postgres | 2026-04-01 10:55:47.260 UTC [48] LOG: received fast shutdown request
openshell-postgres | waiting for server to shut down....2026-04-01 10:55:47.262 UTC [48] LOG: aborting any active transactions
openshell-postgres | 2026-04-01 10:55:47.263 UTC [48] LOG: background worker "logical replication launcher" (PID 54) exited with exit code 1
openshell-postgres | 2026-04-01 10:55:47.266 UTC [49] LOG: shutting down
openshell-postgres | 2026-04-01 10:55:47.267 UTC [49] LOG: checkpoint starting: shutdown immediate
openshell-postgres | 2026-04-01 10:55:47.307 UTC [49] LOG: checkpoint complete: wrote 922 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.014 s, sync=0.020 s, total=0.042 s; sync files=301, longest=0.003 s, average=0.001 s; distance=4239 kB, estimate=4239 kB
openshell-postgres | 2026-04-01 10:55:47.317 UTC [48] LOG: database system is shut down
openshell-postgres | done
openshell-postgres | server stopped
openshell-postgres |
openshell-postgres | PostgreSQL init process complete; ready for start up.
openshell-postgres |
openshell-postgres | 2026-04-01 10:55:47.399 UTC [1] LOG: starting PostgreSQL 15.17 (Debian 15.17-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
openshell-postgres | 2026-04-01 10:55:47.399 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
openshell-postgres | 2026-04-01 10:55:47.399 UTC [1] LOG: listening on IPv6 address "::", port 5432
openshell-postgres | 2026-04-01 10:55:47.402 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
openshell-postgres | 2026-04-01 10:55:47.407 UTC [64] LOG: database system was shut down at 2026-04-01 10:55:47 UTC
openshell-postgres | 2026-04-01 10:55:47.412 UTC [1] LOG: database system is ready to accept connections
openshell-postgres | 2026-04-01 10:55:47.866 UTC [68] ERROR: syntax error at or near "binary" at character 884
openshell-postgres | 2026-04-01 10:55:47.866 UTC [68] STATEMENT: -- Draft policy chunks: proposed network policy rules awaiting user approval.
openshell-postgres | --
openshell-postgres | -- One row per (sandbox_id, host, port, binary). The toggle model allows:
openshell-postgres | -- pending -> approved | rejected (initial decision)
openshell-postgres | -- approved <-> rejected (toggle via approve/revoke)
openshell-postgres | --
openshell-postgres | -- Upserts bump hit_count / last_seen_ms when the same denial recurs.
openshell-postgres | CREATE TABLE IF NOT EXISTS draft_policy_chunks (

openshell-gateway | Error: × execution error: migration error: while executing migration 3: error
openshell-postgres | id TEXT PRIMARY KEY,

openshell-postgres | sandbox_id TEXT NOT NULL,

openshell-gateway |
openshell-postgres | draft_version BIGINT NOT NULL,
openshell-postgres | status TEXT NOT NULL DEFAULT 'pending',
openshell-postgres | rule_name TEXT NOT NULL,
openshell-postgres | proposed_rule BYTEA NOT NULL,
openshell-postgres | rationale TEXT NOT NULL DEFAULT '',
openshell-postgres | security_notes TEXT NOT NULL DEFAULT '',
openshell-postgres | confidence DOUBLE PRECISION NOT NULL DEFAULT 0.0,
openshell-postgres | host TEXT NOT NULL DEFAULT '',
openshell-postgres | port INTEGER NOT NULL DEFAULT 0,
openshell-postgres | binary TEXT NOT NULL DEFAULT '',
openshell-postgres | hit_count INTEGER NOT NULL DEFAULT 1,
openshell-postgres | first_seen_ms BIGINT NOT NULL,
openshell-postgres | last_seen_ms BIGINT NOT NULL,
openshell-postgres | created_at_ms BIGINT NOT NULL,
openshell-postgres | decided_at_ms BIGINT
openshell-postgres | );
openshell-postgres |
openshell-postgres | CREATE INDEX IF NOT EXISTS idx_draft_chunks_sandbox
openshell-postgres | ON draft_policy_chunks (sandbox_id, status);
openshell-postgres |
openshell-postgres | CREATE UNIQUE INDEX IF NOT EXISTS idx_draft_chunks_endpoint
openshell-postgres | ON draft_policy_chunks (sandbox_id, host, port, binary)
openshell-postgres | WHERE status IN ('pending', 'approved', 'rejected');
openshell-postgres |
openshell-postgres | 2026-04-01 10:55:47.866 UTC [68] LOG: could not receive data from client: Connection reset by peer
openshell-gateway exited with code 1 (restarting)
openshell-gateway | 2026-04-01T10:55:48.203515Z INFO openshell_server: TLS disabled — listening on plaintext HTTP
openshell-gateway | 2026-04-01T10:55:48.203601Z INFO openshell_server: Starting OpenShell server bind=0.0.0.0:8080
openshell-gateway | 2026-04-01T10:55:48.216099Z INFO sqlx::postgres::notice: relation "_sqlx_migrations" already exists, skipping
openshell-postgres | 2026-04-01 10:55:48.218 UTC [69] ERROR: syntax error at or near "binary" at character 884
openshell-postgres | 2026-04-01 10:55:48.218 UTC [69] STATEMENT: -- Draft policy chunks: proposed network policy rules awaiting user approval.
openshell-postgres | --
openshell-postgres | -- One row per (sandbox_id, host, port, binary). The toggle model allows:
openshell-postgres | -- pending -> approved | rejected (initial decision)
openshell-postgres | -- approved <-> rejected (toggle via approve/revoke)
openshell-postgres | --
openshell-postgres | -- Upserts bump hit_count / last_seen_ms when the same denial recurs.
openshell-postgres | CREATE TABLE IF NOT EXISTS draft_policy_chunks (
openshell-postgres | id TEXT PRIMARY KEY,
openshell-postgres | sandbox_id TEXT NOT NULL,
openshell-postgres | draft_version BIGINT NOT NULL,
openshell-postgres | status TEXT NOT NULL DEFAULT 'pending',
openshell-postgres | rule_name TEXT NOT NULL,
openshell-postgres | proposed_rule BYTEA NOT NULL,
openshell-postgres | rationale TEXT NOT NULL DEFAULT '',
openshell-postgres | security_notes TEXT NOT NULL DEFAULT '',
openshell-postgres | confidence DOUBLE PRECISION NOT NULL DEFAULT 0.0,
openshell-postgres | host TEXT NOT NULL DEFAULT '',
openshell-postgres | port INTEGER NOT NULL DEFAULT 0,
openshell-postgres | binary TEXT NOT NULL DEFAULT '',
openshell-postgres | hit_count INTEGER NOT NULL DEFAULT 1,
openshell-postgres | first_seen_ms BIGINT NOT NULL,
openshell-postgres | last_seen_ms BIGINT NOT NULL,
openshell-postgres | created_at_ms BIGINT NOT NULL,
openshell-postgres | decided_at_ms BIGINT
openshell-postgres | );
openshell-postgres |
openshell-postgres | CREATE INDEX IF NOT EXISTS idx_draft_chunks_sandbox
openshell-postgres | ON draft_policy_chunks (sandbox_id, status);
openshell-postgres |
openshell-postgres | CREATE UNIQUE INDEX IF NOT EXISTS idx_draft_chunks_endpoint
openshell-postgres | ON draft_policy_chunks (sandbox_id, host, port, binary)
openshell-postgres | WHERE status IN ('pending', 'approved', 'rejected');
openshell-postgres |
openshell-postgres | 2026-04-01 10:55:48.218 UTC [69] LOG: could not receive data from client: Connection reset by peer
openshell-gateway exited with code 1 (restarting)
openshell-gateway | 2026-04-01T10:55:48.646608Z INFO openshell_server: TLS disabled — listening on plaintext HTTP
openshell-gateway | 2026-04-01T10:55:48.646636Z INFO openshell_server: Starting OpenShell server bind=0.0.0.0:8080
openshell-gateway | 2026-04-01T10:55:48.660500Z INFO sqlx::postgres::notice: relation "_sqlx_migrations" already exists, skipping

---

Reproduction

1. Use the official docker-compose setup with postgres:15
2. Start the gateway container
3. Observe migration execution
4. Gateway enters a restart loop due to migration failure

---

Analysis

• The failure occurs during execution of PostgreSQL migration: 003_create_policy_recommendations.sql

• OpenShell maintains separate migrations:

• /migrations/postgres/

• /migrations/sqlite/

• The error points to usage of binary in the schema definition: (sandbox_id, host, port, binary)

• This appears in policy-related schema (constraints/indexing for network policy rules)

• PostgreSQL reports a syntax error near binary, which suggests:

• binary is being interpreted incorrectly in the SQL context

• It may require quoting ("binary") or renaming

• OR the SQL statement structure is malformed around that identifier

• The PostgreSQL migration otherwise uses correct types such as BYTEA, indicating the issue is not datatype-related, but identifier/context-related

• SQLite migration for the same feature does not fail, likely due to more permissive parsing

---

Expected Behavior

• PostgreSQL migrations should execute cleanly using valid PostgreSQL syntax
• Gateway should initialize successfully and start listening on configured port

---

Proposed Fix

Update the PostgreSQL migration to ensure identifier compatibility and correct SQL parsing:

• Ensure binary is used safely:

• Quote it as "binary" where used in constraints/indexes

• OR rename it to a safer identifier (e.g., binary_name)

• Verify that SQL blocks are properly structured:

• No malformed comments or concatenation issues

• Clear separation between comments and statements

• Ensure PostgreSQL migration is fully validated independently of SQLite migration

---

Impact

• Blocks fresh deployments using PostgreSQL
• Causes gateway startup failure in default Docker-based setups
• Affects first-time users and production environments relying on Postgres

---

Additional Context

• Issue persists after:

• Full volume reset (docker compose down -v)

• Clean environment setup

• Using stable image (0.0.7)

• Database connectivity and configuration are verified as correct

---

Suggested Improvement (Optional)

• Add migration validation/testing per supported database (PostgreSQL, SQLite)
• Prevent dialect-specific issues from reaching release builds

Reproduction Steps

Reproduction

1. Create a minimal docker-compose.yml using:
   • ghcr.io/nvidia/openshell/gateway:0.0.7
   • postgres:15

Minimal Reproducible Example

services:
  postgres:
    image: postgres:15
    container_name: openshell-postgres
    environment:
      POSTGRES_USER: openshell
      POSTGRES_PASSWORD: openshell
      POSTGRES_DB: openshell
    volumes:
      - postgres-data:/var/lib/postgresql/data

  gateway:
    image: ghcr.io/nvidia/openshell/gateway:0.0.7
    container_name: openshell-gateway
    depends_on:
      - postgres
    command:
      - --db-url
      - postgres://openshell:openshell@postgres:5432/openshell
      - --port
      - "8080"
      - --disable-tls

volumes:
  postgres-data:

2. Configure the gateway with a PostgreSQL connection:
   --db-url postgres://openshell:openshell@postgres:5432/openshell

3. Start the stack:

docker compose up

4. Observe container logs:

docker compose logs -f gateway

5. During startup, the gateway attempts to run migrations and fails with:

migration error: while executing migration 3
syntax error at or near "binary"

Environment

Environment

• OpenShell Gateway: 0.0.7
• Database: PostgreSQL 15
• Deployment: Docker Compose
• Architecture: amd64

Logs

openclaw@staging:~/openshell-gateway$ docker compose up
[+] up 29/29
 ✔ Image ghcr.io/nvidia/openshell/gateway:0.0.7 Pulled                                                                                                                                                                                                                                                                83.5ss
 ✔ Image postgres:15                            Pulled                                                                                                                                                                                                                                                                143.4s
 ✔ Network openshell-gateway_openshell-net      Created                                                                                                                                                                                                                                                               0.0s
 ✔ Volume openshell-gateway_openshell-data      Created                                                                                                                                                                                                                                                               0.0s
 ✔ Volume openshell-gateway_openshell-workspace Created                                                                                                                                                                                                                                                               0.0s
 ✔ Volume openshell-gateway_postgres-data       Created                                                                                                                                                                                                                                                               0.0s
 ✔ Container openshell-postgres                 Created                                                                                                                                                                                                                                                               0.2s
 ✔ Container openshell-gateway                  Created                                                                                                                                                                                                                                                               0.0s
Attaching to openshell-gateway, openshell-postgres
openshell-postgres  | The files belonging to this database system will be owned by user "postgres".
openshell-postgres  | This user must also own the server process.
openshell-postgres  | 
openshell-postgres  | The database cluster will be initialized with locale "en_US.utf8".
openshell-postgres  | The default database encoding has accordingly been set to "UTF8".
openshell-postgres  | The default text search configuration will be set to "english".
openshell-postgres  | 
openshell-postgres  | Data page checksums are disabled.
openshell-postgres  | 
openshell-postgres  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
openshell-postgres  | creating subdirectories ... ok
openshell-postgres  | selecting dynamic shared memory implementation ... posix
openshell-postgres  | selecting default max_connections ... 100
openshell-postgres  | selecting default shared_buffers ... 128MB
openshell-postgres  | selecting default time zone ... Etc/UTC
openshell-postgres  | creating configuration files ... ok
openshell-gateway   | 2026-04-01T10:55:46.522430Z  INFO openshell_server: TLS disabled — listening on plaintext HTTP
openshell-gateway   | 2026-04-01T10:55:46.522469Z  INFO openshell_server: Starting OpenShell server bind=0.0.0.0:8080
openshell-postgres  | running bootstrap script ... ok
openshell-postgres  | performing post-bootstrap initialization ... ok
openshell-postgres  | syncing data to disk ... ok
openshell-postgres  | 
openshell-postgres  | 
openshell-postgres  | Success. You can now start the database server using:
openshell-postgres  | 
openshell-postgres  |     pg_ctl -D /var/lib/postgresql/data -l logfile start
openshell-postgres  | 
openshell-postgres  | initdb: warning: enabling "trust" authentication for local connections
openshell-postgres  | initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
openshell-postgres  | waiting for server to start....2026-04-01 10:55:47.060 UTC [48] LOG:  starting PostgreSQL 15.17 (Debian 15.17-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
openshell-postgres  | 2026-04-01 10:55:47.062 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
openshell-postgres  | 2026-04-01 10:55:47.070 UTC [51] LOG:  database system was shut down at 2026-04-01 10:55:46 UTC
openshell-postgres  | 2026-04-01 10:55:47.076 UTC [48] LOG:  database system is ready to accept connections
openshell-postgres  |  done
openshell-postgres  | server started
openshell-postgres  | CREATE DATABASE
openshell-postgres  | 
openshell-postgres  | 
openshell-postgres  | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
openshell-postgres  | 
openshell-postgres  | 2026-04-01 10:55:47.260 UTC [48] LOG:  received fast shutdown request
openshell-postgres  | waiting for server to shut down....2026-04-01 10:55:47.262 UTC [48] LOG:  aborting any active transactions
openshell-postgres  | 2026-04-01 10:55:47.263 UTC [48] LOG:  background worker "logical replication launcher" (PID 54) exited with exit code 1
openshell-postgres  | 2026-04-01 10:55:47.266 UTC [49] LOG:  shutting down
openshell-postgres  | 2026-04-01 10:55:47.267 UTC [49] LOG:  checkpoint starting: shutdown immediate
openshell-postgres  | 2026-04-01 10:55:47.307 UTC [49] LOG:  checkpoint complete: wrote 922 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.014 s, sync=0.020 s, total=0.042 s; sync files=301, longest=0.003 s, average=0.001 s; distance=4239 kB, estimate=4239 kB
openshell-postgres  | 2026-04-01 10:55:47.317 UTC [48] LOG:  database system is shut down
openshell-postgres  |  done
openshell-postgres  | server stopped
openshell-postgres  | 
openshell-postgres  | PostgreSQL init process complete; ready for start up.
openshell-postgres  | 
openshell-postgres  | 2026-04-01 10:55:47.399 UTC [1] LOG:  starting PostgreSQL 15.17 (Debian 15.17-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
openshell-postgres  | 2026-04-01 10:55:47.399 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
openshell-postgres  | 2026-04-01 10:55:47.399 UTC [1] LOG:  listening on IPv6 address "::", port 5432
openshell-postgres  | 2026-04-01 10:55:47.402 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
openshell-postgres  | 2026-04-01 10:55:47.407 UTC [64] LOG:  database system was shut down at 2026-04-01 10:55:47 UTC
openshell-postgres  | 2026-04-01 10:55:47.412 UTC [1] LOG:  database system is ready to accept connections
openshell-postgres  | 2026-04-01 10:55:47.866 UTC [68] ERROR:  syntax error at or near "binary" at character 884
openshell-postgres  | 2026-04-01 10:55:47.866 UTC [68] STATEMENT:  -- Draft policy chunks: proposed network policy rules awaiting user approval.
openshell-postgres  |   --
openshell-postgres  |   -- One row per (sandbox_id, host, port, binary). The toggle model allows:
openshell-postgres  |   --   pending -> approved | rejected   (initial decision)
openshell-postgres  |   --   approved <-> rejected            (toggle via approve/revoke)
openshell-postgres  |   --
openshell-postgres  |   -- Upserts bump hit_count / last_seen_ms when the same denial recurs.
openshell-postgres  |   CREATE TABLE IF NOT EXISTS draft_policy_chunks (


openshell-gateway   | Error:   × execution error: migration error: while executing migration 3: error
openshell-postgres  |       id              TEXT PRIMARY KEY,


openshell-postgres  |       sandbox_id      TEXT NOT NULL,

openshell-gateway   | 
openshell-postgres  |       draft_version   BIGINT NOT NULL,
openshell-postgres  |       status          TEXT NOT NULL DEFAULT 'pending',
openshell-postgres  |       rule_name       TEXT NOT NULL,
openshell-postgres  |       proposed_rule   BYTEA NOT NULL,
openshell-postgres  |       rationale       TEXT NOT NULL DEFAULT '',
openshell-postgres  |       security_notes  TEXT NOT NULL DEFAULT '',
openshell-postgres  |       confidence      DOUBLE PRECISION NOT NULL DEFAULT 0.0,
openshell-postgres  |       host            TEXT NOT NULL DEFAULT '',
openshell-postgres  |       port            INTEGER NOT NULL DEFAULT 0,
openshell-postgres  |       binary          TEXT NOT NULL DEFAULT '',
openshell-postgres  |       hit_count       INTEGER NOT NULL DEFAULT 1,
openshell-postgres  |       first_seen_ms   BIGINT NOT NULL,
openshell-postgres  |       last_seen_ms    BIGINT NOT NULL,
openshell-postgres  |       created_at_ms   BIGINT NOT NULL,
openshell-postgres  |       decided_at_ms   BIGINT
openshell-postgres  |   );
openshell-postgres  | 
openshell-postgres  |   CREATE INDEX IF NOT EXISTS idx_draft_chunks_sandbox
openshell-postgres  |       ON draft_policy_chunks (sandbox_id, status);
openshell-postgres  | 
openshell-postgres  |   CREATE UNIQUE INDEX IF NOT EXISTS idx_draft_chunks_endpoint
openshell-postgres  |       ON draft_policy_chunks (sandbox_id, host, port, binary)
openshell-postgres  |       WHERE status IN ('pending', 'approved', 'rejected');
openshell-postgres  | 
openshell-postgres  | 2026-04-01 10:55:47.866 UTC [68] LOG:  could not receive data from client: Connection reset by peer
openshell-gateway exited with code 1 (restarting)
openshell-gateway   | 2026-04-01T10:55:48.203515Z  INFO openshell_server: TLS disabled — listening on plaintext HTTP
openshell-gateway   | 2026-04-01T10:55:48.203601Z  INFO openshell_server: Starting OpenShell server bind=0.0.0.0:8080
openshell-gateway   | 2026-04-01T10:55:48.216099Z  INFO sqlx::postgres::notice: relation "_sqlx_migrations" already exists, skipping
openshell-postgres  | 2026-04-01 10:55:48.218 UTC [69] ERROR:  syntax error at or near "binary" at character 884
openshell-postgres  | 2026-04-01 10:55:48.218 UTC [69] STATEMENT:  -- Draft policy chunks: proposed network policy rules awaiting user approval.
openshell-postgres  |   --
openshell-postgres  |   -- One row per (sandbox_id, host, port, binary). The toggle model allows:
openshell-postgres  |   --   pending -> approved | rejected   (initial decision)
openshell-postgres  |   --   approved <-> rejected            (toggle via approve/revoke)
openshell-postgres  |   --
openshell-postgres  |   -- Upserts bump hit_count / last_seen_ms when the same denial recurs.
openshell-postgres  |   CREATE TABLE IF NOT EXISTS draft_policy_chunks (
openshell-postgres  |       id              TEXT PRIMARY KEY,
openshell-postgres  |       sandbox_id      TEXT NOT NULL,
openshell-postgres  |       draft_version   BIGINT NOT NULL,
openshell-postgres  |       status          TEXT NOT NULL DEFAULT 'pending',
openshell-postgres  |       rule_name       TEXT NOT NULL,
openshell-postgres  |       proposed_rule   BYTEA NOT NULL,
openshell-postgres  |       rationale       TEXT NOT NULL DEFAULT '',
openshell-postgres  |       security_notes  TEXT NOT NULL DEFAULT '',
openshell-postgres  |       confidence      DOUBLE PRECISION NOT NULL DEFAULT 0.0,
openshell-postgres  |       host            TEXT NOT NULL DEFAULT '',
openshell-postgres  |       port            INTEGER NOT NULL DEFAULT 0,
openshell-postgres  |       binary          TEXT NOT NULL DEFAULT '',
openshell-postgres  |       hit_count       INTEGER NOT NULL DEFAULT 1,
openshell-postgres  |       first_seen_ms   BIGINT NOT NULL,
openshell-postgres  |       last_seen_ms    BIGINT NOT NULL,
openshell-postgres  |       created_at_ms   BIGINT NOT NULL,
openshell-postgres  |       decided_at_ms   BIGINT
openshell-postgres  |   );
openshell-postgres  | 
openshell-postgres  |   CREATE INDEX IF NOT EXISTS idx_draft_chunks_sandbox
openshell-postgres  |       ON draft_policy_chunks (sandbox_id, status);
openshell-postgres  | 
openshell-postgres  |   CREATE UNIQUE INDEX IF NOT EXISTS idx_draft_chunks_endpoint
openshell-postgres  |       ON draft_policy_chunks (sandbox_id, host, port, binary)
openshell-postgres  |       WHERE status IN ('pending', 'approved', 'rejected');
openshell-postgres  | 
openshell-postgres  | 2026-04-01 10:55:48.218 UTC [69] LOG:  could not receive data from client: Connection reset by peer
openshell-gateway exited with code 1 (restarting)
openshell-gateway   | 2026-04-01T10:55:48.646608Z  INFO openshell_server: TLS disabled — listening on plaintext HTTP
openshell-gateway   | 2026-04-01T10:55:48.646636Z  INFO openshell_server: Starting OpenShell server bind=0.0.0.0:8080
openshell-gateway   | 2026-04-01T10:55:48.660500Z  INFO sqlx::postgres::notice: relation "_sqlx_migrations" already exists, skipping

Agent-First Checklist

• I pointed my agent at the repo and had it investigate this issue
• I loaded relevant skills (e.g., debug-openshell-cluster, debug-inference, openshell-cli)
• My agent could not resolve this — the diagnostic above explains why

[AdnanSattar]

Proposed Fix (Ready)

I’ve identified and tested a minimal fix for this issue.

Patch

- ON draft_policy_chunks (sandbox_id, host, port, binary)
+ ON draft_policy_chunks (sandbox_id, host, port, "binary")

Explanation

PostgreSQL requires quoting for binary in this context to avoid parsing errors.

Validation

• Tested locally with PostgreSQL 15
• Migration completes successfully
• Gateway starts without errors

PR Attempt

I attempted to open a PR from my fork:
https://github.com/AdnanSattar/OpenShell/tree/fix/postgres-binary-migration

However, PR creation appears restricted for external contributors.

Happy to submit via alternative workflow or if permissions can be granted.

If possible, could PR permissions be enabled for this contribution?

[dimityrmirchev]

Hi @AdnanSattar ,

I stumbled on the same issue while doing some experimenting with OpenShell. I think if you want to be able to create PRs, you should first open a vouch discussion, a process described in https://github.com/NVIDIA/OpenShell/blob/main/CONTRIBUTING.md#first-time-contributors

[AdnanSattar]

Thanks for pointing that out, appreciate it.

I’ve now opened a vouch request here: #847

This is specifically for the PostgreSQL migration issue (#723) where the gateway fails on the binary identifier in the index definition. I’ve already validated a minimal fix locally and ready to raise a PR once vouched.

Let me know if you think I should add more context to the vouch request.

[johntmyers]

All DB tables have been consolidated into a single DB objects table.