A leading drug policy reform campaigner who championed safe consumption rooms has died at the age of 48.