Document SQL query tool security measures

[pierrevalade]

Context

A customer inquired about the security of the MCP SQL query tool. We need to document the existing security measures to help users understand how their data is protected.

Task

Add a documentation page explaining the security of the SQL query tool.

Current Security Implementation

We're using:

• ClickHouse Access Rights: https://clickhouse.com/docs/operations/access-rights
• Read-only permissions: https://clickhouse.com/docs/operations/settings/permissions-for-queries
• Role-based permissions in ClickHouse
• Read-only queries only

Suggested Documentation Structure

1. Overview: High-level security approach
2. Technical Implementation:
   • ClickHouse role-based access control (RBAC)
   • Read-only query enforcement
   • Query validation before execution
3. Security Guarantees:
   • What users can and cannot do
   • Data isolation between customers
   • Audit logging (if applicable)
4. Best Practices: Recommendations for secure usage

Location

Add this as a new page in the documentation, potentially under:

• /content/docs/observability/sql-security or
• /content/docs/security/sql-query-tool

Acceptance Criteria

• Documentation page created explaining SQL query tool security
• References ClickHouse security documentation appropriately
• Explains our specific implementation and configuration
• Clear and accessible to non-technical users
• Reviewed for technical accuracy

References

• ClickHouse Access Rights: https://clickhouse.com/docs/operations/access-rights
• ClickHouse Read-only Settings: https://clickhouse.com/docs/operations/settings/permissions-for-queries

-- Claude Code

[pierrevalade]

Code References for Security Implementation

I've located the specific code that implements the security measures for the SQL query tool:

1. Read-only Query Enforcement

File: backend/core/storage/clickhouse/clickhouse_client.py:159-186

• The raw_query method creates a read-only client with restricted permissions
• Sets readonly: 1 flag to enforce read-only mode
• Applies memory and execution time limits

2. Tenant Row-Level Security

File: backend/core/storage/clickhouse/_utils.py:33-45

• sanitize_readonly_privileges function creates row policies per tenant
• Creates policies like: CREATE ROW POLICY ... USING tenant_uid = {tenant_uid}
• Ensures each tenant can only access their own data

3. User Isolation

File: backend/core/storage/clickhouse/_utils.py:47-81

• Creates unique read-only users per tenant (readonly_{tenant_uid})
• clone_client function creates isolated client connections
• Each tenant gets their own database user with restricted privileges

4. MCP Tool Integration

File: backend/protocol/api/_mcp.py (query_completions tool)
File: backend/protocol/api/_services/completion_service.py (query_completions method)

• The MCP tool calls the service which uses the secured raw_query method

Key Security Features in Code:

1. ClickHouse readonly flag: Line 170 in clickhouse_client.py
2. Row policies: Line 42 in _utils.py creates tenant_{tenant_uid}_{table}_readonly policies
3. Memory limits: 1GB max (line 27 clickhouse_client.py)
4. Execution time limits: 60 seconds max (line 28 clickhouse_client.py)
5. Tenant isolation: All queries filtered by tenant_uid through row policies

This implementation ensures:

• Users can only execute SELECT queries (readonly=1)
• Each tenant's data is isolated via row policies
• Resource consumption is limited
• Queries are validated and sanitized

-- Claude Code

[guillaq]

@anyacherniss do we have an "infrastructure" documentation started somewhere ? We could document the cloud offering, as well as the high availability setup, etc.