Thread: Reverting SET SESSION AUTHORIZATION command

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

Tom Lane writes:

> The syntax that comes to mind offhand is
>     SET SESSION AUTHORIZATION DEFAULT;
> but perhaps someone has a better idea.

My first thought had been RESET SESSION ... but both are fine, and they're
equivalent for all the other SET commands.

> 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.

Well, the tradeoff is "restoring user has to be superuser" vs "restoring
user has to know everyone's authentication secrets".  The former is
obviously more likely.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
>> 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.

> Well, the tradeoff is "restoring user has to be superuser" vs "restoring
> user has to know everyone's authentication secrets".  The former is
> obviously more likely.

Hmm, good point.  But we could ease this if the system allowed
"SET SESSION AUTHORIZATION <myself>" to non-superusers.  Then a
pg_dump of your own stuff would still restore without trouble.
A pg_dump of multiple people's stuff is superuser territory anyway.
        regards, tom lane

> 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?

\connect has _hack_ written all over it.  If we can eliminate it, it
would be a good thing.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Tom Lane wrote:
> 
> The syntax that comes to mind offhand is
>         SET SESSION AUTHORIZATION DEFAULT;
> but perhaps someone has a better idea.
> 

Tom,

The SQL standard has already defined what should go there.
That would be CURRENT_USER, so you would have:

SET SESSION AUTHORIZATION CURRENT_USER

Note that CURRENT_USER is _not_ to be affected by a 
It remains the same user that was used for the connection.

The SESSION_USER is the one that changes if you issue a SET 
SESSION AUTHORIZATION (otherwise is the same as the CURRENT_USER).

P.S.: I did not name those things.  Complains to the SQL std committee
:-)

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Fernando Nasser writes:

> The SQL standard has already defined what should go there.
> That would be CURRENT_USER, so you would have:
>
> SET SESSION AUTHORIZATION CURRENT_USER

I don't think so.  SET SESSION AUTHORIZATON sets the current user, so the
identity of the original current user is lost (or at least it's not
available through the CURRENT_USER function).

-- 
Peter Eisentraut   peter_e@gmx.net