Automate SDK upgrades for integrations-owned inputs and modules

We currently do not have good process or automation for keeping libraries up to date. The result of this is that we see library versions which are years out of date.

Typically, our modules are well tested and the stability of a known library version is a comfort. The problem comes when we are forced to upgrade after a long period, due perhaps to a CVE discovery or an upstream dependency. In these situations we end up onboarding many months or years of changes in one go, which can be a non-trivial undertaking. We should automate more granular library upgrades to avoid this situation.

The common concern with this approach is that we onboard new bugs. A recent example being that we were forced to [upgrade the MongoDB Go driver](https://github.com/elastic/beats/pull/41014), which introduced a connection leak bug. This is a valid concern, but is heavily mitigated by the fact that we would also pull in more frequent fixes, in the Mongo case the issue was fixed in the next patch release. Typically mature libraries of the sort we depend on for our Beats inputs get more stable over time.

We already take this approach for several specific sets of libraries, in particular [CSP SDKs](https://github.com/elastic/beats/blob/main/.github/dependabot.yml#L38). We should extend this to cover many more.

Below is the full list of integrations-owned inputs and modules, excluding AWS/Azure/GCP. We need to make a call on how we approach this - do we enable automatic upgrades for all of these? I think that should be our eventual goal, but we should start small and dial in our process for ensuring the resulting PRs are reviewed quickly, well tested by CI, and merged into appropriate releases.

```
/filebeat/module/apache
/filebeat/module/haproxy
/filebeat/module/iis
/filebeat/module/kafka
/filebeat/module/mongodb
/filebeat/module/mysql
/filebeat/module/nats
/filebeat/module/nginx
/filebeat/module/postgresql
/filebeat/module/redis

/x-pack/filebeat/input/cometd/
/x-pack/filebeat/input/salesforce
/x-pack/filebeat/module/activemq
/x-pack/filebeat/module/ibmmq
/x-pack/filebeat/module/mssql
/x-pack/filebeat/module/oracle
/x-pack/filebeat/module/rabbitmq
/x-pack/filebeat/module/salesforce
/x-pack/filebeat/module/tomcat
/x-pack/filebeat/module/zookeeper

/metricbeat/module/aerospike
/metricbeat/module/apache
/metricbeat/module/ceph
/metricbeat/module/couchbase
/metricbeat/module/couchdb
/metricbeat/module/etcd
/metricbeat/module/golang
/metricbeat/module/haproxy
/metricbeat/module/http
/metricbeat/module/jolokia
/metricbeat/module/kafka
/metricbeat/module/memcached
/metricbeat/module/mongodb
/metricbeat/module/mysql
/metricbeat/module/nats
/metricbeat/module/nginx
/metricbeat/module/php_fpm
/metricbeat/module/prometheus
/metricbeat/module/postgresql
/metricbeat/module/rabbitmq
/metricbeat/module/redis
/metricbeat/module/vsphere
/metricbeat/module/windows/wmi
/metricbeat/module/zookeeper

/x-pack/metricbeat/module/activemq
/x-pack/metricbeat/module/airflow
/x-pack/metricbeat/module/cloudfoundry
/x-pack/metricbeat/module/cockroachdb
/x-pack/metricbeat/module/coredns
/x-pack/metricbeat/module/ibmmq
/x-pack/metricbeat/module/iis
/x-pack/metricbeat/module/mssql
/x-pack/metricbeat/module/meraki
/x-pack/metricbeat/module/openai
/x-pack/metricbeat/module/oracle
/x-pack/metricbeat/module/panw
/x-pack/metricbeat/module/prometheus
/x-pack/metricbeat/module/redisenterprise
/x-pack/metricbeat/module/sql
/x-pack/metricbeat/module/statsd
/x-pack/metricbeat/module/tomcat
```

Start with .github/dependabot.yml and its existing CSP SDK configuration, then review the listed Filebeat and Metricbeat integration paths to choose a small pilot. Done means dependency upgrades are automated for the selected inputs or modules, with resulting pull requests reviewed promptly, validated by CI, and merged into appropriate releases.

Written by the indexing model from the issue text.