[postgis-devel] PSC Vote: set schema search_path for select functions

Sun Mar 15 11:13:24 PDT 2015

I actually don't care so much about enforcing non-relocatability.  The only benefit of doing that is then we can have a single schema to set the search_path and/or we can schema qualify all our PostGIS function calls – which is a big exercise in monotonous labor.  I'd still go with just setting search_path for each function since we can automate that and will be valuable if we DO in future decide to enforce PostGIS installed in schema postgis.

The main issue is that whether we go down the path of making it non-relocatable or not, we are still going to have to set the search_path of each function that calls other functions and tables or schema qualify all our calls.

For example – let's hypothesize we were to enforce postgis be always installed in postgis schema.  If someone puts their data in another schema say public, they still won't be able to restore their data cleanly if they have raster constraints or use ST_Distance (and other functions that call other PostGIS functions)  functions in their materialized views or use postgres_fdw and use distance functions.

So in my mind FUNCTION search_path is both a current fixing and future proofing way to go.  The only thing that would change if we force non-relocatability is our search_path would have just pg_catalog (this one isn't even necessary since its always added)  and postgis instead of also accounting for the other search_paths people may add.

Regina

From: Paul Ramsey [mailto:pramsey at cleverelephant.ca] 
Sent: Sunday, March 15, 2015 12:17 PM
To: PostGIS Development Discussion; Paragon Corporation
Subject: Re: [postgis-devel] PSC Vote: set schema search_path for select functions

I’d like to figure out what the long-term solution is before committing to the short-term hacks.

Is it to make postgis non-relocateable and stick it into its own schema? The mechanics of having it relocatable seem intractable given the complexity of our machinery. Opinions on this very much welcome.

P.

-- 
Paul Ramsey
http://cleverelephant.ca

http://postgis.net 

On March 14, 2015 at 6:38:38 PM, Paragon Corporation (lr at pcorp.us <mailto:lr at pcorp.us> ) wrote:

That would be a long term solution, but unfortunately that would break too many people's installs and cause an upgrade nightmare, so not going to enforce that at least not in the short-term.  This proposal at least ensures people who choose to install in postgis schema won't be screwed during database restore.

Thanks,

Regina

From: postgis-devel-bounces at lists.osgeo.org <mailto:postgis-devel-bounces at lists.osgeo.org>  [mailto:postgis-devel-bounces at lists.osgeo.org] On Behalf Of Rémi Cura
Sent: Saturday, March 14, 2015 8:31 PM
To: PostGIS Development Discussion
Subject: Re: [postgis-devel] PSC Vote: set schema search_path for select functions

I personnaly would really prefer that all postgis matter is in a postgis schema by default.

It is confusing and annoying that everything gets into public schema

For instance with postigs topology everything is in the topology schema.

Cheers,

Rémi-C

2015-03-14 5:53 GMT+01:00 Paragon Corporation <lr at pcorp.us <mailto:lr at pcorp.us> >:

I'm thinking of doing something somewhat controversial which I should
probably get PSC vote on before I go thru the effort of putting it in and
testing out.

We've been getting a lot of complaints of this sort:

http://trac.osgeo.org/postgis/ticket/3076

http://trac.osgeo.org/postgis/ticket/2485

With the rise of materialized views, postgres_fdw,  PostGIS raster,  general
database users (with more purist ways of organizing data starting to use
PostGIS)  we are going to hear a lot more screams of this sort coming down
the pike of the worst case being people not being able to restore their data
without some workarounds.  I in fact helped a guy just last month and felt
so bad I didn't charge him much for the consultation.  He was having
problems restoring data from his production to his identical dev setup and
spent hours scratching his head.

So anyway explaining the issue.  This issue arises whenever we have
functions in PostGIS that call other PostGIS functions or tables and people
either put their data in a schema other than where they installed PostGIS or
in case of postgres_fdw where schema is not as controllable.

This is the case with a lot of the raster constraint functions, many of our
ST_Distance functions, and ST_Transform to name the main ones that bite
people.

My proposition is to in these functions, set the function schema search_path
to include the common schemas people decide to install PostGIS in.
So for example for :

ALTER FUNCTION st_distance(text, text) SET
search_path=postgis,contrib,extensions,public;

I would put this in a separate file to be included just so it doesn't mess
up our postgis parsers and can be easily pulled out if we find the need to.

This is a bit of a breaking change in that if people don't install PostGIS
in one of the common schemas we assume they would, then functions will just
not work for them since the Function search_path will override whatever
search path they set.

The above seems like a less bothersome issue than people not being able to
restore their data without having a doctorate in PostGIS.

Does anyone have an issue with this change?  I was going to put it in
PostGIS 2.2 and then backport it to 2.1 after doing some more extensive
tests.

Thanks,
Regina

_______________________________________________
postgis-devel mailing list
postgis-devel at lists.osgeo.org <mailto:postgis-devel at lists.osgeo.org> 
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel

_______________________________________________ 
postgis-devel mailing list 
postgis-devel at lists.osgeo.org <mailto:postgis-devel at lists.osgeo.org>  
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20150315/f49c8dd9/attachment.html>