Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE

On Dec13, 2010, at 16:40 , Tom Lane wrote:
> Florian Pflug <fgp(at)phlo(dot)org> writes:
>> On Dec13, 2010, at 00:16 , Robert Haas wrote:
>>> And in fact it strikes me that we might not have much choice about how
>>> to fix this. I think we are not going to retroactively change the
>>> behavior of ALTER DATABASE .. SET ROLE in a released version, but yet
>>> we do, I think, want to make pg_upgrade work.
>
>> A simple fix is to teach pg_upgrade to issue "RESET SESSION
> AUTHORIZATION" immediately after connecting to a database.
>
> How is that a fix? RESET is defined to reset to the state you get
> immediately after connection. Including anything established by those
> ALTER commands.

I thought so too until yesterday when I tried it (the database "db"
has ROLE set to "db").

fgp(at)master:~$ psql db
psql (9.0.1)

db=> select session_user, current_user;
session_user | current_user
--------------+----------------
fgp | db
(1 row)

db=> reset session authorization;
RESET

db=> select session_user, current_user;
session_user | current_user
--------------+--------------
fgp | fgp
(1 row)

The manual agrees with this behaviour, it states

"The session user identifier is initially set to be the (possibly authenticated) user name provided by the client."

and then goes on to explain

"The current user identifier is normally equal to the session user identifier, but might change temporarily in the context of SECURITY DEFINER functions and similar mechanisms; it can also be changed by SET ROLE."

best regards,
Florian Pflug