pg_upgrade using appname to lock out other users
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? Something for 9.1 or 9.2?
This idea came from Andrew Dunstan via IRC during a pg_upgrade run by
Stephen Frost when some clients accidentally connected. (Stephen reran
pg_upgrade successfully.)
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:
/rtmp/appnametext/x-diffDownload+12-0
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.
regards, tom lane
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.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On 2011-06-15 05:01, Bruce Momjian wrote:
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? Something for 9.1 or 9.2?
This idea came from Andrew Dunstan via IRC during a pg_upgrade run by
Stephen Frost when some clients accidentally connected. (Stephen reran
pg_upgrade successfully.)
Couldn't the -b flag also imply a very strict hba.conf configuration, that
essentially only lets pg_upgrade in..?
--
Jesper
Jesper Krogh wrote:
On 2011-06-15 05:01, Bruce Momjian wrote:
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? Something for 9.1 or 9.2?
This idea came from Andrew Dunstan via IRC during a pg_upgrade run by
Stephen Frost when some clients accidentally connected. (Stephen reran
pg_upgrade successfully.)Couldn't the -b flag also imply a very strict hba.conf configuration, that
essentially only lets pg_upgrade in..?
Yes, it could. What rules would we use? We could prohibit non-local
connections.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
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.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
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
On 06/14/2011 11:01 PM, Bruce Momjian wrote:
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? Something for 9.1 or 9.2?
This idea came from Andrew Dunstan via IRC during a pg_upgrade run by
Stephen Frost when some clients accidentally connected. (Stephen reran
pg_upgrade successfully.)
What I actually had in mind was rather different: an HBA mechanism based
on appname. But on second thoughts maybe the protocol wouldn't support that.
cheers
andrew
On Wed, Jun 15, 2011 at 10:05 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
What I actually had in mind was rather different: an HBA mechanism based on
appname. But on second thoughts maybe the protocol wouldn't support that.
Ah, a similar thought struck me.
Independent of this particular feature, it would be rather useful to
augment pg_hba.conf to filter based on appname.
For my "pet" case, that would mean one might let "slon" and "slonik"
(Slony appname values) in, whilst keeping other appnames out, during a
system maintenance.
It's debatable whether or not that's more useful than filtering on the
basis of user names, which are likely to be pretty nearly isomorphic
to appnames.
Due to the near-isomorphism, I would not be comfortable trying to
claim that this is anywhere near essential.
During upgrade, I expect that we'd want everything but the upgrade
process locked out, including some backend-ish processes such as
autovacuum. That doesn't have quite the same "feel" as pg_hba.conf,
which also piques my "not totally comfortable" with this being a
pg_hba.conf thing.
That doesn't mean the idea's useless in and of itself, nor that
there's not some useful adaption to be made.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
On Wed, Jun 15, 2011 at 10:05 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
What I actually had in mind was rather different: an HBA mechanism based on
appname. But on second thoughts maybe the protocol wouldn't support that.
Ah, a similar thought struck me.
Independent of this particular feature, it would be rather useful to
augment pg_hba.conf to filter based on appname.
For my "pet" case, that would mean one might let "slon" and "slonik"
(Slony appname values) in, whilst keeping other appnames out, during a
system maintenance.
It's debatable whether or not that's more useful than filtering on the
basis of user names, which are likely to be pretty nearly isomorphic
to appnames.
Due to the near-isomorphism, I would not be comfortable trying to
claim that this is anywhere near essential.
During upgrade, I expect that we'd want everything but the upgrade
process locked out, including some backend-ish processes such as
autovacuum. That doesn't have quite the same "feel" as pg_hba.conf,
which also piques my "not totally comfortable" with this being a
pg_hba.conf thing.
That doesn't mean the idea's useless in and of itself, nor that
there's not some useful adaption to be made.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
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
On Wed, Jun 15, 2011 at 12:12 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Excerpts from Robert Haas's message of mié jun 15 08:45:21 -0400 2011:
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.
Writing it to a file which is ready by postmaster seems promising.
Then you wouldn't even need a command line option; you could just have
the postmaster write out binary_upgrade.conf and have that work like
recovery.conf to trigger the system to start up in a different mode.
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.
I'm not sure exactly what is needed here - what programmable tasks are
you thinking of, other than pg_upgrade?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Alvaro Herrera <alvherre@commandprompt.com> writes:
Excerpts from Robert Haas's message of mié jun 15 08:45:21 -0400 2011:
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.
So we fix the interface presented by standalone mode to be less insane.
That way, we can *reduce* the net amount of cruft in the system, rather
than adding more as all these proposals do.
regards, tom lane
Excerpts from Robert Haas's message of mié jun 15 12:51:29 -0400 2011:
On Wed, Jun 15, 2011 at 12:12 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
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.I'm not sure exactly what is needed here - what programmable tasks are
you thinking of, other than pg_upgrade?
Well, something to use on shell scripts and the like first and foremost;
see
http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Excerpts from Tom Lane's message of mié jun 15 12:52:30 -0400 2011:
Alvaro Herrera <alvherre@commandprompt.com> writes:
Excerpts from Robert Haas's message of mié jun 15 08:45:21 -0400 2011:
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.So we fix the interface presented by standalone mode to be less insane.
That way, we can *reduce* the net amount of cruft in the system, rather
than adding more as all these proposals do.
+1 on that general idea, but who is going to do the work?
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Wed, Jun 15, 2011 at 1:13 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Excerpts from Robert Haas's message of mié jun 15 12:51:29 -0400 2011:
On Wed, Jun 15, 2011 at 12:12 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote: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.I'm not sure exactly what is needed here - what programmable tasks are
you thinking of, other than pg_upgrade?Well, something to use on shell scripts and the like first and foremost;
see
http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html
I don't think there's anything wrong with using psql for running
scripts. It might need some work and maybe some better flags, but I
don't think we need to throw it out wholesale.
What we do need for pg_upgrade is to build more of the logic into
either pg_upgrade itself or the server, so it's not spawning external
programs right and left. It might help to "library"-ify some of the
functionality that's being used so that it can be invoked via a
function call rather than a shell exec.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Alvaro Herrera wrote:
Excerpts from Tom Lane's message of mi�� jun 15 12:52:30 -0400 2011:
Alvaro Herrera <alvherre@commandprompt.com> writes:
Excerpts from Robert Haas's message of mi�� jun 15 08:45:21 -0400 2011:
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.So we fix the interface presented by standalone mode to be less insane.
That way, we can *reduce* the net amount of cruft in the system, rather
than adding more as all these proposals do.+1 on that general idea, but who is going to do the work?
And you are going to backpatch all this? I don't find this promising at
all.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Robert Haas wrote:
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.
I now believe we are overthinking all this. pg_upgrade has always
supported specification of a port number. Why not just tell users to
specify an unused port number > 1023, and not to use the default value?
Both old and new clusters will happily run on any specified port number
during the upgrade. This allows the lockout to work for both old and
new clusters, which is better than enhancing -b because that will only
be for > 9.1 servers.
This requires no new backend code. We could even _require_ the port
number to be specified in pg_upgrade.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Wed, Jun 15, 2011 at 5:35 PM, Bruce Momjian <bruce@momjian.us> wrote:
This requires no new backend code. We could even _require_ the port
number to be specified in pg_upgrade.
+1... That seems to have lots of nice properties.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"Christopher Browne <cbbrowne@gmail.com> writes:
On Wed, Jun 15, 2011 at 5:35 PM, Bruce Momjian <bruce@momjian.us> wrote:
[ just recommend using a different port number during pg_upgrade ]
+1... That seems to have lots of nice properties.
Yeah, that seems like an appropriate expenditure of effort. It's surely
not bulletproof, since someone could intentionally connect to the actual
port number, but getting to bulletproof is a lot more work than anyone
seems to want to do right now. (And, as Bruce pointed out, no complete
solution would be back-patchable anyway.)
regards, tom lane
