Compile PostGIS with protobuf-c support

[lopezjurip]

Feature request

Is your feature request related to a problem? Please describe.

I'm trying to create and endpoint as an RPC function that returns a TileJSON response and looks like PostgREST supports binary responses: https://postgrest.org/en/stable/api.html?highlight=bytea#binary-output.

PostGIS has some functions that enables doing so, these are: ST_AsMVT and ST_AsMVTGeom. PostGIS requires to be compiled with protobuf support. Unfortunately supabase's extension has disabled protobuf https://github.com/supabase/postgres/blob/6b399474c5bddeeb3ea2e8bbabc7764a39fd4ae4/ansible/tasks/postgres-extensions/01-postgis.yml#L76

Describe the solution you'd like

Removing the --without-protobuf flag from /postgres/ansible/tasks/postgres-extensions/01-postgis.yml

Describe alternatives you've considered

I have tried to return geometry and jsonb responses instead of protobuf binaries with ST_AsMVTGeom but is not possible because for ST_AsMVTGeom to execute it needs protobuf support.

Additional context

This example query fails:

SELECT ST_AsText(ST_AsMVTGeom(
	ST_GeomFromText('POLYGON ((0 0, 10 0, 10 5, 0 -5, 0 0))'),
	ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)),
	4096, 0, false));

https://postgis.net/docs/ST_AsMVTGeom.html

[dragarcia]

Hi @lopezjurip 👋

This should be feasible. I'll move this over to the supabase/postgres repository to get it better tracked.

[pessimo]

any update on this? or any thought on this change? sounds like a good change

[pessimo]

I tried to build an image and found that 2 files need to be changed.

the first is docker.vars.json, change the pg version to "14-bullseye", I'm not sure if this one need change, but as the official readme file says, supabase uses ubuntu 20, there might be some difference between Debian-based image and ubuntu based.

the second is ansible/tasks/postgres-extensions/01-postgis.yml

besides the change @lopezjurip provide, you also need to add

apt:
    pkg:
      - libprotobuf-c-dev
      - protobuf-c-compiler

and in order to make sure dependencies do not get purged. I also add them to the following apt mark block, though this might be not needed.

[bienalon]

Adding my vote to incorporate the recommended change.

[rohfosho]

Any updates on this front?

[kylerummens]

Any updates to this?

This is the only thing preventing me from serving tiles from my supabase instance.

[pessimo]

@kylerummens hi, you can try my build above. I've been using this build for two months to serve tiles online with no problem.

[kylerummens]

@kylerummens hi, you can try my build above. I've been using this build for two months to serve tiles online with no problem.

I'm not self hosting supabase (I guess my wording of "from my supabase instance" was misleading). So the changes that need to be made to my database hosted by supabase can only done by supabase.

Please correct me if I'm wrong.

[kylerummens]

Looks like this is in the works. Commit: https://github.com/supabase/postgres/commit/4dd26f5108ef151f5c4328f806007c69dc29f944

[pashkinelfe]

Seems like it's done already.