[Tech debt] Remove duplicated definition of `default_profiles_dir` (without adding circular dependency)

[dbeatty10]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

The following code duplicated the definition of default_profiles_dir within #6788 in order to avoid a circular dependency issue.

dbt-core/core/dbt/config/profile.py

Lines 167 to 174 in 7df04b0

	# TODO: Duplicating this method as direct copy of the implementation in dbt.cli.resolvers
	# dbt.cli.resolvers implementation can't be used because it causes a circular dependency.
	# This should be removed and use a safe default access on the Flags module when
	# https://github.com/dbt-labs/dbt-core/issues/6259 is closed.
	def default_profiles_dir():
	from pathlib import Path
	return Path.cwd() if (Path.cwd() / "profiles.yml").exists() else Path.home() / ".dbt"

We'd like to remove this duplication while still avoiding any circular dependencies.

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

See proposed implementation in #11137

Anything else?

No response

[emmyoop]

This causes some CI failures in dbt-postgres and we needed to revert the fix so reopening the issue. See #11175 for details.