PostgreSQL authentication method.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgreSQL authentication method.

Moen, Paul T.
I recently tried a Mapserver layer connected to a PostgreSQL 13.2 database that had password_encryption = scram-sha-256.  I was unsuccessful in getting it to work until I switched it to md5 and reset the passwords.  The following error was logged and the interesting part seems to be (authentication method 10 not supported).

[Thu Feb 18 13:57:02.652879 2021] [php7:warn] [pid 16668] [client 192.168.100.4:57341] PHP Warning:  mapObj::drawQuery(): [MapServer Error]: msPostGISLayerOpen(): Database connection failed. Check server logs for more details.Is the database running? Is it allowing connections? Does the specified user exist? Is the password valid? Is the database on the standard port?\n in /Volumes/Data/WebServer/newswc/php/drawmappm.php on line 243, referer: http://stagmapservice.swc.nd.gov/
Database connection failed (authentication method 10 not supported
) with connect string 'user=dauser password=****** dbname=swc host=dadatabase.blah.blah.blah port=5432'
Is the database running? Is it allowing connections? Does the specified user exist? Is the password valid? Is the database on the standard port? in msPostGISLayerOpen()msPostGISLayerOpen(): Query error. Database connection failed. Check server logs for more details.Is the database running? Is it allowing connections? Does the specified user exist? Is the password valid? Is the database on the standard port?
msDrawMap(): Image handling error. Failed to draw layer named 'Countries - Natl Atlas'.

Is this an issue with MapServer?  If so, will scram-sha-256 authentication be supported by Mapserver?

Thanks,

Paul

MapServer version 7.6.2 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=ICONV SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI SUPPORTS=GEOS SUPPORTS=POINT_Z_M SUPPORTS=PBF INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL authentication method.

Steve Lime-2
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.

On Thu, Feb 18, 2021 at 2:25 PM Moen, Paul T. <[hidden email]> wrote:
I recently tried a Mapserver layer connected to a PostgreSQL 13.2 database that had password_encryption = scram-sha-256.  I was unsuccessful in getting it to work until I switched it to md5 and reset the passwords.  The following error was logged and the interesting part seems to be (authentication method 10 not supported).

[Thu Feb 18 13:57:02.652879 2021] [php7:warn] [pid 16668] [client 192.168.100.4:57341] PHP Warning:  mapObj::drawQuery(): [MapServer Error]: msPostGISLayerOpen(): Database connection failed. Check server logs for more details.Is the database running? Is it allowing connections? Does the specified user exist? Is the password valid? Is the database on the standard port?\n in /Volumes/Data/WebServer/newswc/php/drawmappm.php on line 243, referer: http://stagmapservice.swc.nd.gov/
Database connection failed (authentication method 10 not supported
) with connect string 'user=dauser password=****** dbname=swc host=dadatabase.blah.blah.blah port=5432'
Is the database running? Is it allowing connections? Does the specified user exist? Is the password valid? Is the database on the standard port? in msPostGISLayerOpen()msPostGISLayerOpen(): Query error. Database connection failed. Check server logs for more details.Is the database running? Is it allowing connections? Does the specified user exist? Is the password valid? Is the database on the standard port?
msDrawMap(): Image handling error. Failed to draw layer named 'Countries - Natl Atlas'.

Is this an issue with MapServer?  If so, will scram-sha-256 authentication be supported by Mapserver?

Thanks,

Paul

MapServer version 7.6.2 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=ICONV SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI SUPPORTS=GEOS SUPPORTS=POINT_Z_M SUPPORTS=PBF INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL authentication method.

Mapserver - User mailing list
The key is the message "authentication method 10 not supported". It seems that PostgreSQL client library's version is older and does not support that encryption method. Versions 10 and later supports it.

On Thursday, February 18, 2021, 3:49:48 PM CST, Steve Lime <[hidden email]> 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.

On Thu, Feb 18, 2021 at 2:25 PM Moen, Paul T. <[hidden email]> wrote:
I recently tried a Mapserver layer connected to a PostgreSQL 13.2 database that had password_encryption = scram-sha-256.  I was unsuccessful in getting it to work until I switched it to md5 and reset the passwords.  The following error was logged and the interesting part seems to be (authentication method 10 not supported).

[Thu Feb 18 13:57:02.652879 2021] [php7:warn] [pid 16668] [client 192.168.100.4:57341] PHP Warning:  mapObj::drawQuery(): [MapServer Error]: msPostGISLayerOpen(): Database connection failed. Check server logs for more details.Is the database running? Is it allowing connections? Does the specified user exist? Is the password valid? Is the database on the standard port?\n in /Volumes/Data/WebServer/newswc/php/drawmappm.php on line 243, referer: http://stagmapservice.swc.nd.gov/
Database connection failed (authentication method 10 not supported
) with connect string 'user=dauser password=****** dbname=swc host=dadatabase.blah.blah.blah port=5432'
Is the database running? Is it allowing connections? Does the specified user exist? Is the password valid? Is the database on the standard port? in msPostGISLayerOpen()msPostGISLayerOpen(): Query error. Database connection failed. Check server logs for more details.Is the database running? Is it allowing connections? Does the specified user exist? Is the password valid? Is the database on the standard port?
msDrawMap(): Image handling error. Failed to draw layer named 'Countries - Natl Atlas'.

Is this an issue with MapServer?  If so, will scram-sha-256 authentication be supported by Mapserver?

Thanks,

Paul

MapServer version 7.6.2 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=ICONV SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI SUPPORTS=GEOS SUPPORTS=POINT_Z_M SUPPORTS=PBF INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL authentication method.

Moen, Paul T.
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 <[hidden email]>
Date: Thursday, February 18, 2021 at 5:53 PM
To: "Moen, Paul T." <[hidden email]>, Steve Lime <[hidden email]>
Cc: "[hidden email]" <[hidden email]>
Subject: Re: [mapserver-users] PostgreSQL authentication method.

On Thursday, February 18, 2021, 3:49:48 PM CST, Steve Lime <[hidden email]> 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.



_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users