Connector Service

The Connector Service manages the catalog of external tenant data sources — the connector framework’s substrate. It is the 12th service in the monorepo and, by design (R44 spec D1), the only process that holds tenant data-source credentials and opens outbound connections to external systems. R44a shipped the catalog CRUD, credential custody, egress policy, fail-closed read-only probes, and schema discovery; R44b added exposure authoring — parameterized, sqlglot-validated, hash-pin-approved SELECT queries with capped preview and the shared read-execution core. R44c binds approved exposures to skills as callable LLM tools (xq_{connector}_{exposure}) and adds the service-internal execute/status/bindings endpoints orchestration calls at runtime — the first phase where an agent actually reads external data.

Overview

Tenants register connectors — named references to external databases the platform may later read from. Two engines are supported in v1 (dual-engine, spec decision 11): postgres (the local dev/proving engine) and snowflake (the customer-facing engine, key-pair auth). Registering a connector stores only non-secret config; the credential is written once, write-through, to a secret store and never returned by any API. Before a connector becomes usable, a fail-closed read-only probe must prove the supplied credential cannot write; schema discovery then snapshots the source’s information_schema so admins (and later, skills) can browse what the connector exposes.

The pipeline for a new connector:

POST /connectors            → status: unconfigured   (config validated, egress-checked)
PUT  .../credential         → status: probing        (write-through to secret store)
  probe passes              → status: active         (read_only_verified_at set; discovery runs)
  probe fails               → status: probe_failed   (remediation GRANT/REVOKE text returned)
POST .../disable            → status: disabled       (enable re-probes — never straight to active)

Port & Language

Key Endpoints

All routes are proxied by the gateway at /api/v1/connectors*. Reads require the exposures.propose permission (admin, power_user); every mutation requires connectors.manage (admin). See the Connectors API reference for full request/response shapes.

The three internal endpoints take the tenant from the X-Tenant-Id header only (never a request body — the cross-tenant control, spec T10); a header-less caller (a direct box-shell curl with no perimeter) gets 401. The gateway NotFounds them by path segment (status / bindings / execute), so a connector legitimately keyed one of those names is not stranded. | GET | /health | Health check. |

Architecture

src/connector_service/
├── main.py            # App wiring, lifespan (pool + migrations + secret store)
├── config.py          # Env-driven settings
├── migrate.py         # Boot-time idempotent migration runner (auth-service pattern)
├── migrations/        # 001_connector_catalog.sql (catalog-guarded, re-run safe)
├── models.py          # Per-engine typed config/credential schemas + API shapes (+ exposure shapes)
├── routes.py          # Catalog CRUD, credential, probe, discovery routes
├── exposure_routes.py # Exposure CRUD/lifecycle, /validate, capped preview (R44b)
├── repo.py            # ConnectorRepo (integration_connectors + snapshots)
├── exposure_repo.py   # ExposureRepo + connector_execution_log writer (R44b)
├── executor.py        # Shared read-execution core: validate→bind→wrap→cap; hash-pin (R44b)
├── deps.py            # app.state accessors + UNCONDITIONAL permission gates
├── secretstore.py     # SecretStore backends (gcp | dev) + 10-min read cache
├── egress.py          # Egress policy — the single outbound-connection chokepoint
├── discovery.py       # Snapshot size guards (schema cap, byte cap)
├── audit.py           # HMAC-signed append-only audit rows
├── migrations/        # 001_connector_catalog.sql, 002_connector_exposures.sql
└── engines/
    ├── base.py        # EngineAdapter seam (config/credential models, probe, discover, read_execute)
    ├── postgres.py    # Role/privilege scan + behavioral check, resolve-then-pin TLS, RO-txn reads
    └── snowflake.py   # SHOW GRANTS transitive walk, key-pair auth, numeric-bound reads

The structural SQL validator itself lives in shared — shared/src/aegis_shared/sandbox/validation/sql.py (beside python.py) — so it is one reviewed boundary for both this service’s /validate endpoint and R44c’s execute path. It is imported from the submodule directly (never re-exported from the validation package __init__) so services that only need validate_python do not inherit the sqlglot dependency.

Migration runner

The service applies migrations/*.sql in order on every boot (the auth-service pattern — no applied-tracking; every statement is catalog-predicate-guarded so re-runs converge). The CI double-boot test ci/connector_double_boot.sh guards the idempotency invariant. Tables owned by this service:

Security Model

Credential custody (SecretStore)

The backend is selected by AEGIS_SECRET_BACKEND:

• gcp (the box): GCP Secret Manager via the VM service account, secret id aegis-conn-{tenant_id}-{connector_key}. A first write for a new connector creates the secret.
• dev (local default): Fernet-encrypted file store under .aegis-secrets/ (key auto-generated beside the store, or set CONNECTOR_DEV_STORE_KEY). Refuses to start with AEGIS_ENV=production.

Invariants, regardless of backend:

• The database stores a credential_ref only — secret material never lands in Postgres.
• No API response ever contains credential material; the UI only sees set/unset (has_credential).
• Audit rows carry SHA-256 digests of the payload, never the payload.
• Credential validation errors are sanitized to field names (pydantic’s default error body would echo the secret).
• A 10-minute in-memory read cache fronts the backend, invalidated on rotation.

Credential shapes are per-engine: postgres takes {username, password}; snowflake takes {username, private_key} — a PEM (PKCS#8) key-pair, no password auth (Snowflake’s MFA enforcement makes password service credentials a dead end).

Egress policy

egress.py is the single chokepoint for every outbound connection (register validation, probe, discovery, and R44c execution). SSRF against a trusted network-capable process accepting user-supplied destinations is the connector-equivalent of a jail escape, so policy is default-deny for: loopback, link-local 169.254.0.0/16 (GCP metadata), RFC1918, CGNAT 100.64.0.0/10, unique-local IPv6, and unspecified/broadcast/multicast/reserved ranges. IPv4-mapped IPv6 notation is unwrapped so the v4 rules cannot be bypassed.

DNS handling is resolve-then-pin: resolve once, validate every returned address (one denied address fails the whole destination — that mixed-record shape is DNS rebinding), then connect to the pinned IP. The postgres adapter preserves TLS identity by pinning server_hostname to the original hostname (verify-full semantics survive IP-pinning); the snowflake adapter validates all resolved addresses pre-connect and relies on strict TLS certificate verification against snowflakecomputing.com.

The only escape is CONNECTOR_EGRESS_ALLOW_CIDRS — a comma-separated CIDR list set as a deployment env var (ships empty, never tenant-reachable). Violations return the typed error egress_denied and land a connector.egress_denied audit row.

Read-only probe (fail-closed)

The probe must prove the credential cannot write; any check that cannot evaluate is a failure (fail-safe, not fail-graceful). Per engine:

• postgres: transitive role-attribute scan through pg_auth_members (rolsuper / rolcreatedb / rolcreaterole / rolbypassrls), a has_table_privilege write scan (INSERT/UPDATE/DELETE/TRUNCATE on any relation), schema/database CREATE scans, and a rollback-wrapped CREATE TABLE behavioral check.
• snowflake: Snowflake has no session read-only mode, so the probe is stricter — a transitive SHOW GRANTS TO USER/ROLE walk over every reachable role, failing on any INSERT / UPDATE / DELETE / TRUNCATE / MERGE / MODIFY / OWNERSHIP / CREATE* grant or membership in ACCOUNTADMIN / SYSADMIN / SECURITYADMIN / ORGADMIN, plus a CREATE TEMPORARY TABLE behavioral check.

A passing probe sets read_only_verified_at; any failure sets status probe_failed and returns per-check detail with remediation GRANT/REVOKE text an admin can hand to the source DBA. Discovery refuses (409 not_verified) until verification passes, and a credential or destination change clears it.

Schema Discovery

Discovery walks the source’s information_schema (snowflake scoped to the configured database/schema) and writes a snapshot row (is_current = true). Size guards keep a 40k-table warehouse from OOMing the service: a schema count above CONNECTOR_DISCOVERY_MAX_SCHEMAS (50) degrades to a schema-name-only list, and a snapshot above CONNECTOR_DISCOVERY_MAX_BYTES (2 MB) drops column detail — both loudly, with truncated: true and a truncation_reason, never silently.

Exposure Authoring (R44b)

An exposure is a first-class, connector-scoped, parameterized SELECT query — the only SQL surface an agent can eventually call (R44c). Authoring flows draft → pending_review → approved (or rejected), plus retired.

The SQL validator (the security boundary)

shared/src/aegis_shared/sandbox/validation/sql.py is the AST security boundary — exposure SQL runs against a customer’s database, outside any jail, so every finding blocks (there is no lint mode, unlike the Python validator). It is dialect-aware (postgres | snowflake) and threat-modeled for parser differentials (spec §9 risk 1):

• Single statement. sqlglot.parse splits on real boundaries only (a ; inside a string or $$-quoted literal does not split); any count ≠ 1 is rejected.
• Allowlisted read root + full-tree denylist walk. The root must be a read exp.Query (SELECT / set-op / subquery — an INSERT is not a Query), and no node anywhere may be a DML/DDL/utility type. The walk is load-bearing: WITH t AS (DELETE … RETURNING *) SELECT … and SELECT … INTO tbl both keep a Select root but embed a Delete / Into node a root-only check would miss.
• Unknown syntax denied by construction. Anything sqlglot cannot parse degrades to an exp.Command node (CALL, VACUUM, EXECUTE IMMEDIATE, SET, …) which is on the denylist — “the parser did not understand this” fails closed.
• Per-dialect function blocklist (one reviewed constant each): postgres pg_read*/pg_ls*/lo_*/dblink*/pg_sleep/set_config/…; snowflake SYSTEM$*/GET_DDL/…. Dangerous functions parse as Anonymous nodes; safe builtins (COUNT, LOWER) parse as typed nodes and are never matched.
• Named :placeholders only — positional (?) and server params ($1) are rejected; each :name must be declared in params.

sqlglot is pinned to an exact version in both shared and connector-service pyprojects. A parser upgrade is a security-relevant change: it goes through the differential corpus (tests/test_sql_validation.py, additions-only) first.

Hash-pinned approval

sql_hash is the sha256 of the raw exact sql_text (no canonicalization — that would open a parser-differential laundering vector). Approval records approved_sql_hash = sql_hash. Any content edit reverts the exposure to draft and clears the pin (atomic in the repo), and R44c’s execute path calls executor.assert_executable, which refuses anything not approved or whose current hash ≠ approved_sql_hash. That refusal ships now (with a test) so it cannot be forgotten when execute lands.

The shared read-execution core

executor.py is the one path both R44b preview and R44c execute run through — no preview-vs-execute divergence. Per call: validate_sql (structural gate) → coerce_params (declared types; required present) → prepare_query (sqlglot-wraps the read as SELECT * FROM (<sql>) aegis_q LIMIT row_cap+1 and rewrites each :name to the engine’s native bind token on the AST, never by string paste) → adapter.read_execute (driver-bound, read-only session posture, per-exposure timeout_s, through the egress chokepoint) → loud row/byte truncation.

Binding is driver-native, numbered, and server-side for both engines (never string-interpolated): postgres → $N inside a readonly=True transaction that is always rolled back; snowflake → :N numeric paramstyle (there is no session read-only mode — the RO-verified grant model is the barrier). Snowflake deliberately avoids %(name)s pyformat, whose client-side sql % params substitution collides with a literal % in the SQL (e.g. LIKE 'TX%'); numeric binds treat % as data.

Preview

POST …/exposures/{ekey}/preview is the first path that runs SQL against a customer DB. It is fail-closed — only against a probe-verified connector (active + read_only_verified_at, the same gate as schema/discovery) — caps at min(row_cap, 50) rows (also a Snowflake credit courtesy), and is the authoritative table/column existence check (a stale snapshot is not trusted for that). Every attempt writes a connector_execution_log row with the params hash only.

Skill Binding & Execution (R44c)

An approved exposure becomes an agent-callable tool by binding it to a skill. A SkillConnectorRef (agent-config models/skills.py) gains an exposures: list[str]; on skill save agent-config consults connector-service (/status + /bindings) and refuses (422) a binding whose connector is not active, whose exposures are not approved, or whose access is not read_only (read_write is rejected at bind time — write-back is a future release). At conversation load, orchestration’s _expose_exposure_tools (the third tool-exposure twin beside capabilities and code blocks) describes the bound exposures and emits one LLM tool per approved exposure, named xq_{connector_key}_{exposure_key}, collision-guarded against the tool registry exactly like code blocks.

When the LLM calls the tool, tool_node routes it to POST /connectors/{key}/exposures/{ekey}/execute. Execute is a thin governance wrapper around the same executor core preview uses, with two additions preview skips:

1. Hash-pin re-verification (T7). The stored sql_hash column is not trusted — execute recomputes the hash from the current sql_text and calls assert_executable, which compares it to approved_sql_hash. An insider who edits the SQL directly in the DB leaves the old hash in place, so the recomputed hash differs → hash_mismatch refusal, a distinct connector.exposure_hash_mismatch audit event, and a denied log row.
2. The exposure’s full row_cap (preview clamps to 50).

The reply is the envelope {status, columns, rows, row_count, truncated, elapsed_ms, sql_hash}. Failures surface as typed errors (connector_unavailable, exposure_not_approved, hash_mismatch, param_validation, egress_denied, query_timeout) — never a fabricated empty result (the A5 rule: absence of data must be distinguishable from failure to fetch it). Truncation is loud and visible to the LLM. Reads flow ungated (no forced HITL, like KG reads); the rules engine still sees every call via after_tool_call, so a tenant can attach rules. A mid-conversation disable or a rotation to a write-capable credential (→ probe_failed) makes the next tool call degrade exactly like the pre-existing missing_connectors path — no hang, no fabricated empty.

Effective classification. Each bound exposure’s effective data_classification (its own label, else the connector’s floor) is computed server-side in /bindings and stamped on the tool’s manifest entry; when an exposure result feeds a code block, a label above public makes the execution classification-bearing, engaging the fail-closed sandbox rule gate (spec D7/D8).

RBAC

Operators and reviewers have no connector surface. Enforcement is unconditional — the service was born after the R42d enforce flip and holds credential custody, so its gates return 401/403 regardless of AEGIS_AUTH_MODE (a mis-set env var must not silently open the credential surface).

Local Development: the operator-demo Sidecar

docker compose up -d starts an operator-demo sidecar (postgres:15-alpine, host port 5433, database operator_demo) seeded with a synthetic operator production/allocation dataset and two fixture roles:

Register it as {"host": "localhost", "port": 5433, "database": "operator_demo", "sslmode": "disable"} — note that localhost is deny-listed by default, so local probing requires CONNECTOR_EGRESS_ALLOW_CIDRS=127.0.0.0/8 in your .env (the same deliberate-allowlist step a deployment performs for the box’s docker network).

./infrastructure/scripts/start-all.sh starts the connector service on port 8011 alongside the other services.

Admin UI

The Connectors admin page at /configuration/connectors covers the connector lifecycle: register (engine-typed config forms driven by GET /connectors/engines), credential set/unset display, probe results with per-check detail and remediation text, the schema browser, and disable/delete.

The Exposures page at /configuration/connectors/[key]/exposures (R44b) covers authoring: an SQL editor with live validation (POST /connectors/validate), a declared-param table, the schema browser as an authoring aid, and a capped preview panel — plus an admin approval queue showing the exact SQL under signature with approve/reject-with-note. exposures.propose (admin, power_user) sees authoring; exposures.approve (admin) sees the approval actions.

Dependencies

Infrastructure Dependencies

Configuration

The service binds to 0.0.0.0:8011 by default (hardcoded in __main__).

Running Locally

cd services/connector-service
poetry install
poetry run uvicorn connector_service.main:app --reload --port 8011
 
# Run tests (no DB needed — fail-closed matrices are unit-tested with fakes)
poetry run pytest
 
# Apply migrations standalone (CI, box ops)
poetry run python -m connector_service.migrate

Audit Events

Every mutation lands an HMAC-signed, append-only audit_logs row: connector.created / updated / deleted / disabled / enabled, connector.credential_set (SHA-256 digests only), connector.probe_passed / probe_failed, connector.discovered, connector.egress_denied, and the exposure lifecycle — exposure.proposed / edited / deleted / submitted / approved (carries approved_sql_hash) / rejected / retired. Exposure previews and executions land in connector_execution_log (params hash only), not audit_logs.