You could sometimes get there. The problem is in an effort to simplify the getting-started with postgres (or other databases) most projects assume they would also automate the management of that postgres instance.

The getting started would be a lot different if it first instructed you to install postgres, run a script or create a database and users and configure access controls. Also certain things become easier if you assume you can also manage the postgres instance, like on an upgrade, maybe you just create a completely new database and drop the old one, or isolate tenants within one application into different databases, etc.

In general, I agree with OP that sqlite is all you need for selfhosted applications (most of the time at least)