Configurable location for extension .control files

Hello

I am working with the NixOS Linux Distribution [nixos], which has a
fairly radical approach to package management. If you aren't familiar
with it, essentially all packages are installed in isolation - such that
packages cannot interfere with each other.

good.

This is causing a bit of a problem with PostgreSQL extensions that are
usually installed via CREATE EXTENSION. For extensions to be used, a
.control file must be placed in SHAREDIR/extension, but this is not
possible under NixOS as once PostgreSQL is installed that directory is
essentially immutable.

What about shared objects, .sql files and documentation an extension may have
to install ?

What wolud work best for us is to allow this path to be configurable,
ideally through either an environment variable, command line switch, or
(and this is the least desirable) a postgresql.conf option.

There is another point into allowing installation in different path : "make
check" and "make installcheck" targets...

Would love to hear your thoughts. Once I get confirmation on the best
approach to take, I can try my hand at providing a patch.

No idea on the best approach yet. But I am interested in this topic (for
debian packaging).

PS: I have a serie of bugfix and patches pending in the current commitfest
(http://commitfest.postgresql.org) to help build with VPATH. You may be
interested in them...
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Oliver Charles <ollie@ocharles.org.uk> writes:

I am working with the NixOS Linux Distribution [nixos], which has a
fairly radical approach to package management. If you aren't familiar
with it, essentially all packages are installed in isolation - such that
packages cannot interfere with each other.

Maybe you need to rethink that concept. Surely there are many other
cases where package A extends package B and needs to be installed
somewhere where B is expecting to look for extensions.

This is causing a bit of a problem with PostgreSQL extensions that are
usually installed via CREATE EXTENSION. For extensions to be used, a
.control file must be placed in SHAREDIR/extension, but this is not
possible under NixOS as once PostgreSQL is installed that directory is
essentially immutable.

What wolud work best for us is to allow this path to be configurable,
ideally through either an environment variable, command line switch, or
(and this is the least desirable) a postgresql.conf option.

Basically, none of those are likely to get accepted because of security
concerns. We *don't* want this path to be run-time adjustable.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 06/04/2013 06:25 PM, Tom Lane wrote:

What wolud work best for us is to allow this path to be configurable,
ideally through either an environment variable, command line switch, or
(and this is the least desirable) a postgresql.conf option.

Basically, none of those are likely to get accepted because of security
concerns. We *don't* want this path to be run-time adjustable.

It turns out that the current way things are done is almost enough.
Here's how it works at the moment:

We have a 'postgresql' expression which builds PostgreSQL from a tarball
- configure/make/make install to some directory. Next, extensions are
built into their own (entirely separate) directories. Finally, there is
a bit of glue at the end that builds a 'postgresql-and-plugins'
directory, which is a tree consisting entirely of symlinks to the
'postgresql' directory and all the various extensions. Thus the final
tree looks as would be expected if this were on a mutable file system.

I'm currently having a bit of a problem in that Pg still seems to look
in the 'postgresql' directory, not the '-and-plugins' one. I'm not sure
what's going on here, because if I understand the source code correctly,
it should be finding a common root based on #defines at install time,
and then finding this relative to the currently executing path. Could it
be that the symlink to the postgres binary is being expanded before this
path construction takes place?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 06/04/2013 10:25 AM, Tom Lane wrote:

What wolud work best for us is to allow this path to be configurable,
ideally through either an environment variable, command line switch, or
(and this is the least desirable) a postgresql.conf option.

Basically, none of those are likely to get accepted because of security
concerns. We *don't* want this path to be run-time adjustable.

Really? I don't see a security concern in having a postgresql.conf
option which requires a full restart. If the user can edit
postgresql.conf and do a cold restart, presumably they can do anything
they want anyway.

If SET PERSISTENT gets into 9.4, then we might need to restrict it from
setting certain settings, like this one. But until that feature is
real, I don't see the potential expliot here.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Josh Berkus <josh@agliodbs.com> writes:

On 06/04/2013 10:25 AM, Tom Lane wrote:

Basically, none of those are likely to get accepted because of security
concerns. We *don't* want this path to be run-time adjustable.

Really? I don't see a security concern in having a postgresql.conf
option which requires a full restart. If the user can edit
postgresql.conf and do a cold restart, presumably they can do anything
they want anyway.

Yeah, if the config option were to be superuser-only, the security issue
would be ameliorated --- not removed entirely, IMO, but at least
weakened. However, this seems to me to be missing the point, which is
that the extensions feature is designed to let the DBA have control over
which extensions are potentially installable. If we allow extension
control files to be loaded from any random directory then we lose that.
Part of the argument for not requiring superuser permissions to execute
CREATE EXTENSION was based on that restriction, so we'd need to go back
and rethink the permissions needed for CREATE EXTENSION.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-06-04 13:25:10 -0400, Tom Lane wrote:

Oliver Charles <ollie@ocharles.org.uk> writes:

I am working with the NixOS Linux Distribution [nixos], which has a
fairly radical approach to package management. If you aren't familiar
with it, essentially all packages are installed in isolation - such that
packages cannot interfere with each other.

Maybe you need to rethink that concept. Surely there are many other
cases where package A extends package B and needs to be installed
somewhere where B is expecting to look for extensions.

Yea, I have my doubt about that concept as well.

This is causing a bit of a problem with PostgreSQL extensions that are
usually installed via CREATE EXTENSION. For extensions to be used, a
.control file must be placed in SHAREDIR/extension, but this is not
possible under NixOS as once PostgreSQL is installed that directory is
essentially immutable.

What wolud work best for us is to allow this path to be configurable,
ideally through either an environment variable, command line switch, or
(and this is the least desirable) a postgresql.conf option.

Basically, none of those are likely to get accepted because of security
concerns. We *don't* want this path to be run-time adjustable.

But I have to say, I think this argument isn't all that convincing
either. Without superuser rights loading a control file shouldn't give
you any more permissions than plainly executing the sql script
yourself. Everything else would be a bug we need to fix.
With superuser rights there is nothing stopping the user to LOAD the
library directly or create a C function that has an arbitrary library
path hardcoded directly. With the latter you can trivially enough
replace pg internal functions that are normally executed so you effectively
have something which will get executed on every connection.

Placing restrictions on what can be done in postgresql.conf, which
already has access to shared_preload_libraries, doesn't provide
additional protection as well since we don't require specific locations
for files in s_p_l anyway.

The only argument with a good bit of merit I can see is that it could
lead to unexpected extensions being loaded if e.g. hstore isn't
installed in the normal extension directory but another extension with
the same name somewhere else. But that seems like a problem of the
system administrator that configured the additional directories.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> writes:

The only argument with a good bit of merit I can see is that it could
lead to unexpected extensions being loaded if e.g. hstore isn't
installed in the normal extension directory but another extension with
the same name somewhere else.

And just think about the fun you could have with inconsistent files
named hstore--1.0--1.1.sql in different directories. The extension
feature is really really not designed to be able to search a path of
directories.

It presumably wouldn't be terribly hard for Oliver to patch the sources
to look in something other than SHAREDIR/extension/, but I'm not sure
I see the point of inventing a platform-specific name for that
directory; seems like it would mostly just confuse users coming from
other platforms. Instead, what about not treating that directory as
part of the base package in the first place? If you've got the concept
of directories that multiple packages can install into, just make this
be one of those.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-06-04 16:07:23 -0400, Tom Lane wrote:

Andres Freund <andres@2ndquadrant.com> writes:

The only argument with a good bit of merit I can see is that it could
lead to unexpected extensions being loaded if e.g. hstore isn't
installed in the normal extension directory but another extension with
the same name somewhere else.

And just think about the fun you could have with inconsistent files
named hstore--1.0--1.1.sql in different directories. The extension
feature is really really not designed to be able to search a path of
directories.

Well, some definitional work would be needed. That specific problem
seems sensibl solveable by defineing those to be relative to the control
file.

I don't really care much about Oliver's usecase TBH, but I would very much
welcome making it easier for application developers to package part of
ther in-database application code as extensions without either requiring
a selfcompiled postgres with a custom extension dir or them having have
root access to the machine running postgres.
Providing them with access to a directory that's only configured as
additional extension directory for a development cluster would be a huge
improvement.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> writes:

I don't really care much about Oliver's usecase TBH, but I would very much
welcome making it easier for application developers to package part of
ther in-database application code as extensions without either requiring
a selfcompiled postgres with a custom extension dir or them having have
root access to the machine running postgres.

Well, if you're installing an extension that includes C code, you're
going to need root access anyway to install the shlib (at least on
conservatively-configured machines). For pure-SQL extensions, Dimitri's
been pushing a different approach that needn't involve the filesystem at
all. We didn't get that finished in 9.3 but I think it's still on the
agenda for 9.4.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 06/04/2013 09:07 PM, Tom Lane wrote:

It presumably wouldn't be terribly hard for Oliver to patch the sources
to look in something other than SHAREDIR/extension/, but I'm not sure
I see the point of inventing a platform-specific name for that
directory; seems like it would mostly just confuse users coming from
other platforms. Instead, what about not treating that directory as
part of the base package in the first place? If you've got the concept
of directories that multiple packages can install into, just make this
be one of those.

I currently have postgresql patched to search getenv("PGSHAREDIR")
before looking at SHAREDIR, which seems to work. As I said in a previous
reply - by my understanding it should all just work without any patches,
but for some reason the path coming out from get_share_dir is not
relative to the executable symlink, but is relative to executable under
that symlink. If that problem can be solved, then everything can just
carry on working.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-06-04 16:24:07 -0400, Tom Lane wrote:

Andres Freund <andres@2ndquadrant.com> writes:

I don't really care much about Oliver's usecase TBH, but I would very much
welcome making it easier for application developers to package part of
ther in-database application code as extensions without either requiring
a selfcompiled postgres with a custom extension dir or them having have
root access to the machine running postgres.

Well, if you're installing an extension that includes C code, you're
going to need root access anyway to install the shlib (at least on
conservatively-configured machines). For pure-SQL extensions, Dimitri's
been pushing a different approach that needn't involve the filesystem at
all. We didn't get that finished in 9.3 but I think it's still on the
agenda for 9.4.

Yea, I know of Dimitri's work ;). But I really would like this to work
for C extensions as well. For me personally its no problem at all that
this wouldn't work on conservatively configured machines. Heck, I
*don't* want it to work on production machines. But being able to
configure a dev machine to allow it would be very helpful.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 5 June 2013 05:58, Andres Freund <andres@2ndquadrant.com> wrote:

Yea, I know of Dimitri's work ;). But I really would like this to work
for C extensions as well. For me personally its no problem at all that
this wouldn't work on conservatively configured machines. Heck, I
*don't* want it to work on production machines. But being able to
configure a dev machine to allow it would be very helpful.

Just the other day I was looking at what it would take to drop some extra
compiled extensions somewhere that the Mac Postgres.app could find them,
and was mildly disappointed to see that it would have to be inside the app
bundle itself, so they would then disappear on upgrade etc. The more maccy
way to install extensions for a user app generally is to stick them under
~/Library/<appname>.

Note that Postgres.app is very much an application developer workstation
targeted distribution, so the security issues don't really come into the
picture. It would also be enough in this case to allow multiple paths to be
compiled in rather than pulled from postgresql.conf, but either would work.

Cheers

Tom

Tom,

Yeah, if the config option were to be superuser-only, the security issue
would be ameliorated --- not removed entirely, IMO, but at least
weakened. However, this seems to me to be missing the point, which is
that the extensions feature is designed to let the DBA have control over
which extensions are potentially installable. If we allow extension
control files to be loaded from any random directory then we lose that.
Part of the argument for not requiring superuser permissions to execute
CREATE EXTENSION was based on that restriction, so we'd need to go back
and rethink the permissions needed for CREATE EXTENSION.

I do see the utility in having the extension folder relocatable by
packagers; I could really use this for vagrant builds of PostgreSQL,
which I use for testing. Right now I do a lot of file copying of .so
files. In my case, though, I only need to change the whole extension
folder location, I don't need to have multiple locations, a dirpath, or
anything sophisticated. That is, a super-user, cold-start only option
of "extension_path='/vagrant/extensions/'" would work for my case, and I
suspect most packaging cases as well.

This seems like it would work for Oliver's case. And I don't see how
making the folder relocatable as an on-start option hurts our security
at all; we're simply doing something which the same user could do with
symlinks, only much more neatly.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom Lane <tgl@sss.pgh.pa.us> writes:

Andres Freund <andres@2ndquadrant.com> writes:

I don't really care much about Oliver's usecase TBH, but I would very much
welcome making it easier for application developers to package part of
ther in-database application code as extensions without either requiring
a selfcompiled postgres with a custom extension dir or them having have
root access to the machine running postgres.

Well, if you're installing an extension that includes C code, you're
going to need root access anyway to install the shlib (at least on
conservatively-configured machines).

At most places, that means you require the extension to be properly
packaged (RPM or DEB or something else) in a way that the sysadmins are
able to manage it in production.

For sites where they don't have in-house system packagers, it would be
very welcome to be able to setup PostgreSQL in a way that allows it to
LOAD the extension's binary code (.so, .dll, .dylib) from a non-root
owned place even if you installed it from official packages.

Of course, it wouldn't be activated by default and frowned upon in the
docs for conservative production environments. The use case still seems
valid to me, and would nicely complete the Extension Templates facility
given the right tooling.

Can I prepare a patch allowing PostgreSQL to load extension control
files and modules from a non-default place in the file-system? Please?

For pure-SQL extensions, Dimitri's
been pushing a different approach that needn't involve the filesystem at
all. We didn't get that finished in 9.3 but I think it's still on the
agenda for 9.4.

Yes it is. The patch is listed in for the next commitfest, I intend to
check about bitrot and update it before the CF starts.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:

For sites where they don't have in-house system packagers, it would be
very welcome to be able to setup PostgreSQL in a way that allows it to
LOAD the extension's binary code (.so, .dll, .dylib) from a non-root
owned place even if you installed it from official packages.

Of course, it wouldn't be activated by default and frowned upon in the
docs for conservative production environments. The use case still seems
valid to me, and would nicely complete the Extension Templates facility
given the right tooling.

Can I prepare a patch allowing PostgreSQL to load extension control
files and modules from a non-default place in the file-system? Please?

I don't see the need for this. The sort of situation you're describing
probably has PG installed at a non-default install --prefix anyway, and
thus the "standard" extension directory is already not root-owned.

More generally, it seems pretty insane to me to want to configure a
"trusted" PG installation so that it can load C code from an untrusted
place. The trust level cannot be any higher than the weakest link.
Thus, I don't see a scenario in which any packager would ship binaries
using such an option, even if it existed.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-06-10 10:13:45 -0400, Tom Lane wrote:

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:

For sites where they don't have in-house system packagers, it would be
very welcome to be able to setup PostgreSQL in a way that allows it to
LOAD the extension's binary code (.so, .dll, .dylib) from a non-root
owned place even if you installed it from official packages.

Of course, it wouldn't be activated by default and frowned upon in the
docs for conservative production environments. The use case still seems
valid to me, and would nicely complete the Extension Templates facility
given the right tooling.

Can I prepare a patch allowing PostgreSQL to load extension control
files and modules from a non-default place in the file-system? Please?

I don't see the need for this. The sort of situation you're describing
probably has PG installed at a non-default install --prefix anyway, and
thus the "standard" extension directory is already not root-owned.

Why does it need to be a non-distribution postgres? A customer
needing to develop a postgres extensions is a fairly frequent thing, but
often enough they are still happy to use a distribution postgres.

More generally, it seems pretty insane to me to want to configure a
"trusted" PG installation so that it can load C code from an untrusted
place. The trust level cannot be any higher than the weakest link.
Thus, I don't see a scenario in which any packager would ship binaries
using such an option, even if it existed.

I fail to see the logic here. We do allow LOAD with arbitrary paths. We
do allow untrusted languages. We do allow specifying arbitrary paths in
'C' CREATE FUNCTION statements. ...
Sure, all of that requires superuser, but so does anything in an
extension that can cause an .so to be loaded?

Why are extensions different?

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> writes:

On 2013-06-10 10:13:45 -0400, Tom Lane wrote:

More generally, it seems pretty insane to me to want to configure a
"trusted" PG installation so that it can load C code from an untrusted
place. The trust level cannot be any higher than the weakest link.
Thus, I don't see a scenario in which any packager would ship binaries
using such an option, even if it existed.

I fail to see the logic here.

You are confusing location in the filesystem with permissions. Assuming
that a sysadmin wants to allow, say, the postgres DBA to install random
extensions, all he has to do is adjust the permissions on the .../extension
directory to allow that (or not). Putting the extension directory
somewhere else doesn't change that meaningfully, it just makes things
more confusing and hence error-prone.

In any case, no packager is going to ship an insecure-by-default
configuration, which is what Dimitri seems to be fantasizing would
happen. It would have to be local option to relax the permissions
on the directory, no matter where it is.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-06-10 10:39:48 -0400, Tom Lane wrote:

Andres Freund <andres@2ndquadrant.com> writes:

On 2013-06-10 10:13:45 -0400, Tom Lane wrote:

More generally, it seems pretty insane to me to want to configure a
"trusted" PG installation so that it can load C code from an untrusted
place. The trust level cannot be any higher than the weakest link.
Thus, I don't see a scenario in which any packager would ship binaries
using such an option, even if it existed.

I fail to see the logic here.

You are confusing location in the filesystem with permissions. Assuming
that a sysadmin wants to allow, say, the postgres DBA to install random
extensions, all he has to do is adjust the permissions on the .../extension
directory to allow that (or not). Putting the extension directory
somewhere else doesn't change that meaningfully, it just makes things
more confusing and hence error-prone.

That's different because that a) effects all clusters on the machine and
b) will get reversed by package management on the next update.

In any case, no packager is going to ship an insecure-by-default
configuration, which is what Dimitri seems to be fantasizing would
happen. It would have to be local option to relax the permissions
on the directory, no matter where it is.

*I* don't want that at all. All I'd like to have is a postgresql.conf
option specifying additional locations.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> writes:

In any case, no packager is going to ship an insecure-by-default
configuration, which is what Dimitri seems to be fantasizing would
happen. It would have to be local option to relax the permissions
on the directory, no matter where it is.

*I* don't want that at all. All I'd like to have is a postgresql.conf
option specifying additional locations.

Same from me. I think I would even take non-plural location.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers