search

pramsey / pgsql-ogr-fdw

PostgreSQL foreign data wrapper for OGR
MIT License
237 stars 34 forks source link

location & permissions for VRT file? #224

Closed Brent-Wood closed 2 years ago

Brent-Wood commented 2 years ago

I have a Postgres DB with ogr_fdw installed. I can successfully connect to a WFS service, and query the foreign table, and I'm now trying to access an external MySQL database using a VRT file.

The VRT file is working with QGIS (to retrieve and plot the data) and with ogr_fdw_info, eg:

ogr_fdw_info -s "/home/woodb/fdw/SpecifyQuery3.vrt" Format: OGR_VRT

Layers: collectionObjects

When I try to create the server in the Postgres db I get an error: CREATE SERVER specify_vrt FOREIGN DATA WRAPPER ogr_fdw OPTIONS ( datasource '/home/woodb/fdw/SpecifyQuery3.vrt', format 'VRT');" ERROR: unable to connect to data source "/home/woodb/fdw/SpecifyQuery3.vrt"

I understand the message to mean the VRT file can't be found, rather than the file can be found but the datasource it points at can't. I have tried changing permissions & locations of the file, but have always got this error message.

Any advice on what I'm doing wrong here, and how to make this work?

Thanks.

pramsey commented 2 years ago

Well, the database will be attempting to access the file as the user the database is running under (usually postgres) so unless that user has full access not just to the file, but to every directory containing the file (/home, /home/woodb, /home/woodb/fdw, etc). A common mistake is to set the file world readable, but not allow access into a containing directory (home directories are commonly initialized with permissions set to 0700).

Brent-Wood commented 2 years ago

Thanks Paul, appreciated.

Found it...

I had (sourced from an example at: https://github.com/pramsey/pgsql-ogr-fdw/issues/26 )

CREATE SERVER specify_vrt FOREIGN DATA WRAPPER ogr_fdw OPTIONS ( --datasource '/var/lib/postgresql/SpecifyQuery3.vrt', --datasource '/home/woodb/Downloads/SpecifyQuery3.vrt', --datasource '/home/woodb/fdw/SpecifyQuery3.vrt', datasource '/tmp/SpecifyQuery3.vrt', format 'VRT');

Finally found something not quite right... format should be: 'OGR_VRT'

Then it works fine!!!

Many thanks for all your efforts in providing such fantastic tools for people like me to play with... and finding time to help us use them...

robe2 commented 2 years ago

I think this ticket can be closed?