[mapserver-users] PostgreSQL authentication method.

Fri Feb 19 08:32:22 PST 2021

My compiling is the problem. I have it working now, but I had to edit the cmake/FindPostgreSQL.cmake file to get it to work because the wrong libpq library was being found by cmake.  I apologize for this being long, but I am not sure how else to explain my process.  Also, I am new to c, c++ and cmake so I hope I don't lead anyone astray here.

I am using macOS 10.15.7.  I used homebrew to install the mapserver dependencies with the following command.

	brew install httpd php cmake pkg-config swig cairo fcgi freetype gd gdal geos giflib libpng postgresql proj protobuf-c

I cloned mapserver from github, swtiched to the rel-7-6-2 tag, made a build directory, changed into the build directory and issued the following cmake.

	cmake ..  -DCMAKE_BUILD_TYPE=RELEASE -DCMAKE_FIND_FRAMEWORK=LAST -DCMAKE_VERBOSE_MAKEFILE=ON -Wno-dev -DWITH_CLIENT_WFS=ON -DWITH_CLIENT_WMS=ON -DWITH_PHP=ON -DWITH_CURL=ON -DWITH_FCGI=ON -DWITH_FRIBIDI=OFF -DWITH_GEOS=ON -DWITH_HARFBUZZ=OFF -DWITH_KML=ON -DWITH_POSTGIS=ON -DWITH_PYTHON=OFF -DWITH_SOS=ON -DWITH_WFS=ON -WITH_CAIRO=ON -DCMAKE_SKIP_RPATH=ON -DWITH_PHPNG=ON

This is where things go wrong.  The pg_config for postgresql 13 is in my path, yet the PostgreSQL and libpq found is listed as something completely different than the homebrew installed postgresql which is in /usr/local/lib

	--   * POSTGIS: /Library/Developer/CommandLineTools/SDKs/MacOSX10.15.sdk/usr/lib/libpq.tbd

I added the following to the FindPostgreSQL.cmake file to see what it was finding.
	message(NOTICE "PG_CONFIG is set to ${PG_CONFIG}")
	message(NOTICE "PG_INC_PATH is set to ${PG_INC_PATH}")
	message(NOTICE "PG_LIB_PATH is set to ${PG_LIB_PATH}")
	message(NOTICE "POSTGRESQL_INCLUDE_DIR is set to ${POSTGRESQL_INCLUDE_DIR}")
	message(NOTICE "POSTGRESQL_LIBRARY is set to ${POSTGRESQL_LIBRARY}")

The results were.
	PG_CONFIG is set to /usr/local/bin/pg_config
	PG_INC_PATH is set to /usr/local/include
	PG_LIB_PATH is set to /usr/local/lib
	POSTGRESQL_INCLUDE_DIR is set to /usr/local/include
	POSTGRESQL_LIBRARY is set to /Library/Developer/CommandLineTools/SDKs/MacOSX10.15.sdk/usr/lib/libpq.tbd

As I understand it, in the FindPostgreSQL.cmake file, the following code tries to find the pg_config file.  It looks in the PATHS listed after looking the default places known to cmake.  My system finds /usr/local/bin/pg_config, which is the PostgreSQL 13 installed by homebrew.
	find_program(PG_CONFIG NAMES pg_config
		PATHS
		$ENV{ProgramFiles}/PostgreSQL/*/bin
		$ENV{SystemDrive}/PostgreSQL/*/bin
	)

The following part executes the pg_config command, if pg_config was found, to set 2 variables to be used as search paths to find libpq-fe.h and libpq.
	if (PG_CONFIG)
		exec_program( ${PG_CONFIG} ARGS "--includedir" OUTPUT_VARIABLE PG_INC_PATH )
		exec_program( ${PG_CONFIG} ARGS "--libdir" OUTPUT_VARIABLE PG_LIB_PATH )
	else()
		message(WARNING "pg_config not found, will try some defaults")
	endif()

At this point, the following is set on my system.
	PG_CONFIG is set to /usr/local/bin/pg_config
	PG_INC_PATH is set to /usr/local/include
	PG_LIB_PATH is set to /usr/local/lib

The following find_path command tries to find the path where the libpg-fe.h include file is located.  Since, the PG_INC_PATH on my system is set to /usr/local/include, I assumed that it would find it there and set the POSTGRESQL_INCLUDE_DIR to /usr/local/include.  It doesn't because it searches additional paths, as per the documentation for find_path, before using the PATHS specified.  It looks like the PATHS specified are number 7 in the search process according to documentation. https://cmake.org/cmake/help/v3.12/command/find_path.html.
	find_path(POSTGRESQL_INCLUDE_DIR libpq-fe.h
		${PG_INC_PATH}
		/usr/include/server
		/usr/include/postgresql
		/usr/include/pgsql/server
		/usr/local/include/pgsql/server
		/usr/include/postgresql/server
		/usr/include/postgresql/*/server
		/usr/local/include/postgresql/server
		/usr/local/include/postgresql/*/server
		$ENV{ProgramFiles}/PostgreSQL/*/include/server
		$ENV{SystemDrive}/PostgreSQL/*/include/server
	)

The following find_path command trys to find the path where the libpq library file is located.  Since, the PG_INC_PATH on my system is set to /usr/local/lib, I assumed that it would find it there and set the POSTGRESQL_INCLUDE_DIR to /usr/local/lib.  It again doesn't because it searches additional paths that cmake may know.
	find_library(POSTGRESQL_LIBRARY NAMES pq libpq
		PATHS
		${PG_LIB_PATH}
		/usr/lib
		/usr/local/lib
		/usr/lib/postgresql
		/usr/lib64
		/usr/local/lib64
		/usr/lib64/postgresql
		$ENV{ProgramFiles}/PostgreSQL/*/lib/ms
		$ENV{SystemDrive}/PostgreSQL/*/lib/ms
	)

To make it work, I changed the IF block for PG_CONFIG as follows.  What this does is only search and use the library and include paths that are set by executing the pg_config command.  If pg_config is not found, then it searches the default locations. It seems to me that if pg_config is set my by environment, then it should use my pg_config to set the library and include paths.  Can anyone comment on whether this makes sense or not?  
	if (PG_CONFIG)
		exec_program( ${PG_CONFIG} ARGS "--includedir" OUTPUT_VARIABLE PG_INC_PATH )
		exec_program( ${PG_CONFIG} ARGS "--libdir" OUTPUT_VARIABLE PG_LIB_PATH )
		find_path(POSTGRESQL_INCLUDE_DIR libpq-fe.h
			PATHS
			${PG_INC_PATH}
			NO_DEFAULT_PATH
		)
		find_library(POSTGRESQL_LIBRARY NAMES pq libpq
			PATHS
			${PG_LIB_PATH}
			NO_DEFAULT_PATH
		)
	else()
		message(WARNING "pg_config not found, will try some defaults")
		find_path(POSTGRESQL_INCLUDE_DIR libpq-fe.h
			/usr/include/server
			/usr/include/postgresql
			/usr/include/pgsql/server
			/usr/local/include/pgsql/server
			/usr/include/postgresql/server
			/usr/include/postgresql/*/server
			/usr/local/include/postgresql/server
			/usr/local/include/postgresql/*/server
			$ENV{ProgramFiles}/PostgreSQL/*/include/server
			$ENV{SystemDrive}/PostgreSQL/*/include/server
		)

		find_library(POSTGRESQL_LIBRARY NAMES pq libpq
			PATHS
			/usr/lib
			/usr/local/lib
			/usr/lib/postgresql
			/usr/lib64
			/usr/local/lib64
			/usr/lib64/postgresql
			$ENV{ProgramFiles}/PostgreSQL/*/lib/ms
			$ENV{SystemDrive}/PostgreSQL/*/lib/ms
		)
	endif()

By changing FindPostgreSQL.cmake file I get the following results.  
	PG_CONFIG is set to /usr/local/bin/pg_config
	PG_INC_PATH is set to /usr/local/include
	PG_LIB_PATH is set to /usr/local/lib
	POSTGRESQL_INCLUDE_DIR is set to /usr/local/include
	POSTGRESQL_LIBRARY is set to /usr/local/lib/libpq.dylib

And cmake output shows the libpq that was installed from homebrew.
	--   * POSTGIS: /usr/local/lib/libpq.dylib

After this, the compile works fine and scram-sha-256 authentication works as expected.

Thanks,

Paul

From: Carlos Ruiz <boolean10001 at yahoo.com>
Date: Thursday, February 18, 2021 at 5:53 PM
To: "Moen, Paul T." <pmoen at nd.gov>, Steve Lime <sdlime at gmail.com>
Cc: "mapserver-users at lists.osgeo.org" <mapserver-users at lists.osgeo.org>
Subject: Re: [mapserver-users] PostgreSQL authentication method.

On Thursday, February 18, 2021, 3:49:48 PM CST, Steve Lime <sdlime at gmail.com> wrote: 

I wonder if that's more a function of the PostgreSQL client library version/capabilities. Do you know what MapServer is compiled against? You might try connecting to your database with psql as another test of the client lib.