What is the best method for using the UI with a remote server that only has SSH access? The database is too large to rsync locally and seems risky to start opening ports?
Given the above I'm not sure it supports SSH functionality? Since it exposes an API though there is probably a way to access it, but the easiest solution is probably the one you don't want, which is to open the expected port and just hit it up in a browser. You could open it only to your (office/VPN) IP address, that way at least you're only exposing the port to yourself.
And re-reading a bit it does appear to support remote data warehouses, as it has Mother Duck integration, and that is what Mother Duck is. Someone will probably add an interface to make this kind of thing possible for privately hosted DBs. The question is will it be dynamic via SSH tunnel or is it exclusively API driven? And does it depend on the closed source (I think?) Mother Duck authentication system.
ssh -F ssh.config -L 4213:localhost:4213 dev 'DUCKDB_HTTPPORT=4213 ~/.duckdb/cli/latest/duckdb -ui'