Re: pg_upgrade improvements

Hi all,

I've written a pg_upgrade wrapper for upgrading our users (heroku) to
postgres 9.1. In the process I encountered a specific issue that could
easily be improved. We've had this process work consistently for many users
both internal and external, with the exception of just a few for whom the
process fails and required manual handholding.

Before it performs the upgrade, the pg_upgrade program starts the old
cluster, does various checks, and then attempts to stop it. On occasion
stopping the cluster fails - I've posted command output on a gist [1].
Manually running the pg_upgrade shortly afterwards succeeds. We believe
stopping the cluster times out because there are other connections to the
cluster that are established in that small window. There could be incoming
connections for a number of reasons: either the user or the user's
applications are reestablishing connections, or something like collectd on
the localhost attempts to connect during that small window.

Possible workarounds on the current version:

* Add an iptables rule to temporarily reject connections from the outside.
This is not viable because in a multitenant environment a process may write
an iptables rule, and meanwhile another process may permanently save rules,
including the temporary one. We can defend against that, but it does add a
lot of complexity.
* Rewrite pg_hba.conf temporarily while the pg_upgrade script runs to
disallow any other connections.

A possible solution for pg_upgrade is for it to make pg_upgrade use the
--force flag when stopping the cluster to kick connections out. There is no
reason to be polite in this case. Another idea that was kicked around with
my colleagues was to start the cluster in single-user mode, or only allow
unix socket connections somewhere in /tmp. Anything that rejects other
connections would be helpful.

It would also be nice if the invocation of pg_ctl didn't pipe its output to
/dev/null. I'm sure it would contain information that would directly point
at the root cause and could've saved some debugging and hand waving time.

Finally, just a note that while we haven't performed a huge number of
upgrades yet, we have upgraded a few production systems and for the most
part it has worked great.

Regards,

-Harold

[1] https://gist.github.com/112c97378c490d8f70fc

Harold,

* Harold Giménez (harold(dot)gimenez(at)gmail(dot)com) wrote:
> Possible workarounds on the current version:

This has actually been discussed before and unfortunately there aren't
any trivial solutions.

> * Rewrite pg_hba.conf temporarily while the pg_upgrade script runs to
> disallow any other connections.

This is probably my favorite, from a 'practical' and 'effective'
standpoint. I wonder if there'd be a way to specify the pg_hba.conf
to use on the command-line or in some other way, to avoid having to
actually modify the existing one (which would have to be 'unmodified'
after, of course..).

The other options would work, of course. Perhaps my second favorite
option (second because I think it'd be more challenging and invasive) is
making the PG daemon only listens on a unix socket (which is not where
the default unix socket is).

The single-user option *sounds* viable, but, iirc, it actually isn't due
to the limitations on what can be done in that mode.

> It would also be nice if the invocation of pg_ctl didn't pipe its output to
> /dev/null. I'm sure it would contain information that would directly point
> at the root cause and could've saved some debugging and hand waving time.

Agreed.

> Finally, just a note that while we haven't performed a huge number of
> upgrades yet, we have upgraded a few production systems and for the most
> part it has worked great.

Great!

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> The single-user option *sounds* viable, but, iirc, it actually isn't due
> to the limitations on what can be done in that mode.

Yeah. IMO the right long-term fix is to be able to run pg_dump and psql
talking to a standalone backend, but nobody's gotten round to making
that possible.

regards, tom lane

On Wed, Apr 4, 2012 at 11:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> The single-user option *sounds* viable, but, iirc, it actually isn't due
>> to the limitations on what can be done in that mode.
>
> Yeah.  IMO the right long-term fix is to be able to run pg_dump and psql
> talking to a standalone backend, but nobody's gotten round to making
> that possible.

Are you thinking about something like postgres --single
--port=PORT_NUMBER_OR_SOCKET_DIRECTORY?

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Apr 4, 2012 at 11:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Yeah. IMO the right long-term fix is to be able to run pg_dump and psql
>> talking to a standalone backend, but nobody's gotten round to making
>> that possible.

> Are you thinking about something like postgres --single
> --port=PORT_NUMBER_OR_SOCKET_DIRECTORY?

No, opening up a port is exactly what we *don't* want it to do.
Otherwise you're right back to worrying about how to make sure that
unwanted connections don't get in. Notions like private socket
directories don't solve this because we don't have that option
available on Windows.

The vague idea I had was for libpq to have a connection option that
would cause it to spawn a standalone backend as a child process and
communicate with that over two pipes, just like any popen'd process.
The backend would see this exactly like standalone mode now, except for
speaking FE/BE protocol over its stdin/stdout rather than the existing
ad-hoc user interface for standalone mode.

regards, tom lane

On Thursday, April 05, 2012 03:46:54 PM Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Wed, Apr 4, 2012 at 11:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Yeah. IMO the right long-term fix is to be able to run pg_dump and psql
> >> talking to a standalone backend, but nobody's gotten round to making
> >> that possible.
> >
> > Are you thinking about something like postgres --single
> > --port=PORT_NUMBER_OR_SOCKET_DIRECTORY?
>
> No, opening up a port is exactly what we *don't* want it to do.
> Otherwise you're right back to worrying about how to make sure that
> unwanted connections don't get in. Notions like private socket
> directories don't solve this because we don't have that option
> available on Windows.
I wonder if it wouldn't be better to pass a named pipe under windows and use a
AF_UNIX socket everwhere else. Both should be pretty easily usable with the
existing code. PG already seems to use named pipes under windows, so...

Andres

* Andres Freund (andres(at)anarazel(dot)de) wrote:
> I wonder if it wouldn't be better to pass a named pipe under windows and use a
> AF_UNIX socket everwhere else. Both should be pretty easily usable with the
> existing code. PG already seems to use named pipes under windows, so...

I didn't think Tom's suggestion was really all that difficult to
implement and sounds like a more-generally-useful change anyway (which
you might want to use outside of this specific use case).

Thanks,

Stephen

On Thursday, April 05, 2012 04:44:11 PM Stephen Frost wrote:
> * Andres Freund (andres(at)anarazel(dot)de) wrote:
> > I wonder if it wouldn't be better to pass a named pipe under windows and
> > use a AF_UNIX socket everwhere else. Both should be pretty easily usable
> > with the existing code. PG already seems to use named pipes under
> > windows, so...
>
> I didn't think Tom's suggestion was really all that difficult to
> implement and sounds like a more-generally-useful change anyway (which
> you might want to use outside of this specific use case).
Hm. Changing libpq to use two pipes at the same time sounds considerably more
invasive than basically just changing the socket creation and some minor
details.
Why would pipes be more useful? Its not like you could build useful pipelines
with them.

Also, it might open a window for implementing AF_UNIX like connections on
windows...

Andres

* Andres Freund (andres(at)anarazel(dot)de) wrote:
> Hm. Changing libpq to use two pipes at the same time sounds considerably more
> invasive than basically just changing the socket creation and some minor
> details.

It's not something we'd back-patch, but I don't believe it'd be very
difficult to implement..

> Why would pipes be more useful? Its not like you could build useful pipelines
> with them.

The point is to avoid the risk that someone else could connect to the
database at the same time you're doing work on it.

> Also, it might open a window for implementing AF_UNIX like connections on
> windows...

That's an unrelated discussion, imv..

Stephen

Hi,

Not sure if were just missing each others point?

On Thursday, April 05, 2012 05:20:04 PM Stephen Frost wrote:
> > Why would pipes be more useful? Its not like you could build useful
> > pipelines with them.
>
> The point is to avoid the risk that someone else could connect to the
> database at the same time you're doing work on it.
I got that. I just fail to see what the advantage of using two pipes instead
of one socket as every other plain connection would be?

Using named pipes solves that tidbit from Tom:
> Notions like private socket directories don't solve this because we don't
> have that option available on Windows.
If you have named pipes or AF_UNIX sockets you can solve that by either just
passing the fd to your child and not allowing any access to it (no problem on
either platform) or by using a private directory.

Andres

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Andres Freund (andres(at)anarazel(dot)de) wrote:
>> Why would pipes be more useful? Its not like you could build useful pipelines
>> with them.

> The point is to avoid the risk that someone else could connect to the
> database at the same time you're doing work on it.

Right. Unless I misunderstand the semantics of named pipes on Windows,
we don't want that because in principle some unrelated process could
connect to it as soon as you set it up.

regards, tom lane

Andres Freund <andres(at)anarazel(dot)de> writes:
>> The point is to avoid the risk that someone else could connect to the
>> database at the same time you're doing work on it.

> I got that. I just fail to see what the advantage of using two pipes instead
> of one socket as every other plain connection would be?

Yeah, that would be a small pain in the neck, but it eliminates a huge
pile of practical difficulties, like your blithe assumption that you can
find a "private directory" somewhere (wrong) or disallow access to other
people (also wrong, if they are using the same account as you).

The short answer is that sockets and named pipes are *meant* to be
publicly accessible. Guaranteeing that they are not is a difficult
task full of possibilities for security holes.

regards, tom lane

On Thursday, April 05, 2012 05:39:19 PM Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> >> The point is to avoid the risk that someone else could connect to the
> >> database at the same time you're doing work on it.
> >
> > I got that. I just fail to see what the advantage of using two pipes
> > instead of one socket as every other plain connection would be?
>
> Yeah, that would be a small pain in the neck, but it eliminates a huge
> pile of practical difficulties, like your blithe assumption that you can
> find a "private directory" somewhere (wrong) or disallow access to other
> people (also wrong, if they are using the same account as you).
I don't think this needs to protect against malicious intent of a user running
with the *same* privileges as the postmaster. That one can simply delete the
whole cluster anyway. For everybody else you can just create a directory in
PGDATA and revoke all permissions on it for everybody but the owner.
For named pipes you could just create a random name with permissions only for
the current user (thats possible in the same call) and be done with it.

Andres

Andres Freund <andres(at)anarazel(dot)de> writes:
> On Thursday, April 05, 2012 05:39:19 PM Tom Lane wrote:
>> Yeah, that would be a small pain in the neck, but it eliminates a huge
>> pile of practical difficulties, like your blithe assumption that you can
>> find a "private directory" somewhere (wrong) or disallow access to other
>> people (also wrong, if they are using the same account as you).

> I don't think this needs to protect against malicious intent of a user running
> with the *same* privileges as the postmaster.

Who said anything about malicious intent? Please re-read the original
gripe in this thread. There's nothing un-legitimate about, eg, clients
trying to connect to the database during your maintenance window.

What we want is to be sure that nobody can connect to the database
except the person running the standalone instance. To my mind "sure"
means "sure"; it does not include qualifiers like "unless some
other process tries to do the same thing at about the same time".

regards, tom lane

On Thursday, April 05, 2012 06:12:48 PM Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On Thursday, April 05, 2012 05:39:19 PM Tom Lane wrote:
> >> Yeah, that would be a small pain in the neck, but it eliminates a huge
> >> pile of practical difficulties, like your blithe assumption that you can
> >> find a "private directory" somewhere (wrong) or disallow access to other
> >> people (also wrong, if they are using the same account as you).
> >
> > I don't think this needs to protect against malicious intent of a user
> > running with the *same* privileges as the postmaster.
>
> Who said anything about malicious intent? Please re-read the original
> gripe in this thread. There's nothing un-legitimate about, eg, clients
> trying to connect to the database during your maintenance window.
Yes, there is not. But those clients won't connect to a socket in some
directory thats created extra for this purpose which they don't even know the
name of. Scanning the directory tree for that would require malicious intent.

> What we want is to be sure that nobody can connect to the database
> except the person running the standalone instance. To my mind "sure"
> means "sure"; it does not include qualifiers like "unless some
> other process tries to do the same thing at about the same time".
Its not like creating a file/directory/pipename with a random name and
retrying if it already exists is an especially hard thing. That does make
*sure* it does not happen by accident. Beside the fact that single run backend
should already make sure were not running concurrently. So even a *fixed*
atomically created filename name should be enough if its not the regular name
and in a place thats not accessible from the outside.

Anyway, I don't think those reasons are sensible, but I don't care enough to
argue further.

Andres

On Thu, Apr 5, 2012 at 17:34, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> * Andres Freund (andres(at)anarazel(dot)de) wrote:
>>> Why would pipes be more useful? Its not like you could build useful pipelines
>>> with them.
>
>> The point is to avoid the risk that someone else could connect to the
>> database at the same time you're doing work on it.
>
> Right.  Unless I misunderstand the semantics of named pipes on Windows,
> we don't want that because in principle some unrelated process could
> connect to it as soon as you set it up.

By default it does, and that's what we're using it for (pg_ctl kill).
That can be handled with permissions and counters, I think.

But more importantly in this case, you cannot communicate across a
named pipe on windows with the normal socket calls send() and recv().
You have to use the Windows API file calls like ReadFile() and
WriteFile(). So it would require some rather major surgery to use
that.

This is why the pgpipe implementation we had used a socketpair instead
of named pipes.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On ons, 2012-04-04 at 19:26 -0700, Harold Giménez wrote:
> It would also be nice if the invocation of pg_ctl didn't pipe its
> output to /dev/null. I'm sure it would contain information that would
> directly point at the root cause and could've saved some debugging and
> hand waving time.

This aspect has been reworked in 9.2devel. Check it out to see if it
works better for you.

On Sat, Apr 07, 2012 at 01:13:23PM +0300, Peter Eisentraut wrote:
> On ons, 2012-04-04 at 19:26 -0700, Harold Giménez wrote:
> > It would also be nice if the invocation of pg_ctl didn't pipe its
> > output to /dev/null. I'm sure it would contain information that would
> > directly point at the root cause and could've saved some debugging and
> > hand waving time.
>
> This aspect has been reworked in 9.2devel. Check it out to see if it
> works better for you.

Yes, we now spit out 5 log files if pg_upgrade fails, and tell you which
one to look at.

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

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

On Wed, Apr 04, 2012 at 07:26:58PM -0700, Harold Giménez wrote:
> Hi all,
>
> I've written a pg_upgrade wrapper for upgrading our users (heroku)
> to postgres 9.1. In the process I encountered a specific issue that
> could easily be improved. We've had this process work consistently
> for many users both internal and external, with the exception of just
> a few for whom the process fails and required manual handholding.
>
> Before it performs the upgrade, the pg_upgrade program starts the
> old cluster, does various checks, and then attempts to stop it. On
> occasion stopping the cluster fails - I've posted command output
> on a gist [1]. Manually running the pg_upgrade shortly afterwards
> succeeds. We believe stopping the cluster times out because there
> are other connections to the cluster that are established in that
> small window. There could be incoming connections for a number of
> reasons: either the user or the user's applications are reestablishing
> connections, or something like collectd on the localhost attempts to
> connect during that small window.

Well, we did address this in PG 9.2 by having pg_upgrade use a
non-default port number when starting servers, 50432. You can do that
too in PG 9.1 by just specifying non-default port numbers when you run
pg_upgrade. We do start the server with a special --binary-upgrade
mode, and we could do all sorts of connection limits in that mode, but
having the port number be different seemed the clearest solution.

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

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

On Wed, Apr 11, 2012 at 5:40 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Wed, Apr 04, 2012 at 07:26:58PM -0700, Harold Giménez wrote:
> > There could be incoming connections for a number of
> > reasons: either the user or the user's applications are reestablishing
> > connections, or something like collectd on the localhost attempts to
> > connect during that small window.
>
> Well, we did address this in PG 9.2 by having pg_upgrade use a
> non-default port number when starting servers, 50432. You can do that
> too in PG 9.1 by just specifying non-default port numbers when you run
> pg_upgrade. We do start the server with a special --binary-upgrade
> mode, and we could do all sorts of connection limits in that mode, but
> having the port number be different seemed the clearest solution.
>

The non-default port number is a good solution, better than modifying
pg_hba.
Thanks for pointing that out!

-Harold