[Mapserver-users] MapServer and PostGIS Connection, tip #9

Mon Jan 26 18:54:50 EST 2004

I forgot to add to a previous post so am posting this separately...

If you run into a MapServer error when using a PostGIS layer, and you
are sure that your CONNECTIONSTRING and DATA syntax are correct, check
the postgresql.log for connection problems.  If you don't have a log
file, please consult the PostgreSQL manual at
http://www.postgresql.org/docs/7.2/interactive/runtime-config.html#LOGGING on how to create one. 

Note: If you're using postgres other than version 7.2, replace "7.2" on
the url above with the proper PostgreSQL version number (eg: 7.3 or
7.4).

Example of errors from postgresql.log:
ERROR:  permission denied for relation geometry_columns
CONTEXT:  PL/pgSQL function "find_srid" line 27 at select into variables
ERROR:  permission denied for relation geometry_columns
CONTEXT:  PL/pgSQL function "find_srid" line 27 at select into variables
ERROR:  permission denied for relation geometry_columns
CONTEXT:  PL/pgSQL function "find_srid" line 27 at select into variables
ERROR:  permission denied for relation geometry_columns
CONTEXT:  PL/pgSQL function "find_srid" line 27 at select into variables
ERROR:  permission denied for relation geometry_columns
CONTEXT:  PL/pgSQL function "find_srid" line 27 at select into variables
---

The errors above tell me I don't have SELECT previlege on the table
"geometry_columns".  This can be fixed by GRANTing SELECT ON the_table
TO the user or the PUBLIC. (Yes, the SQL statement is "GRANT SELECT ON
geometry_columns TO PUBLIC;")

The error below is self-explanatory:
FATAL:  user "msuser" does not exist
---

Use CREATEUSER or change to an existing user.  

There may be other errors not directly related to MapServer and PostGIS
but rather to PostgreSQL.  Reading PostgreSQL's excellent documentation
is highly recommended.

Perhaps the PostGIS and MapServer folks can add this to their docs/FAQ?

-Perry N.

On Mon, 2004-01-26 at 15:18, Pericles S. Nacionales wrote:
> Stephane,
> 
> First try using the postgres account on your CONNECTIONSTRING and see if
> it works.  If it does, go back to your database and run GRANT SELECT ON
> <TABLE_NAME> TO PUBLIC run it for every table you want to use in
> MapServer (and perhaps to the SPATIAL_REF_SYS and the
> GEOMETRY_COLUMNS).  Now change your user back to what it was you used
> before and see if it works.
> 
> If that still doesn't fix it, try one of these:
> http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0401/msg00331.html
> http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0401/msg00334.html
> 
> 
> Good luck!
> -Perry N.
> 
> 
> On Mon, 2004-01-26 at 02:07, Stéphane RIFF wrote:
> > I don't want to upgrade to 3.7 i wan't the 3.6.7 version... and i didn't 
> > use templates and CGI all the application is in PHPMapscripts.
> > 
> > Swaminathan, Gayathri wrote:
> > 
> > >Stephane:
> > >
> > >Did anyone answer your question at all? Well if you are still wondering what went wrong, the "Migration guide" in Wiki explains this problem
> > >
> > >In order to enforce some security the previous CGI param has been changed. It will be only be accessible if you set DATAPATTERN <regex>.
> > >
> > >Also search for DATAPATTERN in archive, there are tons of information related to this problem
> > >
> > >HTH,
> > >Gayathri
> > >
> > >-----Original Message-----
> > >From: mapserver-users-admin at lists.gis.umn.edu [mailto:mapserver-users-admin at lists.gis.umn.edu] On Behalf Of Stéphane RIFF
> > >Sent: Tuesday, January 20, 2004 3:23 AM
> > >To: mapserver
> > >Subject: [Mapserver-users] Mapserver 3.6.5 -> 3.6.7
> > >
> > >
> > >I use Postgres 7.2 and postgis 0.7.5, php 4.3.2....
> > >
> > >I tried to upgrade phpmapscript from 3.6.5 to 3.6.7 and my application 
> > >doesn't work anymore.
> > >I get an error from postgresql :
> > >
> > >   *    Warning: MapServer Error in prep_DB(): Error executing POSTGIS 
> > >DECLARE (the actual query)     statement: 'DECLARE mycursor BINARY 
> > >CURSOR FOR SELECT 
> > >asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from 
> > >giscontourfrance WHERE the_geom && setSRID('BOX3D(42370.624 
> > >1616618.5,1203176.624 2677218.5)'::BOX3D, 
> > >find_srid('','giscontourfrance','the_geom') )'
> > >
> > >Postgresql reports the error ''
> > >*
> > >But when i launch this query from phpPgAdmin :
> > >
> > >    *SELECT 
> > >asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from 
> > >giscontourfrance WHERE the_geom && setSRID('BOX3D(42370.624 
> > >1616618.5,1203176.624 2677218.5)'::BOX3D, 
> > >find_srid('','giscontourfrance','the_geom') )*
> > >
> > >It works....
> > >
> > >Can someone point me to use mapserver 3.6.7 because i want to upgrade 
> > >postgresql 7.2.4 to 7.3.*
> > >and only 3.6.7 support pgsql 7.3 ( I don't want to upgrade to mapserver 
> > >4.0.1 now)
> > >
> > >Thanks
> > >
> > >_______________________________________________
> > >Mapserver-users mailing list
> > >Mapserver-users at lists.gis.umn.edu http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
> > >
> > >
> > >  
> > >
> > 
> > 
> > _______________________________________________
> > Mapserver-users mailing list
> > Mapserver-users at lists.gis.umn.edu
> > http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
-- 
"He's no geek.His tan's too good." -Benjamin Choate