Re: [HACKERS] Configurable location for extension .control files

2015-07-30 Thread Heikki Linnakangas

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

diff --git a/.travis.yml b/.travis.yml
index b30af7e..9197690 100644
--- a/.travis.yml
+++ b/.travis.yml
@@ -11,7 +11,7 @@ script:
   - PATH=/usr/lib/postgresql/$PGVER/bin/:$PATH ./localtests.sh
 
 after_failure:
-  - cat regressiondata/regression.diffs
+  - cat regression.diffs
 
 env:
   - PGVER=9.1
diff --git a/localtests.sh b/localtests.sh
index d43e15f..956e300 100755
--- a/localtests.sh
+++ b/localtests.sh
@@ -2,17 +2,55 @@
 
 # Create a clean PostgreSQL cluster for our testing
 
-TESTDIR=$(pwd)/regressiondata
+TESTDIR=$(pwd)/tmp_check
 export PGPORT=$((10240 + RANDOM / 2))
-export PGDATA=$TESTDIR/pg
+export PGDATA=$TESTDIR/data
 
 rm -rf $TESTDIR
+
+# Create a minimal PostgreSQL installation, by copying an existing
+# installation. This is a replacement for doing make install in the
+# PostgreSQL source tree, for when you don't have the source tree available.
+#
+# The minimum we need to copy from the existing installation are server 'lib'
+# and 'share' directories, and a few binaries.
+#
+# Note that 'pg_config --libdir' is the path to client-side libraries, i.e.
+# libpq, and we don't want to copy that. (on many distributions, libdir points
+# to just /usr/lib, and we certainly don't want to copy that in whole).
+# Also note that we cannot use symlinks for the binaries, because the binaries
+# look at the absolute path they are run from to find the rest of the files
+# they need.
+
+TMP_INSTALL=$TESTDIR/install
+
+bindir=`pg_config --bindir`
+pkglibdir=`pg_config --pkglibdir`
+sharedir=`pg_config --sharedir`
+
+mkdir -p $TMP_INSTALL
+mkdir -p $TMP_INSTALL$bindir
+mkdir -p $TMP_INSTALL$pkglibdir
+mkdir -p $TMP_INSTALL$sharedir
+
+cp -a $bindir/postgres $TMP_INSTALL$bindir
+cp -a $bindir/initdb $TMP_INSTALL$bindir
+cp -a $bindir/pg_ctl $TMP_INSTALL$bindir
+cp -a $pkglibdir/* $TMP_INSTALL$pkglibdir
+cp -a $sharedir/* $TMP_INSTALL$sharedir
+
+export PATH=$TMP_INSTALL$bindir:$PATH
+
+# Install pgmemcache to the temporary installation
+make install DESTDIR=$TMP_INSTALL
+
+# Set up a temporary cluster
 mkdir -p $PGDATA
 
 initdb -E UTF-8 --no-locale
+# XXX: Should use pg_config --config-auth to lock down the cluster
 sed -e s%^#port =.*%port = $PGPORT% \
 -e s%^#\(unix_socket_director[a-z]*\) =.*%\1 = '$PGDATA'% \
--e s%^#dynamic_library_path = .*%dynamic_library_path = '$(pwd):\$libdir'% \
 -e s%^#fsync = .*%fsync = off% \
 -e s%^#synchronous_commit = .*%synchronous_commit = off% \
 -i $PGDATA/postgresql.conf
@@ -20,15 +58,5 @@ pg_ctl -l $PGDATA/log start
 while [ ! -S $PGDATA/.s.PGSQL.$PGPORT ]; do sleep 2; done
 trap pg_ctl stop -m immediate EXIT
 
-# It's not possible to override the extension path, so we'll just execute
-# the extension SQL directly after mangling it a bit with sed
-
-cp -a Makefile test.sql sql/ expected/ $TESTDIR
-sed -e s%MODULE_PATHNAME%pgmemcache% \
--e /CREATE EXTENSION/d -e /^--/d -e /^$/d \
-ext/pgmemcache.sql  $TESTDIR/sql/init.sql
-cp $TESTDIR/sql/init.sql $TESTDIR/expected/init.out
-
-# Run the actual tests
-
-make -C $TESTDIR installcheck REGRESS_OPTS=--host=$PGDATA --port=$PGPORT
+# Run the actual tests in the temporary cluster.
+make installcheck REGRESS_OPTS=--host=$PGDATA --port=$PGPORT

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


Re: [HACKERS] Configurable location for extension .control files

2015-07-02 Thread Heikki Linnakangas

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 and...@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.


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



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


Re: [HACKERS] Configurable location for extension .control files

2015-03-05 Thread Jim Nasby

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


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


Re: [HACKERS] Configurable location for extension .control files

2015-03-03 Thread Oskari Saarenmaa
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 and...@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.

 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


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


Re: [HACKERS] Configurable location for extension .control files

2015-02-17 Thread Jim Nasby

On 2/17/15 4:39 PM, Oskari Saarenmaa wrote:

10.06.2013, 17:51, Dimitri Fontaine kirjoitti:

Andres Freund and...@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.


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


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


Re: [HACKERS] Configurable location for extension .control files

2015-02-17 Thread Oskari Saarenmaa
10.06.2013, 17:51, Dimitri Fontaine kirjoitti:
 Andres Freund and...@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.

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
From 35cae53aa5e9cf89b19a3ae276e635b42fbe5313 Mon Sep 17 00:00:00 2001
From: Oskari Saarenmaa o...@ohmu.fi
Date: Tue, 17 Feb 2015 23:27:59 +0200
Subject: [PATCH] Allow overriding extension_directory

Add a new GUC, 'extension_directory' to override the default directory for
extensions.  This allows users running their own PostgreSQL clusters using
the system binaries to install custom extensions and makes testing
extensions easier.
---
 doc/src/sgml/config.sgml  | 38 +++
 src/backend/commands/extension.c  | 21 +--
 src/backend/utils/misc/guc.c  | 12 +
 src/backend/utils/misc/postgresql.conf.sample |  2 ++
 src/include/commands/extension.h  |  2 ++
 5 files changed, 67 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index c669f75..f0c762a 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -6341,6 +6341,44 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
   /listitem
  /varlistentry
 
+ varlistentry id=guc-extension-directory xreflabel=extension_directory
+  termvarnameextension_directory/varname (typestring/type)
+  indexterm
+   primaryvarnameextension_directory/ configuration parameter/primary
+  /indexterm
+  indextermprimaryextensions//
+  /term
+  listitem
+   para
+Look up extensions from this path when an extension is created using
+the commandCREATE EXTENSION/command.
+   /para
+
+   para
+The value for varnameextension_directory/varname must be an
+existing directory containing literal.control/literal files for
+extensions.
+   /para
+
+   para
+By default this is the empty string, which uses a directory based
+on the compiled-in productnamePostgreSQL/productname package
+share data directory; this is where the extensions provided by the
+standard productnamePostgreSQL/productname distribution are
+installed.
+   /para
+
+   para
+This parameter can be changed at run time by superusers, but a
+setting done that way will only persist until the end of the
+client connection, so this method should be reserved for
+development purposes. The recommended way to set this parameter
+is in the filenamepostgresql.conf/filename configuration
+file.
+   /para
+  /listitem
+ /varlistentry
+
  varlistentry id=guc-gin-fuzzy-search-limit xreflabel=gin_fuzzy_search_limit
   termvarnamegin_fuzzy_search_limit/varname (typeinteger/type)
   indexterm
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 9a0afa4..365ad59 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -56,6 +56,9 @@
 #include utils/tqual.h
 
 
+/* GUC */
+char	   *Extension_directory;
+
 /* Globally visible state variables */
 bool		creating_extension = false;
 Oid			CurrentExtensionObject = InvalidOid;
@@ -348,6 +351,9 @@ get_extension_control_directory(void)
 	char		sharepath[MAXPGPATH];
 	char	   *result;
 
+	if (Extension_directory != NULL)
+		return pstrdup(Extension_directory);
+
 	get_share_path(my_exec_path, sharepath);
 	result = (char *) palloc(MAXPGPATH);
 	snprintf(result, MAXPGPATH, %s/extension, sharepath);
@@ -358,13 +364,11 @@ get_extension_control_directory(void)
 static char *
 get_extension_control_filename(const char *extname)
 {
-	char		sharepath[MAXPGPATH];
 	char	   *result;
 
-	get_share_path(my_exec_path, sharepath);
 	result = (char *) 

Re: [HACKERS] Configurable location for extension .control files

2013-06-12 Thread Tom Dunstan
On 12 June 2013 14:19, Craig Ringer cr...@2ndquadrant.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


Re: [HACKERS] Configurable location for extension .control files

2013-06-12 Thread Craig Ringer
On 06/12/2013 02:24 PM, Tom Dunstan wrote:
 On 12 June 2013 14:19, Craig Ringer cr...@2ndquadrant.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



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


Re: [HACKERS] Configurable location for extension .control files

2013-06-12 Thread Peter Geoghegan
On Tue, Jun 11, 2013 at 11:42 PM, Craig Ringer cr...@2ndquadrant.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


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


Re: [HACKERS] Configurable location for extension .control files

2013-06-12 Thread Craig Ringer
On 06/12/2013 02:52 PM, Peter Geoghegan wrote:
 On Tue, Jun 11, 2013 at 11:42 PM, Craig Ringer cr...@2ndquadrant.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



Re: [HACKERS] Configurable location for extension .control files

2013-06-12 Thread Tom Dunstan
On 12 June 2013 16:12, Craig Ringer cr...@2ndquadrant.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


Re: [HACKERS] Configurable location for extension .control files

2013-06-12 Thread Tom Dunstan
On 12 June 2013 16:30, Craig Ringer cr...@2ndquadrant.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


Re: [HACKERS] Configurable location for extension .control files

2013-06-12 Thread Dave Page
On Wed, Jun 12, 2013 at 7:24 AM, Tom Dunstan pg...@tomd.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


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


Re: [HACKERS] Configurable location for extension .control files

2013-06-12 Thread Tom Dunstan
On 12 June 2013 17:30, Dave Page dp...@pgadmin.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


Re: [HACKERS] Configurable location for extension .control files

2013-06-12 Thread Tom Lane
Craig Ringer cr...@2ndquadrant.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


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


Re: [HACKERS] Configurable location for extension .control files

2013-06-11 Thread Tom Dunstan
Hi Josh

On 11 June 2013 04:37, Josh Berkus j...@agliodbs.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


Re: [HACKERS] Configurable location for extension .control files

2013-06-11 Thread Craig Ringer
On 06/12/2013 08:52 AM, Tom Dunstan wrote:
 Hi Josh
 
 On 11 June 2013 04:37, Josh Berkus j...@agliodbs.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


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


Re: [HACKERS] Configurable location for extension .control files

2013-06-10 Thread Dimitri Fontaine
Tom Lane t...@sss.pgh.pa.us writes:
 Andres Freund and...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-10 Thread Tom Lane
Dimitri Fontaine dimi...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-10 Thread Andres Freund
On 2013-06-10 10:13:45 -0400, Tom Lane wrote:
 Dimitri Fontaine dimi...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-10 Thread Andres Freund
On 2013-06-10 10:39:48 -0400, Tom Lane wrote:
 Andres Freund and...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-10 Thread Dimitri Fontaine
Andres Freund and...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-10 Thread Tom Lane
Andres Freund and...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-10 Thread Josh Berkus

 *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


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


Re: [HACKERS] Configurable location for extension .control files

2013-06-05 Thread Josh Berkus
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


[HACKERS] Configurable location for extension .control files

2013-06-04 Thread Oliver Charles

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


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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Cédric Villemain
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


signature.asc
Description: This is a digitally signed message part.


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Tom Lane
Oliver Charles ol...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Oliver Charles

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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Josh Berkus
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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Tom Lane
Josh Berkus j...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Andres Freund
On 2013-06-04 13:25:10 -0400, Tom Lane wrote:
 Oliver Charles ol...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Tom Lane
Andres Freund and...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Andres Freund
On 2013-06-04 16:07:23 -0400, Tom Lane wrote:
 Andres Freund and...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Tom Lane
Andres Freund and...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Oliver Charles

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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Andres Freund
On 2013-06-04 16:24:07 -0400, Tom Lane wrote:
 Andres Freund and...@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


Re: [HACKERS] Configurable location for extension .control files

2013-06-04 Thread Tom Dunstan
On 5 June 2013 05:58, Andres Freund and...@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