I've been doing a lot of thought about how my prior and current work could be useful, not just intellectual exercises that might be useful to some people, and keep coming back to the idea that my focus should probably be other server-side extensions and not the SQL user. They won't be excluded - far from it - but the emphasis should be providing an open source solution that can handle much of the encryption and authentication required by other extensions.
Most importantly - giving them a way to avoid knowing their private keys. They can't expose what they don't have. >From this and other considerations I've concluded that the best way to meet these needs is probably a FDW. It can store the information in its own schema, on local files, pull them from a web service, or wrap an existing website like Hashicorp Vault. The extension would be able to use the FDW and related UDF to perform tasks that require the key for initialization but not ongoing work. A SSL connection would be a very good example of this since you need the key to perform the initial negotiation but can discard it once you have a valid SSL connection. IIRC you don't even need it during routine session renegotiations - it can use the existing session key and information cached in the SSL code. There's still a lot of handwaving here - especially with the question of whether a UDF can take a FDW instance as a parameter or how it could return an open SSL connection - but there are other situations where this would work as a simple function converting one value to another, e.g., applying or verifying cryptographic signatures. Providers This brings us to Providers (replacements for Engines in OpenSSL 3). Specifically there are clearly two types of providers: SOFTWARE ONLY. For instance the default OpenSSL implementation. This can be incorporated into the thread like any other library. HARDWARE. This could be a motherboard's TPM, dedicated crypto hardware, etc. These require at least some coordination between threads, and more likely will require a stub implementation with all of the real work done by a different thread. Or even a different program due to access restrictions on the device. The required interface is known - it's the OpenSSL Provider SPI - and it should be easy to use existing implementations. The only tricky bit will be managing access (esp. in multi-hosted environments) since physical devices often require explicit "open" and "close" and it's costl;y enough you don't want to perform multiple tasks if possible, while still keeping the hardware 'closed' as much as possible. TPM HARDWARE: This seems like it would be a problem, esp. in the cloud and esp. with transient instances, but it's not. It's common for virtual machines to provide virtual TPMs (or allow passthrough to the physical one), and there's a library that you can use on any machine. This is exactly the type of thing that can be prepopulated in cloud instances, although they'll require a bit of extra work if the contents are every modified. However I suspect anyone using a TPM will be treating it as a read-only device with any updates handled by a separate process. This means a TPM is an accessible and practical example of hardware-based key management, hardware, etc. The code won't useless to anyone without access to a $10k device. Bear
