Documentation
Deploy, configure and use Query Gate to give your team governed access to SQL Server, PostgreSQL, MySQL and MariaDB.
Overview
Query Gate is a .NET 10 Blazor Server application backed by PostgreSQL for governed database access across PostgreSQL, SQL Server, MySQL and MariaDB. Users work in a query workspace without seeing database credentials. Permissions, approvals and audit records control and document every execution.
Key capabilities: permission-gated SQL with ScriptDom AST parsing, multi-engine support, granular object-level scoping and role boundaries, dynamic PII data masking (HIPAA, GDPR, PCI-DSS, SOC 2), visual query execution plans, time-boxed access grants, audit-first execution, passkey / TOTP authentication, an access-request workflow, and multi-format exports (CSV, Excel XLSX/XLSB, JSON, Markdown, and SQL INSERT scripts).
User guide
Query Gate provides engineers and auditors with controlled database access while eliminating credential sprawl.
Query workspace
Select your database, write SQL with Monaco syntax assistance and keyboard shortcuts (F5, Ctrl+Enter), and execute queries. Results appear in a responsive, filterable data grid with row counters.
SQL Gate validation
Queries are parsed and verified using Microsoft ScriptDom (and dialect parsers) before execution. Disallowed statements, cross-database queries, or forbidden system procedures are denied upfront.
Access requests & approval
Need temporary or elevated access? Request access directly from the Access page. Administrators review, approve with expiration dates, or reject requests with full audit records.
Auditing & multi-format export
Every executed statement is recorded with duration, outcome, and user identity. Users with EXPORT permission can download query results as CSV, Excel (.xlsx, .xlsb), JSON, Markdown, or SQL INSERT statements.
Object scoping & boundaries
Configure granular table and schema boundaries at both database and team-role levels. Protect sensitive tables with exclusion rules and wildcards like dbo.* or *.secrets.
Dynamic PII masking
Columns matching PII patterns (credit cards, SSNs, patient names, medical IDs) are redacted automatically. Users with the high-risk UNMASK permission see plain data.
Object-level scoping and boundaries
Query Gate allows administrators to restrict access not just per database, but down to specific schemas and tables:
- Database-level rules: Applied to all users querying a database. Excluded tables (e.g.
dbo.AuditLogs,*.passwords) cannot be queried by anyone, even administrators. - Role-level rules: Applied to members of specific roles (e.g. Developers, Support, QA). Whitelists allow querying only approved tables, while blacklists protect confidential tables.
- Wildcard syntax: Use
*for all objects,schema.*for an entire schema (e.g.sales.*), or*.column/*.tablefor cross-schema matching. - Conflict prevention: Query Gate automatically detects if an object is simultaneously included and excluded, preventing accidental misconfigurations.
Dynamic data masking and compliance
Query Gate includes built-in compliance presets aligned with major regulatory standards:
- PCI-DSS 3.4 & 3.2: Masks credit card PANs (preserving only last 4 digits) and completely redacts CVVs and PINs.
- HIPAA Safe Harbor: Redacts patient names (
J***n S***h), medical record numbers (MRNs), admission dates, and geographic locations smaller than a state. - GDPR & CCPA: Redacts email addresses, IP addresses, government IDs, and telephone numbers.
- SOC 2 Confidentiality: Redacts employee salaries, compensation figures, and authentication secrets.
- The UNMASK permission: Marked as High Risk in the access matrix; required to view unredacted data. All unmasked queries are recorded in the audit trail.
Exports and query explain plans
Query results can be exported into multiple formats depending on your workflow:
- CSV (.csv): Standard comma-separated format with formula injection neutralization.
- Excel (.xlsx & .xlsb): Native OpenXML and binary spreadsheet formats with styled header rows.
- JSON (.json): Formatted array of objects for API and developer integration.
- Markdown: Clean GitHub-flavored Markdown table copied directly to clipboard.
- SQL INSERT script: Generates runnable
INSERT INTOscripts with optional idempotency (IF NOT EXISTS) checks based on selected primary/unique keys. - Explain Plan: Click Explain in the query workspace to generate and visualize estimated subtree costs, operator hierarchies, and execution steps.
Deployment options
Query Gate is a .NET 10 interactive server application backed by PostgreSQL. Interactive sessions use SignalR, which requires WebSocket support and HTTPS on remote hosts. Deploy Query Gate at the root of a domain name rather than a subpath. Choose the deployment option that fits your team's workflow and infrastructure:
One-command setup with bundled PostgreSQL 17, automated migrations, persistent keys, and setup wizard.
Production TLS termination with Caddy, Nginx, Traefik, Cloudflare Tunnel, or Coolify PaaS.
Dedicated application pool with No Managed Code, WebSockets, and .NET 10 Hosting Bundle.
Managed cloud hosting with HTTPS Only, WebSockets, persistent keys, and Key Vault integration.
Direct Kestrel HTTPS service under a dedicated unprivileged user managed with systemd.
Deploy with Docker
Run Query Gate with Docker Compose. The setup includes a bundled PostgreSQL 17 container, automatic database migrations, auto-generated persistent encryption keys, and a web setup wizard for creating the initial root administrator.
- Clone the GitHub repository.
- (Optional) Copy
.env.exampleto.envto customize optional settings like database password or port. All variables are optional. - Start the stack:
docker compose up -d
docker compose logs -f query-gate
- Open
http://localhost:5176in your browser. (For remote hosts or production, see the HTTPS requirement below). - On first visit, Query Gate launches the setup wizard to create your root administrator account directly in the browser.
- Sign in and explore your new governed workspace.
HTTPS requirement on remote hosts: Browsers treat localhost as a secure context, so plain HTTP works locally. When deploying to a remote server or domain, HTTPS is mandatory. Modern browsers block Blazor Server interactive circuits and antiforgery tokens on unencrypted HTTP, which prevents database registration and query execution from functioning.
External PostgreSQL database: To connect to an existing database instead of the bundled container, set DB_CONNECTION_STRING in .env. Inside containers, localhost resolves to the container itself; on Docker Desktop use host.docker.internal.
For detailed setup, upgrades, backups, and security notes, see the Docker deployment guide.
Reverse proxies and Coolify
When deploying in production, it is common to place Query Gate behind a reverse proxy such as Caddy, Nginx, Traefik, Cloudflare Tunnel, or a self-hosting platform like Coolify.
Query Gate listens on plain HTTP port 5176 inside the container. The reverse proxy handles public HTTPS traffic, manages Let's Encrypt certificates, and forwards requests internally to port 5176.
Deploying on Coolify
- In Coolify, create a new Project and choose Docker Compose.
- Connect your Git repository or paste the repository Compose configuration.
- Set your domain name (for example,
querygate.yourdomain.com). Coolify will automatically provision SSL certificates and route traffic to port 5176. - Deploy the stack and visit your domain to complete the initial setup wizard.
Forwarded headers
Configure your reverse proxy to forward client headers (X-Forwarded-For and X-Forwarded-Proto) so that client IP addresses and protocol schemes are preserved accurately in the audit log.
Deploy to IIS on Windows
Install IIS, the WebSocket Protocol feature and the .NET 10 Hosting Bundle. Publish the project:
dotnet publish DbAccessManager.csproj -c Release -r win-x64 --self-contained false -o ./publish
Deploy the published files, including web.config, to an IIS site at the domain root. Configure a dedicated application pool with No Managed Code, bind a trusted HTTPS certificate, and give the pool identity access to a persistent data protection key directory. Set the application configuration and run the first migration with one instance, as described in the IIS guide.
Deploy to Azure App Service
Create an App Service with a .NET 10 runtime and connectivity to your PostgreSQL database. Enable HTTPS Only and WebSockets, and use one instance. Publish the application and deploy the contents of the publish directory:
dotnet publish DbAccessManager.csproj -c Release -o ./publish
Set the PostgreSQL connection, encryption and JWT keys, bootstrap credentials and a persistent data protection key path in App Service configuration. Apply migrations on the first start, then remove bootstrap credentials. See the Azure guide for settings and upgrade steps.
Deploy to a Linux server
Install the .NET 10 ASP.NET Core runtime, publish the application, and run it under a dedicated service account with systemd:
dotnet publish DbAccessManager.csproj -c Release -r linux-x64 --self-contained false -o ./publish
Configure PostgreSQL access, secrets and migrations before starting the service. See the Linux guide for a systemd unit and environment settings.
Configuration and operations
All secrets must be kept private and backed up securely. Data protection and encryption keys are stored on the persistent data-protection-keys volume.
| Variable | Description | Default / Example |
|---|---|---|
PORT | Host port mapped to Query Gate. (Optional) | 5176 |
DB_PASSWORD | Password for the bundled PostgreSQL container (user querygate). (Optional) | QueryGateDefault!2026 |
DB_CONNECTION_STRING | PostgreSQL connection string overriding the bundled container. (Optional) | Host=querygate-postgres;Port=5432;Database=QueryGate;Username=querygate;Password=... |
Encryption__Key | Base-64 key used to encrypt stored database connection strings. Auto-generated and persisted in volume if unset. (Optional) | openssl rand -base64 32 |
Jwt__Key | Base-64 key used to sign session tokens. Auto-generated and persisted in volume if unset. (Optional) | openssl rand -base64 64 |
BootstrapAdmin__Email | Optional email for headless administrator creation. If omitted, the web setup wizard is used. | [email protected] |
BootstrapAdmin__Password | Optional password for headless administrator creation. | Min 12 chars, mixed case + digit |
Database__ApplyMigrationsOnStartup | Automatically apply EF Core migrations on startup. (Optional) | true |
DataProtection__KeyPath | Persistent volume path for key files. | /var/query-gate/keys |
Keys stored in the persistent volume remain consistent across container restarts. Apply migrations on one instance at a time after a database backup.
First steps
Once deployed, create your administrator account on the setup screen or via optional environment variables on first start.
- Complete the initial setup wizard to create your root administrator account.
- Register a target database (SQL Server, PostgreSQL, MySQL, or MariaDB) using a dedicated, least-privilege account.
- Create users and grant permissions or review their access requests.
- Open the query workspace to run permitted SQL and review activity in the audit log.
Troubleshooting
- Migrations fail on first start
- The PostgreSQL user must have schema and table creation permissions (e.g. database owner) on the application database during migration. The bundled PostgreSQL container uses the
querygateuser with full permissions automatically. - Certificate errors behind a reverse proxy
- When running behind a reverse proxy (such as Caddy, Traefik, or Nginx), terminate TLS at the reverse proxy and route plain HTTP to port 5176. Ensure your reverse proxy forwards headers correctly (X-Forwarded-For, X-Forwarded-Proto).
- Cannot register database or run queries in workspace (HTTP on remote host)
- Query Gate uses Blazor Server interactive circuits, antiforgery tokens, and secure cookies that require a secure context (HTTPS) on remote hosts. When accessed over plain HTTP on a remote domain or IP, browser security policies block circuit execution. Deploy behind an HTTPS reverse proxy (such as Cloudflare Tunnel, Coolify, Traefik, or Nginx).
- "localhost" cannot reach PostgreSQL in Docker
- Inside a container,
localhostresolves to the container itself. Use the container service namequerygate-postgreson the Docker network, orhost.docker.internal(Docker Desktop) to reach a PostgreSQL database running on the host machine. - Data protection key errors after restart
- The key directory must be on a persistent volume. If the volume is deleted, authentication cookies and encrypted values become invalid. Mount the key directory as a named volume in your Compose file.
- Users see "Account inactive or expired"
- An administrator must activate the account and optionally set an expiry date. Check the Users admin page and ensure the account is active.
- Five failed login attempts: account locked
- Accounts lock for 15 minutes after five consecutive failed password attempts. An administrator can unlock the account early from the Users admin page.
More resources
- User guide: step-by-step usage for end users: requesting access, running queries, reading errors and exporting results.
- Admin cheat sheet: quick reference for database registration, permission management and approvals.
- Full deployment guide: detailed steps for IIS, Azure, Linux and Docker including upgrades and backups.
- Contributing guide: local setup, running tests and opening pull requests.
- Source on GitHub: browse the code, report issues or follow the project.