c.f. #15264

The two changes are:
1. Add indexes so that the select / deletes don't do sequential scans
2. Don't repeatedly call `SELECT count(*)` each iteration, as that's slow

* Convert async to normal tests in `TestSSOHandler`

* newsfile

* Enable `directory`

* move to worker store

* newsfile

* disable `ClientDirectoryListServer` and `ClientAppserviceDirectoryListServer` for workers

The registration fallback is broken and unspecced. This removes it
since there is no plan to spec it.

Note that this does not modify the login fallback code.

Improve robustness when handling a perspective key response by deduplicating received server keys. (#15423)

* Change `store_server_verify_keys` to take a `Mapping[(str, str), FKR]`

This is because we already can't handle duplicate keys — leads to cardinality violation

* Newsfile

Signed-off-by: Olivier Wilkinson (reivilibre) <oliverw@matrix.org>

---------

Signed-off-by: Olivier Wilkinson (reivilibre) <oliverw@matrix.org>

* Add missing comma

* Newsfile

Signed-off-by: Olivier Wilkinson (reivilibre) <oliverw@matrix.org>

---------

Signed-off-by: Olivier Wilkinson (reivilibre) <oliverw@matrix.org>

This moves `redacts` from being a top-level property to
a `content` property in a new room version.

MSC2176 (which was previously implemented) states to not
`redact` this property.

* raise a ConfigError on an invalid app_service_config_files

* changelog

* Move config check to read_config

* Add test

* Ensure list also contains strings

By inlining the typechecking job from backend-meta.

This seems to resolve odd errors (maybe due to caching?)
to have been seen on Dependabot PRs.

* Bump pyopenssl from 23.1.0 to 23.1.1

Bumps [pyopenssl](https://github.com/pyca/pyopenssl) from 23.1.0 to 23.1.1.
- [Release notes](https://github.com/pyca/pyopenssl/releases)
- [Changelog](https://github.com/pyca/pyopenssl/blob/main/CHANGELOG.rst)
- [Commits](https://github.com/pyca/pyopenssl/compare/23.1.0...23.1.1

)

---
updated-dependencies:
- dependency-name: pyopenssl
  dependency-type: direct:production
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

---------

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>
Co-authored-by: Mathieu Velten <mathieuv@matrix.org>
Co-authored-by: David Robertson <davidr@element.io>

* Trust dtolnay/rust-toolchain

The author is a big deal in the Rust world and I'm happy to trust them.
I'm also bored of the dependabot updates tbh.

* Changelog

* Bump types-psycopg2 from 2.9.21.8 to 2.9.21.9

Bumps [types-psycopg2](https://github.com/python/typeshed) from 2.9.21.8 to 2.9.21.9.
- [Release notes](https://github.com/python/typeshed/releases)
- [Commits](https://github.com/python/typeshed/commits

)

---
updated-dependencies:
- dependency-name: types-psycopg2
  dependency-type: direct:development
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

---------

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>
Co-authored-by: Mathieu Velten <mathieuv@matrix.org>
Co-authored-by: David Robertson <davidr@element.io>

* Bump types-netaddr from 0.8.0.6 to 0.8.0.7

Bumps [types-netaddr](https://github.com/python/typeshed) from 0.8.0.6 to 0.8.0.7.
- [Release notes](https://github.com/python/typeshed/releases)
- [Commits](https://github.com/python/typeshed/commits

)

---
updated-dependencies:
- dependency-name: types-netaddr
  dependency-type: direct:development
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

---------

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>
Co-authored-by: David Robertson <davidr@element.io>

* Bump types-opentracing from 2.4.10.3 to 2.4.10.4

Bumps [types-opentracing](https://github.com/python/typeshed) from 2.4.10.3 to 2.4.10.4.
- [Release notes](https://github.com/python/typeshed/releases)
- [Commits](https://github.com/python/typeshed/commits

)

---
updated-dependencies:
- dependency-name: types-opentracing
  dependency-type: direct:development
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

---------

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump dawidd6/action-download-artifact from 2.26.0 to 2.26.1

Bumps [dawidd6/action-download-artifact](https://github.com/dawidd6/action-download-artifact) from 2.26.0 to 2.26.1.
- [Release notes](https://github.com/dawidd6/action-download-artifact/releases)
- [Commits](https://github.com/dawidd6/action-download-artifact/compare/5e780fc7bbd0cac69fc73271ed86edf5dcb72d67...7132ab516fba5f602fafae6fdd4822afa10db76f

)

---
updated-dependencies:
- dependency-name: dawidd6/action-download-artifact
  dependency-type: direct:production
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

---------

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Use setup-go instead of relying on 1.17 to exist

See https://github.com/actions/runner-images/issues/7276

* Changelog

This will be done in a future room version, for now an unstable
room version is added which redacts the origin field.

Refer to the correct field from the response when updating
the background update progress.

This change fixes a rare bug where initial /syncs would fail with a
`KeyError` under the following circumstances:
 1. A user fast joins a remote room.
 2. The user is kicked from the room before the room's full state has
    been synced.
 3. A second local user fast joins the room.
 4. Events are backfilled into the room with a higher topological
    ordering than the original user's leave. They are assigned a
    negative stream ordering. It's not clear how backfill happened here,
    since it is expected to be equivalent to syncing the full state.
 5. The second local user leaves the room before the room's full state
    has been synced. The homeserver does not complete the sync.
 6. The original user performs an initial /sync with lazy_load_members
    enabled.
     * Because they were kicked from the room, the room is included in
       the /sync response even though the include_leave option is not
       specified.
     * To populate the room's timeline, `_load_filtered_recents` /
       `get_recent_events_for_room` fetches events with a lower stream
       ordering than the leave event and picks the ones with the highest
       topological orderings (which are most recent). This captures the
       backfilled events after the leave, since they have a negative
       stream ordering. These events are filtered out of the timeline,
       since the user was not in the room at the time and cannot view
       them. The sync code ends up with an empty timeline for the room
       that notably does not include the user's leave event.
       This seems buggy, but at least we don't disclose events the user
       isn't allowed to see.
     * Normally, `compute_state_delta` would fetch the state at the
       start and end of the room's timeline to generate the sync
       response. Since the timeline is empty, it fetches the state at
       `min(now, last event in the room)`, which corresponds with the
       second user's leave. The state during the entirety of the second
       user's membership does not include the membership for the first
       user because of partial state.
       This part is also questionable, since we are fetching state from
       outside the bounds of the user's membership.
     * `compute_state_delta` then tries and fails to find the user's
       membership in the auth events of timeline events. Because there
       is no timeline event whose auth events are expected to contain
       the user's membership, a `KeyError` is raised.

Also contains a drive-by fix for a separate unlikely race condition.

Signed-off-by: Sean Quah <seanq@matrix.org>

* Note that Synapse 1.74 queued a user dir rebuild

* Changelog