Postgresql – Can plpython3u open files on the file system

plpythonpostgresql

I am trying to write a plpython3u function that should open a file in the file system and read some values out of it that get returned by a query. But I am getting a permission denied error when doing so. I am well aware of SQL injection and the dangers of mixing up the database with the file system, this is just for the sake of testing the boundaries for my own knowledge, not for deployment in a production environment.

I tried using chmod 777 on the file in question so that anybody can do anything with it, but I still get permission denied when trying to open the file.

This is the script in question:

CREATE OR REPLACE FUNCTION roof_type_to_name(roof_type TEXT) RETURNS TEXT AS
$$

import xml.etree.ElementTree as ET
ns = {'gml': "http://www.opengis.net/gml",
    'bldg': "http://www.opengis.net/citygml/building/1.0"}
rooftype_schema = ET.parse(r'/path/to/file.xml')
definitions = rooftype_schema.findall(".//gml:Definition", ns)
definition_list = list(definitions)
for definition in definition_list:
    prop_list = list(definition)
    if prop_list[1].text == roof_type:
        return prop_list[2].text

$$ LANGUAGE plpython3u;

I also know that there are other ways of reading xml with postgres, again I am just using this as a personal learning experience with plpython3u with something I am familiar with.

Is it possible at all to open a file on the file system with plpython3u? Or is it totally locked-down for safety reasons?

Best Answer

It is quite possible. But the OS user running the PostgreSQL server (usually 'postgres') might not have permission to traverse the directory tree containing the file even if it has permissions to the file itself. Or might be in a chroot jail and so can't even see the directory. Or it could be blocked by SELinux policy. This is system's level stuff, nothing specific to PostgreSQL or even databases generally.

Related Question