Security Best Practices

Security Overview

Quail implements multiple layers of security to protect your data and database credentials. This guide covers essential security practices for production deployments.

Encryption

All database credentials encrypted at rest using AES-256-GCM

Read-Only Access

Queries execute with read-only permissions to prevent data modification

Authentication

Supabase-powered authentication with JWT tokens

Network Security

IP whitelisting and SSL/TLS encryption for database connections

Encryption Keys

Generating a Secure Encryption Key

Quail uses AES-256-GCM encryption to protect database connection strings and credentials stored in browser localStorage. The encryption key is critical for security.

The NEXT_PUBLIC_ENCRYPTION_KEY is the most sensitive configuration value. If compromised, attackers can decrypt all stored database credentials.

Generate a cryptographically secure key:

openssl rand -base64 32

This produces a 256-bit key encoded in base64 (exactly 32 characters).

Key Management Best Practices

Generate Once: Create the encryption key before initial deployment and never change it

Secure Storage: Store the key in a secrets manager (AWS Secrets Manager, HashiCorp Vault, Azure Key Vault)

Access Control: Limit who can view the encryption key to essential personnel only

No Version Control: Never commit .env.local or encryption keys to Git repositories

Environment Isolation: Use different keys for development, staging, and production

Why NEXT_PUBLIC prefix? The encryption key must be available in the browser to decrypt connection strings on the client side. While this seems counterintuitive, the security model assumes the browser environment is trusted for the authenticated user. The key is still not exposed in the codebase or version control.

Encryption Implementation

Quail’s encryption system (~/workspace/source/components/stores/utils/encrypted_store.ts) uses:

Algorithm: AES-256-GCM (Galois/Counter Mode)
Key Size: 256 bits (32 bytes)
IV: Randomly generated 12-byte initialization vector per encryption
Authentication: GCM mode provides authenticated encryption (prevents tampering)

// Encryption happens automatically for database connections
const ALGORITHM = "aes-256-gcm";

// Each encrypted value includes:
// [12-byte IV][16-byte auth tag][encrypted data]

Key Rotation

Changing the encryption key will make all existing encrypted data unrecoverable. Key rotation requires careful planning.

If you must rotate the encryption key:

Notify Users: Inform users they’ll need to re-enter database connections

Schedule Maintenance: Plan rotation during low-traffic periods

Update Key: Change NEXT_PUBLIC_ENCRYPTION_KEY in all environments

Clear Storage: Users must clear browser localStorage or re-add connections

Verify: Test that new connections can be saved and retrieved

Database Credential Security

Connection String Storage

Database connection strings contain sensitive credentials. Quail stores them securely:

Client-side encryption: Connection strings encrypted in browser before localStorage storage (~/workspace/source/components/stores/utils/encrypted_store.ts)
Server-side handling: Connection strings transmitted over HTTPS to Azure Functions for query execution
No persistent server storage: Credentials never stored on application servers or in logs

Connection strings are stored encrypted in the user’s browser localStorage. This means:

Credentials don’t leave the user’s device except during query execution
Each user’s database access is isolated
Clearing browser data removes all saved connections

Creating Read-Only Database Users

Never use admin or write-access credentials with Quail. Always create dedicated read-only users for BI tools.

PostgreSQL Read-Only User

-- Create dedicated user
CREATE USER quail_readonly WITH PASSWORD 'your-secure-password';

-- Grant connection and schema access
GRANT CONNECT ON DATABASE your_database TO quail_readonly;
GRANT USAGE ON SCHEMA public TO quail_readonly;

-- Grant SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO quail_readonly;

-- Ensure future tables are also readable (optional)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO quail_readonly;

-- Grant access to information_schema (required for schema introspection)
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO quail_readonly;

MySQL Read-Only User

-- Create dedicated user
CREATE USER 'quail_readonly'@'%' IDENTIFIED BY 'your-secure-password';

-- Grant SELECT privileges
GRANT SELECT ON your_database.* TO 'quail_readonly'@'%';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify permissions
SHOW GRANTS FOR 'quail_readonly'@'%';

SQL Server Read-Only User

-- Create login
CREATE LOGIN quail_readonly WITH PASSWORD = 'your-secure-password';

-- Create user in database
USE your_database;
CREATE USER quail_readonly FOR LOGIN quail_readonly;

-- Add to db_datareader role (read-only)
ALTER ROLE db_datareader ADD MEMBER quail_readonly;

Password Requirements

For database user passwords:

Minimum 16 characters
Mix of uppercase, lowercase, numbers, and symbols
Generate using password manager or: openssl rand -base64 24
Rotate passwords quarterly
Never reuse passwords across environments

Special Characters in Connection Strings

If passwords contain special characters, URL-encode them:

# Original password: P@ssw0rd!#
# URL encoded: P%40ssw0rd%21%23

postgresql://user:P%40ssw0rd%21%23@host:5432/database

Common encodings:

@ → %40
# → %23
! → %21
: → %3A
/ → %2F

Environment Variable Security

Required Environment Variables

# .env.local (NEVER commit this file)

# Encryption (32 characters, base64 encoded)
NEXT_PUBLIC_ENCRYPTION_KEY="your-32-char-key-here"

# Supabase Authentication
NEXT_PUBLIC_SUPABASE_URL="https://your-project.supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="your-anon-key"
SUPABASE_SERVICE_ROLE_KEY="your-service-role-key"  # Most sensitive!

# MongoDB (stores user metadata, NOT database credentials)
MONGODB_URI="mongodb+srv://user:[email protected]/quail"

# Optional: Default demo database
NEXT_PUBLIC_DEFAULT_DB_CONNECTION_STRING="postgresql://demo:demo@demo-db:5432/demo"

Securing Environment Variables

Vercel
Docker / Kubernetes
AWS / Azure / GCP

Go to Project Settings > Environment Variables
Add variables individually (don’t import .env files)
Mark sensitive keys as “Encrypted” in UI
Use Preview/Production environments appropriately
Enable “Sensitive” flag for:
- SUPABASE_SERVICE_ROLE_KEY
- MONGODB_URI
- NEXT_PUBLIC_ENCRYPTION_KEY

Use Secrets, Not Environment Variables:

# kubernetes-secret.yaml
apiVersion: v1
kind: Secret
metadata:
  name: quail-secrets
type: Opaque
stringData:
  encryption-key: "your-key"
  supabase-service-key: "your-key"
  mongodb-uri: "mongodb+srv://..."

# deployment.yaml
env:
  - name: NEXT_PUBLIC_ENCRYPTION_KEY
    valueFrom:
      secretKeyRef:
        name: quail-secrets
        key: encryption-key

Use Native Secrets Managers:

AWS: Use AWS Secrets Manager or SSM Parameter Store
Azure: Use Azure Key Vault
GCP: Use Secret Manager

Example with AWS:

# Store secret
aws secretsmanager create-secret \
  --name quail/encryption-key \
  --secret-string "your-key"

# Retrieve in application
aws secretsmanager get-secret-value \
  --secret-id quail/encryption-key \
  --query SecretString --output text

What NOT to Do

Never:

Commit .env.local, .env.production, or any file with real credentials to Git
Store secrets in plain text in wikis, documentation, or chat
Email credentials
Hard-code credentials in source code
Use the same credentials across development, staging, and production
Store production credentials on developer machines

Network Security

IP Whitelisting

Most production databases require IP whitelisting. This prevents unauthorized access even if credentials are compromised.

Identify Outbound IP: Determine your Quail deployment’s outbound IP address

# From within your deployment
curl ifconfig.me

Whitelist in Database Provider:

AWS RDS: Security Groups > Inbound Rules
Azure SQL: Networking > Firewall Rules
GCP Cloud SQL: Connections > Authorized Networks
MongoDB Atlas: Network Access > IP Access List

Use Specific IPs: Avoid whitelisting 0.0.0.0/0 (allows all IPs)

Document IPs: Keep a record of whitelisted IPs for each environment

For dynamic deployment platforms (e.g., serverless), you may need to whitelist a range of IPs or use a NAT gateway with a static IP.

SSL/TLS for Database Connections

Always enable SSL/TLS for database connections in production:

# PostgreSQL
postgresql://user:pass@host:5432/db?sslmode=require

# MySQL
mysql://user:pass@host:3306/db?ssl=true

# MongoDB
mongodb+srv://user:[email protected]/db  # (SSL enabled by default)

SSL Modes (PostgreSQL):

disable - No SSL (never use in production)
require - SSL required, no certificate verification
verify-ca - SSL + verify certificate authority
verify-full - SSL + verify hostname matches certificate

Use at minimum sslmode=require for production databases. For highly sensitive data, use verify-full with proper certificate management.

Authentication Security

Supabase Configuration

Quail uses Supabase for user authentication and authorization. Secure your Supabase project:

Enable RLS (Row Level Security): Ensure all Supabase tables have RLS policies

Configure Auth Settings:

Set appropriate JWT expiration (default: 1 hour)
Enable email confirmation for new users
Configure password requirements (min length, complexity)

Secure API Keys:

Use anon key for client-side (limited access)
Protect service_role key (full database access)
Rotate keys if compromised

URL Configuration:

Add production URL to Supabase “Site URL”
Add auth callback URLs: https://your-app.com/auth/callback

Session Management

The middleware (~/workspace/source/middleware.ts) handles session validation on every request:

JWT tokens validated on each request
Expired sessions automatically redirect to login
Cookies secured with httpOnly, secure, and sameSite flags

Query Security

Read-Only Enforcement

Quail enforces read-only access by design:

Database users should only have SELECT privileges
Query execution happens on Azure Functions with tier-based rate limiting (~/workspace/source/utils/actions/runSQL.ts)
No write operations (INSERT, UPDATE, DELETE, DROP) should be permitted

Defense in Depth:

Database-level: Use read-only user permissions
Network-level: Firewall rules allowing only SELECT queries
Application-level: UI doesn’t provide write interfaces
Monitoring: Log all queries for audit trails

SQL Injection Prevention

While Quail uses AI to generate SQL queries, security measures are in place:

Parameterized queries where possible
Input validation on connection strings
Query execution through trusted Azure Function endpoint
User database credentials never exposed to other users

For self-hosted deployments: If you modify query execution logic, always use parameterized queries and never concatenate user input directly into SQL strings.

Monitoring & Auditing

Security Monitoring

Implement monitoring for security events:

// Log authentication events
- Login attempts (successful/failed)
- Password reset requests
- Session expirations

// Log database access
- Connection attempts
- Query execution (without exposing data)
- Failed authentication to databases

// Monitor for anomalies
- Unusual query patterns
- High volume of requests from single user
- Failed login spikes

Audit Logs

Enable audit logging in your infrastructure:

Application logs: Next.js application logs (stdout/stderr)
Database logs: Query logs in your database (enabled separately)
Supabase logs: Auth events and API requests
MongoDB logs: User metadata changes

Compliance Considerations

For regulated industries (HIPAA, SOC 2, GDPR):

Data encryption: At rest (database) and in transit (SSL/TLS)
Access controls: Role-based access, read-only database users
Audit trails: Log all data access and authentication events
Data residency: Choose database regions that comply with requirements
User consent: Implement consent flows for data collection
Data retention: Configure automatic data deletion policies

Incident Response

If Encryption Key is Compromised

Immediate: Rotate the encryption key (see Key Rotation)

Notify users: All saved connections must be re-entered

Rotate database passwords: Change passwords for all database users

Audit access: Review logs for unauthorized database access

Post-mortem: Document incident and improve key security procedures

If Database Credentials are Leaked

Revoke access: Immediately disable the compromised database user

Create new user: Generate new read-only user with different password

Update connections: Users must update to new connection string

Review logs: Check database logs for unauthorized queries

Assess impact: Determine if sensitive data was accessed

If Supabase Keys are Compromised

Rotate keys: Generate new API keys in Supabase dashboard

Update environment: Deploy with new keys

Invalidate sessions: Force all users to re-authenticate

Review activity: Check Supabase logs for suspicious activity

Security Checklist

Before deploying to production:

☐ Generate strong encryption key with openssl rand -base64 32

☐ Store all secrets in secrets manager (not plain text environment variables)

☐ Create read-only database users (never use admin credentials)

☐ Enable SSL/TLS for all database connections (sslmode=require)

☐ Whitelist only necessary IPs in database firewall rules

☐ Configure Supabase auth URLs and enable RLS

☐ Verify .env.local and other secret files are in .gitignore

☐ Test authentication flow in production environment

☐ Enable monitoring and audit logging

☐ Document incident response procedures

☐ Schedule regular security reviews and key rotation

Security is an ongoing process. Regularly review access logs, rotate credentials, apply security updates, and stay informed about vulnerabilities in dependencies.

Self-Hosting

Operations

​Security Overview

Encryption

Read-Only Access

Authentication

Network Security

​Encryption Keys

​Generating a Secure Encryption Key

​Key Management Best Practices

​Encryption Implementation

​Key Rotation

​Database Credential Security

​Connection String Storage

​Creating Read-Only Database Users

​PostgreSQL Read-Only User

​MySQL Read-Only User

​SQL Server Read-Only User

​Password Requirements

​Special Characters in Connection Strings

​Environment Variable Security

​Required Environment Variables

​Securing Environment Variables

​What NOT to Do

​Network Security

​IP Whitelisting

​SSL/TLS for Database Connections

​Authentication Security

​Supabase Configuration

​Session Management

​Query Security

​Read-Only Enforcement

​SQL Injection Prevention

​Monitoring & Auditing

​Security Monitoring

​Audit Logs

​Compliance Considerations

​Incident Response

​If Encryption Key is Compromised

​If Database Credentials are Leaked

​If Supabase Keys are Compromised

​Security Checklist

Build docs developers (and LLMs) love

Security Overview

Encryption Keys

Generating a Secure Encryption Key

Key Management Best Practices

Encryption Implementation

Key Rotation

Database Credential Security

Connection String Storage

Creating Read-Only Database Users

PostgreSQL Read-Only User

MySQL Read-Only User

SQL Server Read-Only User

Password Requirements

Special Characters in Connection Strings

Environment Variable Security

Required Environment Variables

Securing Environment Variables

What NOT to Do

Network Security

IP Whitelisting

SSL/TLS for Database Connections

Authentication Security

Supabase Configuration

Session Management

Query Security

Read-Only Enforcement

SQL Injection Prevention

Monitoring & Auditing

Security Monitoring

Audit Logs

Compliance Considerations

Incident Response

If Encryption Key is Compromised

If Database Credentials are Leaked

If Supabase Keys are Compromised

Security Checklist