PostgreSQL

PostgreSQL is a powerful, open-source relational database system known for its reliability, feature robustness, and performance. This connector enables you to interact with PostgreSQL databases through PostgREST, which automatically generates a RESTful API from your database schema.

Prerequisites

To use this connector, you need:

1. A PostgreSQL database (version 9.6 or higher)
2. PostgREST installed and configured
3. A JWT token for authentication

What is PostgREST?

PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. It reads your database schema and creates API routes automatically, making it perfect for use with this MCP connector. PostgREST handles authentication, authorization through PostgreSQL's row-level security, and provides a powerful query interface.

Authentication Types

PostgreSQL supports API Key authentication using JWT tokens:

• API Key (JWT) - Uses JSON Web Tokens for secure, stateless authentication
  • Pros: Stateless, secure, industry standard, supports role-based access
  • Cons: Requires initial PostgREST setup and understanding of JWT
  • Best for: Production environments with proper security requirements

Setting up PostgREST

1. Install PostgREST

Choose one of the following installation methods:

Docker (Recommended for testing):

docker pull postgrest/postgrest

Binary Download:

Download from https://github.com/PostgREST/postgrest/releases

Package Managers:

# macOS
brew install postgrest

# Ubuntu/Debian
apt-get install postgrest

# Arch Linux
pacman -S postgrest

2. Configure PostgREST

Create a postgrest.conf file in your project directory:

# Database connection
db-uri = "postgres://username:password@localhost:5432/database_name"

# Schema to expose via API (typically 'public' or a custom schema)
db-schemas = "public"

# Anonymous role for unauthenticated requests
db-anon-role = "web_anon"

# JWT secret for token validation (use a strong secret in production)
jwt-secret = "your-secret-key-minimum-32-characters-long"

# Server configuration
server-host = "*"
server-port = 3000

3. Set up Database Roles

Create the necessary database roles in PostgreSQL:

-- Create anonymous role for API access
CREATE ROLE web_anon NOLOGIN;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO web_anon;

-- Grant select on all tables (adjust permissions as needed)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO web_anon;

-- For write operations, also grant INSERT, UPDATE, DELETE
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO web_anon;

-- Grant usage on sequences (for auto-increment columns)
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO web_anon;

-- Optional: Create helper functions for schema introspection
-- These functions enable the List Tables, Describe Table, etc. tools

CREATE OR REPLACE FUNCTION postgrest_get_tables(schema_name text DEFAULT 'public', pattern text DEFAULT NULL)
RETURNS TABLE(table_name text, table_type text) AS $$
BEGIN
  RETURN QUERY
  SELECT t.table_name::text, t.table_type::text
  FROM information_schema.tables t
  WHERE t.table_schema = schema_name
    AND (pattern IS NULL OR t.table_name ILIKE '%' || pattern || '%')
  ORDER BY t.table_name;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION postgrest_describe_table(table_name text)
RETURNS TABLE(
  column_name text,
  data_type text,
  is_nullable text,
  column_default text
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    c.column_name::text,
    c.data_type::text,
    c.is_nullable::text,
    c.column_default::text
  FROM information_schema.columns c
  WHERE c.table_name = postgrest_describe_table.table_name
    AND c.table_schema = 'public'
  ORDER BY c.ordinal_position;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execute permission on helper functions
GRANT EXECUTE ON FUNCTION postgrest_get_tables TO web_anon;
GRANT EXECUTE ON FUNCTION postgrest_describe_table TO web_anon;

4. Generate JWT Token

You need a JWT token with a role claim to authenticate with PostgREST. Here's how to generate one:

Using jwt.io (Quick Testing):

1. Go to https://jwt.io
2. In the "PAYLOAD" section, use:

   {
     "role": "web_anon",
     "exp": 1735689600
   }

3. In the "VERIFY SIGNATURE" section, paste your JWT secret
4. Copy the encoded token from the left panel

Using Node.js:

const jwt = require('jsonwebtoken');

const token = jwt.sign(
  { role: 'web_anon' },
  'your-secret-key-minimum-32-characters-long',
  { expiresIn: '7d' }
);

console.log(token);

Using Python:

import jwt
import datetime

token = jwt.encode(
    {
        'role': 'web_anon',
        'exp': datetime.datetime.utcnow() + datetime.timedelta(days=7)
    },
    'your-secret-key-minimum-32-characters-long',
    algorithm='HS256'
)

print(token)

5. Start PostgREST

Using the binary:

postgrest postgrest.conf

Using Docker:

docker run -d \
  -p 3000:3000 \
  -v $(pwd)/postgrest.conf:/etc/postgrest.conf \
  postgrest/postgrest

PostgREST will start on http://localhost:3000 by default.

Verify it's working:

curl http://localhost:3000/
# Should return a list of available tables

Configure in Webrix

1. Go to Integrations → Add Connector → PostgreSQL
2. Enter your PostgREST URL:
   • For local development: http://localhost:3000
   • For production: https://api.yourdomain.com (use HTTPS!)
3. Select API Key authentication type
4. Paste your JWT token in the Token field
5. Click Connect

Common Use Cases

Querying Data

Ask natural language questions and the AI will translate them to PostgREST queries:

• "Show me all users who registered last month"
• "Get the top 10 products by sales"
• "Find customers in New York with orders over $1000"
• "What's the average order value by customer segment?"

Data Modification

Make changes through conversation:

• "Add a new product: name 'Widget X', price $29.99, category 'tools'"
• "Update user [email protected] to set status as premium"
• "Delete all sessions that expired before yesterday"
• "Insert these 5 new customer records: [data]"

Schema Discovery

Understand your database structure:

• "What tables are in the database?"
• "Describe the users table"
• "Show me a sample of the orders table"
• "What columns does the products table have?"

Working with Functions

Call stored procedures and custom functions:

• "List all available database functions"
• "Call the calculate_discount function with price 100"
• "Execute search_products function to find items containing 'laptop'"

Advanced Queries

Leverage PostgreSQL's power:

• "Search for 'artificial intelligence' in all blog post contents" (full-text search)
• "Get posts with their author information in one query" (embedding)
• "Show me distinct categories from the products table"
• "Calculate the sum of all order totals grouped by month"

Troubleshooting

Connection Refused

Cause: PostgREST is not running or URL is incorrect

Solution:

1. Verify PostgREST is running: curl http://localhost:3000/
2. Check the PostgREST URL in your connector settings
3. Ensure firewall/network allows access to the PostgREST port
4. If using Docker, verify port mapping is correct

401 Unauthorized

Cause: Invalid or expired JWT token

Solution:

1. Verify JWT secret matches between token and PostgREST config
2. Check token expiration time (exp claim)
3. Ensure token includes the correct role claim (should match db-anon-role)
4. Generate a new token using the steps above
5. Verify the token is properly formatted (should start with eyJ)

404 Table Not Found

Cause: Table not in exposed schema or insufficient permissions

Solution:

1. Check db-schemas in PostgREST config includes the schema containing your table
2. Verify the database role has SELECT permission on the table:

   GRANT SELECT ON table_name TO web_anon;

3. Restart PostgREST to refresh schema cache
4. Check table name spelling (PostgreSQL is case-sensitive)

Permission Denied

Cause: Database role lacks necessary permissions

Solution:

Grant appropriate permissions in PostgreSQL:

-- For reading data
GRANT SELECT ON table_name TO web_anon;

-- For inserting data
GRANT INSERT ON table_name TO web_anon;

-- For updating data
GRANT UPDATE ON table_name TO web_anon;

-- For deleting data
GRANT DELETE ON table_name TO web_anon;

-- For all operations
GRANT ALL PRIVILEGES ON table_name TO web_anon;

-- For sequences (auto-increment columns)
GRANT USAGE ON SEQUENCE table_name_id_seq TO web_anon;

After granting permissions, restart PostgREST.

Schema Cache Not Updating

Cause: PostgREST caches schema information

Solution:

1. Restart PostgREST to reload the schema cache
2. Or send a SIGUSR1 signal to reload without restarting:

   killall -SIGUSR1 postgrest

3. Or use the admin API (if enabled) to reload:

   curl -X POST http://localhost:3001/

CORS Errors (Browser Applications)

Cause: PostgREST doesn't handle CORS by default

Solution:

Add to your postgrest.conf:

# Allow all origins (development only!)
server-cors-allowed-origins = "*"

# Or specify allowed origins
server-cors-allowed-origins = "https://yourdomain.com"

Or use a reverse proxy (nginx, Caddy) to handle CORS headers.

Advanced Configuration

Row Level Security (RLS)

Enable fine-grained access control using PostgreSQL's Row Level Security:

-- Enable RLS on a table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create a policy (users can only see their own data)
CREATE POLICY user_policy ON users
  FOR ALL
  TO web_anon
  USING (id = current_setting('request.jwt.claims', true)::json->>'user_id');

-- Grant permission
GRANT ALL ON users TO web_anon;

Custom Functions

Create PostgreSQL functions for complex operations:

-- Simple calculation function
CREATE OR REPLACE FUNCTION calculate_discount(price numeric, discount_pct numeric)
RETURNS numeric AS $$
BEGIN
  RETURN price * (1 - discount_pct / 100);
END;
$$ LANGUAGE plpgsql;

-- Search function
CREATE OR REPLACE FUNCTION search_products(query text)
RETURNS TABLE(id int, name text, price numeric) AS $$
BEGIN
  RETURN QUERY
  SELECT p.id, p.name, p.price
  FROM products p
  WHERE p.name ILIKE '%' || query || '%'
     OR p.description ILIKE '%' || query || '%';
END;
$$ LANGUAGE plpgsql;

-- Grant execute permission
GRANT EXECUTE ON FUNCTION calculate_discount TO web_anon;
GRANT EXECUTE ON FUNCTION search_products TO web_anon;

Call via MCP: "Calculate discount for price 100 with 20% off" or "Search for products containing 'laptop'"

Views for Simplified Access

Create views to simplify complex queries and hide implementation details:

-- View for active customers with their order count
CREATE VIEW active_customers AS
SELECT 
  c.id,
  c.name,
  c.email,
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.id, c.name, c.email;

-- Grant permission
GRANT SELECT ON active_customers TO web_anon;

Full-Text Search Setup

Create indexes for efficient full-text search:

-- Add a tsvector column
ALTER TABLE articles ADD COLUMN content_search tsvector;

-- Create index
CREATE INDEX articles_content_search_idx ON articles USING GIN(content_search);

-- Update the tsvector column (one-time)
UPDATE articles SET content_search = to_tsvector('english', title || ' ' || content);

-- Auto-update trigger
CREATE TRIGGER articles_content_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(content_search, 'pg_catalog.english', title, content);

Performance Optimization

1. Create Indexes on frequently queried columns:

   CREATE INDEX idx_users_email ON users(email);
   CREATE INDEX idx_orders_customer_id ON orders(customer_id);
   CREATE INDEX idx_products_category ON products(category);

2. Use Connection Pooling with pgBouncer for high-traffic applications

3. Optimize Queries by selecting only needed columns instead of *

4. Set Statement Timeout in PostgREST config:

   db-pool-timeout = 10
   db-pre-request = "SET statement_timeout = 5000"

Security Best Practices

1. Use HTTPS in production - never send JWT tokens over HTTP
2. Rotate JWT secrets regularly and use strong, random secrets (32+ characters)
3. Set token expiration - don't create tokens that never expire
4. Use Row Level Security to enforce data access policies at the database level
5. Grant minimal permissions - only give roles the permissions they need
6. Validate input using PostgreSQL constraints and triggers
7. Use prepared statements - PostgREST does this automatically
8. Monitor access logs to detect unusual activity
9. Keep PostgREST updated to get security patches
10. Never expose your admin role - use separate roles for API access

Additional Resources

• PostgREST Documentation
• PostgreSQL Documentation
• JWT.io - Token debugger and information
• PostgREST API Reference
• PostgreSQL Row Level Security