How to debug PostGIS connection issue from MapServer?

Question

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 '<snip of PG connection 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.

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

• user30184: I would send mail to mapserver-users, there are not many MapServer specialists on this forum.
• JGH: try quoting the timestamp column name, as it is an SQL keyword.
• Thomas: I've tried that without luck unfortunately. @JGH

Answer 1

After consulting mapserver-users, the problem was isolated.

I removed all single quotes from the connection string, so that it looked like this:

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\''"

This allowed the connection to be established (perhaps due to special characters in the username or password?), though MapServer still throws an error on the where clause.

For this, we can use Postgres dollar-quoted string constants as I could not get any combination of escape characters to work properly:

where='timestamp=$2019ドル-03-02 00:00:00$$'

Now everything works!