Preserving extension ownership in dump/restore/pg_upgrade

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