Re: pg_upgrade using appname to lock out other users

Excerpts from Robert Haas's message of mié jun 15 08:45:21 -0400 2011:

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

Seems good, except that passing the password as a command line argument
is obviously broken from a privacy perspective -- anyone could see the
process list and get it.  Maybe have postmaster ask for it on startup
somehow, or have pg_upgrade write it in a file which is read by
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.

Agreed on both counts ... but ... does this mean that we need a
different program for programmable tasks as opposed to interactive
ones?  Dealing with standalone backends *is* a pain, that's for sure.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support