Yet another failure mode in pg_upgrade

On Mon, Aug 13, 2012 at 4:34 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I've been experimenting with moving the Unix socket directory to
/var/run/postgresql for the Fedora distribution (don't ask :-().
It's mostly working, but I found out yet another way that pg_upgrade
can crash and burn: it doesn't consider the possibility that the
old or new postmaster is compiled with a different default
unix_socket_directory than what is compiled into the libpq it's using
or that pg_dump is using.

This is another hazard that we could forget about if we had some way for
pg_upgrade to run standalone backends instead of starting a postmaster.

Yeah, that would be nice.

But in the meantime, I suggest it'd be a good idea for pg_upgrade to
explicitly set unix_socket_directory (or unix_socket_directories in
HEAD) when starting the postmasters, and also explicitly set PGHOST
to ensure that the client-side code plays along.

That sounds like a good idea for other reasons as well - manual
connections attempting to get in during an upgrade will just fail with
a "no connection" error, which makes sense...

So, +1.

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

On Mon, Aug 13, 2012 at 12:46:43PM +0200, Magnus Hagander wrote:

On Mon, Aug 13, 2012 at 4:34 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I've been experimenting with moving the Unix socket directory to
/var/run/postgresql for the Fedora distribution (don't ask :-().
It's mostly working, but I found out yet another way that pg_upgrade
can crash and burn: it doesn't consider the possibility that the
old or new postmaster is compiled with a different default
unix_socket_directory than what is compiled into the libpq it's using
or that pg_dump is using.

This is another hazard that we could forget about if we had some way for
pg_upgrade to run standalone backends instead of starting a postmaster.

Yeah, that would be nice.

But in the meantime, I suggest it'd be a good idea for pg_upgrade to
explicitly set unix_socket_directory (or unix_socket_directories in
HEAD) when starting the postmasters, and also explicitly set PGHOST
to ensure that the client-side code plays along.

That sounds like a good idea for other reasons as well - manual
connections attempting to get in during an upgrade will just fail with
a "no connection" error, which makes sense...

So, +1.

OK, I looked this over, and I have a patch, attached.

Because we are already playing with socket directories, this patch creates
the socket files in the current directory for upgrades and non-live
checks, but not live checks. This eliminates the "someone accidentally
connects" problem, at least on Unix, plus we are using port 50432
already. This also turns off TCP connections for unix domain socket
systems.

For "live check" operation, you are checking a running server, so
assuming the socket is in the current directory is not going to work.
What the code does is to read the 5th line from the running server's
postmaster.pid file, which has the socket directory in PG >= 9.1. For
pre-9.1, pg_upgrade uses the compiled-in defaults for socket directory.
If the defaults are different between the two servers, the new binaries,
e.g. pg_dump, will not work. The fix is for the user to set pg_upgrade
-O to match the old socket directory, and set PGHOST before running
pg_upgrade. I could not find a good way to generate a proper error
message because we are blind to the socket directory in pre-9.1.
Frankly, this is a problem if the old pre-9.1 server is running in a
user-configured socket directory too, so a documentation addition seems
right here.

So, in summary, this patch moves the socket directory to the current
directory all but live check operation, and handles different socket
directories for old cluster >= 9.1. I have added a documentation
mention of how to make this work for for pre-9.1 old servers.

Thus completes another "surgery on a moving train" that is pg_upgrade
development.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Sat, Sep 1, 2012 at 11:45:58AM -0400, Bruce Momjian wrote:

So, in summary, this patch moves the socket directory to the current
directory all but live check operation, and handles different socket
directories for old cluster >= 9.1. I have added a documentation
mention of how to make this work for for pre-9.1 old servers.

Thus completes another "surgery on a moving train" that is pg_upgrade
development.

Oh, one more thing. We have talked about creating some special pipe for
pg_upgrade to communicate the a backend directly, but live check mode
hightlights that we will _still_ need traditional connection abilities
even if we add the pipe ability.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:

On Sat, Sep 1, 2012 at 11:45:58AM -0400, Bruce Momjian wrote:

So, in summary, this patch moves the socket directory to the current
directory all but live check operation, and handles different socket
directories for old cluster >= 9.1. I have added a documentation
mention of how to make this work for for pre-9.1 old servers.

Thus completes another "surgery on a moving train" that is pg_upgrade
development.

Oh, one more thing. We have talked about creating some special pipe for
pg_upgrade to communicate the a backend directly, but live check mode
hightlights that we will _still_ need traditional connection abilities
even if we add the pipe ability.

So? By definition, the live check mode is not guaranteed to produce
correct answers, since other connections could be changing the
database's contents. The problem we are interested in solving here is
preventing other connections from occurring when we're doing the upgrade
"for real". All this stuff with moving sockets around is nothing but
security by obscurity; it cannot positively guarantee that there's
nobody else connecting to the database while pg_upgrade runs. (Most
notably, on Windows there's no guarantee at all.)

regards, tom lane

Bruce Momjian <bruce@momjian.us> writes:

+ 	/*
+ 	 *	Report the Unix domain socket directory location to the postmaster.
+ 	 */

"Report" seems entirely the wrong verb there.

+ #define LISTEN_STR	" -c listen_addresses=''"
+ 
+ 	/* Have a sockdir to use? */
+ 	if (strlen(os_info.sockdir) != 0)
+ 		snprintf(socket_string, sizeof(socket_string) - strlen(LISTEN_STR),
+ 			" -c %s='%s'",
+ 			(GET_MAJOR_VERSION(cluster->major_version) < 903) ?
+ 				"unix_socket_directory" : "unix_socket_directories",
+ 			os_info.sockdir);
+ 	
+ 	/* prevent TCP/IP connections */
+ 	strcat(socket_string, LISTEN_STR);

IMO this would be simpler and more readable if you got rid of the LISTEN_STR
#define and just included -c listen_addresses='' in the snprintf format
string. The comment for the whole thing should be something like
"If we have a socket directory to use, command the postmaster to use it,
and disable TCP/IP connections altogether".

regards, tom lane

On Sat, Sep 1, 2012 at 02:23:22PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

+ 	/*
+ 	 *	Report the Unix domain socket directory location to the postmaster.
+ 	 */

"Report" seems entirely the wrong verb there.

+ #define LISTEN_STR	" -c listen_addresses=''"
+ 
+ 	/* Have a sockdir to use? */
+ 	if (strlen(os_info.sockdir) != 0)
+ 		snprintf(socket_string, sizeof(socket_string) - strlen(LISTEN_STR),
+ 			" -c %s='%s'",
+ 			(GET_MAJOR_VERSION(cluster->major_version) < 903) ?
+ 				"unix_socket_directory" : "unix_socket_directories",
+ 			os_info.sockdir);
+ 	
+ 	/* prevent TCP/IP connections */
+ 	strcat(socket_string, LISTEN_STR);

IMO this would be simpler and more readable if you got rid of the LISTEN_STR
#define and just included -c listen_addresses='' in the snprintf format
string. The comment for the whole thing should be something like
"If we have a socket directory to use, command the postmaster to use it,
and disable TCP/IP connections altogether".

Well, you only want the unix_socket* if sockdir is defined, but you want
LISTEN_STR unconditionally, even if there is no sockdir. Not sure how
that could cleanly be in a single snprintf.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:

Well, you only want the unix_socket* if sockdir is defined, but you want
LISTEN_STR unconditionally, even if there is no sockdir.

Really? What will happen when the installation's default is to not
listen on any Unix socket? (unix_socket_directories = '' in
postgresql.conf.)

I'm inclined to think that the "no sockdir" case is broken and you
should get rid of it. If you're starting a postmaster, you can and
should tell it a sockdir, period. If you're running a live check this
code is all moot anyway.

regards, tom lane

On Sat, Sep 1, 2012 at 02:18:59PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Sat, Sep 1, 2012 at 11:45:58AM -0400, Bruce Momjian wrote:

So, in summary, this patch moves the socket directory to the current
directory all but live check operation, and handles different socket
directories for old cluster >= 9.1. I have added a documentation
mention of how to make this work for for pre-9.1 old servers.

Thus completes another "surgery on a moving train" that is pg_upgrade
development.

Oh, one more thing. We have talked about creating some special pipe for
pg_upgrade to communicate the a backend directly, but live check mode
hightlights that we will _still_ need traditional connection abilities
even if we add the pipe ability.

So? By definition, the live check mode is not guaranteed to produce
correct answers, since other connections could be changing the
database's contents. The problem we are interested in solving here is

True.

preventing other connections from occurring when we're doing the upgrade
"for real". All this stuff with moving sockets around is nothing but
security by obscurity; it cannot positively guarantee that there's
nobody else connecting to the database while pg_upgrade runs. (Most
notably, on Windows there's no guarantee at all.)

My point is that we are still going to need traditional connections for
live checks. If we could find a solution for Windows, the socket in
current directory might be enough to lock things down, especially if we
put the socket in a new subdirectory that only we can read/write to.
Should I persue that in my patch?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Sat, Sep 1, 2012 at 02:43:35PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

Well, you only want the unix_socket* if sockdir is defined, but you want
LISTEN_STR unconditionally, even if there is no sockdir.

Really? What will happen when the installation's default is to not
listen on any Unix socket? (unix_socket_directories = '' in
postgresql.conf.)

Well, don't do that then. Locking out TCP seems like a big win.

I'm inclined to think that the "no sockdir" case is broken and you
should get rid of it. If you're starting a postmaster, you can and
should tell it a sockdir, period. If you're running a live check this
code is all moot anyway.

I don't think you understand. The "no sockdir" case is only for live
checks of pre-9.1 old servers, because we can't find the socket
directory being used. Everything else uses the local directory for the
socket. If we remove that case, we can't do live checks on pre-9.1
servers.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:

My point is that we are still going to need traditional connections for
live checks.

Yes, but that's not terribly relevant, IMO. All it means is that we
don't want to invent some solution that doesn't go through libpq.

If we could find a solution for Windows, the socket in
current directory might be enough to lock things down, especially if we
put the socket in a new subdirectory that only we can read/write to.

Who is "we"? Somebody else logged in under the postgres userid could
still connect.

Should I persue that in my patch?

I think this is just a band-aid, and we shouldn't be putting more
effort into it than needed to ensure that unexpected configuration
settings won't break it. The right fix is a better form of
standalone-backend mode. Maybe I will go pursue that, since nobody
else seems to want to.

regards, tom lane

Bruce Momjian <bruce@momjian.us> writes:

On Sat, Sep 1, 2012 at 02:43:35PM -0400, Tom Lane wrote:

I'm inclined to think that the "no sockdir" case is broken and you
should get rid of it. If you're starting a postmaster, you can and
should tell it a sockdir, period. If you're running a live check this
code is all moot anyway.

I don't think you understand. The "no sockdir" case is only for live
checks of pre-9.1 old servers, because we can't find the socket
directory being used. Everything else uses the local directory for the
socket. If we remove that case, we can't do live checks on pre-9.1
servers.

If it's a live check, then (a) you aren't restarting the postmaster,
and (b) you wouldn't want to lock out TCP anyway. So adding
--listen-addresses to the string seems pointless and/or wrong.

regards, tom lane

On Sat, Sep 1, 2012 at 03:06:57PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Sat, Sep 1, 2012 at 02:43:35PM -0400, Tom Lane wrote:

I'm inclined to think that the "no sockdir" case is broken and you
should get rid of it. If you're starting a postmaster, you can and
should tell it a sockdir, period. If you're running a live check this
code is all moot anyway.

I don't think you understand. The "no sockdir" case is only for live
checks of pre-9.1 old servers, because we can't find the socket
directory being used. Everything else uses the local directory for the
socket. If we remove that case, we can't do live checks on pre-9.1
servers.

If it's a live check, then (a) you aren't restarting the postmaster,
and (b) you wouldn't want to lock out TCP anyway. So adding
--listen-addresses to the string seems pointless and/or wrong.

What about the new server? That is still started and stopped. You are
right that this code is never going to be called for the check of a
running old server.

Let's walk through the options:

non-live check:
uses current directory, start/stop old/new servers

live check, old server >= 9.1:
only new server started/stopped, new server uses old server's
socket directory and PGHOST set so clients use the same directory

live check, old server < 9.1:
only new server started/stopped, old/new servers use their
default/configured socket directory

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Sat, Sep 1, 2012 at 03:05:01PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

My point is that we are still going to need traditional connections for
live checks.

Yes, but that's not terribly relevant, IMO. All it means is that we
don't want to invent some solution that doesn't go through libpq.

If we could find a solution for Windows, the socket in
current directory might be enough to lock things down, especially if we
put the socket in a new subdirectory that only we can read/write to.

Who is "we"? Somebody else logged in under the postgres userid could
still connect.

But they have to find the current directory to do that; seems unlikely.
They could kill -9 pg_upgrade too if they are the same user id.

Should I persue that in my patch?

I think this is just a band-aid, and we shouldn't be putting more
effort into it than needed to ensure that unexpected configuration
settings won't break it. The right fix is a better form of
standalone-backend mode. Maybe I will go pursue that, since nobody
else seems to want to.

I am worried that is going to be a complex solution to a very minor
problem. Also, how is that going to get backpatched?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Sat, Sep 1, 2012 at 11:45 AM, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Aug 13, 2012 at 12:46:43PM +0200, Magnus Hagander wrote:

On Mon, Aug 13, 2012 at 4:34 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I've been experimenting with moving the Unix socket directory to
/var/run/postgresql for the Fedora distribution (don't ask :-().
It's mostly working, but I found out yet another way that pg_upgrade
can crash and burn: it doesn't consider the possibility that the
old or new postmaster is compiled with a different default
unix_socket_directory than what is compiled into the libpq it's using
or that pg_dump is using.

This is another hazard that we could forget about if we had some way for
pg_upgrade to run standalone backends instead of starting a postmaster.

Yeah, that would be nice.

But in the meantime, I suggest it'd be a good idea for pg_upgrade to
explicitly set unix_socket_directory (or unix_socket_directories in
HEAD) when starting the postmasters, and also explicitly set PGHOST
to ensure that the client-side code plays along.

That sounds like a good idea for other reasons as well - manual
connections attempting to get in during an upgrade will just fail with
a "no connection" error, which makes sense...

So, +1.

OK, I looked this over, and I have a patch, attached.

Because we are already playing with socket directories, this patch creates
the socket files in the current directory for upgrades and non-live
checks, but not live checks. This eliminates the "someone accidentally
connects" problem, at least on Unix, plus we are using port 50432
already. This also turns off TCP connections for unix domain socket
systems.

For "live check" operation, you are checking a running server, so
assuming the socket is in the current directory is not going to work.
What the code does is to read the 5th line from the running server's
postmaster.pid file, which has the socket directory in PG >= 9.1. For
pre-9.1, pg_upgrade uses the compiled-in defaults for socket directory.
If the defaults are different between the two servers, the new binaries,
e.g. pg_dump, will not work. The fix is for the user to set pg_upgrade
-O to match the old socket directory, and set PGHOST before running
pg_upgrade. I could not find a good way to generate a proper error
message because we are blind to the socket directory in pre-9.1.
Frankly, this is a problem if the old pre-9.1 server is running in a
user-configured socket directory too, so a documentation addition seems
right here.

So, in summary, this patch moves the socket directory to the current
directory all but live check operation, and handles different socket
directories for old cluster >= 9.1. I have added a documentation
mention of how to make this work for for pre-9.1 old servers.

I don't think this is reducing the number of failure modes; it's just
changing it from one set of obscure cases to a slightly different set
of obscure cases.

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

On Sun, Sep 2, 2012 at 01:06:28AM -0400, Robert Haas wrote:

For "live check" operation, you are checking a running server, so
assuming the socket is in the current directory is not going to work.
What the code does is to read the 5th line from the running server's
postmaster.pid file, which has the socket directory in PG >= 9.1. For
pre-9.1, pg_upgrade uses the compiled-in defaults for socket directory.
If the defaults are different between the two servers, the new binaries,
e.g. pg_dump, will not work. The fix is for the user to set pg_upgrade
-O to match the old socket directory, and set PGHOST before running
pg_upgrade. I could not find a good way to generate a proper error
message because we are blind to the socket directory in pre-9.1.
Frankly, this is a problem if the old pre-9.1 server is running in a
user-configured socket directory too, so a documentation addition seems
right here.

So, in summary, this patch moves the socket directory to the current
directory all but live check operation, and handles different socket
directories for old cluster >= 9.1. I have added a documentation
mention of how to make this work for for pre-9.1 old servers.

I don't think this is reducing the number of failure modes; it's just
changing it from one set of obscure cases to a slightly different set
of obscure cases.

Tom reported problems with having old/new with different default socket
locations. This fixes that, and reduces the possibility of acciental
connections. What problems does this add?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Sat, Sep 1, 2012 at 02:35:06PM -0400, Bruce Momjian wrote:

On Sat, Sep 1, 2012 at 02:23:22PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

+ 	/*
+ 	 *	Report the Unix domain socket directory location to the postmaster.
+ 	 */

"Report" seems entirely the wrong verb there.

Fixed.

+ #define LISTEN_STR	" -c listen_addresses=''"
+ 
+ 	/* Have a sockdir to use? */
+ 	if (strlen(os_info.sockdir) != 0)
+ 		snprintf(socket_string, sizeof(socket_string) - strlen(LISTEN_STR),
+ 			" -c %s='%s'",
+ 			(GET_MAJOR_VERSION(cluster->major_version) < 903) ?
+ 				"unix_socket_directory" : "unix_socket_directories",
+ 			os_info.sockdir);
+ 	
+ 	/* prevent TCP/IP connections */
+ 	strcat(socket_string, LISTEN_STR);

IMO this would be simpler and more readable if you got rid of the LISTEN_STR
#define and just included -c listen_addresses='' in the snprintf format
string. The comment for the whole thing should be something like
"If we have a socket directory to use, command the postmaster to use it,
and disable TCP/IP connections altogether".

Well, you only want the unix_socket* if sockdir is defined, but you want
LISTEN_STR unconditionally, even if there is no sockdir. Not sure how
that could cleanly be in a single snprintf.

I restructured the code to add the listen_addresses string first,
allowing the removal of the #define, as Tom suggested. I also added
unix_socket_permissions=0700 to further restrict socket access.

Updated patch attached.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:

On Sun, Sep 2, 2012 at 01:06:28AM -0400, Robert Haas wrote:

I don't think this is reducing the number of failure modes; it's just
changing it from one set of obscure cases to a slightly different set
of obscure cases.

Tom reported problems with having old/new with different default socket
locations. This fixes that, and reduces the possibility of acciental
connections. What problems does this add?

I'm going to be needing some fix in this area in any case, though
whether it's exactly Bruce's current patch is not clear to me. I found
out last night while making a test build of 9.2rc1 as a Fedora package
that pg_upgrade's regression test fails in the Fedora build environment,
if the postmaster has been patched so that its default socket directory
is /var/run/postgresql. That happens because /var/run/postgresql
doesn't exist in the build environment (it is only going to exist once
the postgresql-server package is installed), so the postmaster fails
to start because it can't create a socket where it expects to.
I have a patch to pg_regress that instructs the temporary postmaster
to use /tmp as unix_socket_directory regardless of its built-in default,
so that "make check" works for the regular core and contrib regression
tests. However, that doesn't affect pg_upgrade's regression test case.

It looks rather messy to persuade pg_upgrade to do things differently
for regression testing and normal use, not to mention that it would make
the test even less representative of normal use. So I'm thinking that
we do need the pg_upgrade feature Bruce is suggesting of forcing the
socket directory to be the current directory. What's more, if that's
not back-patched to 9.2, I'm going to have to carry it as a Fedora patch
anyway.

Alternatively, I can prevent "make check" from testing pg_upgrade
(which is what I did so I could carry on with package testing).
I'd just as soon not ship it like that, though.

regards, tom lane

On Sun, Sep 2, 2012 at 01:13:52PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Sun, Sep 2, 2012 at 01:06:28AM -0400, Robert Haas wrote:

I don't think this is reducing the number of failure modes; it's just
changing it from one set of obscure cases to a slightly different set
of obscure cases.

Tom reported problems with having old/new with different default socket
locations. This fixes that, and reduces the possibility of acciental
connections. What problems does this add?

I'm going to be needing some fix in this area in any case, though
whether it's exactly Bruce's current patch is not clear to me. I found
out last night while making a test build of 9.2rc1 as a Fedora package
that pg_upgrade's regression test fails in the Fedora build environment,
if the postmaster has been patched so that its default socket directory
is /var/run/postgresql. That happens because /var/run/postgresql
doesn't exist in the build environment (it is only going to exist once
the postgresql-server package is installed), so the postmaster fails
to start because it can't create a socket where it expects to.
I have a patch to pg_regress that instructs the temporary postmaster
to use /tmp as unix_socket_directory regardless of its built-in default,
so that "make check" works for the regular core and contrib regression
tests. However, that doesn't affect pg_upgrade's regression test case.

It looks rather messy to persuade pg_upgrade to do things differently
for regression testing and normal use, not to mention that it would make
the test even less representative of normal use. So I'm thinking that
we do need the pg_upgrade feature Bruce is suggesting of forcing the
socket directory to be the current directory. What's more, if that's
not back-patched to 9.2, I'm going to have to carry it as a Fedora patch
anyway.

Alternatively, I can prevent "make check" from testing pg_upgrade
(which is what I did so I could carry on with package testing).
I'd just as soon not ship it like that, though.

Well, I don't know of any known problems with the patch. On the other
hand, I don't know our policy in pushing patches into RC releases at the
request of packagers.

If you want to stand behind the patch, it might be OK. I think that's
how we handle such requests --- someone has to put their neck out for
it. Fortunately the patch is not very large so is easy to review.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:

Updated patch attached.

[ looks at that for a bit... ] Now I see why you were on about that:
the method you used here requires both clusters to have the same socket
directory. Which is silly and unnecessary. Revised patch attached.

regards, tom lane