PostgreTool 1.1.0

PostgreTool

AI-Powered Database Development Tool for PostgreSQL/Supabase

A .NET 9.0 tool that provides a safe SQL execution API designed for AI assistants (Claude, ChatGPT, etc.) to help you build and modify your PostgreSQL database schema in development environments.

⚠️ FOR DEVELOPMENT/SANDBOX ENVIRONMENTS ONLY ⚠️ This tool allows full DDL operations including DROP, ALTER, and TRUNCATE. Never use in production.

Why Use This Tool?

Let AI assistants help you build your database:

✅ Full Schema Control - AI can CREATE, DROP, ALTER tables and run any SQL ✅ Safe Parameterization - Queries use @p1, @p2 parameters to prevent injection ✅ Complete Audit Trail - Every SQL command is logged ✅ Works with Claude Code - Perfect for AI-assisted development workflows ✅ Supabase Compatible - Works great with Supabase development projects

Quick Start

1. Install from NuGet

# Install the tool globally
dotnet tool install --global PostgreTool

2. Configure Your Database

Set your database connection string (use a development/sandbox database only):

export DATABASE_URL="Host=db.yourproject.supabase.co;Database=postgres;Username=postgres;Password=your-password;Port=5432"

3. Start the Tool

# Start the service
postgresql-tool

The service runs on http://localhost:5000 by default.

4. Integrate with Claude Code

Add this to your project's claude.md file (or create it):

## Database Access

You have access to a PostgreSQL database development tool running at http://localhost:5000

### Execute SQL Queries

Use the `/tool/sql_query` endpoint for SELECT queries:

```json
POST http://localhost:5000/tool/sql_query
{
  "sql": "SELECT * FROM users WHERE id = @p1 LIMIT 10",
  "parameters": [123]
}
```

### Execute Schema Changes

Use the `/tool/sql_execute` endpoint for DDL and write operations:

```json
POST http://localhost:5000/tool/sql_execute
{
  "sql": "CREATE TABLE products (id serial PRIMARY KEY, name text NOT NULL, price numeric(10,2))",
  "parameters": []
}
```

### Allowed Operations

**Full DDL Access:**
- CREATE TABLE, DROP TABLE, ALTER TABLE
- CREATE INDEX, DROP INDEX
- TRUNCATE, VACUUM, ANALYZE
- GRANT, REVOKE

**Data Operations:**
- SELECT, INSERT, UPDATE, DELETE
- WITH (CTEs), EXPLAIN

**Safety Features:**
- All queries use parameterized statements
- Single statement per request (no batching)
- Complete audit logging
- Automatic LIMIT injection for SELECT queries without LIMIT

### Example: Create a New Table

When the user asks you to create a new table, use sql_execute:

```json
POST http://localhost:5000/tool/sql_execute
{
  "sql": "CREATE TABLE blog_posts (id serial PRIMARY KEY, title text NOT NULL, content text, author_id integer REFERENCES users(id), created_at timestamp DEFAULT now())",
  "parameters": []
}
```

### Example: Modify Schema

```json
POST http://localhost:5000/tool/sql_execute
{
  "sql": "ALTER TABLE users ADD COLUMN email_verified boolean DEFAULT false",
  "parameters": []
}
```

### Example: Query Data

```json
POST http://localhost:5000/tool/sql_query
{
  "sql": "SELECT id, title, created_at FROM blog_posts WHERE author_id = @p1 ORDER BY created_at DESC LIMIT 20",
  "parameters": [5]
}
```

That's it! Claude can now help you build and modify your database schema.

---

Configuration Options

All configuration via environment variables:

Variable	Description	Default	Required
DATABASE_URL	PostgreSQL connection string	-	✅
STATEMENT_TIMEOUT_SECONDS	PostgreSQL statement timeout	20	❌
ROW_LIMIT_FALLBACK	Auto-inject LIMIT for queries without one	5000	❌
ALLOWED_SCHEMAS	Comma-separated list of allowed schemas	public	❌
MAX_RESULT_BYTES	Maximum JSON response size	2000000 (2MB)	❌
MAX_EXECUTION_SECONDS	Maximum HTTP request duration	30	❌
COMMAND_LOG_PATH	Path to SQL command audit log	./logs/sql-commands.log	❌

Example Configuration

export DATABASE_URL="Host=localhost;Database=myapp_dev;Username=dev_user;Password=dev_pass"
export ALLOWED_SCHEMAS="public,app_private"
export ROW_LIMIT_FALLBACK=1000
export STATEMENT_TIMEOUT_SECONDS=10

API Endpoints

POST /tool/sql_query - Read-Only Queries

Execute SELECT, WITH, EXPLAIN queries.

Request:

{
  "sql": "SELECT id, name FROM users WHERE active = @p1 LIMIT 10",
  "parameters": [true]
}

Response:

{
  "rowCount": 5,
  "rows": [
    { "id": 1, "name": "Alice" },
    { "id": 2, "name": "Bob" }
  ],
  "truncated": false,
  "notice": "LIMIT 5000 applied"
}

POST /tool/sql_execute - Write/DDL Operations

Execute INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, TRUNCATE, etc.

Request:

{
  "sql": "CREATE TABLE logs (id serial PRIMARY KEY, message text, created_at timestamp DEFAULT now())",
  "parameters": []
}

Response:

{
  "rowCount": 0
}

GET /healthz - Health Check

Response:

{
  "status": "ok"
}

What Operations Are Allowed?

✅ Read Operations (sql_query endpoint)

• SELECT, WITH (CTEs), EXPLAIN
• SHOW, DESCRIBE

✅ Write/DDL Operations (sql_execute endpoint)

• Tables: CREATE TABLE, DROP TABLE, ALTER TABLE, TRUNCATE
• Indexes: CREATE INDEX, DROP INDEX, REINDEX
• Data: INSERT, UPDATE, DELETE
• Maintenance: VACUUM, ANALYZE, CLUSTER
• Permissions: GRANT, REVOKE
• Materialized Views: REFRESH MATERIALIZED VIEW
• Bulk Operations: COPY
• Comments: COMMENT ON

❌ Restrictions

• ❌ Multiple statements in one request (prevents SQL injection)
• ❌ String concatenation-based queries (use parameters)

Audit Logging

All SQL commands are logged to:

1. Console - Structured JSON logs
2. Application Log - ./logs/application-{date}.log
3. Command Log - ./logs/sql-commands.log (newline-delimited JSON)

Command Log Format

{
  "timestampUtc": "2025-01-15T10:30:00Z",
  "requestId": "0HMVD8Q3N4A5C:00000001",
  "endpoint": "/tool/sql_execute",
  "statementType": "CREATE",
  "schemasReferenced": ["public"],
  "executionDurationMs": 45,
  "rowCount": 0,
  "sqlText": "CREATE TABLE products (id serial PRIMARY KEY, name text)",
  "clientIp": "127.0.0.1"
}

Development Workflow Examples

Example 1: AI Creates a Blog Schema

User: "Create a blog schema with posts, comments, and tags"

Claude uses the tool:

# Step 1: Create posts table
POST /tool/sql_execute
{
  "sql": "CREATE TABLE posts (id serial PRIMARY KEY, title text NOT NULL, content text, author_id integer, published_at timestamp)",
  "parameters": []
}

# Step 2: Create comments table
POST /tool/sql_execute
{
  "sql": "CREATE TABLE comments (id serial PRIMARY KEY, post_id integer REFERENCES posts(id) ON DELETE CASCADE, content text NOT NULL, created_at timestamp DEFAULT now())",
  "parameters": []
}

# Step 3: Create tags and junction table
POST /tool/sql_execute
{
  "sql": "CREATE TABLE tags (id serial PRIMARY KEY, name text UNIQUE NOT NULL)",
  "parameters": []
}

POST /tool/sql_execute
{
  "sql": "CREATE TABLE post_tags (post_id integer REFERENCES posts(id), tag_id integer REFERENCES tags(id), PRIMARY KEY (post_id, tag_id))",
  "parameters": []
}

Example 2: AI Modifies Schema

User: "Add an email column to users and make it required"

# Step 1: Add column
POST /tool/sql_execute
{
  "sql": "ALTER TABLE users ADD COLUMN email text",
  "parameters": []
}

# Step 2: Add constraint
POST /tool/sql_execute
{
  "sql": "ALTER TABLE users ALTER COLUMN email SET NOT NULL",
  "parameters": []
}

# Step 3: Add unique constraint
POST /tool/sql_execute
{
  "sql": "CREATE UNIQUE INDEX idx_users_email ON users(email)",
  "parameters": []
}

Updating the Tool

# Update to latest version
dotnet tool update --global PostgreTool

# Uninstall if needed
dotnet tool uninstall --global PostgreTool

Security Considerations

⚠️ Development Only

This tool is designed for development and sandbox environments where you want AI to have full schema manipulation capabilities.

Recommended Safety Practices

1. Never use production databases - Always use a development/staging database
2. Use restricted database users - Don't use superuser/admin accounts
3. Enable connection encryption - Use SSL/TLS for database connections
4. Review audit logs - Check ./logs/sql-commands.log to see what AI executed
5. Use version control - Keep schema migrations in git
6. Backup before major changes - Dump your schema before letting AI make big changes

Database User Permissions

Even in development, you can restrict certain operations at the database level:

-- Create a restricted development user
CREATE ROLE dev_user WITH LOGIN PASSWORD 'dev_password';

-- Grant basic permissions
GRANT CONNECT ON DATABASE dev_db TO dev_user;
GRANT USAGE ON SCHEMA public TO dev_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dev_user;

-- Prevent dropping the database itself
REVOKE DROP ON DATABASE dev_db FROM dev_user;

Advanced Usage

Docker Deployment

# Build image
docker build -t postgresql-tool:latest .

# Run container
docker run -d \
  --name postgresql-tool \
  -p 5000:8080 \
  -e DATABASE_URL="your-connection-string" \
  -e ALLOWED_SCHEMAS="public" \
  postgresql-tool:latest

Kubernetes Deployment

kubectl apply -f k8s/

Custom Port

# Set custom port via environment
export ASPNETCORE_URLS="http://localhost:8080"
postgresql-tool

Troubleshooting

Connection Issues

Problem: Can't connect to database

Solution:

• Verify DATABASE_URL is correct
• Check database firewall/network rules
• Ensure database user has LOGIN privilege
• Test connection with psql: psql "your-connection-string"

Schema Validation Errors

Problem: "Schema not in allowlist"

Solution:

• Add schema to ALLOWED_SCHEMAS: export ALLOWED_SCHEMAS="public,app_private,myschema"
• Or qualify table names: public.users instead of users

Query Timeouts

Problem: Queries timing out

Solution:

• Increase timeout: export STATEMENT_TIMEOUT_SECONDS=60
• Optimize slow queries
• Add indexes

Tool Won't Start

Problem: postgresql-tool command not found

Solution:

# Verify installation
dotnet tool list --global

# Reinstall if needed
dotnet tool uninstall --global PostgreTool
dotnet tool install --global PostgreTool

Project Structure

PostgreTool/
├── Configuration/          # Environment variable configuration
├── Data/                  # PostgreSQL connection and execution
├── Guards/                # SQL validation
│   ├── SqlClassifier.cs   # Statement type validation
│   └── SchemaAllowlist.cs # Schema allowlist validation
├── Models/                # Request/response models
├── Observability/         # Audit logging
├── Serialization/         # JSON serialization
└── Tests/                 # Unit tests

Contributing

Contributions welcome! This tool is designed to make AI-assisted database development safe and productive.

Running Tests

dotnet test

Building from Source

git clone https://github.com/yourusername/PostgreTool.git
cd PostgreTool
dotnet restore
dotnet build
dotnet run

License

Apache License 2.0 - See LICENSE for details.

Support

• Issues: GitHub Issues
• Docs: This README and included claude.md
• Logs: Check ./logs/ for detailed execution logs

---

Made for AI-assisted development workflows 🤖 + 🗄️