Foreign Data Wrappers (who? - need review)

CrateDB offers the possibility to access PostgreSQL database tables on remote database servers as if they would be stored within CrateDB.

Prerequisites

Before configuring a Foreign Data Wrapper (FDW), you should have CrateDB and Postgres instances up and running.

To have access to FDW in CrateDB, make sure you have a cluster in version 5.7 or above.

---

What to Watch Out For

• Read‑only – only SELECT (DQL) is supported on foreign tables.

• Postgres support - currently CrateDB only supports FDW for Postgres.

• Filter push‑down is best‑effort – use EXPLAIN to see what is pushed.

• Security guard‑rail – by default only the crate user may connect to localhost targets. Override via fdw.allow_local = true if you must.

---

Set up

Ensure outbound firewall rules allow CrateDB → remote‑DB traffic before proceeding with the following steps.

Create a server in CrateDB

CREATE SERVER my_postgresql FOREIGN DATA WRAPPER jdbc
OPTIONS (url 'jdbc:postgresql://example.com:5432/');

By default only the crate user can use server definitions that connect to localhost. Other users are not allowed to connect to instances running on the same host as CrateDB. This is a security measure to prevent users from by-passing Host-Based Authentication (HBA)restrictions. See fdw.allow_local.

Create a User Mapping

This step is a DDL statement that maps a CrateDB user to another user on a foreign server. If not set, your session details will be used instead.

CREATE USER MAPPING
FOR mylocaluser
SERVER my_postgresql
OPTIONS ("user" 'myremoteuser', password '*****');

Create Foreign Tables

CREATE FOREIGN TABLE doc.remote_readings (
  ts      timestamp,
  device  text,
  value   double
) SERVER my_postgresql
  OPTIONS (
    schema_name 'public',  -- remote schema
    table_name  'readings'
  );

Remember a foreign table is a view onto data in a foreign system.

---

Usage

Query & debug

Foreign data wrappers allow you to make data in foreign systems available as tables within CrateDB. You can then query these foreign tables like regular user tables.

SELECT ts, value
FROM   remote_readings
WHERE  device = 'sensor‑42';

Query clauses like GROUP BY, HAVING, LIMIT or ORDER BY are executed within CrateDB, not within the foreign system. WHERE clauses can in some circumstances be pushed to the foreign system, but that depends on the concrete foreign data wrapper implementation. You can check if this is the case by using the EXPLAIN statement.

For example, in the following explain output there is a dedicated Filter node, indicating that the filter is executed within CrateDB:

explain select ts, value from remote_readings where device = 'sensor‑42';
+--------------------------------------------------------------------------+
| QUERY PLAN                                                               |
+--------------------------------------------------------------------------+
| Filter[(device = 'sensor‑42')] (rows=0)                                  |
|   └ ForeignCollect[doc.remote_readings | [device] | true] (rows=unknown) |
+--------------------------------------------------------------------------+

Drop Server

DROP SERVER my_postgresql;

You can drop the server once it is no longer used. The clauses available are:

• IF EXISTS: the statement won’t raise an error if any servers listed don’t exist.

• RESTRICT: raiseS an error if any foreign table or user mappings for the given servers exist. This is the default.

• CASCADE: causes DROP SERVER to also delete all foreign tables and mapped users using the given servers.