Stumbling across this.
This is very interesting. Out of interest why?

What's the intended way to set secrets for prod environments?

(We have this issue if someone wants to use HTTPFS with Evidence, for example)

Nice idea! Hmmm... a prepared statement is a single statement (I think) and unlike a stored procedure in that sense, which means I'd have to do it using a single statement?

If I want to pass an environment variable as a parameter in a prepared statement in a single step, I could try to use a "dot command" shelling out to run a system command, such as .system env | grep S3_URI which would display the value (if any set) for the system environment variable containing for example an S3 connection string URI.

It doesn't seem that dot commands are supported in PREPARE statements though:

D PREPARE S1 AS .system echo $1;
Error: Parser Error: parser error : syntax error at or near "."
LINE 1: PREPARE S1 AS .system echo $1;
                      ^
D PREPARE S1 AS .shell echo $1;
Error: Parser Error: parser error : syntax error at or near "."
LINE 1: PREPARE S1 AS .shell echo $1;

So maybe currently this cannot be done a prepared statement? If there had been a utility function getenv(sysenvname) it might have worked better.

So it looks like currently I would need more than one step - something lika a stored procedure with a first step to write the value stored in the environment variable to a file and then one step using a function to read its value (such as read_csv). Slightly convoluted but something along these lines (using $HOME as an example system variable value to read):

D .system printf 'envvar,value\\\nHOME,%s' "$HOME" > /tmp/env
D prepare s1 as select value from read_csv_auto('/tmp/env', header=TRUE) where envvar=$1;
D execute s1('HOME');

But there is no support for multiple prepared statements / stored procedures currently, I think.

It seems that the httpfs extension has a global state for the higher level S3 connection source configuration (and expects bucket name and file specifications to change in the 's3://'-string). So when changing from one source to another one needs to explicitly overwrite those values with new ones before the running a query against another S3 source:

set s3_endpoint=$S3_ENDPOINT_1;
set s3_access_key_id=$S3_USER_1;
set s3_secret_access_key=$S3_PASS_1;
set s3_use_ssl=$S3_USE_SSL_1;
set s3_region=$S3_REGION_1;
set s3_url_style=$S3_URL_STYLE_1;
select count(*) from 's3://bucket_in_source_1/*.parquet';

set s3_endpoint=$S3_ENDPOINT_2;
set s3_access_key_id=$S3_USER_2;
set s3_secret_access_key=$S3_PASS_2;
set s3_use_ssl=$S3_USE_SSL_2;
set s3_region=$S3_REGION_2;
set s3_url_style=$S3_URL_STYLE_2;
select count(*) from 's3://bucket_in_source_2/*.parquet';

It would be cool if an S3_URI connection string could be used to allow something like this:

select count(*) from getenv('S3_URI_1') || '/bucket_in_source_1/*.parquet';
select count(*) from getenv('S3_URI_2') || '/bucket_in_source_2/*.parquet';

Consider writing out a script you want to perform then ".read" it in. For example employing ".once":

.mode list
.headers off
.separator "\r\n" "\r\n"
;
.once "process_s3_1.sql"
SELECT 'set s3_endpoint=''myENDPOINT'';' as stmt01
, 'set s3_access_key_id=''blablabla'';' as stmt02;

.read "process_s3_1.sql"

This technique is similar to an execute type concept with one interesting benefit, there is quite a bit you can work into a script and you have a simple trail to follow for post process reporting and analysis.

Hope that helps. Have a wonderful week.

Thanks for the suggestion, it sounds like a way to do "meta programming" in the CLI using the dot commands to generate "SQL scripts" which unlike a prepared statement allows more than one step to be executed, in essence doing things like what stored procedures can do.

Unlike with the .duckdbrc file (which runs once at startup) one could use this mechanism anytime during a CLI session and I guess the mechanism could be used to "shell out" and run a system command to write system environment variables to a duckdb table, after which this data would be available for use in SQL statements.

The suggestion to support being able to use a duckdb function to read data directly from an environment variable could alleviate this currently required "level of indirection" and would also not be tied to usage from inside the CLI.

Combined with extended support for S3_URIs (or S3-URLs) - like what for example neo4j and arrow allows - maybe it could be used as a nice base for supporting as @samansmink mentions "having different sets of credentials at the same time against different files" residing in different S3 object storage locations, similar to:

-- examples of "fully qualified" S3_URI patterns
select count(*) from 's3://endpoint:port/bucket/key?accessKey=accessKey&secretKey=secretKey[&sessionToken=sessionToken] (sessionToken is optional)'
select count(*) from 's3://minioadmin:minioadmin@?scheme=http&endpoint_override=localhost%3A9000'

Supporting URI options to be passed as URL-encoded parameters in the "connectionstring" (and in addition reading such values from environment variables) might facilitate combining data from different S3 buckets - while keeping some of the connectionstring mojo hidden (if not fully secret) if those could be read from environment variables.

Maybe even allowing for joining data in two different S3 buckets - even when located in different object storage servers - in one query?

@mskyttner The fully qualified s3 urls seem like a nice feature to me, as this would allow joining data from different object storage servers in a single query, i'll open an issue for this!

I figured it was worth noting that this is ppssible now:
https://duckdb.org/docs/extensions/httpfs#per-request-configuration