Re: pg_upgrade using appname to lock out other users - Mailing list pgsql-hackers

On Wed, Jun 15, 2011 at 8:05 AM, Bruce Momjian <bruce@momjian.us> wrote:
> Bruce Momjian wrote:
>> Tom Lane wrote:
>> > Bruce Momjian <bruce@momjian.us> writes:
>> > > You might remember we added a postmaster/postgres -b switch to indicate
>> > > binary upgrade mode.  The attached patch prevents any client without an
>> > > application_name of 'binary-upgrade' from connecting to the cluster
>> > > while it is binary upgrade mode.  This helps prevent unauthorized users
>> > > from connecting during the upgrade.  This will not help for clusters
>> > > that do not have the -b flag, e.g. pre-9.1.
>> >
>> > > Does this seem useful?
>> >
>> > No ... that seems like a kluge.  It's ugly and it's leaky.
>> >
>> > What we really ought to be doing here is fixing things so that
>> > pg_upgrade does not need to have a running postmaster in either
>> > installation, but works with some variant of standalone mode.
>> > That would actually be *safe* against concurrent connections,
>> > rather than only sorta kinda maybe safe.
>>
>> I keep replying to that suggestion by reminding people that pg_upgrade
>> relies heavily on psql features, as does pg_dumpall, and recoding that
>> in the backend will be error-prone.
>
> Also, a standalone backend does not have libpq either so how do you get
> values into application variables?  Parse the text output?  That seems
> like a much larger kludge.

Maybe we could do something like this.

1. pg_upgrade invokes the postmaster with --binary-upgrade=<port>:<password>

2. postmaster starts up into multi-user mode, but it does not start
autovacuum and ignores pg_hba.conf, listen_addresses, and port.
Instead it listens only on the localhost interface on the designated
port (perhaps the port can be a filename on systems that support UNIX
sockets, and it can listen only on a UNIX socket at that location
instead).  It refuses all connections except for those that attempt to
log in with binary_upgrade as the user and the given password as the
password.  pg_upgrade will randomly generate a password (like
C51622FA-7513-4300-A4B7-6423769276F8) and port number at the start of
each run, and use that for all connections to the postmaster.

As a separate issue, I tend to agree with Tom that using psql as part
of the pg_upgrade process is a lousy idea and we need a better
solution.  But let's fix one thing at a time.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company