A MySQL script produces output, which as a standard is stored in /var/lib/mysql-files/. For post-processing I need to move this file to a different location and because this file is created daily I have a script to do so, containing the line:
sudo mv /var/lib/mysql-files/values.out .
However the user that runs the script is not root, so the script fails at this line when added as a cron job. How would I provide the script with authentication in this case?
The easy answer is to add that user to the sudoers file but that means they could use sudo at other times.
Otherwise, omitting the 'sudo' action and just using 'mv', they need write access on the file (because they are effectively going to delete it) and the parent directory at the source, and write access on the target directory. You should be able to organize that using group membership(s) and setting appropriate access rights on the source file (which you could do when it's originally created).
The cron job command will fail immediately if a user requested sudo but is not in the sudoers file.
1 Like
@technossomy , check out select ... into
there it also mentions about setting umask ....
(when i get to a host I can give this a try)
@technossomy , further to teammate @hicksd8 sudo suggestion, be aware than sudo can fine tune which commands a given user can execute - so that may be a workable solution for your challenge.
2 Likes
I tried that too indeed, but then I am still unable to move the file to where it is needed, and hence the deleted post. I have since concluded that a database-native solution is not a good place to tackle it.
I am also looking at changing the options for sudo as per your suggestions, so something like:
$ whoami
nicosia
$ sudo -u nicosia mv values.out /home/nicosia/Documents
mv: cannot move 'values.out' to '/home/nicosia/Documents/values.out': Operation not permitted
$ sudo -u nicosia cp values.out /home/nicosia/Documents
cp: cannot open 'values.out' for reading: Permission denied
So I am still not sure how settings would have to be changed to move this file, or - failing that - copy the contents.
Why not just change the permissions of the directory so your user(s) can write to the directory?
in mysql , you can set the umask for the session - see the link initially posted.
"""
Any file created by INTO OUTFILE or INTO DUMPFILE is owned by the operating system user under whose account mysqld runs. (You should never run mysqld as root for this and other reasons.) As of MySQL 8.0.17, the umask for file creation is 0640; you must have sufficient access privileges to manipulate the file contents. Prior to MySQL 8.0.17, the umask is 0666 and the file is writable by all users on the server host.
If the secure_file_priv system variable is set to a nonempty directory name, the file to be written must be located in that directory.
"""
NB: the above version dependant 8.0.17 or later !
have you tried changing permissions
chmod ugo+rw filename # let everyone read/write
This also requires root permissions, so it would have to be changed to:
sudo chmod ugo+rw values.out
Unless I am mistaken?
By the way, changing the permissions of the MySQL user is proving to be a challenge also.
you could always pipe from stdout to your preferred destination ....
mysql commandline-stuff > /home/nicosia/Documents/values.out
example:
mysql -u munke -D munke <<<'select * from desserts;' > /tmp/desserts.txt
ll /tmp/desserts.txt
-rw-rw-r-- 1 munke munke 196 Feb 27 13:22 /tmp/desserts.txt
Need to have a look at this more carefully, because it would still require the password in clear text to work:
mysql -u munke -D munke -pObfu$cated <<< 'select * from desserts;' > /tmp/desserts.txt
Last stab at this
cat .my.cnf
[client]
user=username
password=password
# secure access
chmod 600 ~/.my.cnf
mysql -u$USER -Dmydb < commands.sql > results.out
Check documentation wrt this(I'm on 8.0.xx)
Let's start with a quick review of file & directory permisions.
The cronjob writes to /var/lib/mysql-files/values.out
Run this command:
ls -ld / /var /var/lib /var/lib/mysql-files /var/lib/mysql-files/values.out /home/nicosia /home/nicosia/Documents
and post the output. Here is a sample output (Sizes and dates have been romoved for clarity):
drwxr-xr-x root root /
drwxr-xr-x root root /var
drwxr-xr-x root root /var/lib
drwxr-x--- mysql mysql /var/lib/mysql-files
-rw-r----- nucosia users /var/lib/mysql-files/values.out
drwxr-x--- nicosia users /home/nicosia
drwxrwxr-x nicosia users /home/nicosia/Documents
Directory permissions are inherited from the parent directory. The Flow down value for directories is the execute flag.
Notice the permissions for the mysql-files directory in the above example. Anyone who is not a member of the mysql group will have no permissions in this directory. Even though the user nicosia is the owner of this file, there will be no to access this file. If nicosia is is also a member of the mysql group, (s)he can read the file (i.e. copy) but not delete (move) the file because there is no write access to the directory.
The exception, of course, is the root user. Root will always have access (which is probably how the file has an ownership ofd a user with no write permission [i.e. chown]).
An exception to the "root always has access rule" would be for remote mounts (such as NFS). /home/nicosia is probably a remote mount. Dependin on how it is mounted, root may not have read or write access to this directory.
Notice how /home/nicosia has permissions of 750? The user nicosia has full access to this directory. Members of the 'users' group have read access only. All others have no access at all.
The directory /home/nicosia/Documents has permissions of 775, which implies that non-members of the users group will have read access to this directory.However, since the parent has no permissions for 'other', no-one else will be able to read in this directory.
Of note: (I'm on Red Hat, BTW)
using sudo in a cron job is problematic and not recommended. I.e. don't do it!
If your cron is running as root, use the 'runuser' command. The main difference is that it does not ask for a password.
runuser [options] -u nicosia _command_ _arguments_
Another option is the /etc/crontab file or /etc/cron.*.
These are executed by the crom daemon (root). unlike the root crontab, the 6th column is the username to run as:
*/10 * * * * nicosia mv /var/lib/mysql-files/values.out /home/nicosia/Documents
will run the move command every 10 minutes.
BTW: This is a better option...
*/10 * * * * nicosia [ -f /var/lib/mysql-files/values.out ] && mv /var/lib/mysql-files/values.out /home/nicosia/Documents
This way, if the file does not exist, it will not throw an error. (see man test for more information).
Others have mentioned umask and/or chmod. You can use these before/during/after the task.
Ex 1:
omask=$( umask )
umask=0
touch /var/lib/mysql-files/values.out
umask $omask
[command] >> /var/lib/mysql-files/values.out
Ex 2:
touch /var/lib/mysql-files/values.out
chmod a+rw /var/lib/mysql-files/values.out
[command] >> /var/lib/mysql-files/values.out
Both will do what you want for the file (YMMV), it does not get past the directory permissions.
If you still have problems with permisions, and you absolutely must have UserA rad the file and UserB write the file, use rsync.
[UserA@MyComputer ~] rsync [options] /var/lib/mysql-files/values.out UserB@MyComputer:/home/UserB/Documents/values.out && rm -f /var/lib/mysql-files/values.out
In this scenario, UserA must be able to
ssh UserB@MyComputer
without asking for a password, server keys, etc.
Good Luck!
1 Like
@technossomy can you create a cron job that runs as the MySQL user that will move the file somewhere that your other user(s) can access it? E.g. push it from the source user instead of trying to pull it from a destination user.
@ScottieH sort of touched on this with the 6th column being the user the cron job runs as.
A correction:
need write access to the file's directory
(The complete story: if mv is called without -f and sees an interactive terminal then it asks for confirmation if the file is write-protected. Otherwise it silently replaces the file.)
'cp' would need write access to the file. (Not to the directory.)
1 Like
Don't you mean 'cp' would need read access to the file (only)? (And write on the target)
1 Like
Ok, this is at least one solution that works. Have to look at the others now.
1 Like
Have tried this already, but there is always the issue of the file and/or its contents not being accessible without providing the password somewhere. The solution that comes close to your suggestion is the one by @munkeHoller that I responded to just now, ie store the password in a secure conf-file.
there is always the issue of the file and/or its contents not being accessible without providing the password somewhere.
That doesn't make sense to me.
The cron job running as the MySQL user should be able to read the file no problem. It should also be able to write the file in a location that it has write permission to.
N.B. None of what I'm suggesting uses sudo or even crosses user security boundaries, so passwords shouldn't be an issue.
What error(s) do you get if a cron job running as the MySQL user tries to cp or mv a file that it owns to another location that it has write access to? (No sudo involved.)
Remember, using sudo in cron jobs is problematic and requires special configuration.
This is actually untrue: the MySQL server runs on the same machine as where the post-processing takes place.
$ ls -ld / /var /var/lib /var/lib/mysql-files /var/lib/mysql-files/values.out /home/nicosia /home/nicosia/Documents
drwxr-xr-x 20 root root /
drwxr-x--- 21 nicosia nicosia /home/nicosia
drwxr-xr-x 4 nicosia nicosia /home/nicosia/Documents
drwxr-xr-x 15 root root /var
drwxr-xr-x 77 root root /var/lib
drwx------ 2 mysql mysql /var/lib/mysql-files
-rw-r----- 1 mysql mysql /var/lib/mysql-files/values.out
The last line shows up depending on whether sudo is used or not. Otherwise there is a line stating ls: cannot access '/var/lib/mysql-files/values.out': Permission denied.
Agreed, and a search for an acceptable alternative motivated the question.
As regards runuser, after trying various combinations, there has been no statement that would execute the mv command. The core issue remains that a root user creates content and that a non-root user consumes this content, so it is still self-inflicted, but I'll experiment with a non-root database user.