Enable Bzlmod for the main repo (#2118)

**What type of PR is this?**

Deps update

**What package or component does this PR mostly affect?**

all

**What does this PR do? Why is it needed?**

This requires an update to rules_go for fixes in `go_bazel_test`. Since
the Bzlmod-aware version of the test only supports test files from a
single repo, we load `rules_go` as an `http_archive` instead.

Also run `go mod tidy -e` and `bazel mod tidy` to clean up `go.mod` and
act on all dependency health warnings shown by Bzlmod and the `go_deps`
extension.

A follow-up PR will add testing with Bazel 8.

**Which issues(s) does this PR fix?**

Fixes #

**Other notes for review**