Currently, we fail to do $SUBJECT: post-restore, an extension will be owned by the role used to do the restore. This is because pg_dump does not consider that extensions have owners at all, so it doesn't issue ALTER EXTENSION OWNER TO, which is a command that the server doesn't have anyway.
We've not gotten complaints about this, AFAIR, suggesting it's only a minor problem in practice. Probably most extensions are superuser-owned anyway. However, we do theoretically support PL extensions that are owned by database owners, and if the "trustable extension" idea that I proposed yesterday[1] gets in, there might soon be a lot more cases of non-superuser-owned extensions. Moreover, although pg_upgrade will preserve the ownership of individual objects within the extension, a regular dump/restore will not. This means a database owner would lose the ability to adjust permissions on a PL after dump/restore, which seems like a problem. So I started thinking about how to fix it. The obvious way to fix it is to implement ALTER EXTENSION OWNER TO, but that has a big problem: what should we do with the ownership of the contained objects? Taking the example of one of the PL extensions, we'd like it to also change ownership of the procedural language object, but it *must not* give away ownership of the C-language handler functions, at least not if the ownership recipient is not a superuser. (A user who owns a C-language function can alter its properties to do almost anything.) There doesn't seem to be any way to distinguish these cases except with very ad-hoc, ugly, restrictive code. I considered proposing that we *only* change ownership of contained procedural language objects, but ick. That's ugly, and it would hobble the usefulness of the @extowner@ mechanism I proposed in [1] (although possibly that's not very useful for anything but PLs anyway?). Maybe, instead of @extowner@ as proposed (which is certainly just a quick-n-dirty mechanism), we could add some syntax that would explicitly identify objects whose ownership ought to track that of the extension. Seems like a lot of work though. Another idea, which is much uglier conceptually but seems like it could be done with not much code, is to teach pg_dump/pg_restore that it must use SET SESSION AUTHORIZATION to set the ownership of an extension even when it's using ALTER OWNER for everything else. The main drawback that I can think of is that if the target user lacks permissions to create the extension in the destination database, CREATE EXTENSION will fail (and then probably later restore commands will too), rather than leaving the extension in place with the wrong owner. I guess, if we're going to need custom restore code anyway, we could imagine solving that problem by emitting SET SESSION AUTHORIZATION joe; CREATE EXTENSION someextension; RESET SESSION AUTHORIZATION; CREATE EXTENSION IF NOT EXISTS someextension; but man is that ugly. Binary-upgrade mode has an independent problem: binary_upgrade_create_empty_extension has a hard-wired assumption that it should use GetUserId() for the extension owner. We could imagine fixing that by passing the owner role name as a separate argument; though if we go with the SET SESSION AUTHORIZATION solution for normal mode, I'm a bit inclined to use it for binary upgrade as well. I don't find any of these approaches terribly appealing. Thoughts, better ideas? regards, tom lane [1] https://www.postgresql.org/message-id/5889.1566415762%40sss.pgh.pa.us