Repository: duckdb/duckdb (language: python)

Issue:
Persistent HTTP secrets work only one time, cause `INTERNAL Error: ExtraValueInfo type mismatch` on 2nd usage.
### What happens?

When creating a persistent HTTP secret on either an in-memory or a file persistent database, the secret will cause any HTTP interaction within it's scope to fail with `INTERNAL Error: ExtraValueInfo type mismatch`

### To Reproduce

Example from motherduck.com, added persistent, first call works

```bash
duckdb -c "
CREATE PERSISTENT SECRET http (
    TYPE HTTP,
    EXTRA_HTTP_HEADERS MAP {
        'Authorization': 'Bearer sk_test_VePHdqKTYQjKNInc7u56JBrQ'
    }
);
select unnest(data) as customers 
from read_json('https://api.stripe.com/v1/customers')"
```

Rerun (duckdb must be restarted)

```bash
duckdb -c "select unnest(data) as customers 
from read_json('https://api.stripe.com/v1/customers')"
```

Result:

```
INTERNAL Error: ExtraValueInfo type mismatch
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors
```



### OS:

MacOS

### DuckDB Version:

v1.1.0 fa5c2fe15f

### DuckDB Client:

DuckDB CLI

### Hardware:

_No response_

### Full Name:

Michael Simons

### Affiliation:

Neo4j, Inc.

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:
Thanks, I could reproduce this locally. We'll take a look.

Fix patch:
diff --git a/extension/httpfs/create_secret_functions.cpp b/extension/httpfs/create_secret_functions.cpp
index daf000ab85dd..bcfdd82c3115 100644
--- a/extension/httpfs/create_secret_functions.cpp
+++ b/extension/httpfs/create_secret_functions.cpp
@@ -60,13 +60,13 @@ unique_ptr<BaseSecret> CreateS3SecretFunctions::CreateSecretFunctionInternal(Cli
 				throw InvalidInputException("Invalid type past to secret option: '%s', found '%s', expected: 'BOOLEAN'",
 				                            lower_name, named_param.second.type().ToString());
 			}
-			secret->secret_map["use_ssl"] = named_param.second.GetValue<bool>();
+			secret->secret_map["use_ssl"] = Value::BOOLEAN(named_param.second.GetValue<bool>());
 		} else if (lower_name == "url_compatibility_mode") {
 			if (named_param.second.type() != LogicalType::BOOLEAN) {
 				throw InvalidInputException("Invalid type past to secret option: '%s', found '%s', expected: 'BOOLEAN'",
 				                            lower_name, named_param.second.type().ToString());
 			}
-			secret->secret_map["url_compatibility_mode"] = named_param.second.GetValue<bool>();
+			secret->secret_map["url_compatibility_mode"] = Value::BOOLEAN(named_param.second.GetValue<bool>());
 		} else if (lower_name == "account_id") {
 			continue; // handled already
 		} else {
diff --git a/src/function/table/system/duckdb_secrets.cpp b/src/function/table/system/duckdb_secrets.cpp
index 44557e349426..6069344bfaed 100644
--- a/src/function/table/system/duckdb_secrets.cpp
+++ b/src/function/table/system/duckdb_secrets.cpp
@@ -107,13 +107,21 @@ void DuckDBSecretsFunction(ClientContext &context, TableFunctionInput &data_p, D
 
 		const auto &secret = *secret_entry.secret;
 
-		output.SetValue(0, count, secret.GetName());
-		output.SetValue(1, count, Value(secret.GetType()));
-		output.SetValue(2, count, Value(secret.GetProvider()));
-		output.SetValue(3, count, Value(secret_entry.persist_type == SecretPersistType::PERSISTENT));
-		output.SetValue(4, count, Value(secret_entry.storage_mode));
-		output.SetValue(5, count, Value::LIST(LogicalType::VARCHAR, scope_value));
-		output.SetValue(6, count, secret.ToString(bind_data.redact));
+		idx_t i = 0;
+		// name
+		output.SetValue(i++, count, secret.GetName());
+		// type
+		output.SetValue(i++, count, Value(secret.GetType()));
+		// provider
+		output.SetValue(i++, count, Value(secret.GetProvider()));
+		// persistent
+		output.SetValue(i++, count, Value(secret_entry.persist_type == SecretPersistType::PERSISTENT));
+		// storage
+		output.SetValue(i++, count, Value(secret_entry.storage_mode));
+		// scope
+		output.SetValue(i++, count, Value::LIST(LogicalType::VARCHAR, scope_value));
+		// secret_string
+		output.SetValue(i++, count, secret.ToString(bind_data.redact));
 
 		data.offset++;
 		count++;
diff --git a/src/include/duckdb/main/secret/secret.hpp b/src/include/duckdb/main/secret/secret.hpp
index decd62ed05e0..f3257f005217 100644
--- a/src/include/duckdb/main/secret/secret.hpp
+++ b/src/include/duckdb/main/secret/secret.hpp
@@ -37,7 +37,8 @@ struct CreateSecretInput {
 	case_insensitive_map_t<Value> options;
 };
 
-typedef unique_ptr<BaseSecret> (*secret_deserializer_t)(Deserializer &deserializer, BaseSecret base_secret);
+typedef unique_ptr<BaseSecret> (*secret_deserializer_t)(Deserializer &deserializer, BaseSecret base_secret,
+                                                        const named_parameter_type_map_t &options);
 typedef unique_ptr<BaseSecret> (*create_secret_function_t)(ClientContext &context, CreateSecretInput &input);
 
 //! A CreateSecretFunction is a function adds a provider for a secret type.
@@ -180,14 +181,30 @@ class KeyValueSecret : public BaseSecret {
 
 	// FIXME: use serialization scripts
 	template <class TYPE>
-	static unique_ptr<BaseSecret> Deserialize(Deserializer &deserializer, BaseSecret base_secret) {
+	static unique_ptr<BaseSecret> Deserialize(Deserializer &deserializer, BaseSecret base_secret,
+	                                          const named_parameter_type_map_t &options) {
 		auto result = make_uniq<TYPE>(base_secret);
 		Value secret_map_value;
 		deserializer.ReadProperty(201, "secret_map", secret_map_value);
 
 		for (const auto &entry : ListValue::GetChildren(secret_map_value)) {
 			auto kv_struct = StructValue::GetChildren(entry);
-			result->secret_map[kv_struct[0].ToString()] = kv_struct[1].ToString();
+			auto key = kv_struct[0].ToString();
+			auto raw_value = kv_struct[1].ToString();
+
+			auto it = options.find(key);
+			if (it == options.end()) {
+				throw IOException("Failed to deserialize secret '%s', it contains an unexpected key: '%s'",
+				                  base_secret.GetName(), key);
+			}
+			auto &logical_type = it->second;
+			Value value;
+			if (logical_type.id() == LogicalTypeId::VARCHAR) {
+				value = Value(raw_value);
+			} else {
+				value = Value(raw_value).DefaultCastAs(logical_type);
+			}
+			result->secret_map[key] = value;
 		}
 
 		Value redact_set_value;
diff --git a/src/main/secret/secret_manager.cpp b/src/main/secret/secret_manager.cpp
index 72f5b924bd07..1a21d2db1ae0 100644
--- a/src/main/secret/secret_manager.cpp
+++ b/src/main/secret/secret_manager.cpp
@@ -108,7 +108,15 @@ unique_ptr<BaseSecret> SecretManager::DeserializeSecret(Deserializer &deserializ
 		    "Attempted to deserialize secret type '%s' which does not have a deserialization method", type);
 	}
 
-	return deserialized_type.deserializer(deserializer, {scope, type, provider, name});
+	auto function_entry = LookupFunctionInternal(type, provider);
+	if (!function_entry) {
+		throw InternalException(
+		    "Attempted to deserialize secret (type: '%s', provider: '%s') which does not have any functions registered",
+		    type, provider);
+	}
+
+	return deserialized_type.deserializer(deserializer, {scope, type, provider, name},
+	                                      function_entry->named_parameters);
 }
 
 void SecretManager::RegisterSecretType(SecretType &type) {


Test patch:
diff --git a/test/sql/secrets/persistent_key_value_secret.test b/test/sql/secrets/persistent_key_value_secret.test
new file mode 100644
index 000000000000..89448fa9a811
--- /dev/null
+++ b/test/sql/secrets/persistent_key_value_secret.test
@@ -0,0 +1,28 @@
+# name: test/sql/secrets/persistent_key_value_secret.test
+# group: [secrets]
+
+load __TEST_DIR__/persistent_extra_headers
+
+require httpfs
+
+require json
+
+statement ok
+CREATE PERSISTENT SECRET http (
+	TYPE HTTP,
+	EXTRA_HTTP_HEADERS MAP {
+		'Authorization': 'Bearer sk_test_not_valid_key'
+	}
+);
+
+restart
+
+# Because this is an https host, the 'EXTRA_HTTP_HEADERS' will be used, as long as this doesn't crash anything
+# we are happy with this test throwing an IO error.
+statement error
+select
+	unnest(data) as customers
+from
+	read_json('https://non.existant/endpoint');
+----
+IO Error: Could not establish connection error for HTTP HEAD to 'https://non.existant/endpoint'