Proof of concept: standalone backend with full FE/BE protocol
Attached is a proof-of-concept patch for talking to a standalone backend
using libpq and a pair of pipes. It works, to the extent that psql and
pg_dump can run without any postmaster present:
$ psql regression
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
$ psql "standalone_datadir = $PGDATA dbname = regression"
psql (9.3devel)
Type "help" for help.
regression=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------------------------+----------+-------
public | a | table | tgl
public | a_star | table | tgl
public | abstime_tbl | table | tgl
public | aggtest | table | tgl
public | array_index_op_test | table | tgl
...
but there's quite a bit of work to do yet before this could be a
committable patch. Some notes:
1. As you can see above, the feature is triggered by specifying the new
connection option "standalone_datadir", whose value must be the location
of the data directory. I also invented an 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. (I don't think it can use the relative
path tricks we use in pg_ctl and elsewhere, since there's no good reason
to assume that it's running in a Postgres-supplied program.) I'm not
particularly wedded to these names or the syntax, but I think this is the
basic functionality we'd need.
2. As far as the backend is concerned, use of FE/BE protocol rather than
traditional standalone mode is triggered by writing "--child" instead of
"--single" as the first argument on the postgres command line. (I'm not
wedded to that name either ... anybody have a better idea?)
3. The bulk of the changes have to do with the fact that we need to keep
track of two file descriptors not one. This was a bit tedious, but fairly
straightforward --- though I was surprised to find that send() and recv()
don't work on pipes, at least not on Linux. You have to use read() and
write() instead.
4. As coded, the backend assumes the incoming pipe is on its FD 0 and the
outgoing pipe is on its FD 1. This made the command line simple but I'm
having second thoughts about it: if anything inside the backend tries to
read stdin or write stdout, unpleasant things will happen. It might be
better to not reassign the pipe FD numbers. In that case we'd have to
pass them on the command line, so that the syntax would be something
like "postgres --child 4,5 -D pgdata ...".
5. The fork/exec code is pretty primitive with respect to error handling.
I didn't put much time into it since I'm afraid we may need to refactor
it entirely before a Windows equivalent can be written. (And I need
somebody to write/test the Windows equivalent - any volunteers?)
6. I didn't bother with passing anything except -D and the database name
to the standalone backend. Probably we'd like to be able to pass other
command-line switches too. Again, it's not clear that it's worth doing
much here until we have equivalent Windows code available.
7. I haven't tried to make pg_upgrade use this yet.
8. PQcancel needs some work - it can't do what it does now, but it could
do kill(conn->postgres_pid, SIGINT) instead. At least in Unix. I have no
idea what we'd do in Windows. This doesn't matter for pg_upgrade of
course, but it'd be important for manual use of this mode.
Although the immediate use of this would be for pg_upgrade, I think that
people would soon drop the traditional --single mode and do anything they
need to do in standalone mode using this method, since psql is so vastly
more user-friendly than a --single backend.
In the longer run, this could provide a substitute for the "embedded
database" mode that we keep saying we're not going to implement. That is,
applications could fire up a standalone backend as a child process and not
need a postmaster anywhere, which would be a lot more convenient for an
app that wants a private database and doesn't want to involve its users in
managing a Postgres server. However, there are some additional things
we'd need to think about before advertising it as a fit solution for that.
Notably, while the lack of any background processes is just what you want
for pg_upgrade and disaster recovery, an ordinary application is probably
going to want to rely on autovacuum; and we need bgwriter and other
background processes for best performance. So I'm speculating about
having a postmaster process that isn't listening on any ports, but is
managing background processes in addition to a single child backend.
That's for another day though.
Comments? Anyone want to have a go at fixing this for Windows?
regards, tom lane
On 03.09.2012 03:23, Tom Lane wrote:
1. As you can see above, the feature is triggered by specifying the new
connection option "standalone_datadir", whose value must be the location
of the data directory. I also invented an 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. (I don't think it can use the relative
path tricks we use in pg_ctl and elsewhere, since there's no good reason
to assume that it's running in a Postgres-supplied program.) I'm not
particularly wedded to these names or the syntax, but I think this is the
basic functionality we'd need.
Are there security issues with this? If a user can specify libpq
connection options, he can now execute any file he wants by passing it
as standalone_backend. Granted, you shouldn't allow an untrusted user to
specify libpq connection options, because allowing to open a TCP
connection to an arbitrary address can be a problem by itself, but it
seems like this might make the situation much worse. contrib/dblink
springs to mind..
3. The bulk of the changes have to do with the fact that we need to keep
track of two file descriptors not one. This was a bit tedious, but fairly
straightforward --- though I was surprised to find that send() and recv()
don't work on pipes, at least not on Linux. You have to use read() and
write() instead.
Would socketpair(2) be simpler?
7. I haven't tried to make pg_upgrade use this yet.
Hmm, pg_upgrade uses pg_dumpall rather than pg_dump, so it needs to
connect once per database. That means launching the standalone backend
multiple times. I guess that works, as long as pg_dumpall doesn't try to
hold multiple connections open at any one time.
- Heikki
Heikki Linnakangas <hlinnaka@iki.fi> writes:
On 03.09.2012 03:23, Tom Lane wrote:
1. As you can see above, the feature is triggered by specifying the new
connection option "standalone_datadir", whose value must be the location
of the data directory. I also invented an option "standalone_backend",
which can be set to specify which postgres executable to launch.
Are there security issues with this? If a user can specify libpq
connection options, he can now execute any file he wants by passing it
as standalone_backend. Granted, you shouldn't allow an untrusted user to
specify libpq connection options, because allowing to open a TCP
connection to an arbitrary address can be a problem by itself, but it
seems like this might make the situation much worse. contrib/dblink
springs to mind..
Hmm, that's a good point. Maybe we should only allow the executable
name to come from an environment variable? Seems kinda klugy though.
3. The bulk of the changes have to do with the fact that we need to keep
track of two file descriptors not one.
Would socketpair(2) be simpler?
Hm, yes, but is it portable enough? It seems to be required by SUS v2,
so we're likely okay on the Unix side, but does Windows have this?
regards, tom lane
On Sun, Sep 02, 2012 at 11:34:42PM -0400, Tom Lane wrote:
Heikki Linnakangas <hlinnaka@iki.fi> writes:
On 03.09.2012 03:23, Tom Lane wrote:
1. As you can see above, the feature is triggered by specifying the new
connection option "standalone_datadir", whose value must be the location
of the data directory. I also invented an option "standalone_backend",
which can be set to specify which postgres executable to launch.Are there security issues with this? If a user can specify libpq
connection options, he can now execute any file he wants by passing it
as standalone_backend. Granted, you shouldn't allow an untrusted user to
specify libpq connection options, because allowing to open a TCP
connection to an arbitrary address can be a problem by itself, but it
seems like this might make the situation much worse. contrib/dblink
springs to mind..Hmm, that's a good point. Maybe we should only allow the executable
name to come from an environment variable? Seems kinda klugy though.
I don't think it's libpq's job to enforce security policy this way. In any
event, it has no reason to presume an environment variable is a safer source.
3. The bulk of the changes have to do with the fact that we need to keep
track of two file descriptors not one.Would socketpair(2) be simpler?
Hm, yes, but is it portable enough? It seems to be required by SUS v2,
so we're likely okay on the Unix side, but does Windows have this?
Windows does not have socketpair(), nor a strict pipe() equivalent. I expect
switching to socketpair() makes the Windows side trickier in some ways and
simpler in others. +1 for exploring that direction first.
Noah Misch <noah@leadboat.com> writes:
On Sun, Sep 02, 2012 at 11:34:42PM -0400, Tom Lane wrote:
Heikki Linnakangas <hlinnaka@iki.fi> writes:
Are there security issues with this? If a user can specify libpq
connection options, he can now execute any file he wants by passing it
as standalone_backend.
Hmm, that's a good point. Maybe we should only allow the executable
name to come from an environment variable? Seems kinda klugy though.
I don't think it's libpq's job to enforce security policy this way. In any
event, it has no reason to presume an environment variable is a safer source.
One easy thing we could do that would at least narrow the risks is to
only allow the executable's *directory* to be specified, hardwiring the
executable file name to "postgres" (or "postgres.exe" I guess).
I'm inclined to think though that environment variables *are* more
secure in this context. In the contrib/dblink example, such a
restriction would certainly help a lot. In any case, we should at
least think of a way that an app using libpq can prevent this type
of attack short of parsing the conn string for itself.
regards, tom lane
Noah Misch <noah@leadboat.com> writes:
Windows does not have socketpair(), nor a strict pipe() equivalent. I expect
switching to socketpair() makes the Windows side trickier in some ways and
simpler in others. +1 for exploring that direction first.
A bit of googling suggests that emulating socketpair() on Windows is not
that hard: basically you create an accepting socket and connect to it.
Ugly I guess but likely to be nicer than emulating the two-pipes trick
exactly.
regards, tom lane
On Mon, Sep 03, 2012 at 12:11:20AM -0400, Tom Lane wrote:
Noah Misch <noah@leadboat.com> writes:
I don't think it's libpq's job to enforce security policy this way. In any
event, it has no reason to presume an environment variable is a safer source.One easy thing we could do that would at least narrow the risks is to
only allow the executable's *directory* to be specified, hardwiring the
executable file name to "postgres" (or "postgres.exe" I guess).
If the user has any interactive access to the machine, he could make a
/tmp/X/postgres symbolic link to the program of his choosing.
I'm inclined to think though that environment variables *are* more
secure in this context. In the contrib/dblink example, such a
restriction would certainly help a lot.
dblink_connect() should only let superusers specify standalone_datadir at all;
normal users have no business manipulating other data directories visible to
the backend running dblink. For superusers, setting both standalone_datadir
and standalone_backend is fair game.
Trusting the environment over connection strings is a wrong policy for, say, a
setuid command-line program. Suppose such a program passes libpq a fixed
connection string to access its embedded database. The program will find
itself explicitly clearing this environment variable to regain safety.
In any case, we should at
least think of a way that an app using libpq can prevent this type
of attack short of parsing the conn string for itself.
My recommendation to affected application authors is to pass the connection
string through PQconninfoParse(). Walk the array, validating or rejecting
arguments like "host" and "standalone_datadir". For maximum safety, the
application would need to reject any unanticipated parameters. We might
soften that by adding a "bool critical" field to PQconninfoOption that
documents whether the option must be understood by a program validating a
connection. Compare the idea of the PNG chunk header's "ancillary" bit.
Parameters like "host" and "standalone_datadir" would be critical, while
"application_name" and "connect_timeout" would be ancillary. But this is a
tough line to draw rigorously.
I was pondering the flexibility from letting the application fork/exec and
supply the client-side descriptor number(s) to libpq. Suppose it wants to
redirect the backend's stderr to a file. A single-threaded application would
temporarily redirect its own stderr while calling PQconnectdb(). In a
multithreaded application, that introduces a race condition when other threads
concurrently write to the normal stderr. By handling redirection in its own
fork/exec code, the application can achieve the outcome safely. Perhaps
offering this can wait until the feature constitutes a more general
embedded-database offering, though.
Thanks,
nm
On Mon, Sep 3, 2012 at 6:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Noah Misch <noah@leadboat.com> writes:
Windows does not have socketpair(), nor a strict pipe() equivalent. I expect
switching to socketpair() makes the Windows side trickier in some ways and
simpler in others. +1 for exploring that direction first.A bit of googling suggests that emulating socketpair() on Windows is not
that hard: basically you create an accepting socket and connect to it.
Ugly I guess but likely to be nicer than emulating the two-pipes trick
exactly.
That sounds a lot like what we were doing in pgpipe() before.. It was
removed in d2c1740dc275543a46721ed254ba3623f63d2204, but that's
because it was dead at the time. Do we need to bring it back?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
5. The fork/exec code is pretty primitive with respect to error handling.
I didn't put much time into it since I'm afraid we may need to refactor it
entirely before a Windows equivalent can be > written. (And I need somebody
to write/test the Windows equivalent - any volunteers?)
I think part of the code for windows can be written by referring function
internal_forkexec(),
If you are okay, I can take up this. Please confirm.
8. PQcancel needs some work - it can't do what it does now, but it could
do kill(conn->postgres_pid, SIGINT) instead. > At least in Unix. I have no
idea what we'd do in Windows. This doesn't matter for pg_upgrade of course,
but it'd be
important for manual use of this mode.
Can pgkill(int pid, int sig) API of PG be used to achieve the same on
Windows.
With Regards,
Amit Kapila.
Amit Kapila <amit.kapila@huawei.com> writes:
I think part of the code for windows can be written by referring function
internal_forkexec(),
If you are okay, I can take up this. Please confirm.
Nobody else volunteered, so have at it. Note that I'm planning to redo
that code to use socketpair(), so possibly you want to wait to see that
before you do anything.
8. PQcancel needs some work - it can't do what it does now, but it could
do kill(conn->postgres_pid, SIGINT) instead. At least in Unix. I have no
idea what we'd do in Windows. This doesn't matter for pg_upgrade of course,
but it'd be important for manual use of this mode.
Can pgkill(int pid, int sig) API of PG be used to achieve the same on
Windows.
Hmm, after looking at src/port/kill.c it doesn't seem like there's much
of a problem with doing that. I had had the idea that our kill
emulation only worked within the backend environment, but of course
pg_ctl wouldn't work if that were so. So this is easier than I thought.
regards, tom lane
On Mon, Sep 3, 2012 at 7:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Amit Kapila <amit.kapila@huawei.com> writes:
8. PQcancel needs some work - it can't do what it does now, but it could
do kill(conn->postgres_pid, SIGINT) instead. At least in Unix. I have no
idea what we'd do in Windows. This doesn't matter for pg_upgrade of course,
but it'd be important for manual use of this mode.Can pgkill(int pid, int sig) API of PG be used to achieve the same on
Windows.Hmm, after looking at src/port/kill.c it doesn't seem like there's much
of a problem with doing that. I had had the idea that our kill
emulation only worked within the backend environment, but of course
pg_ctl wouldn't work if that were so. So this is easier than I thought.
Yeah, kill works fine from non-backend as long as the *receiver* has
our backend environment.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Sep 3, 2012 at 7:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hmm, after looking at src/port/kill.c it doesn't seem like there's much
of a problem with doing that. I had had the idea that our kill
emulation only worked within the backend environment, but of course
pg_ctl wouldn't work if that were so. So this is easier than I thought.
Yeah, kill works fine from non-backend as long as the *receiver* has
our backend environment.
I have another question after thinking about that for awhile: is there
any security concern there? On Unix-oid systems, we expect the kernel
to restrict who can do a kill() on a postgres process. If there's any
similar restriction on who can send to that named pipe in the Windows
version, it's not obvious from the code. Do we have/need any
restriction there?
regards, tom lane
On Mon, Sep 3, 2012 at 8:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Sep 3, 2012 at 7:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hmm, after looking at src/port/kill.c it doesn't seem like there's much
of a problem with doing that. I had had the idea that our kill
emulation only worked within the backend environment, but of course
pg_ctl wouldn't work if that were so. So this is easier than I thought.Yeah, kill works fine from non-backend as long as the *receiver* has
our backend environment.I have another question after thinking about that for awhile: is there
any security concern there? On Unix-oid systems, we expect the kernel
to restrict who can do a kill() on a postgres process. If there's any
similar restriction on who can send to that named pipe in the Windows
version, it's not obvious from the code. Do we have/need any
restriction there?
We use the default for CreateNamedPipe() which is:
" The ACLs in the default security descriptor for a named pipe grant
full control to the LocalSystem account, administrators, and the
creator owner. They also grant read access to members of the Everyone
group and the anonymous account."
(ref: http://msdn.microsoft.com/en-us/library/windows/desktop/aa365150(v=vs.85).aspx)
Given that we only respond to writes (we don't "publish information"
over it), I think that's a reasonable default to use.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Sep 3, 2012 at 8:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I have another question after thinking about that for awhile: is there
any security concern there? On Unix-oid systems, we expect the kernel
to restrict who can do a kill() on a postgres process. If there's any
similar restriction on who can send to that named pipe in the Windows
version, it's not obvious from the code. Do we have/need any
restriction there?
We use the default for CreateNamedPipe() which is:
" The ACLs in the default security descriptor for a named pipe grant
full control to the LocalSystem account, administrators, and the
creator owner. They also grant read access to members of the Everyone
group and the anonymous account."
(ref: http://msdn.microsoft.com/en-us/library/windows/desktop/aa365150(v=vs.85).aspx)
Hm. The write protections sound fine ... but what's the semantics of
reading, is it like Unix pipes? If so, couldn't a random third party
drain the pipe by reading from it, and thereby cause signals to be lost?
regards, tom lane
Noah Misch <noah@leadboat.com> writes:
On Mon, Sep 03, 2012 at 12:11:20AM -0400, Tom Lane wrote:
One easy thing we could do that would at least narrow the risks is to
only allow the executable's *directory* to be specified, hardwiring the
executable file name to "postgres" (or "postgres.exe" I guess).
If the user has any interactive access to the machine, he could make a
/tmp/X/postgres symbolic link to the program of his choosing.
I said "narrow", not "eliminate" ;-)
I'm inclined to think though that environment variables *are* more
secure in this context. In the contrib/dblink example, such a
restriction would certainly help a lot.
Trusting the environment over connection strings is a wrong policy for, say, a
setuid command-line program. Suppose such a program passes libpq a fixed
connection string to access its embedded database. The program will find
itself explicitly clearing this environment variable to regain safety.
Well, a program that was concerned with this would *already* want to
clear every environment variable that affects libpq, else its database
connections could get redirected somewhere surprising. libpq already
provides enough infrastructure to get the list of such variables ... but
only ones that are associated with connection string parameters. So for
this purpose, making the standalone-control parameters not be accessible
through connection strings would actually be worse.
In any case, we should at
least think of a way that an app using libpq can prevent this type
of attack short of parsing the conn string for itself.
My recommendation to affected application authors is to pass the connection
string through PQconninfoParse(). Walk the array, validating or rejecting
arguments like "host" and "standalone_datadir". For maximum safety, the
application would need to reject any unanticipated parameters. We might
soften that by adding a "bool critical" field to PQconninfoOption that
documents whether the option must be understood by a program validating a
connection. Compare the idea of the PNG chunk header's "ancillary" bit.
Parameters like "host" and "standalone_datadir" would be critical, while
"application_name" and "connect_timeout" would be ancillary. But this is a
tough line to draw rigorously.
Even more to the point, we can't seriously expect application authors to
know what to do with connection parameters that didn't exist when they
were writing their code. Every new security-critical parameter would
represent a new set of bugs.
I'm reluctantly coming to the conclusion that we can't pass these
parameters through the regular libpq connection string mechanism, and
will have to invent something else. That's awfully nasty though;
it will pretty much cripple the idea that this would be a simple way to
invoke a quasi-embedded variant of Postgres.
regards, tom lane
On 09/03/2012 04:23 PM, Tom Lane wrote:
I'm reluctantly coming to the conclusion that we can't pass these
parameters through the regular libpq connection string mechanism, and
will have to invent something else. That's awfully nasty though;
it will pretty much cripple the idea that this would be a simple way to
invoke a quasi-embedded variant of Postgres.
That would be a bit sad. BTW, how would this work for things like
auto_vacuum, logging_collector and so on?
cheers
andrew
Hi,
On Monday, September 03, 2012 10:23:52 PM Tom Lane wrote:
I'm reluctantly coming to the conclusion that we can't pass these
parameters through the regular libpq connection string mechanism, and
will have to invent something else. That's awfully nasty though;
it will pretty much cripple the idea that this would be a simple way to
invoke a quasi-embedded variant of Postgres.
What I am asking myself is: why does that have to go through the normal
PQconnectdb* api? This is something completely new and very well might grow
more features that are not necessarily easy to press into PQconnectdb().
PQServer
PQstartServer(const char **keywords, const char **values);
or whatever seems to be more future proof especially considering the
possibility that this will grow into something more featureful.
Greetings,
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Andrew Dunstan <andrew@dunslane.net> writes:
On 09/03/2012 04:23 PM, Tom Lane wrote:
I'm reluctantly coming to the conclusion that we can't pass these
parameters through the regular libpq connection string mechanism, and
will have to invent something else. That's awfully nasty though;
it will pretty much cripple the idea that this would be a simple way to
invoke a quasi-embedded variant of Postgres.
That would be a bit sad. BTW, how would this work for things like
auto_vacuum, logging_collector and so on?
There's already an "options" connection-string option for passing random
GUC settings through to the connected backend. My original plan was to
just add any such settings to the standalone backend's command line.
In this context that would work for postmaster-level GUCs. However,
if we're going to redesign this then maybe something else is more
appropriate.
regards, tom lane
Andres Freund <andres@2ndquadrant.com> writes:
On Monday, September 03, 2012 10:23:52 PM Tom Lane wrote:
I'm reluctantly coming to the conclusion that we can't pass these
parameters through the regular libpq connection string mechanism, and
will have to invent something else. That's awfully nasty though;
it will pretty much cripple the idea that this would be a simple way to
invoke a quasi-embedded variant of Postgres.
What I am asking myself is: why does that have to go through the normal
PQconnectdb* api? This is something completely new and very well might grow
more features that are not necessarily easy to press into PQconnectdb().
Well, what that's mostly going to result in is a huge amount of
duplication :-(. psql, pg_dump, and anything else that wants to support
this will need some alternative command line switch and an alternative
code path to call PQstartServer. I'd hoped to avoid all that. Note
that the POC patch involved not one single line of change in those
application programs.
regards, tom lane
On Monday, September 03, 2012 10:54:23 PM Tom Lane wrote:
Andres Freund <andres@2ndquadrant.com> writes:
On Monday, September 03, 2012 10:23:52 PM Tom Lane wrote:
I'm reluctantly coming to the conclusion that we can't pass these
parameters through the regular libpq connection string mechanism, and
will have to invent something else. That's awfully nasty though;
it will pretty much cripple the idea that this would be a simple way to
invoke a quasi-embedded variant of Postgres.What I am asking myself is: why does that have to go through the normal
PQconnectdb* api? This is something completely new and very well might
grow more features that are not necessarily easy to press into
PQconnectdb().Well, what that's mostly going to result in is a huge amount of
duplication :-(. psql, pg_dump, and anything else that wants to support
this will need some alternative command line switch and an alternative
code path to call PQstartServer. I'd hoped to avoid all that. Note
that the POC patch involved not one single line of change in those
application programs.
I can see why that would be nice, but is it really realistic? Don't we expect
some more diligence in applications using this against letting such a child
continue to run after ctrl-c/SIGTERMing e.g. pg_dump in comparison to closing
a normal database connection? Besides the already mentioned security issues I
would argue that its a good thing to make the applications authors think about
the special requirements of "embedding" PG.
Greetings,
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
