Support for OAuth2

[buremba]

Thanks for this great extension! This seems like a great "extension interface" for high-level programming languages such as Python & Typescript to develop DuckDB extensions.

I was looking into the authentification, and it looks like we need to pass a token as part of the secret. Could this be automated in a way similar to how Gsheets and Motherduck extensions work as follows?

1. The user runs the following query:

CREATE SECRET airport_hello_world (
      type airport,
      scope 'grpc+tls://server.example.com/'
);

2. Airport extension asking the Flight server for an external URL to authenticate the user and redirect the user (or asking for auth key to be entered manually) to the redirect_uri. Once the token is generated with, we can generate the bearer token via exchanging tokenand use it to auth the user for subsequent requests.

[rustyconover]

Hi @buremba,

Thanks for filing this issue. I'm familiar somewhat with OAuth2, but I'll review Motherduck and GSheet's implementations in their extensions and reply.

Rusty

[VGSML]

Great feature request! I'd like to propose an additional mechanism for embedded DuckDB scenarios.

Use Case: Embedded DuckDB in Applications

When DuckDB is embedded in another application (web server, desktop app, etc.), the host application typically manages user authentication independently. In this case, the auth token is already available in the application context, and we just need a way to pass it to Airport.

Proposed Solution: Session Variable for Auth Token

Allow secrets to reference a session variable for the auth token:

-- Create secret that references a session variable
CREATE SECRET airport_api (
    TYPE airport,
    SCOPE 'grpc+tls://api.example.com/',
    AUTH_TOKEN_VARIABLE 'airport_auth_token'
);

-- Application sets the token via session variable (can be updated per-request)
SET airport_auth_token = 'eyJhbGciOiJIUzI1NiIs...';

-- Subsequent queries use the token from the variable
SELECT * FROM airport_api.schema.table;

Benefits

1. Dynamic token updates - Token can be refreshed without recreating the secret
2. Per-session isolation - Different DuckDB connections can have different tokens
3. Application control - Host application manages auth lifecycle (login, refresh, logout)
4. No secret recreation - Token rotation doesn't require DROP SECRET / CREATE SECRET

Example: Web Application

# Python web server example
@app.route('/api/query')
def handle_query():
    user_token = get_current_user_token()  # From session/JWT

    conn = duckdb.connect()
    conn.execute(f"SET airport_auth_token = '{user_token}'")

    # Secret was created once at startup with AUTH_TOKEN_VARIABLE
    result = conn.execute("SELECT * FROM remote.schema.table").fetchall()
    return jsonify(result)

Interaction with OAuth Flow

This could work alongside the OAuth flow you described:

• Interactive users: Use the OAuth redirect flow to obtain token, then set it via variable
• Service accounts: Set token directly from environment/config
• Embedded apps: Host app manages auth, passes token via variable

Would this be feasible to implement? Happy to discuss further or help with implementation if useful.