Re: pg_upgrade using appname to lock out other users

Bruce Momjian wrote:
> Bruce Momjian wrote:
> > I meant the PGPASSWORD environment variable:
> >
> > <indexterm>
> > <primary><envar>PGPASSWORD</envar></primary>
> > </indexterm>
> > <envar>PGPASSWORD</envar> behaves the same as the <xref
> > linkend="libpq-connect-password"> connection parameter.
> > Use of this environment variable
> > is not recommended for security reasons, as some operating systems
> > allow non-root users to see process environment variables via
> > <application>ps</>; instead consider using the
> > <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
> >
> > The only other way to do this is to pass it on the command line, but
> > some options don't allow that (pg_ctl), and PGPASSFILE is going to
> > require me to create a dummy .pgpass password file in a valid format and
> > use that.
>
> One interesting idea would be to write the server password in the
> PGPASSFILE format, and allow the server and libpq to read the same file
> by pointing PGPASSFILE at that file.

Discussion seems to have ended on this thread without a clear direction.
Let me throw out some new ideas.

The advantage of writing a password file is that we can avoid the need
to modify pg_hba.conf to allow access without supplying a password. The
downside is that it will only apply to PG 9.2+ servers, making
configuration even more complex because the new and old servers would
behave differently.

One simple improvement would be to set listen_addresses to '' on Unix
and 'localhost' on Windows to limit who can connect. This works on old
and new servers. PG 9.1 already allows only super-user connections in
-b binary-upgrade mode.

No one seemed to like the appname filter but it seemed like a cheap
solution. Remember we are not trying to secure the server while in
pg_upgrade mode --- only avoid accidental connections.

And, again, using a default port number of 25432 will work for old/new
servers.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +