search

notum-cz / strapi-plugin-location

This plugin allows users to create location inputs and store latitude and longitude values as geometry types in a PostGIS database. It also provides functionality to filter items based on their location.
MIT License
26 stars 13 forks source link

Crash on startup with PostGIS enabled PostgreSQL 14 #20

Closed claudiobgit closed 1 year ago

claudiobgit commented 1 year ago

Bug report

Describe the bug

When PostGIS is already enabled on server, Strapi crashes on startup in developing environment (not tested in production). This happens after enabling the plugin and adding the custom field to a collection, Strapi could not start and logs show the following:

[2023-08-08 18:57:53.332] info: Error Enabling PostGIS, create extension postgis; - extension "postgis" already exists
[2023-08-08 18:57:53.351] error: Error accessing POSTGIS
Error: Could not find Custom Field: plugin::location-plugin.location

On the server side, the Postgres logs show:

2023-08-08 17:22:32.530 UTC [476909] strapi@develop_strapi ERROR:  function postgis_version() does not exist at character 8
2023-08-08 17:22:32.530 UTC [476909] strapi@develop_strapi HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2023-08-08 17:22:32.530 UTC [476909] strapi@develop_strapi STATEMENT:  SELECT PostGIS_version();
2023-08-08 17:22:32.547 UTC [476909] strapi@develop_strapi ERROR:  extension "postgis" already exists
2023-08-08 17:22:32.547 UTC [476909] strapi@develop_strapi STATEMENT:  create extension postgis;
2023-08-08 17:22:32.566 UTC [476909] strapi@develop_strapi ERROR:  function postgis_version() does not exist at character 8
2023-08-08 17:22:32.566 UTC [476909] strapi@develop_strapir HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2023-08-08 17:22:32.566 UTC [476909] strapi@develop_strapir STATEMENT:  SELECT PostGIS_version();
2023-08-08 17:22:32.748 UTC [476909] strapi@develop_strapi LOG:  could not receive data from client: Connection reset by peer

Steps to reproduce the behavior

  1. Install the strapi-plugin-location following documentation
  2. Execute npm install && npm run build && npm run develop
  3. Access the Strapi dashboard and add the custom field provided by the plugin to a collection, Strapi restarts
  4. See error

Expected behavior

Strapi should restart properly.

System

Additional context

PostGIS has its own 'postgis' scheme enabled on the db, Strapi uses the 'public' scheme. The 'search_path' on the database is configured with 'public, postgis' values.

Running the command 'SELECT postgis_full_version();' on the database properly returns PostGIS version: POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.12" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

omikulcik commented 1 year ago

Hello @claudiobgit, thank you for reporting this issue. I will try to replicate it and get back to you.

claudiobgit commented 1 year ago

Thanks, I tried many times but couldn't get through. I didn't try the Docker image as I already have a Postgis enabled server running directly on host. Any detail that could help, please ask.

Il giorno mer 9 ago 2023 alle ore 08:52 Ondrej Mikulcik < @.***> ha scritto:

Hello @claudiobgit https://github.com/claudiobgit, thank you for reporting this issue. I will try to replicate it and get back to you.

ā€” Reply to this email directly, view it on GitHub https://github.com/notum-cz/strapi-plugin-location/issues/20#issuecomment-1670770783, or unsubscribe https://github.com/notifications/unsubscribe-auth/AE3RLOVQPIOAFHBL3AICVLTXUMXSXANCNFSM6AAAAAA3I2GKUI . You are receiving this because you were mentioned.Message ID: @.***>

-- Claudio Bernardini

omikulcik commented 1 year ago

I am using this image https://registry.hub.docker.com/r/postgis/postgis/tags?page=1 and I can't find version 3.2.0 there. Are you sure that is the correct version? I assume the image has a different versioning.

claudiobgit commented 1 year ago

I am using this image https://registry.hub.docker.com/r/postgis/postgis/tags?page=1 and I can't find version 3.2.0 there. Are you sure that is the correct version? I assume the image has a different versioning.

I am not running a Docker image for PostgreSQL but I am sure of the running version that I get with: SELECT * FROM pg_available_extensions WHERE name = 'postgis';

  name   | default_version | installed_version |                          comment                           
---------+-----------------+-------------------+------------------------------------------------------------
 postgis | 3.2.0           | 3.2.0             | PostGIS geometry and geography spatial types and functions

I will try to dig deeper in the plugin and post any advance here.

omikulcik commented 1 year ago

Thank you, I am unfortunately not able to replicate your setup easily.

claudiobgit commented 1 year ago

The problem should be considered solved after having manually granted UC privileges to PUBLIC on 'postgis' schema in the database @omikulcik

omikulcik commented 1 year ago

Ok, so I am closing this issue. Thank you @claudiobgit

emaddoma commented 1 year ago

I've just run into a very similar issue on my end after attempting to deploy to Heroku. Everything worked fine on my local.

PG 15.4.
Strapi 4.12.7.

@claudiobgit What are UC privileges?

2023-08-31T19:37:37.223489+00:00 app[web.1]: [2023-08-31 19:37:37.221] info: Error Enabling PostGIS, no pg_hba.conf entry for host "44.201.222.159", user "qhxyadnfszkhvb", database "d2km56udn7ts5g", no encryption
2023-08-31T19:37:37.238511+00:00 app[web.1]: [2023-08-31 19:37:37.238] error: Error accessing POSTGIS
2023-08-31T19:37:37.243644+00:00 app[web.1]: [2023-08-31 19:37:37.243] debug: ā›”ļø Server wasn't able to start properly.
2023-08-31T19:37:37.243848+00:00 app[web.1]: [2023-08-31 19:37:37.243] error: Could not find Custom Field: plugin::location-plugin.location
2023-08-31T19:37:37.243852+00:00 app[web.1]: Error: Could not find Custom Field: plugin::location-plugin.location
2023-08-31T19:37:37.243852+00:00 app[web.1]: at Object.get (/app/node_modules/@strapi/strapi/lib/core/registries/custom-fields.js:34:15)
2023-08-31T19:37:37.243861+00:00 app[web.1]: at convertCustomFieldType (/app/node_modules/@strapi/strapi/lib/utils/convert-custom-field-type.js:15:67)
2023-08-31T19:37:37.243861+00:00 app[web.1]: at Strapi.register (/app/node_modules/@strapi/strapi/lib/Strapi.js:409:5)
2023-08-31T19:37:37.243861+00:00 app[web.1]: at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
2023-08-31T19:37:37.243862+00:00 app[web.1]: at async Strapi.load (/app/node_modules/@strapi/strapi/lib/Strapi.js:492:5)
2023-08-31T19:37:37.243862+00:00 app[web.1]: at async Strapi.start (/app/node_modules/@strapi/strapi/lib/Strapi.js:218:9)
2023-08-31T19:37:37.526141+00:00 heroku[web.1]: Process exited with status 1
2023-08-31T19:37:37.557614+00:00 heroku[web.1]: State changed from starting to crashed
emaddoma commented 1 year ago

I figured it out. I was missing a strapi config for the environment.