[mapserver-users] Mapserver no longer reading pgpass file?
Hi List, My MapServer mapfiles have been set up to read a Postgres connection with `PG:host=... post=... dbname=...` etc for some time now. I upgraded the packages on my linux box and the mapfiles no longer work (error when connecting to postgres). However, running gdalinfo "PG:host... ..." using the exact same connection string works fine. I realized that it's an authentication issue as the mapfiles work fine again when I add password=... to the connection string. I have a pgpass file in the cgi-bin, and one in my home directory which is referenced with the PGPASS environment variable. Why would mapserver suddenly not provide the correct password or consume the pgpass file anymore? Does it need to be somewhere else? Why does it work fine with gdalinfo without the password? Thanks, Thomas ___ mapserver-users mailing list mapserver-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Help debugging PostGIS raster connection
These findings, along with my entire solution for querying rows of a table with a raster column based on timestamp in the WMS GET parameter, has been added to the MapServer wiki Tips and Tricks section which links to this page: https://github.com/mapserver/mapserver/wiki/Raster-Layers:-GDAL-PostGISRaster-Tricks Cheers Thomas On Wed, Mar 6, 2019 at 6:29 AM Jeff McKenna wrote: > Great news Thomas. I also think (for good karma) all of your steps and > hints for PGraster could be added to a new wiki page in the "Tricks, > Tips, and HowTos" section of https://github.com/mapserver/mapserver/wiki > > (that way people don't have to troll through thousands of email messages > to find your important tip) > > thanks! > > -jeff > > > > On 2019-03-05 7:02 PM, Thomas Horner wrote: > > Great! Thanks Jeff. Turns out it was the single quotes in other parts > > of the connection string... Not sure why. Possibly special characters > > in the password or username? After removing them, I no longer have > > connection issues. With that being solved, removing the WHERE clause at > > least returns a blank image instead of an error. > > > > I then brought back the WHERE clause, which returned an error despite > > working in GDAL. > > This WHERE clause returns a single raster result in GDAL with the exact > > same connection string as above: > > gdalinfo "PG:.. where='timestamp=\'2019-03-05 00:00:00\''" > > > > However, no matter the combination of string literals, escape > > characters, and whatnot, I could not get MapServer to handle this clause > > without error. > > > > But wait! Enter Postgres's dollar-quoted string constants: > > where='timestamp=$$2019-03-05 00:00:00$$' > > > > The DATA connection string now works. Hope this helps anyone in the > future. > > > > Regards, > > Thomas > > > > On Tue, Mar 5, 2019 at 3:24 PM Jeff McKenna > > mailto:jmcke...@gatewaygeomatics.com>> > > wrote: > > > > Hi Thomas, > > > > I would try the following: > > > > - remove all single quotes from your DATA statement (most times they > > are > > unnecessary for my use, and cause me problems) > > - remove the WHERE clause > > - also try to access another table from your database, but not > raster, > > using that same DATA connection statement (I bet the problem has > > nothing > > to do with the raster table, but instead is your connection > parameters) > > - verify that remote connections can access your database (check your > > file pg_hba.conf) > > > > hope that brings ideas and opens doors... > > > > -jeff > > > > > > > > -- > > Jeff McKenna > > MapServer Consulting and Training Services > > https://gatewaygeomatics.com/ > > > > > > On 2019-03-04 2:38 PM, Thomas Horner wrote: > > > I have a PostGIS connection string that works fine in GDAL, as I > > get a > > > raster response when running > > > --- > > > gdalinfo "PG:host=... port=5432 user='...' password='...' > > > dbname='weather_models' schema='rasters' > > > table='wpc_snow_50pct_1551268800' column='rast' mode=1 > > > where='timestamp=\'2019-03-02 00:00:00\''" > > > --- > > > > > > On this very same server, I have cgi-mapserver running, and the > > output > > > from /usr/lib/cgi-bin/mapserv/ -v is > > > --- > > > MapServer version 7.2.1 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML > > SUPPORTS=PROJ > > > SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=SVG_SYMBOLS > > > SUPPORTS=RSVG SUPPORTS=ICONV SUPPORTS=FRIBIDI SUPPORTS=WMS_SERVER > > > SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT > > > SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI > > > SUPPORTS=THREADS SUPPORTS=GEOS SUPPORTS=PBF INPUT=JPEG > INPUT=POSTGIS > > > INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE > > > --- > > > > > > The problem is, when I use that same PG:... connection string as > the > > > DATA for a LAYER in MapServer, I get an unhelpful error: Unable to > > > access file. Corrupt, empty or missing file ' connection > > > string>' Couldn't establish a database connection. > > > > > > Despite turning DEBUG level to 5, and CPL_DEBUG to ON in the MAP > > block > > &g
Re: [mapserver-users] Help debugging PostGIS raster connection
Great! Thanks Jeff. Turns out it was the single quotes in other parts of the connection string... Not sure why. Possibly special characters in the password or username? After removing them, I no longer have connection issues. With that being solved, removing the WHERE clause at least returns a blank image instead of an error. I then brought back the WHERE clause, which returned an error despite working in GDAL. This WHERE clause returns a single raster result in GDAL with the exact same connection string as above: gdalinfo "PG:.. where='timestamp=\'2019-03-05 00:00:00\''" However, no matter the combination of string literals, escape characters, and whatnot, I could not get MapServer to handle this clause without error. But wait! Enter Postgres's dollar-quoted string constants: where='timestamp=$$2019-03-05 00:00:00$$' The DATA connection string now works. Hope this helps anyone in the future. Regards, Thomas On Tue, Mar 5, 2019 at 3:24 PM Jeff McKenna wrote: > Hi Thomas, > > I would try the following: > > - remove all single quotes from your DATA statement (most times they are > unnecessary for my use, and cause me problems) > - remove the WHERE clause > - also try to access another table from your database, but not raster, > using that same DATA connection statement (I bet the problem has nothing > to do with the raster table, but instead is your connection parameters) > - verify that remote connections can access your database (check your > file pg_hba.conf) > > hope that brings ideas and opens doors... > > -jeff > > > > -- > Jeff McKenna > MapServer Consulting and Training Services > https://gatewaygeomatics.com/ > > > On 2019-03-04 2:38 PM, Thomas Horner wrote: > > I have a PostGIS connection string that works fine in GDAL, as I get a > > raster response when running > > --- > > gdalinfo "PG:host=... port=5432 user='...' password='...' > > dbname='weather_models' schema='rasters' > > table='wpc_snow_50pct_1551268800' column='rast' mode=1 > > where='timestamp=\'2019-03-02 00:00:00\''" > > --- > > > > On this very same server, I have cgi-mapserver running, and the output > > from /usr/lib/cgi-bin/mapserv/ -v is > > --- > > MapServer version 7.2.1 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ > > SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=SVG_SYMBOLS > > SUPPORTS=RSVG SUPPORTS=ICONV SUPPORTS=FRIBIDI SUPPORTS=WMS_SERVER > > SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT > > SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI > > SUPPORTS=THREADS SUPPORTS=GEOS SUPPORTS=PBF INPUT=JPEG INPUT=POSTGIS > > INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE > > --- > > > > The problem is, when I use that same PG:... connection string as the > > DATA for a LAYER in MapServer, I get an unhelpful error: Unable to > > access file. Corrupt, empty or missing file ' > string>' Couldn't establish a database connection. > > > > Despite turning DEBUG level to 5, and CPL_DEBUG to ON in the MAP block > > of my mapfile, I don't get any helpful debug information whatsoever in > > my error log -- just the connection string and "Couldn't establish a > > database connection." I have tried a variety of where clauses and other > > tables and get the same result. I have no idea if the actual error is > > coming from GDAL or MapServer, despite all those strings working fine > > when running them directly via GDAL. > > > > By the way, it's the same error whether I use 'timestamp'= or timestamp=. > > > > Here is the mapfile: > > --- > > MAP > > CONFIG "MS_ERRORFILE" "/map/error.txt" > > CONFIG "CPL_DEBUG" "ON" > > DEBUG 5 > > PROJECTION > >"init=epsg:4326" > > END > > LAYER > >NAME wpc_snow_50pct > >TYPE RASTER > >STATUS ON > >VALIDATION > > "timestamp" ".*" > >END > >PROJECTION > > "init=epsg:4326" > >END > >METADATA > > "wms_title" "wpc_snow_50pct" > > "wms_srs" "EPSG:4326" > > "wms_extent" "-108.5 41 -104.5 36" > > "wms_enable_request" "*" > >END > >DATA "PG:host=... port=5432 dbname='weather_models' user='...' > > password='...' schema='rasters' table='wpc_snow_50pct_1551268800' > > column='rast' mode=1 where='timestamp=\'2019-03-02 00:00:00\''" > >PROCESSING "BANDS=1" > > END > > END > > --- > > > > Any help is appreciated! > > > > Thanks, > > Thomas > > > > > ___ > mapserver-users mailing list > mapserver-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/mapserver-users ___ mapserver-users mailing list mapserver-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
[mapserver-users] Help debugging PostGIS raster connection
I have a PostGIS connection string that works fine in GDAL, as I get a raster response when running --- gdalinfo "PG:host=... port=5432 user='...' password='...' dbname='weather_models' schema='rasters' table='wpc_snow_50pct_1551268800' column='rast' mode=1 where='timestamp=\'2019-03-02 00:00:00\''" --- On this very same server, I have cgi-mapserver running, and the output from /usr/lib/cgi-bin/mapserv/ -v is --- MapServer version 7.2.1 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=SVG_SYMBOLS SUPPORTS=RSVG SUPPORTS=ICONV SUPPORTS=FRIBIDI SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI SUPPORTS=THREADS SUPPORTS=GEOS SUPPORTS=PBF INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE --- The problem is, when I use that same PG:... connection string as the DATA for a LAYER in MapServer, I get an unhelpful error: Unable to access file. Corrupt, empty or missing file '' Couldn't establish a database connection. Despite turning DEBUG level to 5, and CPL_DEBUG to ON in the MAP block of my mapfile, I don't get any helpful debug information whatsoever in my error log -- just the connection string and "Couldn't establish a database connection." I have tried a variety of where clauses and other tables and get the same result. I have no idea if the actual error is coming from GDAL or MapServer, despite all those strings working fine when running them directly via GDAL. By the way, it's the same error whether I use 'timestamp'= or timestamp=. Here is the mapfile: --- MAP CONFIG "MS_ERRORFILE" "/map/error.txt" CONFIG "CPL_DEBUG" "ON" DEBUG 5 PROJECTION "init=epsg:4326" END LAYER NAME wpc_snow_50pct TYPE RASTER STATUS ON VALIDATION "timestamp" ".*" END PROJECTION "init=epsg:4326" END METADATA "wms_title" "wpc_snow_50pct" "wms_srs" "EPSG:4326" "wms_extent" "-108.5 41 -104.5 36" "wms_enable_request" "*" END DATA "PG:host=... port=5432 dbname='weather_models' user='...' password='...' schema='rasters' table='wpc_snow_50pct_1551268800' column='rast' mode=1 where='timestamp=\'2019-03-02 00:00:00\''" PROCESSING "BANDS=1" END END --- Any help is appreciated! Thanks, Thomas ___ mapserver-users mailing list mapserver-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
