S3
Overview
S3 foreign tables have been tested against the following object stores that implement the S3 API: Amazon S3, MinIO, Cloudflare R2, and Google Cloud.
This code block demonstrates how to query Parquet file(s) stored in Google Cloud Storage (GCS).
The file path must start with s3
, r2
, or gs
.
-- Parquet format is assumed
CREATE FOREIGN DATA WRAPPER parquet_wrapper
HANDLER parquet_fdw_handler
VALIDATOR parquet_fdw_validator;
CREATE SERVER parquet_server
FOREIGN DATA WRAPPER parquet_wrapper;
CREATE FOREIGN TABLE parquet_table ()
SERVER parquet_server
OPTIONS (files 's3://<bucket>/<path>/<file>.parquet');
The glob pattern can be used to query a directory of files.
CREATE FOREIGN TABLE parquet_table ()
SERVER parquet_server
OPTIONS (files 's3://<bucket>/<path>/*.parquet');
Providing Credentials
CREATE USER MAPPING
is used to provide S3 credentials. These credentials are tied to a specific Postgres user, which enables
multiple users to query the same foreign table with their own credentials.
CREATE USER MAPPING FOR <current_user>
SERVER <server_name>
OPTIONS (
type 'S3',
key_id '<key_id>',
secret '<secret>',
region 'us-east-1'
);
The name of the Postgres user. If set to public
, these credentials will be
applied to all users. SELECT current_user
can be used to get the name of the
current Postgres user.
Foreign server name.
Credentials Options
The following options can be passed into CREATE USER MAPPING
:
Must be one of S3
, GCS
, or R2
.
The region for which to authenticate (should match the region of the bucket to query).
Either vhost
or path
. The default for S3 is vhost
and the default for R2
and GCS is path
.
Whether to use HTTPS or HTTP.
Can help when URLs contain problematic characters.
The R2 account ID to use for generating the endpoint URL.
Credential Chain Provider
Providing credentials via key_id
and secret
requires permanent AWS IAM/Identity Center keys. The CREDENTIAL_CHAIN
provider can
automatically fetch ephemeral credentials using mechanisms provided by the AWS SDK.
CREATE USER MAPPING FOR <current_user>
SERVER <server_name>
OPTIONS (
type 'S3',
provider 'CREDENTIAL_CHAIN',
CHAIN 'env;config;instance'
);
The following values can be passed into CHAIN
: config
, sts
, sso
, env
, instance
, process
.
Was this page helpful?