Improve access delegation mode selection algorithm

[adutra]

Is your feature request related to a problem? Please describe.

#2280 is bringing support for S3 request signing.

It introduces an access delegation mode selection algorithm that is roughly like this:

1. If no delegation mode is requested, use UNKNOWN
2. If one single delegation mode is requested, use that mode
3. If requested modes include both VENDED_CREDENTIALS and REMOTE_SIGNING
   a. If credentials subscoping is enabled for the catalog, use VENDED_CREDENTIALS
   b. Otherwise, use REMOTE_SIGNING
4. Otherwise, throw an error "unsupported mode(s)"

This algorithm aims at being smart but also fast to execute.

But it may select sub-optimal modes. For example, it doesn't check whether STS is available, because that requires fetching the AwsStorageConfigurationInfo for the catalog. So in some cases it may select VENDED_CREDENTIALS while REMOTE_SIGNING would be a better choice.

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response