Raise http errors on requests calls where appropriate

```suggestion
        )
        r.raise_for_status()
        logger.debug(
```
guessing we want to retry if the request fails?

_Originally posted by @carlcsaposs-canonical in https://github.com/canonical/postgresql-operator/pull/1395#discussion_r2758865860_
            