Arch Linux

pgoetz

Member

From: Austin, Texas

Registered: 2014-02-21

Posts: 344

[SOLVED] Did MariaDB recently change security on LOAD DATA INFILE ?

For years I've been using this command (run from a script) to reload a database from a set of generated records:

DELETE FROM my_table;
LOAD DATA INFILE
           "/home/world-read-and-executable-folder/my_data.load"
INTO TABLE my_table 
FIELDS TERMINATED BY "||||"
LINES TERMINATED BY 0x1e;

I don't reload databases every day, so can't pinpoint what upgrade caused this, but now when I try running the same script that has worked forever I get an error message:

pgoetz@www:database$ mysql -u root -p executive_council < load_topics.sql
Enter password: 
ERROR 13 (HY000) at line 15: Can't get stat of '/home/site-development/executive_council-utf8/data/database/topics.load' (Errcode: 13 "Permission denied")

The load file is world readable, and every directory in it's path is world r-x permissions.  I know I can load the data by copying the load file to /var/mysql/my_db and executing the command from the mysql shell, but this is onerous and clunky.  More importantly I haven't found any documented changes in how this should behave.

Last edited by pgoetz (2017-06-15 11:17:40)

pgoetz

Member

From: Austin, Texas

Registered: 2014-02-21

Posts: 344

Re: [SOLVED] Did MariaDB recently change security on LOAD DATA INFILE ?

The issue is that a recent change in MariaDB systemd service file prevents any file access from /home, /root, /run/user, and /tmp:

# Prevent accessing /home, /root and /run/user
ProtectHome=true

access to /tmp is restricted elsewhere in the service file.  For the sake of someone else running into this issue, here are the necessary steps to allow file access from /home.  Note that even if your service file is called mysqld.service (as mine is), you still want to name the folder for the supplementary service files mariadb.service.d (or it won't work).

 # mkdir /etc/systemd/system/mariadb.service.d
 # cd /etc/systemd/system/mariadb.service.d

 - created a file called, say load_data.conf, and enter the following lines in the file:

[Service]

# Override default directive with prevents  accessing /home, /root and /run/user
ProtectHome=false

 # systemctl daemon-reload
 # systemctl restart mysqld

This is all documented here; https://mariadb.com/kb/en/mariadb/systemd/

(You just need to know where to look.)

Last edited by pgoetz (2017-06-15 11:18:18)