It seems to me that it'd be a good idea to have a form of SET SESSION
AUTHORIZATION that restores the effective session ID to whatever user
name was originally authenticated.
In particular, this would eliminate the need for hacks like pg_dump's
need to know a superuser name for certain things (-S switch).
Instead, the convention could be to revert to the original username,
which is expected to be a superuser if you are using the options that
require it. (In this way, the superuser name can be determined when
loading the script, rather than having to hardwire it into the script
with consequent loss of cross-installation portability.)
The syntax that comes to mind offhand isSET SESSION AUTHORIZATION DEFAULT;
but perhaps someone has a better idea.
Comments, objections?
BTW, I think it's well past time to make pg_dump use SET SESSION
AUTHORIZATION by default, or even always, instead of \connect commands.
Thoughts?
regards, tom lane