HsSqlAgent.Server 3.1.0

dotnet add package HsSqlAgent.Server --version 3.1.0
                    
NuGet\Install-Package HsSqlAgent.Server -Version 3.1.0
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="HsSqlAgent.Server" Version="3.1.0" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="HsSqlAgent.Server" Version="3.1.0" />
                    
Directory.Packages.props
<PackageReference Include="HsSqlAgent.Server" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add HsSqlAgent.Server --version 3.1.0
                    
#r "nuget: HsSqlAgent.Server, 3.1.0"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package HsSqlAgent.Server@3.1.0
                    
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=HsSqlAgent.Server&version=3.1.0
                    
Install as a Cake Addin
#tool nuget:?package=HsSqlAgent.Server&version=3.1.0
                    
Install as a Cake Tool

HsSqlAgent.Server

Secret protection now requires independent Base64-encoded random 32-byte keys for database passwords, DML payloads, and DML connection fingerprints. The standard host binds the SecretProtection configuration section; custom runtime registrations must set options.SecretProtection. Legacy encrypted passwords must be re-entered and approvals drained before upgrade. See the documentation website.

Embeddable MCP SQL Agent components for ASP.NET Core. New applications compose capabilities explicitly; each capability owns only the options it needs. The aggregate AddHsSqlAgent() / UseHsSqlAgent() pair remains as a compatibility path for existing consumers.

PostgreSQL Query execution: transaction-enforced read-only

execute_query_sql is a read-only MCP tool, but a PostgreSQL SELECT alone is not a write-prevention boundary: a VOLATILE function can update tables or advance a sequence. When the selected provider is PostgreSQL, Host now wraps the compiled Query command in a real database transaction and runs SET TRANSACTION READ ONLY before executing the command on that same transaction. The result set is fully buffered before rolling back. The database, rather than a function-name blacklist, enforces persistent write restrictions. Read-only transaction failures surface as failed Query operations, not as successful empty results.

For PostgreSQL Query compilation with a nonempty table whitelist, the F# Query policy also restricts function calls to reviewed pg_catalog built-ins (plus non-overloadable SQL expressions such as COALESCE). Allowed functions are rendered as explicitly pg_catalog-qualified calls, so a custom UDF cannot shadow LOWER, COUNT and similar names through search_path or overload selection. Unknown, quoted and non-catalog functions are rejected at the Query policy stage, rather than trying to detect dangerous function names with a blacklist. This restriction applies only to whitelisted PostgreSQL Query compilation, not to the six-dialect Parser or DML/DDL tools. It may reject previously accepted unproven native functions; those require an explicit security capability decision.

PostgreSQL operator resolution is a separate boundary from named SQL functions. A connection can explicitly place an untrusted schema ahead of pg_catalog and shadow built-in operators such as integer =, letting a seemingly allowed SELECT invoke hidden SQL against excluded tables. For PostgreSQL Query only, the same READ ONLY transaction now also uses transaction-local pg_catalog.set_config to put pg_catalog first in search_path while retaining the caller's remaining schema order for ordinary relation lookup. Rollback restores the prior session setting, including pooled connections; both query entrypoints share the single Host gate. The PostgreSQL 16 physical attack/regression and no-pool-reset rollback controls are covered by the Host tests under Services/Query/. This is not a complete sandbox for arbitrary user-defined operators or types; database roles must still have least-privilege relation and CREATE permissions.

The F# SqlCore compiler stays driver-free and retains the original SQL authorization and single-statement gates. DML/DDL and other five providers are unaffected by this provider-specific Host enforcement. Read-only transactions are not an all-purpose sandbox for locks, resource exhaustion, or arbitrary external function effects; limit database privileges and retain timeout/concurrency/audit controls.

Install

dotnet add package HsSqlAgent.Server

Choose the composition you need

Host scenario Registration
Existing app with its own login/permissions Runtime + AdminStore + HostAuthorization + AdminApi
Existing app that also exposes MCP Add MCP to the host-auth composition
Standalone HsSqlAgent identity/Admin experience Runtime + AdminStore + BuiltInAuth + AdminApi, optionally MCP/Telemetry/UI
Existing consumer using the old aggregate API AddHsSqlAgent(...) / UseHsSqlAgent() compatibility preset

AddHsSqlAgentCore() itself is optionless and does not install optional capabilities.

HTTP surfaces

The current public HTTP contracts are intentionally fixed:

  • MCP: /mcp
  • administration API: /api
  • administration UI: /

Canonical permission paths such as /auth/role and /runtime/db-management are authorization resource identifiers, not HTTP mount paths. Changing a page or API URL must not silently change its permission identity.

Cross-module registration consistency

AddHsSqlAgentCore() is safe to call from multiple modules using the same IServiceCollection. They share the registered feature and capability-option state, so a second registration does not duplicate Runtime, Admin Store, filters or middleware configuration. Configuration is only accepted on the first registration of each capability; a later attempt to override it fails with a clear error. Built-in identity and host authorization remain mutually exclusive even when they come from different NuGet integration modules. A second legacy aggregate-options initialization with a different object is rejected rather than silently changing settings.

This registration identity is scoped to IServiceCollection, not global to the process. Independently configured ASP.NET Core hosts remain isolated.

Existing ASP.NET Core host with its own login and permissions

A host can omit the HsSqlAgent frontend and built-in identity entirely. The host keeps ownership of authentication, authorization, exception handling, telemetry, and controller endpoint mapping.

using HsSqlAgent.Server.Extensions;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddAuthentication(/* host schemes */);
builder.Services.AddAuthorization(options =>
{
    options.AddPolicy("SqlAgentAdmin", policy =>
    {
        // Add the host application's own requirement/handler here.
        // The handler can inspect HsSqlAgentPermissionResource.Permissions.
    });
});

var hs = builder.Services.AddHsSqlAgentCore();

hs.AddHsSqlAgentRuntime(options =>
{
    // Required: purpose-specific encryption key rings and independent DML fingerprint key.
    builder.Configuration.GetSection("SecretProtection").Bind(options.SecretProtection);
});
hs.AddHsSqlAgentAdminStore(options =>
{
    options.Provider = "Postgres";
    options.ConnectionString = builder.Configuration.GetConnectionString("HsSqlAgent")!;
});
hs.AddHsSqlAgentHostAuthorization("SqlAgentAdmin");
hs.AddHsSqlAgentAdminApi();

var app = builder.Build();
app.UseAuthentication();
app.UseAuthorization();
app.UseHsSqlAgentAdminApi();
app.MapControllers();
app.Run();

In host-authorization mode HsSqlAgent:

  • does not publish AuthController, MemberController, or RoleController;
  • does not install the HsSqlAgent identity schema;
  • does not require JWT, SMTP, password-reset, or OIDC settings;
  • does not install an /api-wide HsSqlAgent authentication/authorization middleware branch;
  • delegates canonical permission checks to the host policy as HsSqlAgentPermissionResource;
  • leaves the host's authentication defaults, authorization policy provider, MVC JSON options, exception handling, telemetry, and controller mapping under host ownership.

Add MCP independently

MCP machine access is a separate security boundary from human/admin authorization. Add it only when the host needs an MCP endpoint:

hs.AddHsSqlAgentMcp(options =>
{
    options.PublicEndpoint = "https://example.com/mcp";
    options.HmacSecretKey = builder.Configuration["HMAC_KEY"]!; // at least 32 bytes
});

// after builder.Build()
app.UseHsSqlAgentMcp();

If AddHsSqlAgentMcp() is not selected, MCP options and MCP services are not installed.

Standalone server / packaged Admin experience

The first-party ToolBox uses the same modular registration surface as package consumers. It explicitly selects runtime, persistence, built-in identity, MCP, administration API, telemetry, controller mapping, UI, and host-wide exception handling.

using HsSqlAgent.Server.Extensions;
using HsSqlAgent.Server.Middleware;

var builder = WebApplication.CreateBuilder(args);
var hs = builder.Services.AddHsSqlAgentCore();

hs.AddHsSqlAgentRuntime(options =>
{
    // Required: purpose-specific encryption key rings and independent DML fingerprint key.
    builder.Configuration.GetSection("SecretProtection").Bind(options.SecretProtection);
});

hs.AddHsSqlAgentAdminStore(options =>
{
    options.Provider = "Sqlite";
    options.ConnectionString = "Data Source=hsagent.db";
});

hs.AddHsSqlAgentBuiltInAuth(options =>
{
    options.Jwt.SecretKey = builder.Configuration["JWT_KEY"]!; // at least 32 bytes
});

hs.AddHsSqlAgentMcp(options =>
{
    options.PublicEndpoint = "http://localhost:8080/mcp";
    options.HmacSecretKey = builder.Configuration["HMAC_KEY"]!; // at least 32 bytes
});

hs.AddHsSqlAgentAdminApi();
hs.AddHsSqlAgentTelemetry();

builder.Services.AddExceptionHandler<GlobalExceptionHandler>();
builder.Services.AddProblemDetails();

var app = builder.Build();
app.UseExceptionHandler();
app.UseHsSqlAgentMcp();
app.UseHsSqlAgentAdminApi();
app.MapControllers();
app.UseHsSqlAgentAdminUi();
app.Run();

Built-in schemes are namespaced (HsSqlAgent.Jwt, HsSqlAgent.ExternalCookie, HsSqlAgent.Oidc) and do not replace the host application's default authentication schemes or default authorization policy. Built-in identity and host-authorization mode are mutually exclusive.

Capability ownership

New code configures the capability that owns a setting:

Capability Options / responsibility
AddHsSqlAgentRuntime() HsSqlAgentRuntimeOptions: bootstrap, operability, cache, rate limiting, security-policy sync, outbound-delivery sync, SQL concurrency, DML approval store
AddHsSqlAgentAdminStore() HsSqlAgentAdminStoreOptions: administration DB provider and connection string
AddHsSqlAgentBuiltInAuth() HsSqlAgentBuiltInAuthOptions: JWT, password reset/SMTP, enterprise identity/OIDC
AddHsSqlAgentHostAuthorization() Delegate administration permission checks to an existing ASP.NET Core policy
AddHsSqlAgentMcp() McpOptions: public MCP endpoint and MCP-key HMAC secret
AddHsSqlAgentAdminApi() HsSqlAgent controllers plus controller-scoped validation and exception mapping
AddHsSqlAgentTelemetry() TelemetryOptions: Prometheus and OTLP exporters

Dependencies are composed automatically where required. For example, AdminStore ensures Runtime is present; BuiltInAuth ensures AdminStore is present. This does not mean unrelated capabilities are installed.

Defaults and compatibility

Capability defaults preserve the previous aggregate defaults. Omitting a capability does not allocate or validate that capability's options.

Notable preserved defaults:

Setting Default
Admin database provider Sqlite
Admin database connection Data Source=hsagent.db
MCP public endpoint http://localhost:8080/mcp
Cache provider Memory
Rate limiter provider / failure mode Memory / FailClosed
Security-policy sync provider Memory
Outbound-delivery sync provider Memory
SQL concurrency provider / failure mode Memory / FailClosed
SQL concurrency lease 30 seconds
DML approval store provider Memory
JWT issuer / audience HS-Agent / HS-Agent-Users
Access / refresh token lifetime 1 minute / 30 days
Sign-in lockout 5 attempts / 15 minutes
Password reset expiration 30 minutes
SMTP port / SSL 587 / enabled
Prometheus enabled on localhost:9000
Telemetry service name hs-sql-agent
Health probe enabled, 60s interval, 10s timeout, max concurrency 4
Security-policy refresh 30 seconds

Secret values intentionally default to empty strings. They are validated only when their owning capability is selected: BuiltInAuth requires a JWT secret of at least 32 bytes; MCP requires an HMAC secret of at least 32 bytes.

The existing key prefixes are also preserved, including:

hsqlagent:cache:
hsqlagent:ratelimit:
hsqlagent:security-policy:
hsqlagent:outbound-delivery:
hsqlagent:sql-concurrency
hsqlagent:dml-approval:

The first-party ToolBox also preserves the existing configuration fallback chains for distributed runtime connection strings; splitting the options model does not change those deployment semantics.

Regression tests compare the complete legacy-default capability option trees against modular defaults so future refactors cannot silently drift these defaults.

Legacy compatibility preset

Existing package consumers can continue to use the aggregate options shape:

builder.Services.AddHsSqlAgent(options =>
{
    options.AdminDatabaseProvider = "Sqlite";
    options.AdminConnectionString = "Data Source=hsagent.db";
    options.JwtSecretKey = builder.Configuration["JWT_KEY"]!;
    options.HmacSecretKey = builder.Configuration["HMAC_KEY"]!;
    builder.Configuration.GetSection("SecretProtection").Bind(options.SecretProtection);
});

var app = builder.Build();
app.UseExceptionHandler();
app.UseHsSqlAgent().ServeAdminUi();

HsSqlAgentServiceOptions is retained as a compatibility DTO. The compatibility registration translates it into the same capability-specific option objects used by new integrations. New code should prefer AddHsSqlAgentCore() plus explicit capability registration.

Canonical permissions

The frontend and backend use stable canonical permission identities. Examples:

/auth/role.view
/auth/role.edit
/auth/user.create
/runtime/db-management.view
/runtime/db-management/semantic.edit
/runtime/mcp-keys.create

Frontend navigation paths are separate from these identifiers. Relative UI checks such as v-permission="'edit'" resolve from the page's declared canonical permission metadata, never from route.path.

Host authorization handlers receive the requested canonical permission keys through HsSqlAgentPermissionResource.Permissions and can map them into the host application's own authorization model.

MVC and pipeline ownership

UseHsSqlAgentAdminApi() initializes the HsSqlAgent administration surface but deliberately does not call MapControllers() in modular host mode. ASP.NET Core MapControllers() enables attribute-routed controllers for the MVC application as a whole, so the host must own that decision.

HsSqlAgent validation and exception mapping are attached only to HsSqlAgent controllers. The modular Admin API does not globally install FluentValidation auto-validation, HsSqlAgent ProblemDetails, global JSON converters, or a host-wide HsSqlAgent exception handler.

The packaged Admin UI is optional and currently mounts only at /:

app.UseHsSqlAgentAdminUi();

Arbitrary relocation of /mcp, /api, or the SPA is intentionally rejected until routing, frontend base paths, assets, and security boundaries can move together.

Package contents

The Server package currently brings in HsSqlAgent.SqlCore, provider abstractions, and all six supported providers: PostgreSQL, MySQL, SQLite, SQL Server, Oracle, and Firebird. Provider package decomposition is separate from the server embedding contract and may be refined independently.

Compiler observability

HsSqlAgent.SqlCore remains telemetry-provider agnostic and returns deterministic SqlCompileEvidence with each translated or rejected compile decision. When AddHsSqlAgentTelemetry() is selected, the Server package observes that evidence at the runtime boundary:

  • structured ILogger events contain verdict, decision boundary/code, source/target provider, capability-matrix version and evidence fingerprint;
  • when OtlpEndpoint is configured, those logs and sql.compile.decision trace spans are exported through OTLP;
  • hsqlagent.sql.compiles is emitted through the existing OpenTelemetry meter and Prometheus exporter with low-cardinality verdict/boundary/decision/provider labels.

Raw SQL, runtime literal/parameter values, full evidence objects, allowed-table snapshots, evidence fingerprints and trace IDs are not used as Prometheus labels. The database-backed AuditLog remains the durable governance/audit store rather than a general compiler telemetry store.

Do not commit production keys. Supply HMAC/JWT keys and database credentials through your deployment secret store.

Full project documentation: https://github.com/tse-wei-chen/hs-sql-agent

G3: PostgreSQL-only transactional DDL, explicit opt-in

The single MCP tool execute_ddl_sql is disabled and hidden by default and is never automatically selected or granted by DML permissions. Existing four metadata/Query tools remain selected; DML and DDL remain unselected.

G5 MySQL 8.4 LTS — implicit-commit CREATE TABLE Host (opt-in)

MySQL InnoDB atomic DDL is not user-transactional DDL. Only one unqualified CREATE TABLE (1–16 simple signed INT columns; no constraints, defaults, temporary tables or CTAS) can be approved through the already existing execute_ddl_sql MCP tool. All other MySQL Host DDL remains denied.

using HsSqlAgent.Server.Extensions;
using HsSqlAgent.Server.Services;

services.AddHsSqlAgentMySqlDdl(new MySqlDdlExecutionOptions
{
    Enabled = true,
    AllowCreateTable = true,
    AllowedDatabases = new HashSet<string>(StringComparer.Ordinal) { "app_database" },
    PolicyRevision = "mysql84-ddl-governance-v1"
});
services.AddSingleton<IDdlApprovalProvider, MyIndependentDdlApprovalProvider>();
// Separately grant execute_ddl_sql on the intended MCP access key.

Requires real MySQL 8.4 (not MariaDB/Percona), a valid server UUID, the exact selected non-system database matching the access key and allowlist, case-sensitive table names mode 0, session default engine InnoDB, and complete read access to information_schema. A released preview connection never authorizes execution. The new execution connection obtains a cooperative session named lock (not a cross-client CAS), rechecks physical identity, policy, table absence and approval expiry, executes only the immutable compiled SQL once, and verifies exact InnoDB columns afterward.

No BEGIN, COMMIT or ROLLBACK are issued by this Host. The single CREATE statement implicitly commits and cannot be undone by user ROLLBACK. When a native statement may have run but no conclusive response/postcondition can be proven, the result is outcome_unknown, NEVER a safe-to-retry failure. External clients are not prevented from altering schema by GET_LOCK. DML approval never authorizes DDL, startup opt-in alone does not expose the tool, and the four default read-only selections remain unchanged.

A Hosting application must independently enable it, allowlist schema(s) and operations, and register its own DDL approval provider before building DI:

using HsSqlAgent.Server.Extensions;
using HsSqlAgent.Server.Services;
services.AddHsSqlAgentPostgresDdl(new PostgresDdlExecutionOptions
{
    Enabled = true,
    AllowedSchemas = new HashSet<string>(StringComparer.Ordinal) { "app" },
    AllowCreateTable = true,
    AllowAddColumn = true,
    AllowCreateIndex = true,
    PolicyRevision = "ddl-governance-2026-10"
});
services.AddSingleton<IDdlApprovalProvider, MyIndependentDdlApprovalProvider>();
// Implement the provider to obtain independent human approval; return
// the exact request ID, evidence fingerprint and approver identity.
// The MCP access key must separately grant "execute_ddl_sql".

Without this opt-in, an explicit key grant, valid PostgreSQL context and a separately configured IDdlApprovalProvider, execution is denied. The provider is not IDmlApprovalProvider; no DML consent or session is reused. previewOnly=true captures live metadata without execution or approval. Pending approvals cannot be resumed to execute: request a new preview and approval. The result distinguishes preview, committed, rejected, pending, failed (confirmed rollback) and outcome_unknown (commit/rollback reply unverified).

Supported: PostgreSQL 12+ with explicit qualified schema.table and independently enabled operation: ordinary CREATE TABLE, nullable ADD COLUMN, nonunique simple-column CREATE INDEX. Cross-provider, unqualified search_path, IF EXISTS, CASCADE, DROP, TRUNCATE, CONCURRENTLY, dynamic SQL and multi-statement requests are unsupported. The PG account must read physical metadata and pg_control_system() for physical cluster identity (e.g. explicit pg_monitor privilege); unproven metadata fails closed.

The Host verifies actual server/database/cluster identity, table OID, column types, nullability, index keys and schema namespace collisions. Approval fingerprints bind native G1 compiled SQL, typed effects, physical before-snapshot, principal/key, database, policy revision, request ID and expiry, plus a human-readable Before/ExpectedAfter diff. A fresh connection re-verifies the target under a PostgreSQL transaction, with a table lock for ALTER/INDEX, executes only the immutable compiled native SQL once and checks actual post-DDL metadata before COMMIT. The DDL attempt requires a durable pre-execution AuditLog write; outcome is audited separately. Unknown commit or rollback outcomes must be reconciled, never auto-retried.

Exclusive locks, external DDL event triggers and the complete dependency graph are not magically proven by the table-local metadata snapshot. The Host does not claim global cross-client atomic CAS, known lock duration or affected row counts; other database adapters require independent G4/G5 transaction/capability proofs. SqlCore remains driver-free.

G4 SQLite — independent, file-backed transactional DDL adapter

SQLite DDL shares the same existing execute_ddl_sql MCP tool and independent IDdlApprovalProvider interface, but not PostgreSQL's physical OID, schema-lock or runtime proof. It must be explicitly opted in:

services.AddHsSqlAgentSqliteDdl(new SqliteDdlExecutionOptions
{
    Enabled = true,
    AllowCreateTable = true,
    AllowAddColumn = true,
    AllowCreateIndex = true,
    PolicyRevision = "sqlite-ddl-policy-2026-10"
});
services.AddSingleton<IDdlApprovalProvider, MyIndependentDdlApprovalProvider>();
// Also grant execute_ddl_sql explicitly on the MCP access key.

Only file-backed SQLite 3.35+, no attached DB, no :memory:, no TEMP and explicit main.table target. The authenticated database identity must be the target's absolute file path; the public preview provides only its SHA-256 identity, schema cookie, typed columns/indexes and Before/ExpectedAfter. Qualified CREATE INDEX must render CREATE INDEX "main"."index" ON "table" (...) (SQLite forbids a schema-qualified ON table). No second parser and no user SQL execution.

The SQLite-specific adapter checks file identity, PRAGMA main.schema_version, the complete target table SQL, table columns and physical index keys. It releases the preview connection before human approval, binds the physical snapshot and actor/policy into an expiring single-request fingerprint, then starts a BEGIN IMMEDIATE transactional writer reservation before rechecking metadata. Only the F# compiled command is executed, with expected schema-cookie delta and typed postcondition verification before COMMIT; failed or uncertain rollbacks are not reported as committed, and an unknown COMMIT is OutcomeUnknown.

No DML permission or consent is inherited. Startup opt-in, execute_ddl_sql key grant, separate DDL approval provider and durable pre-execution audit are all required. Only the G1 additive operations are executable; unknown operations, expression/partial indexes, unmodeled virtual targets, schema drift and implicit name resolution fail closed. SQL Server and Firebird have separately opt-in, restricted Host adapters described below.

G4 SQL Server 2022 — restricted transactional DDL (independent opt-in)

The existing single execute_ddl_sql MCP tool has a separate SQL Server adapter. Installation and ordinary Query/DML rights never enable it. Only a qualified dbo.table (or another explicitly allowlisted user schema) with 1–16 simple INT columns, no constraints/defaults/identity, is in the initial SQL Server Host executable subset. ALTER TABLE, CREATE INDEX, DROP, conditional/batch DDL, unsupported types or effects are rejected. SQL Server parser / Catalog support is not evidence of Host execution support.

using HsSqlAgent.Server.Extensions;
using HsSqlAgent.Server.Services;
services.AddHsSqlAgentSqlServerDdl(new SqlServerDdlExecutionOptions
{
    Enabled = true,
    AllowedSchemas = new HashSet<string>(StringComparer.OrdinalIgnoreCase) { "dbo" },
    AllowCreateTable = true,
    PolicyRevision = "sqlserver-ddl-approval-v1"
});
services.AddSingleton<IDdlApprovalProvider, MyIndependentDdlApprovalProvider>();
// Separately grant execute_ddl_sql to the intended MCP access key.

The SQL Server 2022+ adapter is distinct from the PostgreSQL/SQLite runtimes. An enabled Host requires an actual non-system database, SQL Server 2022+ version, proven case-insensitive database collation, existing schema ID, complete permission to inspect DDL triggers, and an absent target object. Preview reads physical sys.* metadata without mutations; an expiring, one-time independent approval binds database/server identity, schema/object snapshot, actor, compiled native plan, policy and readable Before/After. The approval connection is released before the human review. At execution, a new connection takes a transaction-owned application lock, rechecks live metadata inside a SERIALIZABLE transaction, executes only the SqlCore compiled native command, verifies actual column types/nullability before commit, then records the committed result. A failure with proven rollback is not success; uncertain COMMIT/rollback returns outcome_unknown and must be reconciled, never automatically retried.

Transaction application locks coordinate this Host's requests; they are not a global compare-and-swap against unrelated external SQL Server DDL. External schema changes or triggers without sufficient evidence fail closed. No public SQL Core DTO can authorize physical execution; the separate MCP grant, DDL approval, and durable pre-execution audit remain mandatory. The four default read-only tools remain selected; DML and DDL are not.

G4 Firebird 5 — CREATE TABLE-only transactional DDL (independent opt-in)

The existing execute_ddl_sql tool also supports an independently configured Firebird 5 Host. The first executable slice permits one unqualified CREATE TABLE name (...) with 1–16 simple INT/INTEGER columns. No constraints, defaults, ALTER, INDEX, DROP, implicit schema, dynamic SQL, or batch statements are executable through this adapter.

using HsSqlAgent.Server.Extensions;
using HsSqlAgent.Server.Services;
// Provision this GUID from an authenticated Firebird DBA session:
// SELECT MON$GUID FROM MON$DATABASE
services.AddHsSqlAgentFirebirdDdl(new FirebirdDdlExecutionOptions
{
    Enabled = true,
    AllowCreateTable = true,
    AllowedDatabaseGuids = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
    {
        "<approved-physical-firebird-database-guid>"
    },
    PolicyRevision = "firebird-ddl-governance-v1"
});
services.AddSingleton<IDdlApprovalProvider, MyIndependentDdlApprovalProvider>();
// Grant execute_ddl_sql explicitly on the intended MCP access key.

The Host requires verifiable Firebird 5 engine identity, SQL dialect 3, physical MON$GUID / MON$FILE_ID / database creation metadata, the configured database GUID allowlist, reliable system catalog reads, an absent target, and no active user database/DDL triggers. Physical identifiers and paths are hashed before exposure to MCP. The preview is read-only and releases its connection before requesting a separately authorized human decision with an expiring, single-request fingerprint.

A new connection starts a Firebird transaction, rechecks the same physical identity, policy, target absence and trigger boundary, executes only the compiled SqlCore native command, and compares exact physical columns before committing. A verified rollback cannot be reported as success; an uncertain COMMIT or rollback returns outcome_unknown, not an automatic retry. This does not promise a global compare-and-swap against unrelated Firebird DDL writers. The startup opt-in, explicit key grant, durable pre-execution audit, and independent IDdlApprovalProvider are mandatory. The four default read-only tools are unchanged; DML and DDL remain unselected.

G5 Oracle 26ai — independently approved, implicit-commit CREATE TABLE

This Host feature reuses the same disabled-by-default execute_ddl_sql MCP tool and F# CompileDdl native SQL renderer. The initial executable subset is one unqualified CREATE TABLE with 1–16 simple INTEGER columns in the exact current user schema. It excludes custom constraints, defaults, indexes, CTAS, ALTER, DROP, dynamic SQL, batch SQL and other Oracle versions.

using HsSqlAgent.Server.Extensions;
using HsSqlAgent.Server.Services;

services.AddHsSqlAgentOracleDdl(new OracleDdlExecutionOptions
{
    Enabled = true,
    AllowCreateTable = true,
    AllowedPdbNames = new HashSet<string>(StringComparer.Ordinal) { "FREEPDB1" },
    AllowedSchemas = new HashSet<string>(StringComparer.Ordinal) { "SQLCORE" },
    PolicyRevision = "oracle-ddl-independent-governance-v1"
});
services.AddSingleton<IDdlApprovalProvider, MyIndependentDdlApprovalProvider>();
// Separately grant execute_ddl_sql to this MCP key.
// All four default read-only tools remain selected; DML/DDL are not.

The designated Oracle Host account must be a non-system user with CURRENT_SCHEMA = SESSION_USER, connecting to an explicitly allowlisted, writable, primary Oracle 26ai (23.26+) PDB. Provision only the necessary dictionary and cooperative-lock privileges, using a DBA-owned deployment step (not an LLM-created or auto-granted privilege):

GRANT SELECT ON SYS.V_$DATABASE TO SQLCORE;
GRANT SELECT ON SYS.V_$PDBS TO SQLCORE;
GRANT SELECT ON SYS.DBA_TRIGGERS TO SQLCORE;
GRANT EXECUTE ON SYS.DBMS_LOCK TO SQLCORE;

These SELECT grants are required to prove CDB/PDB identity and complete visibility of active schema/database DDL triggers. Missing access is a hard denial, not a fallback to ALL_TRIGGERS. The read-only Preview releases the first connection prior to separate human approval. A fresh connection takes a session-held cooperative DBMS_LOCK (which survives DDL implicit COMMIT), rechecks physical metadata, plan/policy/expiration, then executes only the typed compiled statement and verifies actual USER_OBJECTS / USER_TAB_COLS state.

Oracle DDL performs an implicit COMMIT before and after the statement. This Host never uses BeginTransaction, never promises rollback, and never automatically retries an unknown outcome. After native execution starts, a lost response or postcondition mismatch is outcome_unknown and requires live DBA reconciliation. The Host's cooperative session lock does not prevent unrelated Oracle clients from changing objects. Independent access-key grant, DDL approval, pre-execution durable audit, and DB permission checks are all required; install alone enables nothing.

G6 PostgreSQL — separately approved high-risk DROP of an empty table

The existing execute_ddl_sql can now optionally accept a single DROP TABLE schema.table RESTRICT. This is a destructive operation and is not enabled by AllowCreateTable, ordinary DML permission, or a default DDL installation. Enable it only for an isolated, dedicated PostgreSQL 12+ application schema, and explicitly grant execute_ddl_sql to the intended MCP access key.

using HsSqlAgent.Server.Extensions;
using HsSqlAgent.Server.Services;

services.AddHsSqlAgentPostgresDdl(new PostgresDdlExecutionOptions
{
    Enabled = true,
    AllowedSchemas = new HashSet<string>(StringComparer.Ordinal) { "staging_cleanup" },
    AllowDropEmptyTable = true, // false by default, independent from additive DDL
    AllowedDropPrincipals = new HashSet<string>(StringComparer.Ordinal)
    {
        "mcp-key:123" // authenticated ID of the separately authorized MCP key
    },
    PolicyRevision = "postgres-g6-drop-empty-governance-v1"
});
services.AddSingleton<IDdlApprovalProvider, MyIndependentDdlApprovalProvider>();
// Separately grant execute_ddl_sql to the desired MCP key.

The Host also requires that the authenticated MCP key principal match AllowedDropPrincipals. Merely granting execute_ddl_sql to a key does not authorize destructive DROP; changing this allowlist invalidates outstanding approvals.

The MCP preview exposes Risk=Destructive, MayLoseData=true, and the human-readable Before/After impact summary. None of these is an execution grant.

This is not a general DROP TABLE permission. Preview and execution both demand an actual empty ordinary PostgreSQL table with 1–16 INTEGER columns, direct owner privilege, no secondary indexes, constraints, dependent views, triggers, defaults/identity, inherited/partitioned tables, RLS, publication or unproven schema effects. Preview is read-only; any change in those properties before execution is rejected. The approved statement, schema identity, physical PostgreSQL cluster, actor, policy and short-lived nonce are bound to one independent human approval challenge.

Execution acquires an ACCESS EXCLUSIVE lock inside a real PostgreSQL transaction, rechecks the exact table OID, permissions, dependencies and zero-row condition, issues the F#-compiled native RESTRICT command only once, checks that the table is absent, then commits. A confirmed rollback prevents reported success. Lost COMMIT or rollback outcomes are outcome_unknown; never retry automatically. The risk warning explicitly states that after COMMIT the schema definition is permanently deleted. No nonempty tables, CASCADE, IF EXISTS, multi-table DROP, or MySQL/Oracle DROP are enabled.

The four default read-only MCP tools remain selected. DML and DDL remain unselected by default. Rollback of the feature switch means disabling AllowDropEmptyTable; it does not restore a table already committed as deleted.

G6 S16 — PostgreSQL atomic 2–4 statement CREATE TABLE Batch (opt-in)

The existing execute_ddl_sql MCP tool now recognizes an intentionally limited single-schema PostgreSQL 16 atomic batch: 2–4 independently compiled CREATE TABLE schema.name (…) statements, each with a different table name. There are no cross-statement dependencies in this first proven subset. No new MCP tools, SQL parsers, DML approval or inferred permissions are introduced. The F# batch plan is not a promise of atomicity until PostgreSQL actually commits the transaction.

using HsSqlAgent.Server.Extensions;
using HsSqlAgent.Server.Services;

services.AddHsSqlAgentPostgresDdl(new PostgresDdlExecutionOptions
{
    Enabled = true,
    AllowCreateTable = true,
    AllowCreateTableBatch = true,  // false by default, distinct from single DDL
    AllowedBatchPrincipals = new HashSet<string>(StringComparer.Ordinal)
    {
        "mcp-key:123" // authenticated MCP key ID, not caller SQL
    },
    AllowedSchemas = new HashSet<string>(StringComparer.Ordinal)
    {
        "staging_cleanup"
    },
    PolicyRevision = "postgres-g6-atomic-batch-v1"
});
services.AddSingleton<IDdlApprovalProvider, MyIndependentDdlApprovalProvider>();
// Also separately grant execute_ddl_sql to the designated MCP access key.
// Do not select DML/DDL tools by default.

Example input for the same existing execute_ddl_sql tool:

CREATE TABLE staging_cleanup.import_a (id INTEGER NOT NULL);
CREATE TABLE staging_cleanup.import_b (id INTEGER NOT NULL);

previewOnly=true returns the ordered compiled SQL statements, one batch plan fingerprint, every physical Before observation and every hypothetical After plus a human-readable whole-batch diff. Preview never executes SQL or obtains approval. On previewOnly=false, the The entire batch execution transaction shares one configured ExecutionTimeout (maximum 15 seconds), including all statements and physical checks; an uncertain COMMIT remains outcome_unknown.

Host first requires the batch-only key allowlist, then one independent human approval bound to the entire ordered plan. A new PostgreSQL connection rechecks the original server, schema identity, object absence, approval expiry and policy. Every compiled native CREATE and its physical postcondition execute under one PostgreSQL transaction; the Host confirms every final table before COMMIT. A confirmed rollback restores the state when execution fails. A lost COMMIT or uncertain rollback returns outcome_unknown, not a false success/failure, and must never be retried automatically.

Cross-schema targets, repeated table names, statement counts outside 2–4, ALTER/INDEX/DROP, dependencies between statements, data-writing CTAS, DML, GO and other Provider DDL batches remain blocked. In particular, MySQL and Oracle implicit commits prohibit claiming this PostgreSQL atomic batch contract. This Host opt-in does not change the four default read-only tool selections or the default-unselected DML/DDL choices. Disable AllowCreateTableBatch to stop future batches; already-committed schema changes are not undone.

G6/S16 dependent DDL Batch (PostgreSQL 16 only; additional opt-in)

In addition to the existing independent CREATE-only batch, the same execute_ddl_sql tool can optionally execute a single new empty table's dependent chain under one approval and one PostgreSQL transaction:

CREATE TABLE staging_cleanup.new_data (id INTEGER NOT NULL);
ALTER TABLE staging_cleanup.new_data ADD COLUMN count INTEGER NULL;
CREATE INDEX ix_new_data_count ON staging_cleanup.new_data(count);

Enable it only for designated MCP keys and schema(s), in addition to the previous Batch grants:

services.AddHsSqlAgentPostgresDdl(new PostgresDdlExecutionOptions
{
    Enabled = true,
    AllowCreateTable = true,
    AllowAddColumn = true,
    AllowCreateIndex = true,
    AllowCreateTableBatch = true,
    AllowDependentDdlBatch = true, // default false; separately reviewed
    AllowedBatchPrincipals = new HashSet<string>(StringComparer.Ordinal)
    {
        "mcp-key:123"
    },
    AllowedSchemas = new HashSet<string>(StringComparer.Ordinal)
    {
        "staging_cleanup"
    },
    PolicyRevision = "g6-dependent-create-chain-v1"
});
services.AddSingleton<IDdlApprovalProvider, MyIndependentDdlApprovalProvider>();
// Also grant execute_ddl_sql to this authenticated MCP key; no DML inheritance.

This capability cannot ALTER or index an existing table: the root CREATE must be the first operation of the same approved batch. Preview reads only actual physical metadata and explicitly labels all intermediate projections as hypothetical, not observed tables. All ordered effects are bound to the same short-lived approval, including the optional index name and columns. In the single transaction, the Host rechecks root absence and schema identity, then verifies actual physical columns, table OID and index metadata after each operation and at the end before COMMIT. Existing relations, index collisions, unapproved schemas/keys or policy/approval drift must fail closed; confirmed rollback cannot leave an intermediate table. Lost COMMIT or unproven rollback stays outcome_unknown and is never automatically retried.

The existing independent CREATE batches remain available without this new gate. Other providers, arbitrary ALTER, multiple-target dependency graphs, DEFAULT/NOT NULL column additions, concurrent indexes and destructive DDL Batch are still not allowed. The front end still preselects four read-only tools and leaves DML/DDL unselected.

Explicit DML MCP permission gate

The execute_dml_sql mutating tool requires an explicit, exact execute_dml_sql entry in the MCP access key's AllowedTools authorization context. Missing, empty, whitespace-only, substring and delimiter-smuggled tool lists are denied before provider lookup, preview, or approval. It is not enabled by the four default read-only tools; a DDL grant does not imply a DML grant. Existing read-only Query/metadata behavior for a present empty AllowedTools list is unchanged for backwards compatibility. DML additionally requires its independent approval and live row-set/commit proof.

The regression campaign lives in backend/src/UnitTest/HsSqlAgent.Server.Test/Tools/HostAdversarialMutationAuthorizationTests.cs and Services/TypedDmlRuntimeTests.cs.

Product Compatible and additional computed target framework versions.
.NET net8.0 is compatible.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed.  net9.0 was computed.  net9.0-android was computed.  net9.0-browser was computed.  net9.0-ios was computed.  net9.0-maccatalyst was computed.  net9.0-macos was computed.  net9.0-tvos was computed.  net9.0-windows was computed.  net10.0 is compatible.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (1)

Showing the top 1 NuGet packages that depend on HsSqlAgent.Server:

Package Downloads
HsSqlAgent.Hosting

Official batteries-included ASP.NET Core composition for HsSqlAgent, matching the standalone Docker host.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
3.1.0 68 11/14/2026
3.0.0 337 10/25/2026
2.0.6 87 10/18/2026
2.0.5 129 9/8/2026
2.0.4 117 9/7/2026
2.0.3 116 9/5/2026
2.0.2 95 9/4/2026
2.0.1 103 9/3/2026
2.0.0 97 9/2/2026
1.10.10 102 8/25/2026
1.10.9 103 8/24/2026
1.10.8 109 8/23/2026
1.10.7 104 8/18/2026
1.10.6 103 8/14/2026
1.10.5 102 8/14/2026
1.10.4 98 8/11/2026
1.10.3 394 8/1/2026
1.10.2 149 7/7/2026
1.10.1 139 7/7/2026
1.10.0 145 7/4/2026
Loading failed