@thomaszbz opened this Issue on March 24th 2015

I found some issues in the FAQ, which somehow disable security features to get MySQL's LOAD DATA INFILE to work:

Check that the sql user that Piwik is using has the permission to import the files: GRANT FILE on *.* to piwik<a class='mention' href='https://github.com/localhost'>@localhost</a>

It should be

db.*

or even better

db.temptable

and not

*.* 

which would affect all databases (not only the piwik database).

give the process mysqld executable-access (+x) to files in path/to/piwik/tmp/assets/* and all parent folders.

Why would mysqld need to execute files in tmp/assets? Instead, read access to files should do, execution is only needed for directories.

check that the request isn’t blocked by apparmor or any other security-software when accessing this folder. If you are using Ubuntu, you may have to disable apparmor which prevents mysql from accessing files in path/to/piwik/tmp/*
Edit the file /etc/apparmor.d/usr.sbin.mysqld and add the following path in the file: /path/to/piwik/tmp/assets/* rw,
Then restart apparmor with sudo /etc/init.d/apparmor restart
in your Mysql configuration (my.cnf) set the following options: [mysqld] local-infile and [mysql] local-infile
Then restart Mysql.

The local-infile would be system-wide, not only piwik-specific. In general, this opens up mysql. Plus, disabling apparmor is not an option for many users. Plus, why is writeable access proposed for mysqld (rw)?

If the LOAD DATA INFILE is still not working, try the following:
update to the latest PHP version or use the mysqli client (there are some known bugs with older PDO clients for mysql)
and/or switch the client to adapter=MYSQLI in the config/config.ini.php
and/or disable in your php configuration php.ini the options open_basedir and safe_mode. Restart webserver.

Switching to MYSQLI is not an option for everyone. Playing around with open_basedir and safe_mode does not have something to do with PDO/MYSQLI as the reader might assume when reading the text. Plus, open_basedir and safe_mode are important security features which should NOT be disabled just to fix some errors. Users should be taught about that. Plus: What does file read access in the context of mysql have to do with PHP's open_basedir/safe_mode settings? These settings will only affect how apache/PHP process behaves. MySQL is a separate process which will not respect these settings at all!

If you still cannot make LOAD DATA INFILE work, you can disable this feature completely. This is not recommended for medium to high traffic Piwik where this feature should be enabled. To disable LOAD DATA INFILE add to your config.ini.php file under [General] section: enable_load_data_infile=0

Yes, that's the best option for most users who would otherwise insecure their servers.

Documentation needs to be reviewed in terms of security (I just red the part I was writing about). Unexperienced users will insecure their systems otherwise.

And for the code: In general, I would suggest to not use LOAD DATA INFILE in the context of mysqld at all. For PostgreSQL there exists a set of PDO-functions allowing bulk inserts in the context of the PHP-client via file or STDIN. Check out https://bugs.php.net/bug.php?id=63656 . Eventually, there is also an option for MySQL, but I did not search for that right now. That's the way to go: No file access via the database process -> no hassle. And it's fast as hell on PostgreSQL, still allowing transactions by reusing the context of client's connection. I even found a way to reuse doctrine connections for that on PostgreSQL. If you are interested in that, read my comment on the mentioned php.net site (I feel sorry that you used MySQL...).

For users who don't use BULK INSERTs, please check out if a MySQL transaction is used around the whole dataset which is to be inserted. Plus, check out if the MySQL Multi Row syntax insert is used. Both measurements could massively improve performance.

If inserts are done via ORM (doctrine, etc.), please make sure that you use MySQL transactions (doctrine supports that) around the mass INSERTs. Within the transaction, you can flush multiple times using flush(array). Make sure you destroy objects between flushes, and make sure that only few entity objects are instantiated at any time. Having lots of objects instantiated heavily slows down doctrine performance.

@mattab commented on April 8th 2015 Owner

Hi @thomaszbz Thanks for the suggestion to improve the documentation about this feature.

since you seem knowledgeable about the security implications, maybe you would be able to paste here your suggested updated text for this FAQ? it would help us make this change faster. cheers

@fortinux commented on March 1st 2017

As @thomaszbz explain on https://issues.piwik.org/7519 and @mattab commented about improving the documentation these where the steps I did on my Piwik installation just in case someone of you want to update https://piwik.org/faq/troubleshooting/#faq_194:

How do I get LOAD DATA INFILE to work on my server?

Piwik processes huge amount of data and then stores this data in the database. For improved performance, Piwik tries to import a lot of data in the database at once using a Mysql feature called “LOAD DATA INFILE”. You can check whether your server supports this performance improvement in Administration > System Check menu. The system checks two commands: LOAD DATA INFILE and LOAD DATA LOCAL INFILE. It’s enough if either one of these work.

In order to do this, connect to your webserver with SSH. You'll need ROOT ACCESS OR ROOT PERMISSIONS.

ssh root<a class='mention' href='https://github.com/your_webserver'>@your_webserver</a>.com

First check that the sql user that Piwik is using has the permission to import the files:

mysql -u root -p 
GRANT FILE on your_db_name.* to piwik<a class='mention' href='https://github.com/localhost'>@localhost</a>;
exit

Check the “mysqld” process can access the file created in the tmp/assets directory in the piwik-installation: give the process mysqld read-access to files in path/to/piwik/tmp/assets/* and all parent folders.

path/to/piwik/tmp/# chmod -R 755 assets/

check that the request isn’t blocked by apparmor or any other security-software when accessing this folder. If you are using Ubuntu, you may have to disable apparmor which prevents mysql from accessing files in path/to/piwik/tmp/* Edit the file /etc/apparmor.d/usr.sbin.mysqld and add the following path in the file:

vim /etc/apparmor.d/usr.sbin.mysqld

    # Allow piwik access tmp files
      path/to/piwik/tmp/assets/* rw,

Restart apparmor:

sudo /etc/init.d/apparmor restart

in your Mysql configuration (my.cnf) set the following options: [mysqld] local-infile, secure-file-priv = "" and [mysql] local-infile

vim /etc/mysql/my.cnf

    [mysqld]
    local-infile=1
    secure-file-priv = ""
    [mysql]
    local-infile=1

Restart mysql:

sudo service mysql restart

Now you can have that nice "Huzzah! There are no problems with your Piwik setup. Give yourself a pat on the back." message on Piwik's System Check page.

@thomaszbz commented on March 1st 2017

@fortinux Nice catch. It's just that ordinary web space hosters won't let anyone change an apparmor profile. Now that the documentation already suggests to do exactly that, it should be documented that this step requires root access.

Powered by GitHub Issue Mirror