Overview

This code block demonstrates how to create a foreign table over Amazon S3 or an S3-compatible object store.

CREATE FOREIGN DATA WRAPPER <wrapper_name>
HANDLER s3_fdw_handler
VALIDATOR s3_fdw_validator;

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER <wrapper_name>
OPTIONS (
    region '<region>',
    allow_anonymous 'true'
);

-- Replace the dummy schema with the actual schema
CREATE FOREIGN TABLE <table_name> ("x" INT)
SERVER <server_name>
OPTIONS (path '<path>', extension '<extension>');
wrapper_name
required

Foreign data wrapper name. Can be any string.

server_name
required

Foreign server name. Can be any string.

table_name
required

Foreign table name. Can be any string.

Options for CREATE SERVER

region

AWS region, e.g. us-east-1. Required for Amazon S3, MinIO, Scaleway, and Cloudflare R2.

endpoint

The endpoint for communicating with the S3 instance. Defaults to the region endpoint. For example, can be set to http://localhost:4566 if testing against a Localstack instance, or http://127.0.0.1:9000 for MinIO. Required for all non Amazon S3 object stores.

allow_anonymous

If set to true, will not sign requests. This is useful for connecting to public S3 buckets. Defaults to false.

Options for CREATE FOREIGN TABLE

path
required

Must start with s3:// and point to the location of your file. The path should end in a / if it points to a directory of partitioned Parquet files.

extension
required

One of avro, csv, json, and parquet.

format

For now, only delta is supported for Delta Lake (Iceberg is coming soon). If omitted, no table format is assumed.

S3 Credentials

CREATE USER MAPPING is used to pass in credentials for private buckets.

-- Get the name of the current user
SELECT current_user;
 current_user
--------------
 myuser

-- Run this before CREATE FOREIGN TABLE
CREATE USER MAPPING FOR <current_user>
SERVER <server_name>
OPTIONS (
  access_key_id '<access_key_id>',
  secret_access_key '<secret_access_key>'
);

-- Now, run CREATE FOREIGN TABLE
current_user
required

The name of the Postgres user. If set to public, these credentials will be applied to all users.

server_name
required

Foreign server name.

Options for CREATE USER MAPPING

access_key_id
AWS access key ID
secret_access_key
AWS secret access key
security_token
AWS session token

Compatible Services

In addition to Amazon S3, following S3-compatible object stores are supported:

  • Alibaba Object Storage Service
  • Cloudflare R2
  • MinIO
  • QingStor Object Storage
  • Scaleway Object Storage
  • Tencent Cloud Object Storage
  • Wasabi Object Storage