Opening remote FITS files without fsspec leaves temp files around until process ends

### Description

We are running some jupyter notebooks in the Rubin science platform which do things like:

```python
data = astropy.io.fits.getdata("https://signed.url", cache=False)
```
and we notice that `/tmp` fills up because the files seem to stay there for the duration of the process and notebooks can stay active for a very long time. Why isn't `getdata` deleting the file as soon as it has returned the contents? I tried switch to `memmap=False` and it made no difference.

This also happens with
```python
with astropy.io.fits.open(signeed_url", cache=False) as hdulist:
...
```
where one would expect resources to be freed when the context completes.

We have switched to `use_fsspec=True` and that fixes the problem but the unexpected behavior has caused us some issues.

### Expected behavior

Temporary file is deleted once it is no longer needed.

### How to Reproduce

1. Get package from '...'
2. Then run '...'
3. An error occurs.

```python
# Put your Python code snippet here.
import astropy.io.fits as fits
import time
data = fits.getdata("https://raw.githubusercontent.com/lsst/afw/main/tests/data/exposure-version-2.fits", cache=False, memmap=False)
time.sleep(10) # check /tmp for astropy-download file
```

### Versions

```python
import astropy
try:
astropy.system_info()
except AttributeError:
import platform; print(platform.platform())
import sys; print("Python", sys.version)
import astropy; print("astropy", astropy.__version__)
import numpy; print("Numpy", numpy.__version__)
import erfa; print("pyerfa", erfa.__version__)
try:
import scipy
print("Scipy", scipy.__version__)
except ImportError:
print("Scipy not installed")
try:
import matplotlib
print("Matplotlib", matplotlib.__version__)
except ImportError:
print("Matplotlib not installed")
```
```
platform
--------
platform.platform() = 'macOS-15.3.2-arm64-arm-64bit'
platform.version() = 'Darwin Kernel Version 24.3.0: Thu Jan 2 20:24:16 PST 2025; root:xnu-11215.81.4~3/RELEASE_ARM64_T6000'
platform.python_version() = '3.12.9'

packages
--------
astropy 7.0.1
numpy 2.0.2
scipy 1.13.1
matplotlib 3.10.0
pandas 2.2.3
pyerfa 2.0.1.5
```

Start by reproducing the issue with astropy.io.fits.getdata and astropy.io.fits.open using the provided remote URL, cache=False, and memmap=False, then compare with use_fsspec=True. Trace the remote-file handling from those entry points and verify that the temporary file is removed after getdata returns or the context manager exits.

Written by the indexing model from the issue text.