What You’ll Accomplish
Guardrails let you block dangerous queries before they execute. You can:
- Prevent accidental
UPDATE or DELETE without a WHERE clause
- Block
DROP TABLE and other destructive DDL commands
- Enforce read-only access for specific user groups
- Require
LIMIT clauses on large tables
- Block queries that access sensitive columns
The Problem Guardrails Solve
Without guardrails, one typo can destroy production data:
-- Intended: Update one user's email
UPDATE users SET email = 'new@email.com' WHERE id = 123;
-- Accidental: Update ALL users (forgot WHERE clause)
UPDATE users SET email = 'new@email.com';
-- 500,000 rows affected 💥
How Guardrails Work
Query Submitted
User runs a query through Hoop (CLI, Web App, or API)
Rules Evaluated
Each guardrail rule is checked against the query using pattern matching
Decision Made
If a rule matches: block, warn, or require approval based on configuration
Result Returned
User sees either the query result or an error explaining which rule was violated
Rule Types
| Type | Description | Use Case |
|---|
| Input Rules | Evaluate the query before execution | Block dangerous commands |
| Output Rules | Evaluate results after execution | Redact sensitive data in output |
Quick Start
Prerequisites
To get the most out of this guide, you will need to:
- At least one database connection configured
- Admin access to create guardrails
Step 1: Create a Guardrail
Navigate to Guardrails
Go to Manage > Guardrails in the Web App
Create New Guardrail
Click Create New Guardrail in the top-right corner
Set Basic Information
- Name:
block-unsafe-updates
- Description:
Blocks UPDATE/DELETE without WHERE clause
- Click Add Rule
- Select Pattern Match (Regex)
- Enter the pattern:
(?i)(UPDATE|DELETE)\s+\w+\s+(SET|FROM)(?!.*WHERE)
- Set Action to Block
- Enter the error message:
Blocked: UPDATE/DELETE requires a WHERE clause
The (?i) makes the pattern case-insensitive, so it catches update, UPDATE, and Update.
Step 3: Assign to Connections
- In the Connections section, select which connections this guardrail applies to
- Choose your production database connections
- Click Save
Step 4: Test the Guardrail
Try running an unsafe query:
hoop exec prod-db -i "UPDATE users SET status = 'inactive'"
Error: Guardrail violation
Rule: block-unsafe-updates
Message: Blocked: UPDATE/DELETE requires a WHERE clause
hoop exec prod-db -i "UPDATE users SET status = 'inactive' WHERE id = 123"
Common Guardrail Recipes
Recipe 1: Block Destructive DDL
Prevent accidental schema changes in production.
Pattern:
(?i)^\s*(DROP|TRUNCATE|ALTER)\s+(TABLE|DATABASE|INDEX|SCHEMA)
DDL commands are blocked. Use a migration tool or request elevated access.
What it catches:
DROP TABLE usersTRUNCATE TABLE ordersALTER TABLE customers DROP COLUMN email
Recipe 2: Read-Only Access
Block all write operations for analyst or read-only user groups.
Patterns (create separate rules for each):
| Pattern | Blocks |
|---|
(?i)^\s*INSERT\s+INTO | INSERT statements |
(?i)^\s*UPDATE\s+ | UPDATE statements |
(?i)^\s*DELETE\s+FROM | DELETE statements |
(?i)^\s*(CREATE|DROP|ALTER)\s+ | DDL commands |
This connection is read-only. Write operations are not permitted.
Apply this guardrail only to connections used by read-only groups, not to admin connections.
Recipe 3: Block SELECT * on Large Tables
Prevent queries that could return millions of rows.
Pattern:
(?i)SELECT\s+\*\s+FROM\s+(orders|logs|events|transactions)(?!.*LIMIT)
SELECT * without LIMIT is blocked on large tables. Add a LIMIT clause or select specific columns.
What it catches:
SELECT * FROM orders (blocked)SELECT * FROM orders LIMIT 100 (allowed)SELECT id, status FROM orders (allowed)
Recipe 4: Prevent Credential Access
Block queries that might expose passwords or secrets.
Pattern:
(?i)SELECT\s+.*\bFROM\s+\w*users\w*.*\b(password|secret|token|api_key)\b
Queries selecting credential columns are blocked. Use the appropriate service to manage credentials.
Recipe 5: Require LIMIT on All Queries
Warn users when they forget to add LIMIT.
Pattern:
(?i)^\s*SELECT\s+(?!.*\bLIMIT\b).*FROM
Consider adding a LIMIT clause to prevent large result sets.
Use Warn instead of Block when you want to educate users without stopping their work.
Recipe 6: Block Specific Table Access
Restrict access to sensitive tables like salaries or api_keys.
Pattern:
(?i)\b(FROM|JOIN|INTO|UPDATE)\s+(salaries|api_keys|credentials|secrets)\b
Access to this table is restricted. Contact your administrator for access.
Testing Guardrails Safely
Always test guardrails before applying them to production connections.
Testing Process
Create a Test Connection
Create a separate connection to the same database (e.g., prod-db-test)
Apply the Guardrail
Assign the guardrail only to the test connection
Run Test Queries
Test both queries that should be blocked and queries that should pass
Verify Results
Confirm the guardrail blocks what it should and allows what it should
Apply to Production
Once verified, add production connections to the guardrail
Test Cases to Run
For each guardrail, test:
- Should block: A query that matches the pattern exactly
- Should allow: A similar query that doesn’t match
- Edge cases: Queries with different casing, extra whitespace, or variations
Viewing Blocked Queries
When a guardrail blocks a query, it’s logged in Sessions.
Go to Sessions
Navigate to Sessions in the sidebar
Filter by Status
Use the filter to show only Blocked sessions
View Details
Click any session to see:
- The query that was blocked
- Which guardrail rule triggered
- The error message shown to the user
- Timestamp and user information
Emergency Bypass
If a legitimate query is blocked and needs to run immediately:
Option 1: Temporarily Disable the Guardrail
- Go to Manage > Guardrails
- Find the blocking guardrail
- Toggle it to Disabled
- Run your query
- Re-enable the guardrail immediately after
Document any emergency bypass in your incident log. Re-enable the guardrail as soon as possible.
Option 2: Refine the Pattern
If the guardrail is catching legitimate queries, update the pattern:
- Go to Manage > Guardrails > [guardrail name]
- Edit the rule that’s causing issues
- Refine the regex to be more specific
- Save and test
Option 3: Add an Exception
For specific users or groups that need to bypass certain rules:
- Create a new connection without the guardrail
- Restrict access to that connection to specific groups
- Use this “elevated” connection for exceptional cases
Troubleshooting
Guardrail Not Blocking Expected Queries
Check:
- Guardrail is enabled - Verify the toggle is on in Manage > Guardrails
- Connection is assigned - Check that the connection is in the guardrail’s connection list
- Pattern syntax is valid - Test your regex at regex101.com
- Case sensitivity - Add
(?i) at the start for case-insensitive matching
Common pattern mistakes:
| Problem | Bad Pattern | Fixed Pattern |
|---|
| Missing case-insensitive | DROP TABLE | (?i)DROP TABLE |
| Missing word boundaries | DELETE | \bDELETE\b |
| Anchoring too strict | ^DROP | ^\s*DROP (allows leading whitespace) |
False Positives (Legitimate Queries Blocked)
Problem: Pattern is too broad
Example:
- Pattern:
DROP
- Blocks:
SELECT * FROM drop_shipped_orders (has “DROP” in table name)
Fix: Use word boundaries and be specific:
(?i)^\s*DROP\s+(TABLE|DATABASE|INDEX)
- Reduce rule count - Combine similar rules where possible
- Simplify patterns - Complex regex with backtracking is slower
- Use Input Rules - Input rules are faster than Output rules
Guardrails vs Other Features
| Feature | Purpose | When to Use |
|---|
| Guardrails | Block queries based on patterns | Prevent dangerous operations |
| Live Data Masking | Redact sensitive data in output | Protect PII in query results |
| Access Requests | Require approval for access | Time-limited or command-level approval |
| Access Control | Control who can access connections | Restrict connection visibility |
- Guardrails block
DROP TABLE commands
- Live Data Masking redacts SSN in
SELECT results
- Access Requests require approval before connecting
- Access Control limits who sees the connection
Next Steps
