Closes #40848

On sysroot systems like gentoo prefix, as well as nix/guix, our "is
system path" logic is broken cause it's static.

Talking about "the system paths" is not helpful, we have to talk
about default search paths of the dynamic linker instead.

If glibc is recent enough, we can query the dynamic loader's default
search paths, which is a much more robust way to avoid registering
rpaths to system dirs, which can shadow Spack dirs.

This PR adds an additional filter on rpaths the compiler wrapper
adds, dropping rpaths that are default search paths. The PR does
not remove any of the original is_system_path code, which is
questionable code, but can't hurt much.

(In a follow up PR we could look to register rpaths based only on
the default search paths of the dynamic linker, and get rid of
is_system_path. I don't want to make too many changes at the
same time.)

This fixes issues where build systems run just-built executables
linked against their not-yet-installed libraries, typically:

LD_LIBRARY_PATH=. ./exe

which happens in perl, python, and other non-cmake packages.
If a default path is rpath'ed, it takes precedence over
LD_LIBRARY_PATH, and a system library gets loaded instead
of the just-built library in the stage dir, breaking the build. If
default paths are not rpath'ed, then LD_LIBRARY_PATH takes
precedence, as is desired.

This PR additionally fixes an inconsistency in rpaths between
cmake and non-cmake packages. The cmake build system
computed rpaths by itself, but used a different order than
computed for the compiler wrapper. In fact it's not necessary
to compute rpaths at all, since we let cmake do that thanks to
CMAKE_INSTALL_RPATH_USE_LINK_PATH. This covers rpaths
for all dependencies. The only install rpaths we need to set are
<install prefix>/{lib,lib64}, which cmake unfortunately omits,
although it could also know these. Also, cmake does not
delete rpaths added by the toolchain (i.e. Spack's compiler
wrapper), so I don't think it should be controversial to simplify
things.