location & permissions for VRT file?

[Brent-Wood]

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]

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]

Thanks Paul, appreciated.

Found it...

I had (sourced from an example at: #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]

I think this ticket can be closed?