I agree re: your comment on hosted provider support. Extensibility is a major benefit of Postgres, and there is a lot more untapped potential (e.g. even just for custom data types), if only major providers supported it better.

FWIW, from having looked into this, there are two main challenges:

(1) Security - How to avoid the customer becoming superuser by using specifically crafted extensions. In practice this is mostly a defense in depth scenario, since you're running inside a VM already anyway.

(2) Maintenance / Support / SLA - If customers load a broken extension and that causes the server to crash, does the provider still give an SLA? Where to draw the line / how to evaluate this when support tickets get opened?

Both of these could be solved (I think) if there was a better sandboxing mechanism.

Really the issue here is that once you're at the C extension / shared library level, you can do pretty much anything, and thats really hard to support as a Database-as-a-service provider.

Right. Plus (which you sort of hint at under Maintenance), packaging of more elaborate extensions like PostGIS was a nightmare, especially with respect to upgrades (the situation is better now). And under Security, it's not just worrying about users trying to escape your sandbox: it's also making sure they don't leave themselves open to exploits from their users.