unable to use postgis_raster extension: GDAL Drivers Disabled

[lookevink]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I am working with large climate datasets and need to optimize using PostGIS Raster. Both PostGIS and PostGIS_Raster extensions are enabled, as confirmed by SELECT PostGIS_Full_Version(), which indicates proper versions for PostGIS, GDAL, and Raster. However, when attempting to insert raster data using ST_FromGDALRaster, I encounter the error rt_util_gdal_open: Cannot open file. All GDAL drivers disabled, suggesting that GDAL drivers are not correctly enabled despite proper setup. I'm also unable to enable the extensions w/o superuser permission

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

Steps to Reproduce

1. Setup PostgreSQL and PostGIS:
   • Install PostgreSQL 15 and PostGIS 3.3.2.
   • Ensure GDAL (version 3.8.5 or compatible) is installed.
2. Enable PostGIS and Raster Extensions:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster;

3. Check PostGIS Version:
   Run the following query to confirm PostGIS and GDAL are properly installed:

SELECT PostGIS_Full_Version();

getting:

POSTGIS="3.3.2" [...] GDAL="GDAL 3.8.5" [...] RASTER

4. Create a Table for Raster Data:

CREATE TABLE __gpcp_precip (
time TIMESTAMP PRIMARY KEY,
rast RASTER
);

5. Attempt to Insert Raster Data:
   Use the following query with appropriate values:

INSERT INTO __gpcp_precip (time, rast)
VALUES ('2024-12-01 00:00:00', ST_FromGDALRaster('/path/to/raster.tif'))
ON CONFLICT (time) DO UPDATE
SET rast = EXCLUDED.rast;

6. Error Encountered:
   The error rt_util_gdal_open: Cannot open file. All GDAL drivers disabled is thrown.

Expected behavior

data is inserted

Additional context

Add any other context about the problem here.

[samrose]

@lookevink thank you for reporting this issue.

I wanted to ask where you experienced this problem? Are you experiencing this issue in a hosted version of supabase, or are you running supabase/postgres server locally on your machine/deployed to a server?

[lookevink]

@samrose thank you for the prompt response! hosted version

[samrose]

@lookevink when using this query

INSERT INTO __gpcp_precip (time, rast)
VALUES ('2024-12-01 00:00:00', ST_FromGDALRaster('/path/to/raster.tif'))
ON CONFLICT (time) DO UPDATE
SET rast = EXCLUDED.rast;

Where did you upload to the raster.tif file to? Do you have an example raster.tif that I could test this out with?

Testing locally GDAL seems to be working and loading raster files with this query. However, I wanted to test on hosted version following the same approach you are taking and similar file if you don't mind sharing how you uploaded your file, and an example of a file you need to work in this insert? Thank you!

[lookevink]

@samrose .tif file is in a temp file after being converted from an nc file. I pulled the tif from memory for reference. both are in this folder

spinning up a new postgre locally & storing seems to work fine. can definitely do a fdw but prefer to keep everything in supabase