0

I have a bash script that should remove a file from /tmp. Problem: the file is created my user mysql during SELECT INTO OUTFILE export. Thus the script-running user does not own the file.

Still, how can I remove that file though, without having to add the script into sudo crontab?

myuser-crontab:

/opt/my_routine.sh

my_routine.sh:

!#/bin/bash
rm -f "/tmp/mysql_export.csv" #FAILS
mysql --user=user --password=rootpw --database=mydb < /opt/export.sql

export.sql:

SELECT some, fields INTO OUTFILE '/tmp/mysql_export.csv' FROM mytable FIELDS TERMINATED BY ';' ESCAPED BY '' LINES TERMINATED BY '\n';
membersound
  • 154

3 Answers3

3

Problems

Redirecting stdout, so the file is created by the right user (like in this other answer) is a decent idea, but I assume you need TERMINATED BY, ESCAPED BY and LINES TERMINATED BY. These need INTO OUTFILE. A straightforward solution that redirects stdout just omits INTO OUTFILE, so it cannot use TERMINATED BY etc.

An attempt that uses INTO OUTFILE to write to stdout doesn't work:

SELECT some, fields INTO OUTFILE '/proc/self/fd/1' FROM mytable FIELDS TERMINATED BY ';' ESCAPED BY '' LINES TERMINATED BY '\n';

It doesn't work because the file must not exist before; MySQL must create it by itself. There are valid security reasons for this (example). The special file /proc/self/fd/1 is provided by the kernel so MySQL rejects it.

The path in question is /tmp/mysql_export.csv. When you try to delete mysql_export.csv from /tmp/, the permissions you have for the directory matter. This is how /tmp/ usually looks like:

$ ls -ld /tmp/
drwxrwxrwt 1 root root 1772 Nov  1 19:39 /tmp/
$

The lowercase t in drwxrwxrwt denotes execute permission for "others" and the sticky bit set. About the sticky bit:

The most common use of the sticky bit is on directories residing within filesystems for Unix-like operating systems. When a directory's sticky bit is set, the filesystem treats the files in such directories in a special way so only the file's owner, the directory's owner, or root can rename or delete the file. Without the sticky bit set, any user with write and execute permissions for the directory can rename or delete contained files, regardless of the file's owner.

(source: Wikipedia)

You cannot delete the file because you're neither root nor the owner of the file.

Solution

The above citation just gave us a clue. If you're neither the owner of the file nor root then you need to be:

  • (with the sticky bit on the directory) the owner of the directory, or
  • (without the sticky bit) any user with write and execute permissions for the directory.

So create a directory somewhere, allow the other user to create files in it (chmod, setfacl) and adjust your procedure to create mysql_export.csv there. After the file appears you will be able to delete it even if it belongs to another user. Use rm -f. Without -f you will probably be prompted because the file does not belong to you.

Notes

  • The solution does not go beyond what regular users can do. You don't need sudo, you don't need to set any additional group in the system.
  • Allowing other user(s) to create files in your directory may not end well. You will be able to delete "foreign" non-directories and empty subdirectories. But to delete a non-empty subdirectory you need to delete everything in it first. In general it's not up to you if you can delete files from somebody else's directory, even if it's a subdirectory of your directory. In your case this is not a concern if the other user is just a formal user under your control.
Kamil Maciorowski
  • 81,893
1

You could dump the result from mysql, instead of doing INTO FILE:

mysql --user=user --password=rootpw --database=mydb < /opt/export.sql > /tmp/mysql_export.csv

and then your export.sql would be just:

SELECT some, fields FROM mytable;

Alternatively, you could use sudo rm ....

Eduardo Trápani
  • 1,043
0

There are lots of ways depending on what exactly you have to work with.

You can play with umasks or chmod in the creating script to make the file group/world writeable (and ensure both users are in the group).

You could run both scripts as the same user. Are you aware that crown allied specifying of the user, or using user specific crown jobs run as that user?

(Not practical, mental masturbation) you could use a fuse or some overlay file (or file block device with mount perms) /abuse permissions from a primitive filesystem like vfat which has no concept of permissions so you can force them as you want in mounting.

davidgo
  • 73,366