Re: Proof of concept: standalone backend with full FE/BE protocol

I have gone through the mail chain of this thread and tried to find
the different concerns or open ends for this patch.

Summarisation of the discussion and concerns for this patch:

1. Security concern in interface
2. Security concern in Windows implementation
3. Handling of Ctrl-C/SIGTERM
4. Secondary connections for maintenance activities, replication
5. Windows Implementation - what should be behaviour for admin users?
6. Restricting operation's in single backend mode
7. Proposal related to maintenance activities

Description of each concern
----------------------------------------------

1. Security concern in interface -

Interface
---------------
$ psql "standalone_datadir = $PGDATA dbname = regression"
There is another option "standalone_backend", which can be set to
specify which postgres executable to launch.
If the latter isn't specified, libpq defaults to trying the
installation PGBINDIR that was selected by configure.

Security Concern
-----------------------------
If a user can specify libpq connection options, he can now execute
any file he wants by passing it as standalone_backend.

Method to resolve Security concern
--------------------------------------------------------
If an application wants to allow these connection parameters to be
used, it would need to do PQenableStartServer() first. If it doesn't,
those connection parameters will be rejected.

2. Security concern in Windows implementation -

Interface
---------------
PQcancel -
In Unix, need to use kill(conn->postgres_pid, SIGINT)
In Windows, pgkill(int pid, int sig) API can be used.

Security concern
---------------------------
pgkill is used to send cancel signal using pipe mechanism, so
someone else can create a pipe with our name before we do (since we
use the actual name - it's \\.\pipe\pgsinal_<pid>), by
guessing what pid we will have. If that happens, we'll go into a
loop and try to recreate it while logging a warning message to
eventlog/stderr. (this happens for every backend). We can't
throw an error on this and kill the backend because the pipe is
created in the background thread not the main one.

Some suggestions
------------------------------
Once it is detected that already a same Named Pipe already exists,
there can be following options:
a. try to create with some other name, but in that case how to
communicate the new name to client end of pipe. Some solution can be
thought if this approach seems to be reasonable,
though currently I don't have any in mind.
b. give error, as creation of pipe is generally at beginning of
process creation(backend)
c. any other better solution?

3. Handling of Ctrl-C/SIGTERM

Behaviour
---------------
If you kill the client, the child postgres will see connection
closure and will shut down.

Concern
--------------
will make scripting harder because you cannot start another single
backend pg_dump before the old backend noticed it, checkpointed and
shut down. It can happen if you forcibly kill
pg_dump (or some other client) and then immediately try to start a
new one, it's not clear how long you'll have to wait.

Suggestions for alternatives for this case
-------------------------------------------------------------
a. There is no expectation that a standalone PG implementation
would provide performance for a series of standalone sessions that is
equivalent to what you'd get from a persistent
server. If that scenario is what's important to you, you'd use
a persistent server.
b. An extra libpq call to handle this case can be helpful.

4. Secondary connections for data access

Proposal
---------------
A single-user connection database with "no administrative hassles"

Concerns
-----------------
As this proposal will not allow any data it stores to be accessed
by another connection, so all forms of replication are excluded and
all maintenance actions force the database to be
unavailable for a period of time. Those two things are barriers of
the most major kind to anybody working in an enterprise with connected
data and devices.

Suggestions for it's use or make it usable
----------------------------------------------------------------
a. a usable & scriptable --single mode is justification enough.
Having to wait for hours just enter one more command because --single
doesn't support any scripts sucks. Especially in
recovery situations.
b. it's worth having this particular thing because it makes
pg_upgrade more robust.
c. some competing solutions already provide similar solution
(http://www.firebirdsql.org/manual/fbmetasecur-embedded.html).
d. we need to make sure that this isn't foreclosing the option of
having a multi-process environment with a single user connection. I
don't see that it is, but it might be wise to sketch
exactly how that case would work before accepting this.

5. Windows Implementation - what should be behaviour for admin users

Behavior clarification -
does this follow the behavior that admin users will not be allowed
to invoke postgres child process?

6. Restricting operation's in single backend mode

Serializable transactions could skip all the SSI predicate locking
and conflict checking when in single-connection mode. With only one
connection the transactions could never overlap, so
there would be no chance of serialization anomalies when running
snapshot isolation.

It could be of use if someone had code they wanted to run under
both normal and single-connection modes. For single-connection only,
they could just choose REPEATABLE READ to
get exactly the same semantics.

7. Proposal related to maintainence activities

For maintainence activities, in longer run, we can have a
postmaster process that isn't listening on any ports, but is managing
background processes in addition to a single child backend.

As per my understanding, to complete this patch we need to
a. complete the work for #1, #2, #5
b. #6 and #7 can be done as enhancements after the initial feature is committed
c. need to decide what should we do for #3 and #4.

Rebased the patch (changes in patch)
-----------------------------------------------------------
a. fillPGconn(), loops through each connection option, so no need to
do it separately for standalone_datadir and standalone_backend
b. In function, ChildPostgresMain()->PostgresMain() pass third
parameter dbname as NULL.
c. Changed second parameter of read_standalone_child_variables() from
"int *" to "pgsocket *" to remove warning.
d. removed trailing white spaces.
e. update PQconninfoOptions array to include offset.

Any objections for adding this idea/patch to CF?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:
> Any objections for adding this idea/patch to CF?

You should certainly add it to the CF. You've listed lots of matters
for review, but that's no reason to not get it in the queue to be
reviewed.

regards, tom lane

On 14 November 2013 03:41, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> I have gone through the mail chain of this thread and tried to find
> the different concerns or open ends for this patch.

Not enough. This feature is clearly being suggested as a way to offer
Postgres in embedded mode for users by a back door. Doing that forces
us to turn off many of the server's features and we will take a huge
step backwards in features, testing, maintainability of code and
wasted community time.

"No administrative hassles" is just a complete fiction. Admin will
become a huge burden for any user in this mode, which will bite the
community and cause us to waste much time redesigning the server to
operate on a single session.

-1 from me

> 4. Secondary connections for data access
>
> Proposal
> ---------------
> A single-user connection database with "no administrative hassles"
>
> Concerns
> -----------------
> As this proposal will not allow any data it stores to be accessed
> by another connection, so all forms of replication are excluded and
> all maintenance actions force the database to be
> unavailable for a period of time. Those two things are barriers of
> the most major kind to anybody working in an enterprise with connected
> data and devices.
>
> Suggestions for it's use or make it usable
> ----------------------------------------------------------------
> a. a usable & scriptable --single mode is justification enough.
> Having to wait for hours just enter one more command because --single
> doesn't support any scripts sucks. Especially in
> recovery situations.
> b. it's worth having this particular thing because it makes
> pg_upgrade more robust.
> c. some competing solutions already provide similar solution
> (http://www.firebirdsql.org/manual/fbmetasecur-embedded.html).
> d. we need to make sure that this isn't foreclosing the option of
> having a multi-process environment with a single user connection. I
> don't see that it is, but it might be wise to sketch
> exactly how that case would work before accepting this.

Why is not feasible to run a normal server with 1 connection.

Are we really following what Firebird is doing? Why?

> 6. Restricting operation's in single backend mode
>
> Serializable transactions could skip all the SSI predicate locking
> and conflict checking when in single-connection mode. With only one
> connection the transactions could never overlap, so
> there would be no chance of serialization anomalies when running
> snapshot isolation.
>
> It could be of use if someone had code they wanted to run under
> both normal and single-connection modes. For single-connection only,
> they could just choose REPEATABLE READ to
> get exactly the same semantics.

This is an example of my concern that we would begin optimising for
the case of single user mode and encourage its use by users. This
shows that the feature is not being suggested just for recovery.

PostgreSQL has been designed from the ground up to support
concurrency. If we begin optimising for single user mode it will take
years to unpick our work and eventually we'll have a conflict and
someone will say "we can't do that because it will be a problem in
single user mode".

> 7. Proposal related to maintainence activities
>
> For maintainence activities, in longer run, we can have a
> postmaster process that isn't listening on any ports, but is managing
> background processes in addition to a single child backend.
>
> As per my understanding, to complete this patch we need to
> a. complete the work for #1, #2, #5
> b. #6 and #7 can be done as enhancements after the initial feature is committed
> c. need to decide what should we do for #3 and #4.

Huh? Multi-process mode already works. Why would do we need "a
solution" for the problem that single process mode uses only one
process? Don't use it.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2013-11-15 09:51:28 -0200, Simon Riggs wrote:
> On 14 November 2013 03:41, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> > I have gone through the mail chain of this thread and tried to find
> > the different concerns or open ends for this patch.
>
> Not enough. This feature is clearly being suggested as a way to offer
> Postgres in embedded mode for users by a back door.

I think fixing single user mode to work halfway reasonable is enough
justification for the feature. Having to deal with that when solving
critical issues is just embarassing.

> Doing that forces
> us to turn off many of the server's features and we will take a huge
> step backwards in features, testing, maintainability of code and
> wasted community time.

I think the patch as proposed actually reduces maintenance overhead
since we don't have to deal with the strange separate codepaths for
single user mode.

But: I very, very much agree with the other concerns around this. This
should be a patch to fix single user mode, not one to make postgres into
a single process database. It's not, and trying to make it by using
single user mode for it will start to hinder development of normal
postgres because we suddenly need to be concerned about performance and
features in situations where we previously weren't.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> I think fixing single user mode to work halfway reasonable is enough
> justification for the feature. Having to deal with that when solving
> critical issues is just embarassing.

+1

> But: I very, very much agree with the other concerns around this. This
> should be a patch to fix single user mode, not one to make postgres into
> a single process database. It's not, and trying to make it by using
> single user mode for it will start to hinder development of normal
> postgres because we suddenly need to be concerned about performance and
> features in situations where we previously weren't.

+1

Maybe what needs to happen to this patch is away to restrict its usage
to --single. I'm thinking that postgres --single maybe could be made to
fork the server process underneath the psql controler client process
transparently.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On 15 November 2013 09:00, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

> This
> should be a patch to fix single user mode, not one to make postgres into
> a single process database.

+1

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Nov 15, 2013 at 6:06 AM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
>> But: I very, very much agree with the other concerns around this. This
>> should be a patch to fix single user mode, not one to make postgres into
>> a single process database. It's not, and trying to make it by using
>> single user mode for it will start to hinder development of normal
>> postgres because we suddenly need to be concerned about performance and
>> features in situations where we previously weren't.
>
> +1
>
> Maybe what needs to happen to this patch is away to restrict its usage
> to --single. I'm thinking that postgres --single maybe could be made to
> fork the server process underneath the psql controler client process
> transparently.

I personally would prefer not to do that. My main non-administrative
interest in this mode is doing things like benchmarking protocol
overhead. I'm OK with not supporting (and optimizing) for single user
code paths but I don't like the idea of building walls that serve no
purpose other than to make it difficult for other people mess around.
Just document strenuously that this mode is not intended for
application bundling and move on...

merlin

On Fri, Nov 15, 2013 at 5:21 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 14 November 2013 03:41, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
>> I have gone through the mail chain of this thread and tried to find
>> the different concerns or open ends for this patch.
>
> Not enough. This feature is clearly being suggested as a way to offer
> Postgres in embedded mode for users by a back door.

Current patch doesn't have such facility and I don't think somebody
can use it as an embedded database.

> Doing that forces
> us to turn off many of the server's features and we will take a huge
> step backwards in features, testing, maintainability of code and
> wasted community time.
>
> "No administrative hassles" is just a complete fiction. Admin will
> become a huge burden for any user in this mode, which will bite the
> community and cause us to waste much time redesigning the server to
> operate on a single session.
>
> -1 from me

What I could understand from your objection is that you don't want
users to get the impression of this feature as an embedded database.
I think as the patch stands, it doesn't have such facility, so
advertising it as an substitute for embedded database would be anyway
inappropriate.
The use case is to provide a standalone mode which will be useful for
cases where today --single mode is required/used and I think
documenting the feature that way is the right way to proceed. If this
addresses your concern, then we can proceed to discuss solutions for
other concerns like security?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Fri, Nov 15, 2013 at 6:51 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Not enough. This feature is clearly being suggested as a way to offer
> Postgres in embedded mode for users by a back door. Doing that forces
> us to turn off many of the server's features and we will take a huge
> step backwards in features, testing, maintainability of code and
> wasted community time.

That's not clear to me at all. IIRC, the original idea was Tom's, and
the idea is to make it possible to have, for example, a psql session
connected to a standalone database, which can't be done right now. I
don't use standalone mode much, but when I do, I'd sure like to have
the psql interface rather than the existing standalone mode interface.
I'm not aware that there's anything in this patch which targets any
other use case; if there is, sure, rip it out. But let's not assume
this is going in a bad direction, especially considering who it was
that suggested the idea originally.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Nov 15, 2013 at 6:51 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Not enough. This feature is clearly being suggested as a way to offer
>> Postgres in embedded mode for users by a back door. Doing that forces
>> us to turn off many of the server's features and we will take a huge
>> step backwards in features, testing, maintainability of code and
>> wasted community time.

> That's not clear to me at all. IIRC, the original idea was Tom's, and
> the idea is to make it possible to have, for example, a psql session
> connected to a standalone database, which can't be done right now. I
> don't use standalone mode much, but when I do, I'd sure like to have
> the psql interface rather than the existing standalone mode interface.

pg_dump from a standalone backend seems like another core use case.
That's not just more convenience, it's functionality you just don't
have right now.

I think that it might someday be interesting to allow a full server to be
started on-demand in this way. But the patch as proposed is not that,
it's just a nicer interface to the existing standalone mode.

regards, tom lane

On 11/14/13, 1:41 AM, Amit Kapila wrote:
> Security Concern
> -----------------------------
> If a user can specify libpq connection options, he can now execute
> any file he wants by passing it as standalone_backend.
>
> Method to resolve Security concern
> --------------------------------------------------------
> If an application wants to allow these connection parameters to be
> used, it would need to do PQenableStartServer() first. If it doesn't,
> those connection parameters will be rejected.

I don't think this really helps. You can't tell with reasonable effort
or certainty whether a given program is calling PQenableStartServer(),
so you cannot audit this from the outside. Also, someone could,
depending on circumstances, dynamically load a module that calls
PQenableStartServer(), thus circumventing this check. And maybe before
long someone will patch up all drivers to call PQenableStartServer()
automatically, because why shouldn't I be able to run a standalone
backend from PHP or Ruby? Also, at some point at least, something like
phpPgAdmin called pg_dump internally, so you could imagine that in
situations like that, assuming that pg_dump called
PQenableStartServer(), with a little bit craftiness, you could expose
the execute-any-file hole through a web server.

I don't have a better idea right now how to set up these connection
parameters in a way that you can only set them in certain "safe"
circumstances.

I would consider sidestepping this entire issue by having the
stand-alone backend create a Unix-domain socket and have a client
connect to that in the normal way. At least if you split the patch that
way, you might alleviate some concerns of others about whether this
patch is about fixing standalone mode vs. allowing using standalone mode
with psql vs. making a fully embedded database.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> I would consider sidestepping this entire issue by having the
> stand-alone backend create a Unix-domain socket and have a client
> connect to that in the normal way.

Hmm. But that requires the "stand-alone backend" to take on at least
some properties of a postmaster; at the very least, it would need to
accept some form of shutdown signal (not just EOF on its stdin).

Perhaps more to the point, I think this approach actually breaks one of
the principal good-thing-in-emergencies attributes of standalone mode,
namely being sure that nobody but you can connect. With this, you're
right back to having a race condition as to whether your psql command
gets to the socket before somebody else.

I think we'd be better off trying to fix the security issue by
constraining what can be executed as a "standalone backend". Would
it work to insist that psql/pg_dump launch the program named postgres
from the same bin directory they're in, rather than accepting a path
from the connection string?

regards, tom lane

On 2013-11-20 10:48:20 -0500, Tom Lane wrote:
> constraining what can be executed as a "standalone backend". Would
> it work to insist that psql/pg_dump launch the program named postgres
> from the same bin directory they're in, rather than accepting a path
> from the connection string?

But why do we want to start the server through the connection string
using PQconnectb() in the first place? That doesn't really seem right to
me.
Something like PQstartSingleUser(dsn) returning a established connection
seems better to me.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2013-11-20 10:48:20 -0500, Tom Lane wrote:
>> constraining what can be executed as a "standalone backend". Would
>> it work to insist that psql/pg_dump launch the program named postgres
>> from the same bin directory they're in, rather than accepting a path
>> from the connection string?

> But why do we want to start the server through the connection string
> using PQconnectb() in the first place? That doesn't really seem right to
> me.
> Something like PQstartSingleUser(dsn) returning a established connection
> seems better to me.

That just pushes the problem up a level --- how are you going to tell
psql, pg_dump, or other programs that they should do that?

regards, tom lane

On 2013-11-20 11:08:33 -0500, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On 2013-11-20 10:48:20 -0500, Tom Lane wrote:
> >> constraining what can be executed as a "standalone backend". Would
> >> it work to insist that psql/pg_dump launch the program named postgres
> >> from the same bin directory they're in, rather than accepting a path
> >> from the connection string?
>
> > But why do we want to start the server through the connection string
> > using PQconnectb() in the first place? That doesn't really seem right to
> > me.
> > Something like PQstartSingleUser(dsn) returning a established connection
> > seems better to me.
>
> That just pushes the problem up a level --- how are you going to tell
> psql, pg_dump, or other programs that they should do that?

An explicit parameter. A program imo explicitly needs to be aware that a
PQconnect() suddenly starts forking and such. What if it is using
threads? What if it has it's own SIGCHLD handler for other business it's
doing?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> I think we'd be better off trying to fix the security issue by
> constraining what can be executed as a "standalone backend". Would
> it work to insist that psql/pg_dump launch the program named postgres
> from the same bin directory they're in, rather than accepting a path
> from the connection string?

Couldn't that be an issue for people who have multiple major versions of
binaries installed? In particular, the "default" on the system for psql
might be 9.3 while the cluster you're trying to recover may be 9.2. Of
course, in that case you might say to use the 9.2 psql, which would be
fair, but what if you're looking to get the data out of the 9.2 DB and
into the 9.3? In that case, we'd recommend using the 9.3 pg_dump.

Basically, I'd suggest that we try and avoid things like "the binaries
have to be in the same directory".. With regard to access to the
socket, perhaps we create our own socket w/ 0600 and use that? Seems
like it'd be sufficient to prevent the 'normal' users from getting into
the DB while we're working on it. If there's two different individuals
gettings into the same system and trying to start the same cluster as
the same unix user, well.. I'm not convinced we'd be able to come up
with a perfect solution to that anyway.

Thanks,

Stephen

On 2013-11-20 17:19:42 +0100, Andres Freund wrote:
> > That just pushes the problem up a level --- how are you going to tell
> > psql, pg_dump, or other programs that they should do that?
>
> An explicit parameter. A program imo explicitly needs to be aware that a
> PQconnect() suddenly starts forking and such. What if it is using
> threads? What if it has it's own SIGCHLD handler for other business it's
> doing?

Just as an example, consider what happens if somebody does pg_dump -j?
Or somebody specifies such a connection for primary_conninfo?

I am also not sure whether vacuumdb -a/reindexdb -a (both not unlikely
commands to use for single user mode) are careful enough not to have
parallel connections open?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2013-11-20 11:08:33 -0500, Tom Lane wrote:
>> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>>> Something like PQstartSingleUser(dsn) returning a established connection
>>> seems better to me.

>> That just pushes the problem up a level --- how are you going to tell
>> psql, pg_dump, or other programs that they should do that?

> An explicit parameter. A program imo explicitly needs to be aware that a
> PQconnect() suddenly starts forking and such. What if it is using
> threads? What if it has it's own SIGCHLD handler for other business it's
> doing?

Hm. That's a fair point. I don't especially buy your other argument
about additional connections --- if the program tries such, they'll
just fail, which can hardly be said to be unexpected. But it's reasonable
to worry that programs might need to be aware that they now have a child
process. (It occurs to me that we'll need to provide a way to get the
PID of the child, too.)

regards, tom lane

On 11/20/13, 11:31 AM, Stephen Frost wrote:
> Couldn't that be an issue for people who have multiple major versions of
> binaries installed? In particular, the "default" on the system for psql
> might be 9.3 while the cluster you're trying to recover may be 9.2. Of
> course, in that case you might say to use the 9.2 psql, which would be
> fair, but what if you're looking to get the data out of the 9.2 DB and
> into the 9.3? In that case, we'd recommend using the 9.3 pg_dump.

Right. And also, in emergency situations you might have a custom built
postgres binary lying around in a separate path that includes a patch
from a mailing list you're supposed to test or something. Best not to
make that even more difficult.

On 11/20/13, 10:48 AM, Tom Lane wrote:
> Perhaps more to the point, I think this approach actually breaks one of
> the principal good-thing-in-emergencies attributes of standalone mode,
> namely being sure that nobody but you can connect. With this, you're
> right back to having a race condition as to whether your psql command
> gets to the socket before somebody else.

I don't disagree, except maybe about the relative gravity of the various
competing concerns. But I want to see if we can split the proposed
patch into smaller, more acceptable parts.

There is elegance in being able to start a standalone backend from libpq
connection parameters. But there are also security concerns and some
general concerns about promoting an embedded database mode.

If we allow single-user backends to speak protocol over sockets, then we
have at least solved the problem of being able to use standard tools in
emergency mode. And I don't think it precludes adding some of the other
functionality later.

On Wed, Nov 20, 2013 at 10:13 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On 11/14/13, 1:41 AM, Amit Kapila wrote:
>> Security Concern
>> -----------------------------
>> If a user can specify libpq connection options, he can now execute
>> any file he wants by passing it as standalone_backend.
>>
>> Method to resolve Security concern
>> --------------------------------------------------------
>> If an application wants to allow these connection parameters to be
>> used, it would need to do PQenableStartServer() first. If it doesn't,
>> those connection parameters will be rejected.
>
> I don't think this really helps. You can't tell with reasonable effort
> or certainty whether a given program is calling PQenableStartServer(),
> so you cannot audit this from the outside. Also, someone could,
> depending on circumstances, dynamically load a module that calls
> PQenableStartServer(), thus circumventing this check.

What?! The complaint is that somebody who only has access to set
connection parameters could cause a server to be started. There's a
tremendous gulf between "I can set the connection string" and "I can
set LD_PRELOAD". If you can set LD_PRELOAD to a value of your choice,
I'm pretty sure you can do things that are far more entertaining than
calling a hypothetical PQenableStartServer() function.

> And maybe before
> long someone will patch up all drivers to call PQenableStartServer()
> automatically, because why shouldn't I be able to run a standalone
> backend from PHP or Ruby? Also, at some point at least, something like
> phpPgAdmin called pg_dump internally, so you could imagine that in
> situations like that, assuming that pg_dump called
> PQenableStartServer(), with a little bit craftiness, you could expose
> the execute-any-file hole through a web server.

The point is that client applications should expose whether or not to
set this function as a command-line switch separate from whatever they
accept in terms of connection strings. So pg_dump should have a flag
called --standalone-server or something like, and it should all
PQenableStartServer() only when that flag is used. So if the user has
a shell script that invokes pg_dump -d "$1", the user cannot contrive
a server. If they write the script as pg_dump --standalone-server -d
"$1", then they can, but by putting that option in there you pretty
much bought the farm. Any program that calls that function
unconditionally while at the same time accepting untrusted user input
will be insecure, but chmod -R u+s /bin is insecure, too. That's why
we don't do that.

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

On 11/20/13, 3:24 PM, Robert Haas wrote:
> The point is that client applications should expose whether or not to
> set this function as a command-line switch separate from whatever they
> accept in terms of connection strings. So pg_dump should have a flag
> called --standalone-server or something like, and it should all
> PQenableStartServer() only when that flag is used.

The argument elsewhere in this thread was that the reason for putting
this in the connection options was so that you do *not* have to patch up
every client to be able to use this functionality. If you have to add
separate options everywhere, then you might as well just have a separate
libpq function to initiate the session.

On Wed, Nov 20, 2013 at 3:32 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On 11/20/13, 3:24 PM, Robert Haas wrote:
>> The point is that client applications should expose whether or not to
>> set this function as a command-line switch separate from whatever they
>> accept in terms of connection strings. So pg_dump should have a flag
>> called --standalone-server or something like, and it should all
>> PQenableStartServer() only when that flag is used.
>
> The argument elsewhere in this thread was that the reason for putting
> this in the connection options was so that you do *not* have to patch up
> every client to be able to use this functionality. If you have to add
> separate options everywhere, then you might as well just have a separate
> libpq function to initiate the session.

Well, that's fair enough. I don't care much what the syntax is for
invoking the postmaster this way, as long as it's reasonably
convenient. I just want there to be one.

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

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> The argument elsewhere in this thread was that the reason for putting
> this in the connection options was so that you do *not* have to patch up
> every client to be able to use this functionality. If you have to add
> separate options everywhere, then you might as well just have a separate
> libpq function to initiate the session.

Right, Andres was saying that we had to do both (special switches that
lead to calling a special connection function). I'm not terribly happy
about that, because it will greatly constrain the set of programs that are
able to connect to standalone backends --- but I think that there are some
in this discussion who want that, anyway. In practice, as long as psql
and pg_dump and pg_upgrade can do it, I think we've covered most of the
interesting bases.

To my mind, the "create a socket and hope nobody else can get to it"
approach is exactly one of the main things we're trying to avoid here.
If you'll recall, awhile back we had a big discussion about how pg_upgrade
could positively guarantee that nobody messed with the source database
while it was working, and we still don't have a bulletproof guarantee
there. I would like to fix that by making pg_upgrade use only standalone
backends to talk to the source database, never starting a real postmaster
at all. But if the standalone-pg_dump mode goes through a socket, we're
back to square one on that concern.

regards, tom lane

On Wed, Nov 20, 2013 at 3:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>
> To my mind, the "create a socket and hope nobody else can get to it"
> approach is exactly one of the main things we're trying to avoid here.
> If you'll recall, awhile back we had a big discussion about how pg_upgrade
> could positively guarantee that nobody messed with the source database
> while it was working, and we still don't have a bulletproof guarantee
> there. I would like to fix that by making pg_upgrade use only standalone
> backends to talk to the source database, never starting a real postmaster
> at all. But if the standalone-pg_dump mode goes through a socket, we're
> back to square one on that concern.
>

(I couldn't find the pg_upgrade-related thread mentioned above).

I am not sure of the mechanics of this, but can we not launch the
postmaster with a random magic-cookie, and use that cookie while initiating
the connection from libpq. The postmaster will then reject any connections
that don't provide the cookie.

We do something similar to enable applications to send cancellation signals
(postmaster.c:Backend.cancel_key), just that it's establishing trust in the
opposite direction.

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/

EnterprsieDB Inc. www.enterprisedb.com

On 2013-11-20 15:44:03 -0500, Tom Lane wrote:
> In practice, as long as psql and pg_dump and pg_upgrade can do it, I
> think we've covered most of the interesting bases.

I'd say vacuumdb/reindexdb should be added to that list. In my
experience xid wraparound and corrupted system indexes are the most
frequent use-case of single user mode.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Wed, Nov 20, 2013 at 05:38:14PM -0500, Gurjeet Singh wrote:
> On Wed, Nov 20, 2013 at 3:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>
> To my mind, the "create a socket and hope nobody else can get to it"
> approach is exactly one of the main things we're trying to avoid here.
> If you'll recall, awhile back we had a big discussion about how pg_upgrade
> could positively guarantee that nobody messed with the source database
> while it was working, and we still don't have a bulletproof guarantee
> there. I would like to fix that by making pg_upgrade use only standalone
> backends to talk to the source database, never starting a real postmaster
> at all. But if the standalone-pg_dump mode goes through a socket, we're
> back to square one on that concern.
>
>
> (I couldn't find the pg_upgrade-related thread mentioned above).
>
> I am not sure of the mechanics of this, but can we not launch the postmaster
> with a random magic-cookie, and use that cookie while initiating the connection
> from libpq. The postmaster will then reject any connections that don't provide
> the cookie.
>
> We do something similar to enable applications to send cancellation signals
> (postmaster.c:Backend.cancel_key), just that it's establishing trust in the
> opposite direction.

The magic cookie can be tha application_name. I had pg_upgrade code to
prevent anyone from connecting unless their application_name was
"pg_upgrade", but the idea was rejected.

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

+ Everyone has their own god. +

On Thu, Nov 21, 2013 at 2:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> The argument elsewhere in this thread was that the reason for putting
>> this in the connection options was so that you do *not* have to patch up
>> every client to be able to use this functionality. If you have to add
>> separate options everywhere, then you might as well just have a separate
>> libpq function to initiate the session.
>
> Right, Andres was saying that we had to do both (special switches that
> lead to calling a special connection function).

Doesn't the new option 'standalone_datadir' (which is already in
patch) a good candidate for special switch?
How does having one more new switch helps better?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Thu, Nov 21, 2013 at 9:11 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Thu, Nov 21, 2013 at 2:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>>> The argument elsewhere in this thread was that the reason for putting
>>> this in the connection options was so that you do *not* have to patch up
>>> every client to be able to use this functionality. If you have to add
>>> separate options everywhere, then you might as well just have a separate
>>> libpq function to initiate the session.
>>
>> Right, Andres was saying that we had to do both (special switches that
>> lead to calling a special connection function).
>
> Doesn't the new option 'standalone_datadir' (which is already in
> patch) a good candidate for special switch?
> How does having one more new switch helps better?

Here what I have in mind is that:
a. In pg_dump or other internal utilities where we want to use this
feature, they should call PQenableStart() or some other API before
calling PQConnect() which will indicate that it wants to operate
as a standalone mode.
b. In psql, if user specifies this special switch (
'standalone_datadir'), then internally we will call PQenableStart()
and use postgres from same
directory.

So standalone_backend option will not be exposed through psql, but
other internal tools can use it.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:
> Here what I have in mind is that:
> a. In pg_dump or other internal utilities where we want to use this
> feature, they should call PQenableStart() or some other API before
> calling PQConnect() which will indicate that it wants to operate
> as a standalone mode.
> b. In psql, if user specifies this special switch (
> 'standalone_datadir'), then internally we will call PQenableStart()
> and use postgres from same
> directory.

Why would you make psql behave differently from our other command-line
clients? That seems bizarre. If we're going to use a special switch
to enable standalone mode, it should be the same on every program
that supports it.

regards, tom lane

On Thu, Nov 21, 2013 at 8:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:
>> Here what I have in mind is that:
>> a. In pg_dump or other internal utilities where we want to use this
>> feature, they should call PQenableStart() or some other API before
>> calling PQConnect() which will indicate that it wants to operate
>> as a standalone mode.
>> b. In psql, if user specifies this special switch (
>> 'standalone_datadir'), then internally we will call PQenableStart()
>> and use postgres from same
>> directory.
>
> Why would you make psql behave differently from our other command-line
> clients?

No, psql should not behave different from other clients. Sorry, I
was under assumption that for other programs we will not take backend
executable
path.
One other thing which is not clear to me is that how by calling
some special/new API we can ensure that the path provided by user is
a valid path, are we going to validate file given in
'standalone_backend' switch in some way?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Thu, Nov 21, 2013 at 9:54 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Thu, Nov 21, 2013 at 8:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:
>>> Here what I have in mind is that:
>>
>> Why would you make psql behave differently from our other command-line
>> clients?
>
> No, psql should not behave different from other clients. Sorry, I
> was under assumption that for other programs we will not take backend
> executable
> path.
> One other thing which is not clear to me is that how by calling
> some special/new API we can ensure that the path provided by user is
> a valid path, are we going to validate file given in
> 'standalone_backend' switch in some way?

Here, do we mean that if user specifies special switch, then
psql/pg_dump will call new API (eg. PQstartSingleUser(dsn)) which
will use postgres from same bin directory they are in, rather than
using from standalone_backend.
Can we consider this as a way to proceed for this patch?

On a side note, today while reading what other software's does to
protect them from such a security threat, I came across this link
(http://osxbook.com/book/bonus/chapter7/binaryprotection/index.html)
which suggests to have encrypted binaries. The use for encrypted
binaries is somewhat similar to what we discussed as a security threat
in this mail thread. Some text from link which made me think that
this is relevant.
For example, "one could turn the requirement around and say that a
given system must not run any binaries unless they are from a
certain source (or set of sources). This could be used to create an
admission-control mechanism for executables, which in turn could
be used in defending against malware. In a draconian managed
environment, it might be desired to limit program execution on managed
systems to a predefined set of programs—nothing else will execute."

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Thu, 2013-11-14 at 12:11 +0530, Amit Kapila wrote:
> If an application wants to allow these connection parameters to be
> used, it would need to do PQenableStartServer() first. If it doesn't,
> those connection parameters will be rejected.

Stupid idea: Would it work that we require an environment variable to be
set before we allow the standalone_backend connection parameter? That's
easy to do, easy to audit, and doesn't require any extra code in the
individual clients.

On Thu, Dec 5, 2013 at 7:25 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On Thu, 2013-11-14 at 12:11 +0530, Amit Kapila wrote:
>> If an application wants to allow these connection parameters to be
>> used, it would need to do PQenableStartServer() first. If it doesn't,
>> those connection parameters will be rejected.
>
> Stupid idea: Would it work that we require an environment variable to be
> set before we allow the standalone_backend connection parameter? That's
> easy to do, easy to audit, and doesn't require any extra code in the
> individual clients.

This is certainly not a stupid idea, rather something on similar lines
has been discussed previously in this thread.
Tom has suggested something similar, but I am not sure if there was a
conclusion on that point. Please see the
relavant discussion at below link:
http://www.postgresql.org/message-id/17384.1346645480@sss.pgh.pa.us

I think the basic question at that time was why should we consider an
environment variable more safe.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Thu, 2013-12-05 at 09:02 +0530, Amit Kapila wrote:
> This is certainly not a stupid idea, rather something on similar lines
> has been discussed previously in this thread.
> Tom has suggested something similar, but I am not sure if there was a
> conclusion on that point. Please see the
> relavant discussion at below link:
> http://www.postgresql.org/message-id/17384.1346645480@sss.pgh.pa.us

Yeah, I think the environment variable idea wasn't actually refuted
there.

On 5 December 2013 01:55, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On Thu, 2013-11-14 at 12:11 +0530, Amit Kapila wrote:
>> If an application wants to allow these connection parameters to be
>> used, it would need to do PQenableStartServer() first. If it doesn't,
>> those connection parameters will be rejected.
>
> Stupid idea: Would it work that we require an environment variable to be
> set before we allow the standalone_backend connection parameter? That's
> easy to do, easy to audit, and doesn't require any extra code in the
> individual clients.

I like the idea... should it be in pg_hba.conf ?
Or should it be next to listen_addresses in postgresql.conf?

hba might be less convenient but seems like the correct place

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2013-12-04 20:55:08 -0500, Peter Eisentraut wrote:
> On Thu, 2013-11-14 at 12:11 +0530, Amit Kapila wrote:
> > If an application wants to allow these connection parameters to be
> > used, it would need to do PQenableStartServer() first. If it doesn't,
> > those connection parameters will be rejected.
>
> Stupid idea: Would it work that we require an environment variable to be
> set before we allow the standalone_backend connection parameter? That's
> easy to do, easy to audit, and doesn't require any extra code in the
> individual clients.

I still don't think it's ok to start forking in arbitrary applications
without their knowledge. So I don't think that buys us enough.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 12/5/13, 6:07 AM, Simon Riggs wrote:
> On 5 December 2013 01:55, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> On Thu, 2013-11-14 at 12:11 +0530, Amit Kapila wrote:
>>> If an application wants to allow these connection parameters to be
>>> used, it would need to do PQenableStartServer() first. If it doesn't,
>>> those connection parameters will be rejected.
>>
>> Stupid idea: Would it work that we require an environment variable to be
>> set before we allow the standalone_backend connection parameter? That's
>> easy to do, easy to audit, and doesn't require any extra code in the
>> individual clients.
>
> I like the idea... should it be in pg_hba.conf ?
> Or should it be next to listen_addresses in postgresql.conf?

No, it's an environment variable.

I think this proposal is a bit deadlocked now.

- There are technical concerns about launching a server executable from
within a client.

- There are conceptual concerns about promoting an embedded database mode.

On the other hand:

- Everyone would like to have a way to use psql (and other basic
clients) in stand-alone mode.

The compromise would be to not launch the server from within the client,
but have client and server communicate over external mechanisms (e.g.,
Unix-domain socket).

The concern about that was that it would open up standalone mode to
accidental third-party connections. While there are some ways around
that (socket in private directory), they are not easy and not portable.
So standalone mode would became less robust and reliable overall.

The only solutions I see are:

1. do nothing

2. do everything (i.e., existing terminal mode plus socket mode plus
embedded mode), letting the user work out the differences

Pick one. ;-)

On 2013-12-05 11:39:29 -0500, Peter Eisentraut wrote:
> I think this proposal is a bit deadlocked now.
>
> - There are technical concerns about launching a server executable from
> within a client.
>
> - There are conceptual concerns about promoting an embedded database mode.
>
> On the other hand:
>
> - Everyone would like to have a way to use psql (and other basic
> clients) in stand-alone mode.
> The only solutions I see are:
>
> 1. do nothing
>
> 2. do everything (i.e., existing terminal mode plus socket mode plus
> embedded mode), letting the user work out the differences
>
> Pick one. ;-)

3) make it an explicit parameter, outside the database DSN, and let the
clients contain a tiny bit of explict code about it. There really
aren't that many clients that can use such a mode sensibly.

If we ever want to support a real embedded mode, much, much more than
this is needed. I don't think we should let that stop us from improving
single user mode.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Thu, Dec 5, 2013 at 11:52 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-12-05 11:39:29 -0500, Peter Eisentraut wrote:
>> I think this proposal is a bit deadlocked now.
>>
>> - There are technical concerns about launching a server executable from
>> within a client.
>>
>> - There are conceptual concerns about promoting an embedded database mode.
>>
>> On the other hand:
>>
>> - Everyone would like to have a way to use psql (and other basic
>> clients) in stand-alone mode.
>> The only solutions I see are:
>>
>> 1. do nothing
>>
>> 2. do everything (i.e., existing terminal mode plus socket mode plus
>> embedded mode), letting the user work out the differences
>>
>> Pick one. ;-)
>
> 3) make it an explicit parameter, outside the database DSN, and let the
> clients contain a tiny bit of explict code about it. There really
> aren't that many clients that can use such a mode sensibly.
>
> If we ever want to support a real embedded mode, much, much more than
> this is needed. I don't think we should let that stop us from improving
> single user mode.

Yeah, seriously. I don't understand what the big deal is here. The
right design here is 99.44% clear here, and the committer (presumably
Tom) can handle the other 0.56% however he'd like. Let's do this and
move on.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Yeah, seriously. I don't understand what the big deal is here. The
> right design here is 99.44% clear here, and the committer (presumably
> Tom) can handle the other 0.56% however he'd like. Let's do this and
> move on.

Yeah, but the remaining 0.56% is an important decision, not least because
it's got security implications. I think we need some consensus not just
a unilateral committer decision.

I'm pretty much persuaded by Andres' point that we should not allow a
child process to be launched under a client app without clear permission
from the code of the app (and *not* just some environment variable that
might have been set far away, perhaps by someone who doesn't know what the
app assumes about SIGCHLD etc). So a separate connection call seems like
not a bad idea. In the case of psql and pg_dump it'd be reasonable to
invent a separate command line switch that drives use of this call instead
of normal PQconnect. Doing that, and *not* allowing the text of the
connection string to determine it, seems like it pretty well solves any
security objections. It might be unpleasant to use in some cases, though.

Another issue is that we have too many variants of PQconnect already;
which of them are we prepared to clone for this hypothetical new
connection method?

regards, tom lane

On Thu, Dec 5, 2013 at 3:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I'm pretty much persuaded by Andres' point that we should not allow a
> child process to be launched under a client app without clear permission
> from the code of the app (and *not* just some environment variable that
> might have been set far away, perhaps by someone who doesn't know what the
> app assumes about SIGCHLD etc). So a separate connection call seems like
> not a bad idea. In the case of psql and pg_dump it'd be reasonable to
> invent a separate command line switch that drives use of this call instead
> of normal PQconnect. Doing that, and *not* allowing the text of the
> connection string to determine it, seems like it pretty well solves any
> security objections.

Yep.

> It might be unpleasant to use in some cases, though.

Why would there be more than a few cases in the first place? Who is
going to use this beyond psql, pg_dump(all), and pg_upgrade, and why?

> Another issue is that we have too many variants of PQconnect already;
> which of them are we prepared to clone for this hypothetical new
> connection method?

PQconnectdbParams, I assume. Isn't that the one to rule them all,
modulo async connect which I can't think is relevant here?

Or don't clone that one but instead have
PQnextConnectionShouldForkThisBinary('...') and let the psql/pg_dump
switch be --standalone=full-path-to-the-postgres-binary.

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

On 12/05/2013 10:37 PM, Robert Haas wrote:
> On Thu, Dec 5, 2013 at 3:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> It might be unpleasant to use in some cases, though.
>
> Why would there be more than a few cases in the first place? Who is
> going to use this beyond psql, pg_dump(all), and pg_upgrade, and why?

Well, you might want to use pgAdmin, or your other favorite admin tool.
I'm not sure how well it would work, and I think it's OK if we say
"sorry, can't do that", but it's not a crazy thing to want.

>> Another issue is that we have too many variants of PQconnect already;
>> which of them are we prepared to clone for this hypothetical new
>> connection method?
>
> PQconnectdbParams, I assume. Isn't that the one to rule them all,
> modulo async connect which I can't think is relevant here?

Right. Not all of the parameters will make sense for a stand-alone
backend though, like the hostname and port number. And I think you need
need a new parameter to pass the path to the 'postgres' executable,
unless we re-use the host parameter for that.

> Or don't clone that one but instead have
> PQnextConnectionShouldForkThisBinary('...') and let the psql/pg_dump
> switch be --standalone=full-path-to-the-postgres-binary.

I think a separate function makes more sense.

- Heikki

On 2013-12-05 23:01:28 +0200, Heikki Linnakangas wrote:
> On 12/05/2013 10:37 PM, Robert Haas wrote:
> >On Thu, Dec 5, 2013 at 3:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>It might be unpleasant to use in some cases, though.
> >
> >Why would there be more than a few cases in the first place? Who is
> >going to use this beyond psql, pg_dump(all), and pg_upgrade, and why?
>
> Well, you might want to use pgAdmin, or your other favorite admin tool. I'm
> not sure how well it would work, and I think it's OK if we say "sorry, can't
> do that", but it's not a crazy thing to want.

Pgadmin wouldn't work, it uses multiple connections for anything but the
most trivial tasks. You can't even send a manual sql query using only
one connection.
I think that's true for most of the non-trivial tools.

> >>Another issue is that we have too many variants of PQconnect
> >>already; which of them are we prepared to clone for this
> >>hypothetical new connection method?
> >
> >PQconnectdbParams, I assume. Isn't that the one to rule them all,
> >modulo async connect which I can't think is relevant here?

> Right. Not all of the parameters will make sense for a stand-alone backend
> though, like the hostname and port number. And I think you need need a new
> parameter to pass the path to the 'postgres' executable, unless we re-use
> the host parameter for that.

Hm. I'd guessed that we wouldn't use the connection string to pass down
the executable name and the datadir now that we're inventing a separate
function. But maybe that's unneccessary.

What parameters do we require to be set for that mode:
* path to postgres
* data directory
* database name (single mode after all)
* port, because of the shmem key? I'd say that's not important enough

I think we also need to be able to pass some additional parameters to
postgres:
- config_file, hba_file, ... might be required to start pg in some environments
- -P, -O , are sometimes required in cases single user mode is
neccessary for data recovery.

So I think we should just allow passing through arguments to postgres.

Not sure if we need anything but the pid of the postmaster be returned?

> >Or don't clone that one but instead have
> >PQnextConnectionShouldForkThisBinary('...') and let the psql/pg_dump
> >switch be --standalone=full-path-to-the-postgres-binary.

Yuck, that's ugly.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2013-12-05 23:01:28 +0200, Heikki Linnakangas wrote:
>> Right. Not all of the parameters will make sense for a stand-alone backend
>> though, like the hostname and port number. And I think you need need a new
>> parameter to pass the path to the 'postgres' executable, unless we re-use
>> the host parameter for that.

> Hm. I'd guessed that we wouldn't use the connection string to pass down
> the executable name and the datadir now that we're inventing a separate
> function. But maybe that's unneccessary.

> What parameters do we require to be set for that mode:
> * path to postgres
> * data directory
> * database name (single mode after all)
> * port, because of the shmem key? I'd say that's not important enough

> I think we also need to be able to pass some additional parameters to
> postgres:
> - config_file, hba_file, ... might be required to start pg in some environments
> - -P, -O , are sometimes required in cases single user mode is
> neccessary for data recovery.

Right, so by the time we're done, we'd still need a connection string or
the moral equivalent.

My feeling is that we should just treat the executable name and data
directory path as new connection parameters, which'd be ignored in
normal-connection mode, just as some other parameters will be ignored in
single-user mode. Otherwise we'll find ourselves building parameter
setting infrastructure that pretty much duplicates what's there for the
existing connection parameters.

I think the special-purpose command line switches you mention can be
passed through PGOPTIONS, rather than inventing a new parameter -- do you
have an objection to that?

> Not sure if we need anything but the pid of the postmaster be returned?

The new PQconnect routine would certainly hand back a PGconn. I think
we'd need a new query function PQchildPid(PGconn *) or some such to
provide access to the child process PID.

regards, tom lane

On 2013-12-06 11:02:48 -0500, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> My feeling is that we should just treat the executable name and data
> directory path as new connection parameters, which'd be ignored in
> normal-connection mode, just as some other parameters will be ignored in
> single-user mode. Otherwise we'll find ourselves building parameter
> setting infrastructure that pretty much duplicates what's there for the
> existing connection parameters.

Right.

> I think the special-purpose command line switches you mention can be
> passed through PGOPTIONS, rather than inventing a new parameter -- do you
> have an objection to that?

I am not sure if they currently will get recognized early enough and
whether permission checking will interferes, but if so, that's probably
fixable.

> > Not sure if we need anything but the pid of the postmaster be returned?
>
> The new PQconnect routine would certainly hand back a PGconn. I think
> we'd need a new query function PQchildPid(PGconn *) or some such to
> provide access to the child process PID.

I was thinking of a pid_t* argument to the new routine, but it's likely
unneccessary as we're probably going to end up storing it in PGconn
anyway.

There's the question what we're going to end up doing with the current
single user mode? There's some somewhat ugly code around for it...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2013-12-06 11:02:48 -0500, Tom Lane wrote:
>> I think the special-purpose command line switches you mention can be
>> passed through PGOPTIONS, rather than inventing a new parameter -- do you
>> have an objection to that?

> I am not sure if they currently will get recognized early enough and
> whether permission checking will interferes, but if so, that's probably
> fixable.

Shouldn't be a problem --- the single-user mode will just concatenate
the options parameter onto the command line it builds.

> There's the question what we're going to end up doing with the current
> single user mode? There's some somewhat ugly code around for it...

Nothing, in the short term. In a release or two we can get rid of it,
probably, but I'd hesitate to provide no overlap at all of these
usage modes.

regards, tom lane

On Fri, Dec 6, 2013 at 04:04:36PM +0100, Andres Freund wrote:
> On 2013-12-05 23:01:28 +0200, Heikki Linnakangas wrote:
> > On 12/05/2013 10:37 PM, Robert Haas wrote:
> > >On Thu, Dec 5, 2013 at 3:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > >>It might be unpleasant to use in some cases, though.
> > >
> > >Why would there be more than a few cases in the first place? Who is
> > >going to use this beyond psql, pg_dump(all), and pg_upgrade, and why?
> >
> > Well, you might want to use pgAdmin, or your other favorite admin tool. I'm
> > not sure how well it would work, and I think it's OK if we say "sorry, can't
> > do that", but it's not a crazy thing to want.
>
> Pgadmin wouldn't work, it uses multiple connections for anything but the
> most trivial tasks. You can't even send a manual sql query using only
> one connection.
> I think that's true for most of the non-trivial tools.

FYI, pg_upgrade in parallel mode needs multiple database connections
too.

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

+ Everyone has their own god. +