​
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'
);
current_user
required

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.

server_name
required

Foreign server name.

​
Credentials Options

The following options can be passed into CREATE USER MAPPING:

type
required

Must be one of S3, GCS, or R2.

secret
The secret key.
region
default: "us-east-1"

The region for which to authenticate (should match the region of the bucket to query).

session_token
A session token.
endpoint
Specify a custom S3 endpoint.
url_style

Either vhost or path. The default for S3 is vhost and the default for R2 and GCS is path.

use_ssl
default: "true"

Whether to use HTTPS or HTTP.

url_compatibility_mode
default: "true"

Can help when URLs contain problematic characters.

account_id

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?

Google Cloud StoragePostgres