i can confirm this issue on pixel 9a. I have 3 profiles, owner, public and private. Owner has Argon sound for notification and alarm. But in secondary profiles, it doesn't matter which sound you set as default, it always plays castor sound even for alarms.
For ringtones, I was able to solve the issue with setting a ringtone in owner profile and setting to to none in secondary profiles.
I tired the same with normal notifications, didn't work