Re: Danger of automatic connection reset in psql

On Thu, Oct 20, 2016 at 3:58 PM, Oleksandr Shulgin
<oleksandr(dot)shulgin(at)zalando(dot)de> wrote:
> Hi Hackers!
>
> When using psql interactively one might be tempted to guard potentially
> destructive commands such as "UPDATE / DELETE / DROP <object>" by starting
> the input line with an explicit "BEGIN; ...". This has the added benefit
> that then you invoke the command by reverse-searching the command history,
> you get it together with the guarding transaction open statement.
>
> This, however, is not 100% safe as I've found out a few days ago. Should
> the connection to the server get lost, the first of semicolon-separated
> statements, "BEGIN;" will only trigger connection reset, and if that is
> successful the following command(s) are going to be executed on the newly
> opened connection, but without the transaction guard.
>
> I'm not the first one to discover that, a search in archives gives at least
> 3 results:
>
> https://www.postgresql.org/message-id/flat/1097174870(dot)9273(dot)8(dot)camel%40ipso(dot)snappymail(dot)ca#1097174870(dot)9273(dot)8(dot)camel(at)ipso(dot)snappymail(dot)ca
> https://www.postgresql.org/message-id/flat/4BF6A496.2090106%40comgate.c
> https://www.postgresql.org/message-id/flat/CAD3a31U%2BfSBsq%3Dtxw2G-D%2BfPND_UN-nSojrGyaD%2BhkYUzvxusQ%40mail.gmail.com
>
> The second one even resulted in a TODO item:
>
> Prevent psql from sending remaining single-line multi-statement queries
> after reconnection
>
> I was thinking that simply adding a bool flag in the pset struct, to
> indicate that connection was reset during attempt to execute the last query
> would do the trick, but it only helps in exactly the case described above.
>
> Since this is already an improvement, I'm attaching a patch.
>

A couple of doubts/suggestions:
1. Should pset.conn_was_reset be set to false, when we make a
connection in do_connection()? Usually pset.conn_was_reset would be
initialised with 0 since it's a global variable, so this may not be
necessary. But as a precaution should we do this?

2. Comment below should be updated to reflect the new change
/* fall out of loop if lexer reached EOL */
- if (scan_result == PSCAN_INCOMPLETE ||
+ if (pset.conn_was_reset ||
+ scan_result == PSCAN_INCOMPLETE ||

3. What happens when the connection is reset while the source is a
file say specified by \i in an interactive shell?

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company