S3 Wrapper
Overview
S3 Wrapper
AWS S3 is Amazon's object storage service, storing over 500 trillion objects and handling 200 million requests per second. It's the foundation for data lakes, analytics pipelines, AI training datasets, and application storage at any scale.
The S3 Wrapper lets you query files stored in S3 directly from Postgres as foreign tables. Read CSV, JSON Lines, and Parquet files with plain SQL and join them against your application data. Works with any S3-compatible provider, including Supabase Storage, Wasabi, Cloudflare R2, and Backblaze B2. It is read-only and works with Supabase Vault for secure credential management.
Supported File Formats
| Format | Notes |
|---|---|
| CSV | With or without header line. All columns must be defined in the foreign table as text. |
| JSON Lines | All columns must be defined in the foreign table as text. |
| Parquet | Native type mapping supported. Compressed files are loaded fully into memory, so keep file sizes small. |
Supported Compression Algorithms
gzip, bzip2, xz, zlib
Required S3 Permissions
s3:GetObjects3:ListBucket
Supported Data Types for Parquet
| Postgres Type | Parquet Type |
|---|---|
| boolean | BOOLEAN |
| integer | INT32 |
| bigint | INT64 |
| real | FLOAT |
| double precision | DOUBLE |
| numeric | DECIMAL |
| text | BYTE_ARRAY / UTF8 |
| date | DATE |
| timestamp | TIMESTAMP_MILLIS / TIMESTAMP_MICROS |
Preparation
Before you get started, make sure the wrappers extension is installed on your database:
_10create extension if not exists wrappers with schema extensions;
and then create the foreign data wrapper:
_10create foreign data wrapper s3_wrapper_10 handler s3_fdw_handler_10 validator s3_fdw_validator;
Secure your credentials (optional)
By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.
Store your AWS credentials as two separate secrets and use the returned key IDs in the create server command with a vault_ prefix:
_11select vault.create_secret(_11 '<access key id>',_11 's3_access_key_id',_11 'AWS access key for Wrappers'_11);_11_11select vault.create_secret(_11 '<secret access key>',_11 's3_secret_access_key',_11 'AWS secret access key for Wrappers'_11);
Connecting to S3
We need to provide Postgres with the credentials to connect to S3, and any additional options. We can do this using the create server command:
With Vault:
_10create server s3_server_10 foreign data wrapper s3_wrapper_10 options (_10 vault_access_key_id '<your s3_access_key_id from above>',_10 vault_secret_access_key '<your s3_secret_access_key from above>',_10 aws_region 'us-east-1'_10 );
Without Vault:
_10create server s3_server_10 foreign data wrapper s3_wrapper_10 options (_10 aws_access_key_id 'your_aws_access_key_id',_10 aws_secret_access_key 'your_aws_secret_access_key',_10 aws_region 'us-east-1'_10 );
To connect to an S3-compatible provider, add the endpoint_url option and optionally path_style_url:
_10create server s3_server_10 foreign data wrapper s3_wrapper_10 options (_10 aws_access_key_id 'your_access_key',_10 aws_secret_access_key 'your_secret_key',_10 aws_region 'us-east-1',_10 endpoint_url 'https://your-provider-endpoint',_10 path_style_url 'true' -- required by some providers_10 );
Resources
Details
Third-party integrations and docs are managed by Supabase partners.