Revert Project.public_or_visible_to_user changes but apply change to SnippetsFinder

What does this MR do?

These changes were introduced in MR https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/17088. In https://gitlab.com/gitlab-com/infrastructure/issues/3772 we discovered these changes lead to a pretty drastic increase in SQL response timings.

The above MR introduced a change intended to improve SnippetsFinder to the Project.public_or_visible_to_user method. Unfortunately, this affected other places where this method was used - the scope was too broad.

With this MR, we revert the changes in the Project.public_or_visible_to_user method. At the same time, we keep the intended improvements for snippets but keep them limited to SnippetsFinder.

For snippets, we know that the change had a positive impact and rendered the snippets dashboard usable again (for some users, we'd run into statement timeouts): It improved /dashboard/snippets from a SQL timing of 5/23/25s to 0.7/2/4s (mean/p95/p99).

This builds upon https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/17461.

Does this MR meet the acceptance criteria?

• Changelog entry added, if necessary
• Review
  • Has been reviewed by Backend
  • Has been reviewed by Database
• Conform by the merge request performance guides
• Conform by the style guides
• Squashed related commits together
• End-to-end tests pass (package-qa manual pipeline job)

What are the relevant issue numbers?

Closes #42877 (closed)