Re: [HACKERS] new --maintenance-db options
On Sun, 2012-06-24 at 01:26 +0300, Peter Eisentraut wrote: About the new --maintenance-db options: Why was this option not added to createuser and dropuser? In the original discussion[0] they were mentioned, but it apparently never made it into the code. What should we do with this? Add the option to createuser and dropuser now, and think about a more permanent/useful/complete solution in 9.3? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
Peter Eisentraut pete...@gmx.net writes: On Sun, 2012-06-24 at 01:26 +0300, Peter Eisentraut wrote: About the new --maintenance-db options: Why was this option not added to createuser and dropuser? In the original discussion[0] they were mentioned, but it apparently never made it into the code. What should we do with this? Add the option to createuser and dropuser now, and think about a more permanent/useful/complete solution in 9.3? IMO it is now too late for 9.2 ... especially if you're of the opinion that the current design is bad. Propagating a wrong choice into even more places doesn't seem like a step forward. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
On Fri, Jun 29, 2012 at 3:32 PM, Bruce Momjian br...@momjian.us wrote: On Mon, Jun 25, 2012 at 11:57:36AM -0400, Robert Haas wrote: In retrospect, it seems as though it might have been a good idea to make the postgres database read-only and undroppable, so that all client utilities could count on being able to connect to it and get a list of databases in the cluster without the need for all this complexity. Or else having some other way for a client to authenticate and list out all the available databases. In the absence of such a mechanism, I don't think we can turn around and say that not having a postgres database is an unsupported configuration, and therefore we need some way to cope with it when it happens. Well, we certainly don't allow 'template1' to be dropped: test= DROP DATABASE template1; ERROR: cannot drop a template database so you could make the argument that making 'postgres' undroppable seem reasonable. I should point out that it was EnterpriseDB that complained about this related to their Advanced Server product, that doesn't have a 'postgres' database, but an 'edb' one. I said that was their problem, but when community users said they also dropped the 'postgres' database, it became a community problem too. Where are we going on this for PG 9.2? 9.3? I hate to ship options in 9.2 that will be gone in 9.3. FYI, we do allow the 'template1' database to be renamed: test= ALTER DATABASE template1 RENAME TO template2; ALTER DATABASE Oops. TODO? Not only that, but you can change datistemplate and then drop it OR rename it. We don't have a rule that says you can't drop template1. We have a rule that says you can't drop template databases. template1 is merely the default template database, but the user can create more, and they can get rid of, rename, or modify that one. I imagine that most people don't, but let's not make up an imaginary rule that template1 always has to exist, because it doesn't. Also, even if it does exist, it may have datallowconn = false (I think I've actually seen this, on a system that also had no postgres database), or pg_hba.conf may exclude connections to it, or it may be screwed up in a hundred other ways (databases that can't be connected to because the system catalogs are screwed up are not terribly rare). So in my opinion, any code that relies on the existence of, ability to connect to, or sane state of a database with any particular name is plain broken, because somebody somewhere is going to have an installation where it isn't. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
On Sun, Jun 24, 2012 at 01:26:58AM +0300, Peter Eisentraut wrote: About the new --maintenance-db options: What is the purpose of these options? The initial discussion was unclear on this. The documentation contains no explanation of why they should be used. If we want to really support the case where both postgres and template1 are removed, an environment variable might be more useful than requiring this to be typed out for every command. Yes, I had the same question about the usefulness/purpose of the option, but was out-voted. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
On Mon, Jun 25, 2012 at 11:57:36AM -0400, Robert Haas wrote: In retrospect, it seems as though it might have been a good idea to make the postgres database read-only and undroppable, so that all client utilities could count on being able to connect to it and get a list of databases in the cluster without the need for all this complexity. Or else having some other way for a client to authenticate and list out all the available databases. In the absence of such a mechanism, I don't think we can turn around and say that not having a postgres database is an unsupported configuration, and therefore we need some way to cope with it when it happens. Well, we certainly don't allow 'template1' to be dropped: test= DROP DATABASE template1; ERROR: cannot drop a template database so you could make the argument that making 'postgres' undroppable seem reasonable. I should point out that it was EnterpriseDB that complained about this related to their Advanced Server product, that doesn't have a 'postgres' database, but an 'edb' one. I said that was their problem, but when community users said they also dropped the 'postgres' database, it became a community problem too. Where are we going on this for PG 9.2? 9.3? I hate to ship options in 9.2 that will be gone in 9.3. FYI, we do allow the 'template1' database to be renamed: test= ALTER DATABASE template1 RENAME TO template2; ALTER DATABASE Oops. TODO? I think the original report that prompted this change was a complaint that pg_upgrade failed when the postgres database had been dropped. Now, admittedly, pg_upgrade fails for all kinds of crazy stupid reasons and the chances of fixing that problem completely any time in the next 5 years do not seem good, but that's not a reason not to keep plugging the holes we can. Anyhow, the same commit that introduced --maintenance-db fixed that problem by making arranging to try both postgres and template1 before giving up... but have two hard-coded database names either of which can be dropped or renamed seems only marginally better than having one, hence the switch. Really, I think Actually, 'template1' can't be dropped like 'postgres', but can be renamed (which I think needs fixing). I think falling back to template1 for missing 'postgres' database was the goal there. pg_upgrade needs this option too, unless we're going to kill the problem at its root by providing a reliable way to enumerate database names without first knowing the name one that you can connect to. pg_upgrade doesn't use --maintenance-db because the tools now fallback to template1, which again brings up the question of the usefulness of the --maintenance-db options. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
On Mon, Jun 25, 2012 at 02:58:25PM -0400, Robert Haas wrote:
On Mon, Jun 25, 2012 at 2:49 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
Excerpts from Robert Haas's message of lun jun 25 11:57:36 -0400 2012:
Really, I think
pg_upgrade needs this option too, unless we're going to kill the
problem at its root by providing a reliable way to enumerate database
names without first knowing the name one that you can connect to.
I think pg_upgrade could do this one task by using a standalone backend
instead of a full-blown postmaster. It should be easy enough ...
Maybe, but it seems like baking even more hackery into a tool that's
already got too much hackery. It's also hard for pg_upgrade to know
things like - whether pg_hba.conf prohibits access to certain
users/databases/etc. or just requires the use of authentication
methods that happen to fail. From pg_upgrade's perspective, it would
be nice to have a flag that starts the server in some mode where
nobody but pg_upgrade can connect to it and all connections are
automatically allowed, but it's not exactly clear how to implement
nobody but pg_upgrade can connect to it.
pg_upgrade already starts the postmaster with a -b option that disables
non-super-user logins:
/*
* Binary upgrades only allowed super-user connections
*/
if (IsBinaryUpgrade !am_superuser)
{
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg(must be superuser to connect in binary upgrade mode)));
}
It also uses port 50432 by default. Not sure what else we can do.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
On Mon, Jun 25, 2012 at 03:12:00PM -0400, Alvaro Herrera wrote: Excerpts from Robert Haas's message of lun jun 25 14:58:25 -0400 2012: On Mon, Jun 25, 2012 at 2:49 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of lun jun 25 11:57:36 -0400 2012: Really, I think pg_upgrade needs this option too, unless we're going to kill the problem at its root by providing a reliable way to enumerate database names without first knowing the name one that you can connect to. I think pg_upgrade could do this one task by using a standalone backend instead of a full-blown postmaster. It should be easy enough ... Maybe, but it seems like baking even more hackery into a tool that's already got too much hackery. It's also hard for pg_upgrade to know things like - whether pg_hba.conf prohibits access to certain users/databases/etc. or just requires the use of authentication methods that happen to fail. From pg_upgrade's perspective, it would be nice to have a flag that starts the server in some mode where nobody but pg_upgrade can connect to it and all connections are automatically allowed, but it's not exactly clear how to implement nobody but pg_upgrade can connect to it. Well, have it specify a private socket directory, listen only on that (not TCP), and bypass all pg_hba rules. This could be added to the poststmaster -b behavior, but I am concerned about the security of this. We sugest 'trust', but admins can adjust as needed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
Tom Lane t...@sss.pgh.pa.us writes: Amit Kapila amit.kap...@huawei.com writes: [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane The implementation I've wanted to see for some time is that you can start a standalone backend, but it speaks FE/BE protocol to its caller (preferably over pipes, so that there is no issue whatsoever of where you can securely put a socket or anything like that). Can't it be done like follow the FE/BE protocol, but call directly the server API's at required places. That wouldn't be easier, nor cleaner, and it would open us up to client-induced database corruption (from failure to follow APIs, crashes in the midst of an operation, memory stomps, etc). We decided long ago that we would never support truly embedded operation in the sense of PG executing in the client's process/address space. Okay. I like the design suggested above because it has many of the good properties of an embedded database (in particular, no need to manage or contact a server) but still keeps the client code at arm's length. In such a case will that standalone backend manage other processes like (wal writer, checkpoint, ...) or no background processes like in current --single mode. Can there be any performance advantage also in such a mode as compare to current when client and server on same m/c and uses Domain Socket? With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane Robert Haas robertmh...@gmail.com writes: From pg_upgrade's perspective, it would be nice to have a flag that starts the server in some mode where nobody but pg_upgrade can connect to it and all connections are automatically allowed, but it's not exactly clear how to implement nobody but pg_upgrade can connect to it. The implementation I've wanted to see for some time is that you can start a standalone backend, but it speaks FE/BE protocol to its caller (preferably over pipes, so that there is no issue whatsoever of where you can securely put a socket or anything like that). Can't it be done like follow the FE/BE protocol, but call directly the server API's at required places. This kind of implementation can be more performant than adding any communication to it which will be beneficial for embedded databases. Making that happen might be a bit too much work if pg_upgrade were the only use case, but there are a lot of people who would like to use PG as an embedded database, and this might be close enough for such use-cases. Seeing PG to run as embedded database would be interesting for many people using PG. There is another use case of embedded databases that they allow another remote connections as well to monitor the operations in database. However that can be done in a later version of implementation. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
Amit Kapila amit.kap...@huawei.com writes: [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane The implementation I've wanted to see for some time is that you can start a standalone backend, but it speaks FE/BE protocol to its caller (preferably over pipes, so that there is no issue whatsoever of where you can securely put a socket or anything like that). Can't it be done like follow the FE/BE protocol, but call directly the server API's at required places. That wouldn't be easier, nor cleaner, and it would open us up to client-induced database corruption (from failure to follow APIs, crashes in the midst of an operation, memory stomps, etc). We decided long ago that we would never support truly embedded operation in the sense of PG executing in the client's process/address space. I like the design suggested above because it has many of the good properties of an embedded database (in particular, no need to manage or contact a server) but still keeps the client code at arm's length. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
On Sat, Jun 23, 2012 at 6:26 PM, Peter Eisentraut pete...@gmx.net wrote: About the new --maintenance-db options: Why was this option not added to createuser and dropuser? In the original discussion[0] they were mentioned, but it apparently never made it into the code. Oops. That was an oversight. I find the name to be unfortunate. For example, I think of running vacuum as maintenance. So running vacuumdb --maintenance-db=X would imply that the vacuum maintenance is done on X. In fact, the whole point of this option is to find out where the maintenance is to be run, not to run the maintenance. Maybe something like --initial-db would be better? As Dave says, I picked this because pgAdmin has long used that terminology. What is the purpose of these options? The initial discussion was unclear on this. The documentation contains no explanation of why they should be used. If we want to really support the case where both postgres and template1 are removed, an environment variable might be more useful than requiring this to be typed out for every command. [0]: http://archives.postgresql.org/message-id/ca+tgmoacjwsis9nnqjgaaml1vg6c8b6o1ndgqnuca2gm00d...@mail.gmail.com Well, I would be opposed to having ONLY an environment variable, because I think that anything that can be controlled via an environment variable should be able to be overridden on the command line. It might be OK to have both an environment variable AND a command-line option, but I tend to thing it's too marginal to justify that. In retrospect, it seems as though it might have been a good idea to make the postgres database read-only and undroppable, so that all client utilities could count on being able to connect to it and get a list of databases in the cluster without the need for all this complexity. Or else having some other way for a client to authenticate and list out all the available databases. In the absence of such a mechanism, I don't think we can turn around and say that not having a postgres database is an unsupported configuration, and therefore we need some way to cope with it when it happens. I think the original report that prompted this change was a complaint that pg_upgrade failed when the postgres database had been dropped. Now, admittedly, pg_upgrade fails for all kinds of crazy stupid reasons and the chances of fixing that problem completely any time in the next 5 years do not seem good, but that's not a reason not to keep plugging the holes we can. Anyhow, the same commit that introduced --maintenance-db fixed that problem by making arranging to try both postgres and template1 before giving up... but have two hard-coded database names either of which can be dropped or renamed seems only marginally better than having one, hence the switch. Really, I think pg_upgrade needs this option too, unless we're going to kill the problem at its root by providing a reliable way to enumerate database names without first knowing the name one that you can connect to. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
Excerpts from Robert Haas's message of lun jun 25 11:57:36 -0400 2012: Really, I think pg_upgrade needs this option too, unless we're going to kill the problem at its root by providing a reliable way to enumerate database names without first knowing the name one that you can connect to. I think pg_upgrade could do this one task by using a standalone backend instead of a full-blown postmaster. It should be easy enough ... -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
On Mon, Jun 25, 2012 at 2:49 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of lun jun 25 11:57:36 -0400 2012: Really, I think pg_upgrade needs this option too, unless we're going to kill the problem at its root by providing a reliable way to enumerate database names without first knowing the name one that you can connect to. I think pg_upgrade could do this one task by using a standalone backend instead of a full-blown postmaster. It should be easy enough ... Maybe, but it seems like baking even more hackery into a tool that's already got too much hackery. It's also hard for pg_upgrade to know things like - whether pg_hba.conf prohibits access to certain users/databases/etc. or just requires the use of authentication methods that happen to fail. From pg_upgrade's perspective, it would be nice to have a flag that starts the server in some mode where nobody but pg_upgrade can connect to it and all connections are automatically allowed, but it's not exactly clear how to implement nobody but pg_upgrade can connect to it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
Excerpts from Robert Haas's message of lun jun 25 14:58:25 -0400 2012: On Mon, Jun 25, 2012 at 2:49 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of lun jun 25 11:57:36 -0400 2012: Really, I think pg_upgrade needs this option too, unless we're going to kill the problem at its root by providing a reliable way to enumerate database names without first knowing the name one that you can connect to. I think pg_upgrade could do this one task by using a standalone backend instead of a full-blown postmaster. It should be easy enough ... Maybe, but it seems like baking even more hackery into a tool that's already got too much hackery. It's also hard for pg_upgrade to know things like - whether pg_hba.conf prohibits access to certain users/databases/etc. or just requires the use of authentication methods that happen to fail. From pg_upgrade's perspective, it would be nice to have a flag that starts the server in some mode where nobody but pg_upgrade can connect to it and all connections are automatically allowed, but it's not exactly clear how to implement nobody but pg_upgrade can connect to it. Well, have it specify a private socket directory, listen only on that (not TCP), and bypass all pg_hba rules. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
Robert Haas robertmh...@gmail.com writes: From pg_upgrade's perspective, it would be nice to have a flag that starts the server in some mode where nobody but pg_upgrade can connect to it and all connections are automatically allowed, but it's not exactly clear how to implement nobody but pg_upgrade can connect to it. The implementation I've wanted to see for some time is that you can start a standalone backend, but it speaks FE/BE protocol to its caller (preferably over pipes, so that there is no issue whatsoever of where you can securely put a socket or anything like that). Making that happen might be a bit too much work if pg_upgrade were the only use case, but there are a lot of people who would like to use PG as an embedded database, and this might be close enough for such use-cases. However, that has got little to do with whether --maintenance-db is a worthwhile thing or not, because that's about external client-side tools, not pg_upgrade. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] new --maintenance-db options
About the new --maintenance-db options: Why was this option not added to createuser and dropuser? In the original discussion[0] they were mentioned, but it apparently never made it into the code. I find the name to be unfortunate. For example, I think of running vacuum as maintenance. So running vacuumdb --maintenance-db=X would imply that the vacuum maintenance is done on X. In fact, the whole point of this option is to find out where the maintenance is to be run, not to run the maintenance. Maybe something like --initial-db would be better? What is the purpose of these options? The initial discussion was unclear on this. The documentation contains no explanation of why they should be used. If we want to really support the case where both postgres and template1 are removed, an environment variable might be more useful than requiring this to be typed out for every command. [0]: http://archives.postgresql.org/message-id/ca+tgmoacjwsis9nnqjgaaml1vg6c8b6o1ndgqnuca2gm00d...@mail.gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new --maintenance-db options
On Saturday, June 23, 2012, Peter Eisentraut wrote: About the new --maintenance-db options: Why was this option not added to createuser and dropuser? In the original discussion[0] they were mentioned, but it apparently never made it into the code. I find the name to be unfortunate. For example, I think of running vacuum as maintenance. So running vacuumdb --maintenance-db=X would imply that the vacuum maintenance is done on X. In fact, the whole point of this option is to find out where the maintenance is to be run, not to run the maintenance. Maybe something like --initial-db would be better? I'm not saying it's the best name, but I suspect the origin is pgAdmin which has used 'Maintenance DB' for 10+ years. There's likely a certain amount of familiarity with the term amonst pgAdmin users. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
