Switch to using flask-sqlalchemy db session management, and include Auth
Manager Provider Test Isolation and Reliability

• Refactor test fixtures to always use Flask app contexts for DB and app
  operations.
• Add a global pytest fixture to clear SQLAlchemy metadata before each
  test, reducing test flakiness.
• Standardize session access and cleanup patterns across all tests.
• Refactor user/role creation and deletion to ensure proper isolation.
• Update test logic to use new app and auth manager creation utilities.
• Remove or update redundant or fragile test code.
• Normalize test constants and improve code readability.

Note: Clearing SQLAlchemy metadata and explicit app context management
are workarounds for test isolation issues. The root cause of metadata
and pool persistence between tests should be investigated further for a
more robust solution.

General Theme:

The main strategies are:

• Ensuring proper use of Flask app contexts in tests
• Cleaning up database state and metadata between tests
• Using the correct SQLAlchemy session and interface patterns
• Refactoring test fixtures for better isolation and reliability
• Removing or updating code that is no longer needed or that could cause
  test flakiness

Key File-by-File Changes

1. providers/fab/tests/unit/fab/conftest.py (New file)

What: Adds a global pytest fixture that clears SQLAlchemy metadata
before each test.

Why: This is to prevent metadata leakage between tests, which can cause
flaky or non-deterministic test results.

Uncertainty: Clearing metadata is a workaround; the root cause of
metadata persistence between tests may need deeper investigation.

2. Test files for API endpoints, CLI commands, models, schemas, and views

What:

• Many test fixtures now use with app.app_context(): to ensure all DB
  and app operations are performed within a Flask application context.
• User and role creation/deletion is now always wrapped in an app
  context.
• Some teardown logic is moved into the fixture's context manager to
  ensure cleanup happens after the test.
• Session access is standardized to use appbuilder.session instead of
  get_session.
• Some test constants (e.g., default times) are normalized (e.g.,
  removing timezone info).
• Some test logic is refactored for clarity and reliability (e.g., using
  addfinalizer for logout in user endpoint tests).

Why:

Ensures that tests do not leak state or context, which can cause
failures when running tests in parallel or in different environments.
Using the correct session and context patterns is more robust and
future-proof.

Uncertainty:

While these changes improve test isolation, the need to clear metadata
and manage app contexts so explicitly suggests there may be deeper
issues with how the test environment is set up or torn down. Further
investigation into the test infrastructure may be warranted.

3. test_fab_auth_manager.py and related files

What:

• Switches to using the new create_app and get_auth_manager utilities
  for creating Flask apps and auth managers.
• Updates test logic to use the new app and session patterns.
• Fixes some test assertions to compare user IDs instead of user objects
  directly.
• Moves some permission synchronization logic to after DAG creation and
  session commit.

Why:

These changes align the tests with the latest best practices and APIs in
Airflow and Flask AppBuilder. They also fix subtle bugs where tests
could pass or fail depending on object identity rather than value.

Uncertainty:

The need to manually commit and close sessions, and to synchronize
permissions, may indicate that the test setup/teardown is not fully
robust.

4. test_security.py

What:

• Removes some unused or redundant code (e.g., a test for DAG permission
  views).
• Updates session and app context usage.

Why:

Cleans up the test suite and ensures all tests are using the correct patterns.

Uncertainty:

The removal of some tests may need to be reviewed to ensure no loss of coverage.

5. Miscellaneous

What:

• Minor formatting and import cleanups.
• Some test parameters and constants are updated for consistency.

Why:

Improves code readability and maintainability.

Summary of Uncertainties and Next Steps

Clearing SQLAlchemy metadata and disposing pools:

These are workarounds for test isolation issues. The root cause (why
metadata and pools persist between tests) should be investigated
further. Ideally, the test infrastructure should handle this
automatically.

App context management:

The need for explicit app contexts in so many places may indicate that
the test setup could be improved to provide a more consistent
environment.

Session and teardown logic:

Manual session management and teardown in tests can be error-prone.
Consider centralizing this logic or using more robust fixtures.

^ Add meaningful description above
Read the Pull Request Guidelines for more information.
In case of fundamental code changes, an Airflow Improvement Proposal (AIP) is needed.
In case of a new dependency, check compliance with the ASF 3rd Party License Policy.
In case of backwards incompatible changes please leave a note in a newsfragment file, named {pr_number}.significant.rst or {issue_number}.significant.rst, in airflow-core/newsfragments.