Moreover, the use case you've sketched (ie, change ownership of all objects inside a database) doesn't actually have anything to do with following dependencies. It's a lot closer to REASSIGN OWNED ... in fact, it's not clear to me why REASSIGN OWNED doesn't solve that use-case already.
Sometimes I hit the following. You have created a database and schema inside it from the superuser (i.e. postgres). Than you want to change ownership of whole database to another user (i.e. alice), but only this database, not all other objects in all other databases. It seems that REASSIGN OWNED doesn’t solve this already.
So essentially you want to change all the objects in the database except those that were created together with the database itself (i.e. those that were copied from the template database).
Yes. Without such syntax it is now done in a really awful way now, i.e. [0].
That seems a reasonable use-case, but I'm not sure that this ALTER .. OWNER CASCADE is the right thing for that -- What object would you start with? Each schema other than pg_catalog, pg_toast, information_schema? As I recall, the problem is that REASSIGN OWNED refuses to work on pinned objects. Maybe what you want is something like REASSIGN OWNED BY xyz IN SCHEMA public TO xyzxxz i.e., an extension of the current REASSIGN OWNED BY command?
Well, I don’t know what syntax and implementation would be correct. I just want to give a specific user all rights to manage all objects in a specific database (which was created from postgres user earlier). It would be really useful.