Supascale
Sign Up

Foreign Data Wrappers for Self-Hosted Supabase: Query External Data

Learn how to set up Foreign Data Wrappers in self-hosted Supabase to query S3, Stripe, other databases, and more directly from PostgreSQL.

Cover Image for Foreign Data Wrappers for Self-Hosted Supabase: Query External Data

One of PostgreSQL's most powerful but underutilized features is Foreign Data Wrappers (FDW)—the ability to query external data sources as if they were native Postgres tables. For teams running self-hosted Supabase, this opens up possibilities that go far beyond what typical application code can achieve.

Instead of writing application logic to fetch data from Stripe, query S3 files, or join across multiple databases, you can do it all with SQL. This guide walks you through setting up Foreign Data Wrappers in your self-hosted Supabase deployment, with practical examples and security considerations.

What Are Foreign Data Wrappers?

Foreign Data Wrappers are a PostgreSQL standard (SQL/MED) that lets you access external data sources through a unified SQL interface. The data stays in the external system—there's no ETL pipeline, no data duplication, and no synchronization headaches.

When you query a foreign table, PostgreSQL sends the request to the external system, retrieves the results, and returns them as if they came from a local table. You can even join foreign tables with local tables in a single query.

Supabase maintains an open-source framework called Wrappers, written in Rust, that extends PostgreSQL's built-in FDW capabilities. This framework provides ready-to-use wrappers for popular services:

  • Stripe - Query customers, invoices, subscriptions directly
  • Firebase - Access Firestore collections
  • ClickHouse - Run analytics queries
  • BigQuery - Query your data warehouse
  • Airtable - Use spreadsheet data in SQL
  • AWS S3 - Query CSV, JSON, and Parquet files
  • Other PostgreSQL databases - Federate across multiple instances

Why FDW Matters for Self-Hosted Deployments

When you deploy Supabase on your own infrastructure, you gain full control over which extensions to enable and how to configure them. This is particularly valuable for FDW because:

No artificial limits: Managed services often restrict which FDWs you can use or impose connection limits. Self-hosted deployments let you connect to whatever external systems your use case requires.

Network flexibility: You can place your Supabase instance in the same network as your other databases, reducing latency for cross-database queries. VPC peering, private networking, and firewall rules are all under your control.

Credential management: You decide how to store and manage connection credentials. Self-hosted deployments can integrate with your existing secrets management infrastructure.

Setting Up the Wrappers Extension

First, enable the Wrappers extension in your self-hosted Supabase. Connect to your database and run:

CREATE EXTENSION IF NOT EXISTS wrappers WITH SCHEMA extensions;

For the built-in postgres_fdw (connecting to other PostgreSQL databases), the extension is already available:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

Example: Connecting to Another PostgreSQL Database

Let's start with a practical example—federating data from a legacy PostgreSQL database into your Supabase instance.

Step 1: Create a Dedicated Schema

Always store foreign tables in a private schema. This is important for security:

CREATE SCHEMA IF NOT EXISTS legacy_db;

Step 2: Create the Foreign Server

CREATE SERVER legacy_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
  host 'your-legacy-db.example.com',
  port '5432',
  dbname 'legacy_database'
);

Step 3: Map User Credentials

CREATE USER MAPPING FOR postgres
SERVER legacy_server
OPTIONS (
  user 'readonly_user',
  password 'your_password'
);

Step 4: Import Foreign Tables

You can import specific tables or entire schemas:

-- Import specific tables
IMPORT FOREIGN SCHEMA public
LIMIT TO (users, orders, products)
FROM SERVER legacy_server
INTO legacy_db;

-- Or import everything
IMPORT FOREIGN SCHEMA public
FROM SERVER legacy_server
INTO legacy_db;

Step 5: Query Across Databases

Now you can join your Supabase tables with the legacy database:

SELECT 
  s.id,
  s.subscription_tier,
  l.total_orders,
  l.lifetime_value
FROM public.subscriptions s
JOIN legacy_db.customers l ON s.customer_email = l.email
WHERE s.created_at > '2026-01-01';

Example: Querying S3 Data

The S3 wrapper lets you query CSV, JSON, and Parquet files stored in AWS S3 (or S3-compatible storage like MinIO, which is common in self-hosted setups).

Enable the S3 Wrapper

CREATE FOREIGN DATA WRAPPER s3_wrapper
HANDLER s3_fdw_handler
VALIDATOR s3_fdw_validator;

Create the Server Connection

CREATE SERVER s3_server
FOREIGN DATA WRAPPER s3_wrapper
OPTIONS (
  aws_access_key_id 'your_access_key',
  aws_secret_access_key 'your_secret_key',
  aws_region 'us-east-1'
);

Define a Foreign Table

CREATE FOREIGN TABLE analytics.events (
  event_id text,
  user_id text,
  event_type text,
  timestamp timestamptz,
  properties jsonb
)
SERVER s3_server
OPTIONS (
  uri 's3://your-bucket/events/*.parquet',
  format 'parquet'
);

Now you can query your S3 data with SQL:

SELECT 
  event_type,
  COUNT(*) as count,
  COUNT(DISTINCT user_id) as unique_users
FROM analytics.events
WHERE timestamp > NOW() - INTERVAL '7 days'
GROUP BY event_type
ORDER BY count DESC;

Example: Querying Stripe Data

For teams that want real-time access to billing data without building sync pipelines:

CREATE SCHEMA stripe;

CREATE SERVER stripe_server
FOREIGN DATA WRAPPER stripe_wrapper
OPTIONS (
  api_key 'sk_live_your_stripe_key'
);

CREATE FOREIGN TABLE stripe.customers (
  id text,
  email text,
  name text,
  created bigint,
  metadata jsonb
)
SERVER stripe_server
OPTIONS (
  object 'customers'
);

Query customers directly:

SELECT 
  c.id,
  c.email,
  u.subscription_status
FROM stripe.customers c
JOIN public.users u ON c.email = u.email
WHERE c.created > EXTRACT(EPOCH FROM '2026-01-01'::timestamp);

Security Considerations

Foreign Data Wrappers require careful security planning. Here's what you need to know:

No Row Level Security

Foreign tables bypass Supabase's Row Level Security (RLS). This is critical—if you expose foreign tables through your API, anyone with access can query the entire remote dataset.

Best practice: Keep foreign tables in private schemas that aren't exposed through PostgREST. Access them through database functions or server-side code only.

-- Revoke direct access
REVOKE ALL ON SCHEMA stripe FROM anon, authenticated;

-- Create a secure function instead
CREATE OR REPLACE FUNCTION get_customer_billing(customer_email text)
RETURNS TABLE (
  stripe_id text,
  subscription_status text
)
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  -- Verify the requesting user has access to this customer
  IF NOT EXISTS (
    SELECT 1 FROM users 
    WHERE email = customer_email 
    AND id = auth.uid()
  ) THEN
    RAISE EXCEPTION 'Access denied';
  END IF;
  
  RETURN QUERY
  SELECT c.id, c.metadata->>'status'
  FROM stripe.customers c
  WHERE c.email = customer_email;
END;
$$ LANGUAGE plpgsql;

Credential Storage

By default, PostgreSQL stores FDW credentials in plain text in pg_catalog.pg_foreign_server. Anyone with database access can view them.

For self-hosted deployments, use Vault integration for secure credential storage. See our guide on secrets management for self-hosted Supabase for details.

-- Store credentials in Vault instead
INSERT INTO vault.secrets (name, secret)
VALUES ('stripe_api_key', 'sk_live_your_key');

-- Reference the vault secret in your FDW options
CREATE SERVER stripe_server
FOREIGN DATA WRAPPER stripe_wrapper
OPTIONS (
  api_key_id 'stripe_api_key'  -- References vault
);

Network Security

When connecting to external services, ensure your network configuration is secure:

  • Use TLS for all connections (sslmode=require for Postgres connections)
  • Restrict outbound traffic to known IP ranges
  • Consider using a proxy or VPN for sensitive connections
  • Rotate credentials regularly

Performance Considerations

Foreign Data Wrappers have different performance characteristics than local tables:

Query pushdown: Modern FDWs push WHERE clauses and other operations to the remote server when possible. Check your query plans with EXPLAIN to verify predicates are being pushed down.

EXPLAIN (ANALYZE, VERBOSE)
SELECT * FROM legacy_db.orders
WHERE created_at > '2026-01-01'
AND status = 'completed';

Connection pooling: Each FDW connection consumes resources. For high-throughput scenarios, consider caching frequently-accessed foreign data in a materialized view:

CREATE MATERIALIZED VIEW billing_summary AS
SELECT 
  customer_id,
  SUM(amount) as total_spent,
  COUNT(*) as transaction_count
FROM stripe.invoices
WHERE status = 'paid'
GROUP BY customer_id;

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY billing_summary;

Batch queries: Instead of making many small queries to foreign tables, batch your operations. A single query returning 1,000 rows is more efficient than 1,000 queries returning 1 row each.

Self-Hosted Advantages with Supascale

Managing FDW configurations across multiple self-hosted Supabase projects can be complex. Supascale simplifies this by providing:

  • Centralized project management: Deploy and configure multiple Supabase instances from a single dashboard
  • Secure credential handling: Integration with your secrets management infrastructure
  • Backup and restore: Your FDW configurations are included in automated backups
  • Environment consistency: Ensure dev, staging, and production all have consistent FDW setups

Common Use Cases

Here are practical scenarios where FDW shines:

Data federation: Query across multiple databases without building a data warehouse. Useful for microservices architectures where data is distributed.

Real-time analytics: Query analytics services like ClickHouse or BigQuery directly from your application queries.

Migration assistance: Keep your legacy database connected while gradually migrating data. No big-bang cutover required.

Billing integration: Access Stripe, Paddle, or other payment provider data directly in your application queries.

File-based data: Query CSV, JSON, or Parquet files from S3 without importing them into your database.

Conclusion

Foreign Data Wrappers transform PostgreSQL from a database into a data hub. For self-hosted Supabase deployments, this means you can query external systems, federate across databases, and integrate with third-party services—all using SQL you already know.

The key is treating FDW as infrastructure: secure it properly, monitor its performance, and use it where it makes architectural sense. Not every external data access should go through FDW, but for scenarios requiring SQL-level integration, it's hard to beat.

Ready to deploy self-hosted Supabase with full control over extensions and integrations? Check out Supascale's features or see our pricing to get started.

Further Reading