Re: 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.

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.

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.

Thanks,
- Ollie (ocharles)

---
[nixos]: http://nixos.org

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(at)ocharles(dot)org(dot)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

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?

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

Josh Berkus <josh(at)agliodbs(dot)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

On 2013-06-04 13:25:10 -0400, Tom Lane wrote:
> Oliver Charles <ollie(at)ocharles(dot)org(dot)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

Andres Freund <andres(at)2ndquadrant(dot)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

On 2013-06-04 16:07:23 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)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

Andres Freund <andres(at)2ndquadrant(dot)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

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.

On 2013-06-04 16:24:07 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)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

On 5 June 2013 05:58, Andres Freund <andres(at)2ndquadrant(dot)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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Andres Freund <andres(at)2ndquadrant(dot)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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)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

On 2013-06-10 10:13:45 -0400, Tom Lane wrote:
> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)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

Andres Freund <andres(at)2ndquadrant(dot)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

On 2013-06-10 10:39:48 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)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

Andres Freund <andres(at)2ndquadrant(dot)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

>> *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.

I don't personally see a reason for plural locations, but it would be
nice if it recursed (that is, looked for .so's in subdirectories). My
reason for this is that I work on applications which have in-house
extensions as well as public ones, and I'd like to keep the two
separated by directory.

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

Hi Josh

On 11 June 2013 04:37, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> I don't personally see a reason for plural locations, but it would be
> nice if it recursed (that is, looked for .so's in subdirectories). My
> reason for this is that I work on applications which have in-house
> extensions as well as public ones, and I'd like to keep the two
> separated by directory.

I gave one example of a use-case for multiple directories upthread - the
Postgres.app mac app has contrib, plv8 and postgis bundled under its
application folder, but it would be nice to allow users to drop extra
extensions under ~/Library/Postgres.app somewhere.

If we had directory recursion then you could sort of fake it using symlinks
(as long as we follow the symlinks) but it's pretty messy, the wrapper app
would have to make a dir under ~/Library the actual extension dir and have
a symlink back to its bundled extensions. Not the end of the world though.

For any of that to work the dir (or dirs) would need to be settable by
config or startup option - compiled in wouldn't cut it, since the absolute
dir of the end user's home directory isn't known at compile time.

Cheers

Tom

On 06/12/2013 08:52 AM, Tom Dunstan wrote:
> Hi Josh
>
> On 11 June 2013 04:37, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> I don't personally see a reason for plural locations, but it would be
>> nice if it recursed (that is, looked for .so's in subdirectories). My
>> reason for this is that I work on applications which have in-house
>> extensions as well as public ones, and I'd like to keep the two
>> separated by directory.
>
>
> I gave one example of a use-case for multiple directories upthread - the
> Postgres.app mac app has contrib, plv8 and postgis bundled under its
> application folder, but it would be nice to allow users to drop extra
> extensions under ~/Library/Postgres.app somewhere.

Postgres.app is the source of quite a lot of other pain too, though. One
of the bigger problems is that people want/need to link to its libpq
from client drivers like Ruby's Pg gem, but almost inevitably instead
link to libpq from Apple's ancient pre-installed PostgreSQL.

I think this is a far bigger problem than extension locations, and
people trying to do private per-application packaging trees will have
similar issues.

Without a solution to how to sanely share the client libraries I'm not
sure private-tree-packaged PostgreSQL is interesting enough to really
worry about making extensions easier to install. After all, users can
currently just open Postgres.app as a folder and drop the exts in there,
or use PGXS and "make install", just like usual.

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

On 12 June 2013 14:19, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> Postgres.app is the source of quite a lot of other pain too, though. One
> of the bigger problems is that people want/need to link to its libpq
> from client drivers like Ruby's Pg gem, but almost inevitably instead
> link to libpq from Apple's ancient pre-installed PostgreSQL.
>

Oh, interesting. Do the ruby/rails folks use that rather than a pure-ruby
driver? I guess I'm spoiled - most of my development happens on the JVM,
and the JDBC driver doesn't use libpq.

> Without a solution to how to sanely share the client libraries I'm not
> sure private-tree-packaged PostgreSQL is interesting enough to really
> worry about making extensions easier to install.

Hmm, so what might a sane solution look like? It looks like the proper way
to build the pg gem is to specify the full path to pg_config. Maybe we
could convince the pg gem authors to error out if the found version of
postgresql is too old? I presume that we only discover the problems when
someone tries to actually use the driver - or do we find out at gem
installation time?

Another alternative is for the Postgres.app to add its bin dir to the
user's (or system's) path on first startup. Then the correct pg_config will
be found (and the correct psql, pgdump etc etc as well). The app could in
theory even go looking for existing pg gem installed under .rvm or whatever
and prompt the user to reinstall the gem.

> After all, users can
> currently just open Postgres.app as a folder and drop the exts in there,
> or use PGXS and "make install", just like usual.
>

They can do either of those, but if they then upgrade the app, I presume
that the extensions will disappear, and they'll need to rebuild or
reinstall them, which is a bit of a pain.

Cheers

Tom

On 06/12/2013 02:24 PM, Tom Dunstan wrote:
> On 12 June 2013 14:19, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>
>> Postgres.app is the source of quite a lot of other pain too, though. One
>> of the bigger problems is that people want/need to link to its libpq
>> from client drivers like Ruby's Pg gem, but almost inevitably instead
>> link to libpq from Apple's ancient pre-installed PostgreSQL.
>>
> Oh, interesting. Do the ruby/rails folks use that rather than a pure-ruby
> driver? I guess I'm spoiled - most of my development happens on the JVM,
> and the JDBC driver doesn't use libpq.

Yes, they do - including a horde of deeply confused and frustrated Rails
users struggling to understand why they're getting "no such file or
directory" or "permission denied" messages about Pg's unix socket,
because of course they're linked to Apple's libpq which has a different
default unix socket path, and unless they explicitly specify `host:
localhost` in their Rails database.yml they get a unix socket connection.

I only know this because it comes up so much on SO; I don't use Rails
(or a Mac) myself. It's clearly a real pain point for new users, though.
This is one of the more commonly referenced examples, but there are a
few more every week: http://stackoverflow.com/q/6770649/398670

> Hmm, so what might a sane solution look like? It looks like the proper way
> to build the pg gem is to specify the full path to pg_config. Maybe we
> could convince the pg gem authors to error out if the found version of
> postgresql is too old?
Good point ... requiring an explicit `pg_config` to be specified would
help a lot.

Another option would be to have to explicitly allow use of Apple's
PostgreSQL (based on known install paths) though; think
"--use-system-postgresql".

I'm sure the Ruby Pg gem folks have discussed this but I've seen no sign
of any improvement.
> I presume that we only discover the problems when
> someone tries to actually use the driver - or do we find out at gem
> installation time?
Only at runtime, when they try to connect (see above link for one example).

> Another alternative is for the Postgres.app to add its bin dir to the
> user's (or system's) path on first startup. Then the correct pg_config will
> be found (and the correct psql, pgdump etc etc as well). The app could in
> theory even go looking for existing pg gem installed under .rvm or whatever
> and prompt the user to reinstall the gem.
An interesting idea. Unfortunately, many of these people *also* install
PostgreSQL from homebrew or have used it in the past. Don't ask me why,
but it seems common going by SO questions etc. I think they have a
problem with Homebrew so rather than try to fix it they just try
installing postgres.app instead, or vice versa.

Anyway, point being that PostgreSQL from Macports, Homebrew, and/or
EnterpriseDB's installer might be present ... and even in use.

I get the strong impression from what I've been reading that a fairly
typical Rails user setup is:

* Install homebrew
* Install PostgreSQL using homebrew but don't start it
* Build the Pg gem against homebrew postgresql's libpq
* Download and run postgres.app
* Run your Pg gem using the libpq from homebrew against the postgres.app
server

Ugh.

> They can do either of those, but if they then upgrade the app, I presume
> that the extensions will disappear, and they'll need to rebuild or
> reinstall them, which is a bit of a pain.
Good point... though that also raises more concerns regarding consumers
of the Pg library. And extensions, for that matter; if extensions are
out-of-tree you need versioned subdirectories, otherwise you'll have
conflicts between 9.2 and 9.3 (for example) versions of the same extensions.

It's also another issue with libpq. User upgrades Postgres.app and
suddenly their Ruby gems stop working with some linkage error they
probably don't understand.

(All this is, IMO, really a lesson in why Apple should introduce a
non-awful packaging system into OS X).

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

On Tue, Jun 11, 2013 at 11:42 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> Anyway, point being that PostgreSQL from Macports, Homebrew, and/or
> EnterpriseDB's installer might be present ... and even in use.

Perhaps you should direct those users towards http://postgresapp.com

--
Peter Geoghegan

On 06/12/2013 02:52 PM, Peter Geoghegan wrote:
> On Tue, Jun 11, 2013 at 11:42 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>> Anyway, point being that PostgreSQL from Macports, Homebrew, and/or
>> EnterpriseDB's installer might be present ... and even in use.
> Perhaps you should direct those users towards http://postgresapp.com

Well, at that point they're usually already in a bit of a mess that they
don't know how to get themselves out of. They're often *also* attempting
to use Postgres.app, but struggling with issues with unix socket
directory defaults, etc. The postgres.app docs do appear to offer some
useful basic guidance for users on how to uninstalll whatever they
might've installed.

None of this is hard if you have clue what you're doing. Rebuild the Pg
gem against the right libpq by fixing your PATH so it finds the right
pg_config, set host=/tmp, or set host=localhost. Any of the three will
work. Unfortunately most of these users seem to struggle with that, and
their approach to "it didn't work" appears to be "find another
tool/tutorial and try that instead".

Sure, they're not my (or your) problem. I'd still like to see usability
in this area improve if it's possible.

The postgres.app documentation its self doesn't look quite right when it
comes to Ruby, actually. For Ruby/Rails it says the user should use "gem
install pg" but it doesn't tell them to set the PATH first, so they'll
get whatever random pg_config is on the PATH first, often Apple's
elderly Pg with its different socket directory path, etc. Sure, they can
get around that just by setting host: localhost, but it'd be nice to see
that improved so it tells them how to build their Pg gem against the
correct libpq. Or, better, has Postgres.app automatically install it for
them when they install it.

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

On 12 June 2013 16:12, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> Yes, they do - including a horde of deeply confused and frustrated Rails
> users struggling to understand why they're getting "no such file or
> directory" or "permission denied" messages about Pg's unix socket,
> because of course they're linked to Apple's libpq which has a different
> default unix socket path, and unless they explicitly specify `host:
> localhost` in their Rails database.yml they get a unix socket connection.
>

Maybe we could ask the rails people to stick a "host: localhost" into their
postgresql examples? Might help a bit, and most users at that level won't
need the absolutely most up-to-date libpq. Of course, there are probably
hundreds of tutorials all over the net that won't have the fix.

> Another option would be to have to explicitly allow use of Apple's
> PostgreSQL (based on known install paths) though; think
> "--use-system-postgresql".
>

Yeah, or --allow-old-libpq or something like that. How will the gem
installer know if the pg_config that it has found is a system one or not?
Going by version number is probably easier.

> I get the strong impression from what I've been reading that a fairly
> typical Rails user setup is:
>
> * Install homebrew
> * Install PostgreSQL using homebrew but don't start it
> * Build the Pg gem against homebrew postgresql's libpq
> * Download and run postgres.app
> * Run your Pg gem using the libpq from homebrew against the postgres.app
> server
>
> Ugh.
>

Hmm. Seems like all the more reason to steer people away from socket-based
comms.

> Good point... though that also raises more concerns regarding consumers
> of the Pg library. And extensions, for that matter; if extensions are
> out-of-tree you need versioned subdirectories, otherwise you'll have
> conflicts between 9.2 and 9.3 (for example) versions of the same
> extensions.
>

Right. I was picturing something like
~/Library/Postgres.app/9.2/extensions. We shouldn't be breaking extensions
within a major release.

> It's also another issue with libpq. User upgrades Postgres.app and
> suddenly their Ruby gems stop working with some linkage error they
> probably don't understand.
>
> (All this is, IMO, really a lesson in why Apple should introduce a
> non-awful packaging system into OS X).

Well, I'm not holding my breath on their packaging changing anytime soon. :)

I wonder if a better approach might be to actually have the gem bundle its
own copy of libpq. Then there's no question of linkage errors when the
server on the system changes, and if users are using tcp rather than unix
sockets, they should be pretty well insulated from those sorts of issues.
Just specify the right port and you're good to go.

Is libpg buildable without building the whole tree? Is it downloadable
without downloading the whole distribution? Hmm.

Cheers

Tom

On 12 June 2013 16:30, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> None of this is hard if you have clue what you're doing. Rebuild the Pg
> gem against the right libpq by fixing your PATH so it finds the right
> pg_config, set host=/tmp, or set host=localhost. Any of the three will
> work. Unfortunately most of these users seem to struggle with that, and
> their approach to "it didn't work" appears to be "find another
> tool/tutorial and try that instead".
>

So we need an "official" tutorial? But which distribution would we point
people to? :)

> The postgres.app documentation its self doesn't look quite right when it
> comes to Ruby, actually. For Ruby/Rails it says the user should use "gem
> install pg" but it doesn't tell them to set the PATH first, so they'll get
> whatever random pg_config is on the PATH first, often Apple's elderly Pg
> with its different socket directory path, etc. Sure, they can get around
> that just by setting host: localhost, but it'd be nice to see that improved
> so it tells them how to build their Pg gem against the correct libpq. Or,
> better, has Postgres.app automatically install it for them when they
> install it.
>

Hmm, but where to install it? People using rvm or bundler will have their
gems tucked away in a variety of places.

Cheers

Tom

On Wed, Jun 12, 2013 at 7:24 AM, Tom Dunstan <pgsql(at)tomd(dot)cc> wrote:
>
> Another alternative is for the Postgres.app to add its bin dir to the user's
> (or system's) path on first startup. Then the correct pg_config will be
> found (and the correct psql, pgdump etc etc as well). The app could in
> theory even go looking for existing pg gem installed under .rvm or whatever
> and prompt the user to reinstall the gem.

Messing with the path (or the dynamic load path) can cause all sorts
of fun and interesting problems for users, as we found in the early
days with the EDB installers. I realise it doesn't help these users
(who doubtless don't know it exists) but what we do these days is drop
a "pg_env.sh" file in the installation directory that the user can
source to set their PATH and various PG* environment variables when
they need/want to.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 12 June 2013 17:30, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> Messing with the path (or the dynamic load path) can cause all sorts
> of fun and interesting problems for users, as we found in the early
> days with the EDB installers. I realise it doesn't help these users
> (who doubtless don't know it exists) but what we do these days is drop
> a "pg_env.sh" file in the installation directory that the user can
> source to set their PATH and various PG* environment variables when
> they need/want to.
>

Well, I was imagining something like a helpful dialog box saying "would you
like me to fix your path? I'm just going to source
/Applications/Postgres.app/env.sh in your bash_profile" and the user can
click "ok" or "no thanks I'll do it myself". It might lead to even more
confusion, but it's got to be better than the pg gem silently linking
against the wrong libpq and subsequently failing in interesting ways.

Of course, if they've already installed the pg gem then it's too late
anyway, but at least reinstalling it would then work.

Blech. The more I think about it, the more I like the idea of libpq bundled
with the gem.

Cheers

Tom

Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
> On 06/12/2013 02:24 PM, Tom Dunstan wrote:
>> Oh, interesting. Do the ruby/rails folks use that rather than a pure-ruby
>> driver? I guess I'm spoiled - most of my development happens on the JVM,
>> and the JDBC driver doesn't use libpq.

> Yes, they do - including a horde of deeply confused and frustrated Rails
> users struggling to understand why they're getting "no such file or
> directory" or "permission denied" messages about Pg's unix socket,
> because of course they're linked to Apple's libpq which has a different
> default unix socket path, and unless they explicitly specify `host:
> localhost` in their Rails database.yml they get a unix socket connection.

I poked at this a little bit, wondering whether it'd be practical to fix
the problem by configuring third-party postmasters to create a socket
where Apple's libpq is expecting. However, it looks like (in Lion
anyway) what libpq is expecting is this:

$ /usr/bin/psql -l
psql: could not connect to server: Permission denied
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

and that directory is configured with no public permissions at all:

$ ls -ld /var/pgsql_socket
drwxr-x--- 2 _postgres _postgres 68 Jun 13 2011 /var/pgsql_socket/

which basically means that no third-party code should ever be expecting
to communicate with a postmaster through there.

This being the case, I wonder if the Ruby PG gem shouldn't be written
to override the default socket location, which it could do with
something like

if (getenv("PGHOST") == NULL)
putenv("PGHOST=/tmp");

without needing to muck with interpretation of connection strings.
Of course, if third-party packagings of PG aren't consistent about
where they think the socket goes, we won't know what to put there...

regards, tom lane

10.06.2013, 17:51, Dimitri Fontaine kirjoitti:
> Andres Freund <andres(at)2ndquadrant(dot)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.

Here's a patch to allow overriding extension installation directory.
The patch allows superusers to change it at runtime, but we could also
restrict it to postgresql.conf if needed. I don't really see a point in
restricting it (or not implementing the option at all) as the superuser
can already load custom C code and sql from anywhere in the filesystem;
not having configurable extension directory just makes it harder to test
extensions and to create private clusters of PG data in a custom
location without using custom binaries.

I don't think anyone should be packaging and shipping PG with
extension_directory set, but we really should allow it for superusers
and postgresql.conf so people can test extensions without hacks like
this: https://github.com/ohmu/pgmemcache/blob/master/localtests.sh#L23

/ Oskari

On 2/17/15 4:39 PM, Oskari Saarenmaa wrote:
> 10.06.2013, 17:51, Dimitri Fontaine kirjoitti:
>> Andres Freund <andres(at)2ndquadrant(dot)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.
>
> Here's a patch to allow overriding extension installation directory.
> The patch allows superusers to change it at runtime, but we could also
> restrict it to postgresql.conf if needed. I don't really see a point in
> restricting it (or not implementing the option at all) as the superuser
> can already load custom C code and sql from anywhere in the filesystem;
> not having configurable extension directory just makes it harder to test
> extensions and to create private clusters of PG data in a custom
> location without using custom binaries.

I'm interested in this because it could potentially make it possible to
install SQL extensions without OS access. (My understanding is there's
some issue with writing the extension files even if LIBDIR is writable
by the OS account).

> I don't think anyone should be packaging and shipping PG with
> extension_directory set, but we really should allow it for superusers
> and postgresql.conf so people can test extensions without hacks like
> this: https://github.com/ohmu/pgmemcache/blob/master/localtests.sh#L23

FWIW I'd like to see is breaking the cluster setup/teardown capability
in pg_regress into it's own tool. It wouldn't solve the extension test
problem, but it would eliminate the need for most of what that script is
doing, and it would do it more robustly. It would make it very easy to
unit test things with frameworks other than pg_regress.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

18.02.2015, 01:49, Jim Nasby kirjoitti:
> On 2/17/15 4:39 PM, Oskari Saarenmaa wrote:
>> 10.06.2013, 17:51, Dimitri Fontaine kirjoitti:
>>> Andres Freund <andres(at)2ndquadrant(dot)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.
>>
>> Here's a patch to allow overriding extension installation directory.
>> The patch allows superusers to change it at runtime, but we could also
>> restrict it to postgresql.conf if needed. I don't really see a point in
>> restricting it (or not implementing the option at all) as the superuser
>> can already load custom C code and sql from anywhere in the filesystem;
>> not having configurable extension directory just makes it harder to test
>> extensions and to create private clusters of PG data in a custom
>> location without using custom binaries.
>
> I'm interested in this because it could potentially make it possible to
> install SQL extensions without OS access. (My understanding is there's
> some issue with writing the extension files even if LIBDIR is writable
> by the OS account).

I'm not sure this patch makes extensions without OS access any easier to
implement; you still need to write the files somewhere, and someone
needs to set up the cluster with a nonstandard extension path.

>> I don't think anyone should be packaging and shipping PG with
>> extension_directory set, but we really should allow it for superusers
>> and postgresql.conf so people can test extensions without hacks like
>> this: https://github.com/ohmu/pgmemcache/blob/master/localtests.sh#L23
>
> FWIW I'd like to see is breaking the cluster setup/teardown capability
> in pg_regress into it's own tool. It wouldn't solve the extension test
> problem, but it would eliminate the need for most of what that script is
> doing, and it would do it more robustly. It would make it very easy to
> unit test things with frameworks other than pg_regress.

This would certainly be useful. I can try to do something about it if
we get configurable extension path supported. The patch is now in
https://commitfest.postgresql.org/5/170/

/ Oskari

On 3/4/15 1:41 AM, Oskari Saarenmaa wrote:
>> >I'm interested in this because it could potentially make it possible to
>> >install SQL extensions without OS access. (My understanding is there's
>> >some issue with writing the extension files even if LIBDIR is writable
>> >by the OS account).
> I'm not sure this patch makes extensions without OS access any easier to
> implement; you still need to write the files somewhere, and someone
> needs to set up the cluster with a nonstandard extension path.

Right, I wasn't expecting it to work out of the box.

Admittedly I'm foggy on what the exact problem with pushing a file into
that directory via SQL is, so maybe it still won't help.

>>> >>I don't think anyone should be packaging and shipping PG with
>>> >>extension_directory set, but we really should allow it for superusers
>>> >>and postgresql.conf so people can test extensions without hacks like
>>> >>this:https://github.com/ohmu/pgmemcache/blob/master/localtests.sh#L23
>> >
>> >FWIW I'd like to see is breaking the cluster setup/teardown capability
>> >in pg_regress into it's own tool. It wouldn't solve the extension test
>> >problem, but it would eliminate the need for most of what that script is
>> >doing, and it would do it more robustly. It would make it very easy to
>> >unit test things with frameworks other than pg_regress.
> This would certainly be useful. I can try to do something about it if
> we get configurable extension path supported. The patch is now in
> https://commitfest.postgresql.org/5/170/

Awesome! Let me know if I can help. Or I may start on it if I can get
some other stuff off my plate.

By the way, after thinking about this a little more, I think a utility
for standing up "temporary" Postgres clusters would be very welcome by
power users. It's a very common need for QA environments. Originally I
was thinking about it in terms of things like extension testing, but now
I realize there's a much larger audience than that. So I definitely
think this should be a stand alone shell command (pg_temp_cluster?), and
either have pg_regress call it or (probably more logical) add it to the
make files as a dependency for make check (make
temp-cluster/remove-temp-cluster or similar).
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 03/04/2015 09:41 AM, Oskari Saarenmaa wrote:
> 18.02.2015, 01:49, Jim Nasby kirjoitti:
>> On 2/17/15 4:39 PM, Oskari Saarenmaa wrote:
>>> 10.06.2013, 17:51, Dimitri Fontaine kirjoitti:
>>>> Andres Freund <andres(at)2ndquadrant(dot)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.
>>>
>>> Here's a patch to allow overriding extension installation directory.
>>> The patch allows superusers to change it at runtime, but we could also
>>> restrict it to postgresql.conf if needed. I don't really see a point in
>>> restricting it (or not implementing the option at all) as the superuser
>>> can already load custom C code and sql from anywhere in the filesystem;
>>> not having configurable extension directory just makes it harder to test
>>> extensions and to create private clusters of PG data in a custom
>>> location without using custom binaries.
>>
>> I'm interested in this because it could potentially make it possible to
>> install SQL extensions without OS access. (My understanding is there's
>> some issue with writing the extension files even if LIBDIR is writable
>> by the OS account).
>
> I'm not sure this patch makes extensions without OS access any easier to
> implement; you still need to write the files somewhere, and someone
> needs to set up the cluster with a nonstandard extension path.

Hmm. I think you're on to something with this patch, but I couldn't
exactly figure out what. What is the purpose of this patch?

I can see a few things that you might want to do:

1. You might want to create a cluster using existing binaries, and set
it up so that you can install extra extensions from a custom directory.
Handy if you don't have write access to /usr, or you only want to make
an extension available in one cluster but not others.

2. Like 1, but you want to replace the set of set of extensions altogether.

3. Writing an automated regression test for a custom extension.

With all of those, you have the problem that you actually want to
override both the lib-dir and the extensions-dir. So you'll need to set
dynamic_library_path too. For 3, fiddling with the configuration file is
a bit tedious. And PGXS doesn't currently have support for setting up a
test cluster anyway.

Oh, and why are we only worried about extensions? There's other stuff in
'share'-directory that you might also want to override in some clusters
or as part of regression tests: timezone and tsearch data.

Note that you can make Postgres to search for all of those things in a
different directory by copying the "postgres" binary elsewhere. It's a
bit hacky, but works.
- Heikki

02.07.2015, 20:31, Heikki Linnakangas kirjoitti:
> On 03/04/2015 09:41 AM, Oskari Saarenmaa wrote:
>> 18.02.2015, 01:49, Jim Nasby kirjoitti:
>>> On 2/17/15 4:39 PM, Oskari Saarenmaa wrote:
>>>> Here's a patch to allow overriding extension installation directory.
>>>> The patch allows superusers to change it at runtime, but we could also
>>>> restrict it to postgresql.conf if needed. I don't really see a
>>>> point in
>>>> restricting it (or not implementing the option at all) as the superuser
>>>> can already load custom C code and sql from anywhere in the filesystem;
>>>> not having configurable extension directory just makes it harder to
>>>> test
>>>> extensions and to create private clusters of PG data in a custom
>>>> location without using custom binaries.
>>>
>>> I'm interested in this because it could potentially make it possible to
>>> install SQL extensions without OS access. (My understanding is there's
>>> some issue with writing the extension files even if LIBDIR is writable
>>> by the OS account).
>>
>> I'm not sure this patch makes extensions without OS access any easier to
>> implement; you still need to write the files somewhere, and someone
>> needs to set up the cluster with a nonstandard extension path.
>
> Hmm. I think you're on to something with this patch, but I couldn't
> exactly figure out what. What is the purpose of this patch?
>
> I can see a few things that you might want to do:
>
> 1. You might want to create a cluster using existing binaries, and set
> it up so that you can install extra extensions from a custom directory.
> Handy if you don't have write access to /usr, or you only want to make
> an extension available in one cluster but not others.
>
> 2. Like 1, but you want to replace the set of set of extensions altogether.
>
> 3. Writing an automated regression test for a custom extension.
>
> With all of those, you have the problem that you actually want to
> override both the lib-dir and the extensions-dir. So you'll need to set
> dynamic_library_path too. For 3, fiddling with the configuration file is
> a bit tedious. And PGXS doesn't currently have support for setting up a
> test cluster anyway.

I'm somewhat interested in both #1 & #2 for other projects, but I wrote
this patch to address #3, i.e. to simplify the test setup we have in
place for pgmemcache
(https://github.com/ohmu/pgmemcache/blob/master/localtests.sh) and other
extensions. I'd like to be able to set up a local PG cluster in /tmp or
some other location and load the extensions I just built in there.
Overriding dynamic_library_path is already possible, but there's
currently no way to test the actual extension .control files.

I don't think "fiddling with a configuration file" is all that bad --
you just run initdb and modify the few lines that you're interested in
using sed or any other tool; that's something you'll have to do to set
port or socket directory anyway.

> Oh, and why are we only worried about extensions? There's other stuff in
> 'share'-directory that you might also want to override in some clusters
> or as part of regression tests: timezone and tsearch data.

I suppose someone might be interested in using custom timezone data with
existing binaries, but the number of existing third party extensions
must be a lot higher than the number of third party timezone data sets.

/ Oskari

On 07/02/2015 11:37 PM, Oskari Saarenmaa wrote:
> I'm somewhat interested in both #1 & #2 for other projects, but I wrote
> this patch to address #3, i.e. to simplify the test setup we have in
> place for pgmemcache
> (https://github.com/ohmu/pgmemcache/blob/master/localtests.sh) and other
> extensions. I'd like to be able to set up a local PG cluster in /tmp or
> some other location and load the extensions I just built in there.

Now that's a laudable goal. It indeed would be nice to be able to do
"make check" to test an extension, using pgxs. The way "make check"
within the PostgreSQL source tree works is that it performs "make
install" to install PostgreSQL a temporary location, and installs the
extension to that. We can't use "make install" to create a new
PostgreSQL installation in an extension, but perhaps you could have a
substitute of that that copies an existing installation to a temporary
location. I hacked that pgmemcache localtest.sh script to do just that,
see attached. That's usable for pgmemcache as it is, but for a general
facility, you'd need to put that logic into pgxs instead, and it should
also take care of running initdb and starting and stopping the cluster.
But pg_regress can already do those things, so that should be easy.

So, I think that's the direction this should be going. In summary, the
goal is to make "make check" to work for extensions, and the way to do
that is to teach pgxs to create a temporary installation.

- Heikki