feat: storage URL abstraction via custom DuckDB FileSystem scheme

[qsliu2017]

Context

In a managed/serverless PostgreSQL service using pg_ducklake, the service operator stores user data files in an S3-compatible object store. Users interact with data through SQL but should never see sensitive storage details:

• S3 credentials (access key / secret key / STS tokens)
• S3 endpoint URLs (bucket names, prefixes, region-specific endpoints)
• Internal storage architecture (directory layout, tenant isolation boundaries)

Today, these details leak through multiple surfaces:

Surface	What leaks
ducklake.list_files()	Full S3 paths (s3://real-bucket/prefix/schema/table/file.parquet)
ducklake.options()	data_path containing the real S3 URL
SHOW ducklake.default_table_path	Real S3 URL (GUC is PGC_USERSET)
ducklake.set_option('data_path', ...)	Allows user to redirect writes to arbitrary locations
DuckDB error messages	May contain S3 paths, endpoint URLs, auth errors
ducklake.* metadata tables	File paths stored with real S3 prefixes
duckdb_secrets()	Credentials visible if stored as DuckDB secrets

Proposed approach: custom URI scheme + DuckDB FileSystem

Core idea

Instead of storing real S3 URLs in DuckLake metadata, store an opaque URI scheme (e.g., ducklake-store://) that gets resolved to the real storage URL only at the I/O boundary, inside a custom DuckDB FileSystem implementation.

Stored in metadata:    ducklake-store://public/orders/ducklake-{uuid}.parquet
User sees:             ducklake-store://public/orders/ducklake-{uuid}.parquet
DuckDB resolves to:    s3://real-bucket/prefix/public/orders/ducklake-{uuid}.parquet
                       ^^^^ only lives in memory, never persisted

Why a custom URI scheme (not template variables like {{MY_S3}})

• DuckDB's path handling is designed around URI schemes -- path normalization, separator detection, and FileSystem routing all work correctly with URI-style paths
• FileSystem::CanHandleFile() trivially matches the scheme prefix
• DuckLake's GetRelativePath / FromRelativePath use simple prefix matching -- a URI scheme works as-is with zero DuckLake code changes
• No risk of path manipulation (joining, normalization) mangling the placeholder

How it fits DuckLake's path model

DuckLake stores paths hierarchically with a path_is_relative flag:

data_path:     "ducklake-store://"        (stored in catalog metadata)
schema path:     "public/"                (relative to data_path)
table path:        "orders/"              (relative to schema path)
file name:           "ducklake-{uuid}.parquet"  (relative to table path)

Full path:     "ducklake-store://public/orders/ducklake-{uuid}.parquet"

GetRelativePath("ducklake-store://public/orders/file.parquet") correctly strips the data_path prefix and returns "public/orders/file.parquet" with path_is_relative = true. FromRelativePath() correctly reconstructs the full ducklake-store:// path. No DuckLake changes needed.

FileSystem implementation

Register a DuckLakeStoreFileSystem as a DuckDB sub-filesystem. It intercepts ducklake-store:// paths, resolves them to real S3 URLs, and delegates to DuckDB's VirtualFileSystem (which routes the resolved s3:// path to S3FileSystem):

ducklake-store://path
  -> DuckLakeStoreFileSystem.CanHandleFile() = true
  -> ResolvePath() -> s3://real-bucket/prefix/path
  -> VFS.OpenFile("s3://real-bucket/prefix/path")
     -> S3FileSystem.CanHandleFile("s3://...") = true
     -> S3FileSystem.OpenFile()
     -> returns FileHandle (owned by S3FS, subsequent Read/Write go directly there)

The custom FS only needs to override path-taking methods (OpenFile, FileExists, RemoveFile, ListFiles, DirectoryExists, CreateDirectory, MoveFile, Glob). Methods operating on FileHandle (Read, Write, Seek, GetFileSize, etc.) go directly to the underlying S3 FileHandle -- no interception needed.

Registration point

In ducklake_load_extension(), after existing registrations:

void ducklake_load_extension(duckdb::DuckDB &db) {
    // ... existing extension loading and function registration ...

    // Register ducklake-store:// filesystem
    auto store_url = pgducklake::GetStoreURL();  // from SUSET GUC or env var
    if (!store_url.empty()) {
        auto &fs = db.instance->GetFileSystem();
        fs.RegisterSubSystem(
            make_uniq<DuckLakeStoreFileSystem>(*db.instance, store_url));
    }
}

Automatically re-registered on DuckDB recycle since ducklake_load_extension is called again.

Configuration

Operator side (invisible to users):

Setting	Scope	Purpose
ducklake.store_url (new SUSET GUC)	backend	Maps ducklake-store:// to real S3 URL (e.g., s3://real-bucket/prefix)
AWS credentials via IAM role / STS	infra	No static AK/SK anywhere in the system

User side (visible):

Setting	Value	Purpose
data_path	ducklake-store://	Set by service on catalog creation, users see only the scheme

What gets protected

Attack vector	Before	After
ducklake.list_files()	s3://real-bucket/pfx/schema/t/1.parquet	ducklake-store://schema/t/1.parquet
ducklake.options()	data_path = s3://real-bucket/pfx	data_path = ducklake-store://
SHOW ducklake.default_table_path	s3://real-bucket/pfx	ducklake-store://
Error messages	s3://real-bucket/...	ducklake-store://...
Metadata tables	Real S3 paths	ducklake-store:// paths

Complementary hardening (separate PRs)

These are not strictly part of this feature but should be coordinated:

1. ducklake.default_table_path GUC context: change from PGC_USERSET to PGC_SUSET so users cannot override the storage path
2. REVOKE on sensitive functions: ducklake.set_option(), ducklake.add_data_files() should not be callable by regular users in a managed service
3. Block direct file access: users should not be able to use read_parquet('ducklake-store://...') to bypass DuckLake access controls -- either REVOKE on read_parquet/read_csv or add path-scoping in the custom FileSystem

Scope

• New: DuckLakeStoreFileSystem class (thin delegation layer, ~200 lines)
• New: ducklake.store_url SUSET GUC
• Change: ducklake_load_extension() registers the FS when store_url is set
• No changes to upstream DuckLake required
• No changes to pg_duckdb required (for the core feature)