Extension ownership and misuse of SET ROLE/SET SESSION AUTHORIZATION

pg_dump/restore fail to restore the ownership of an extension correctly:
in practice it'll always end up owned by whoever runs the restore
script. We've sort of averted our eyes from that up to now, because
it's not a big deal in a world where most extensions have to be
superuser-owned anyway. But I think it's no longer acceptable in a
world with trusted extensions. So I started looking into fixing that.

Meanwhile ...

pg_dump and pg_restore have a --role switch, which causes them
to attempt to SET ROLE to the specified user name at startup.

They also have a --use-set-session-authorization switch, which causes
them to use SET SESSION AUTHORIZATION before CREATE, rather than
ALTER OWNER after CREATE, to set the ownership of restored objects.
Obviously, those commands will be issued per-object.

Now, for pg_dump there's no real conflict because --role determines
what we send to the source database server, not what is put into the
dump output. But AFAICS, these two switches do not work together
in pg_restore. We'll send SET ROLE at the start of the restore but
it'll be immediately and permanently overridden by the first
SET SESSION AUTHORIZATION. Moreover, because SetSessionAuthorization
inspects the original (authenticated) user ID to decide if the command
is allowed, the SET ROLE doesn't help pass that permission check
even the first time.

Given the current behavior of SET ROLE and SET SESSION AUTHORIZATION,
I don't actually see any way that we could get these features to
play together. SET SESSION AUTHORIZATION insists on the originally
authenticated user being a superuser, so that the documented point of
--role (to allow you to start the restore from a not-superuser role)
isn't going to work. I thought about starting to use SET ROLE for
both purposes, but it checks whether you have role privilege based
on the session userid, so that a previous SET ROLE doesn't get you
past that check even if it was a successful SET ROLE to a superuser.

The quick-and-dirty answer is to disallow these switches from being
used together in pg_restore, and I'm inclined to think maybe we should
do that in the back branches.

But ... the reason I noticed this is that I don't see any way to
restore extension ownership correctly unless we use the SET SESSION
AUTHORIZATION technique. We don't have ALTER EXTENSION OWNER, and I'm
afraid that we never can have it now that we've institutionalized the
expectation that not all objects within an extension need have the
same owner --- that means ALTER EXTENSION OWNER could not know which
contained objects to change the owner of. So while it might be an
acceptable restriction that --role prevents use of
--use-set-session-authorization, it's surely not acceptable that
--role is unable to restore extensions correctly.

The outline of a fix that I'm considering is

(1) In the backend, allow SET ROLE to succeed if either the session
userid or the current userid is a member of the desired role. This
would mean that, given the use-case for --role that you are logging
into an account that can "SET ROLE postgres", it'd work to do

SET ROLE postgres;
SET ROLE anybody;
... create an object to be owned by anybody
SET ROLE postgres;
SET ROLE somebodyelse;
... create an object to be owned by somebodyelse
SET ROLE postgres;
... lather rinse repeat

(2) Adjust pg_dump/pg_restore so that instead of SET SESSION
AUTHORIZATION, they use SET ROLE pairs as shown above to control
object ownership, when not using ALTER OWNER. I'm not sure whether
to rename the --use-set-session-authorization switch ... it'd be
misleadingly named now, but there's backwards compatibility issues
if we change it. Or maybe keep it and invent a separate
--use-set-role switch, though that opens the door for lots of
confusion.

(3) Adjust pg_dump/pg_restore so that extension ownership is
always restored using SET ROLE, whether you gave that switch or not.

Having said that ... I can't find the discussion right now, but
I recall Peter or Stephen complaining recently about how SET ROLE
and SET SESSION AUTHORIZATION allow more than the SQL spec says
they should. Do we want to make successful restores dependent
on an even-looser definition of SET ROLE? If not, how might we
handle this problem without assuming non-SQL semantics?

Thoughts?

regards, tom lane