Foreign Data Wrappers

Connecting to external systems using Postgres Foreign Data Wrappers.

Foreign Data Wrappers (FDW) are a core feature of Postgres that allow you to access and query data stored in external data sources as if they were native Postgres tables.

Postgres includes several built-in foreign data wrappers, such as postgres_fdw for accessing other PostgreSQL databases, and file_fdw for reading data from files. Supabase extends this feature to query other databases or any other external systems. We do this with our open source Wrappers framework. In these guides we'll refer to them as "Wrappers", Foreign Data Wrappers, or FDWs. They are conceptually the same thing.

Concepts#

Wrappers introduce some new terminology and different workflows.

Remote servers#

A Remote Server is an external database, API, or any system containing data that you want to query from your Postgres database. Examples include:

• An external database, like Postgres or Firebase.
• A remote data warehouse, like ClickHouse, BigQuery, or Snowflake.
• An API, like Stripe or GitHub.

It's possible to connect to multiple remote servers of the same type. For example, you can connect to two different Firebase projects within the same Supabase database.

Foreign tables#

A table in your database which maps to some data inside a Remote Server.

Examples:

• An analytics table which maps to a table inside your data warehouse.
• A subscriptions table which maps to your Stripe subscriptions.
• A collections table which maps to a Firebase collection.

Although a foreign table behaves like any other table, the data is not stored inside your database. The data remains inside the Remote Server.

ETL with Wrappers#

ETL stands for Extract, Transform, Load. It's an established process for moving data from one system to another. For example, it's common to move data from a production database to a data warehouse.

There are many popular ETL tools, such as Fivetran and Airbyte.

Wrappers provide an alternative to these tools. You can use SQL to move data from one table to another:

This approach provides several benefits:

1. Simplicity: the Wrappers API is just SQL, so data engineers don't need to learn new tools and languages.
2. Save on time: avoid setting up additional data pipelines.
3. Save on Data Engineering costs: less infrastructure to be managed.

One disadvantage is that Wrappers are not as feature-rich as ETL tools. They also couple the ETL process to your database.

On-demand ETL with Wrappers#

Supabase extends the ETL concept with real-time data access. Instead of moving gigabytes of data from one system to another before you can query it, you can instead query the data directly from the remote server. This additional option, "Query", extends the ETL process and is called QETL (pronounced "kettle"): Query, Extract, Transform, Load.

This approach has several benefits:

1. On-demand: analytical data is immediately available within your application with no additional infrastructure.
2. Always in sync: since the data is queried directly from the remote server, it's always up-to-date.
3. Integrated: large datasets are available within your application, and can be joined with your operational/transactional data.
4. Save on bandwidth: only extract/load what you need.

Batch ETL with Wrappers#

A common use case for Wrappers is to extract data from a production database and load it into a data warehouse. This can be done within your database using pg_cron. For example, you can schedule a job to run every night to extract data from your production database and load it into your data warehouse.

This process can be taxing on your database if you are moving large amounts of data. Often, it's better to use an external tool for batch ETL, such as Fivetran or Airbyte.

Security#

Foreign Data Wrappers do not provide Row Level Security, thus it is not advised to expose them via your API. Wrappers should always be stored in a private schema. For example, if you are connecting to your Stripe account, you should create a stripe schema to store all of your foreign tables inside. This schema should not be added to the “Additional Schemas” setting in the API section.

If you want to expose any of the foreign table columns to your public API, you can create a Database Function with security definer in the public schema, and then you can interact with your foreign table through API. For better access control, the function should have appropriate filters on the foreign table to apply security rules based on your business needs.

As an example, go to SQL Editor and then follow below steps,

1. Create a Stripe Products foreign table:

   
   

   _15

   create foreign table stripe.stripe_products (

   _15

   id text,

   _15

   name text,

   _15

   active bool,

   _15

   default_price text,

   _15

   description text,

   _15

   created timestamp,

   _15

   updated timestamp,

   _15

   attrs jsonb

   _15

   )

   _15

   server stripe_fdw_server

   _15

   options (

   _15

   object 'products',

   _15

   rowid_column 'id'

   _15

   );

   
   

2. Create a security definer function that queries the foreign table and filters on the name prefix parameter:

   
   

   _26

   create function public.get_stripe_products(name_prefix text)

   _26

   returns table (

   _26

   id text,

   _26

   name text,

   _26

   active boolean,

   _26

   default_price text,

   _26

   description text

   _26

   )

   _26

   language plpgsql

   _26

   security definer set search_path = ''

   _26

   as $$

   _26

   begin

   _26

   return query

   _26

   select

   _26

   t.id,

   _26

   t.name,

   _26

   t.active,

   _26

   t.default_price,

   _26

   t.description

   _26

   from

   _26

   stripe.stripe_products t

   _26

   where

   _26

   t.name like name_prefix || '%'

   _26

   ;

   _26

   end;

   _26

   $$;

   
   

3. Restrict the function execution to a specific role only, for example, the authenticated users:

   By default, the function created can be executed by any roles like anon, that means the foreign table is public accessible. Always limit the function execution permission to appropriate roles.

   
   

   _10

   -- revoke public execute permission

   _10

   revoke execute on function public.get_stripe_products from public;

   _10

   revoke execute on function public.get_stripe_products from anon;

   _10

   _10

   -- grant execute permission to a specific role only

   _10

   grant execute on function public.get_stripe_products to authenticated;

   
   

Once the preceding steps are finished, the function can be invoked from Supabase client to query the foreign table:

Resources#

• Official supabase/wrappers documentation