Scott's picture

Hello, I recently used Turnkey to upgrade our wiki system stack.  Now I am working on updating our Redmine system (stack from those "B" guys).  I am running into a privileges issue on the database migration.

Based on the database users in Turnkey Redmine(mariadb.sys,mysql,redmine,root), I believe the correct user to grant database permissions is redmine.

General procedure:

MariaDB>drop database redmine_production;
MariaDB>create database redmine_production;
MariaDB>grant all privileges on redmine_production.* to 'redmine'@'localhost' identified by 'password';
>mysql -u root -p redmine_production < backup.sql

I have also tried 'authentication_string' instead of 'password' as it appears the database schema uses this field.  But in either case, when I try to migrate the database (bundle exec rake db:migrate RAILS_ENV=production), I get access denied for user 'redmine'@'localhost'

rake aborted! 
ActiveRecord::ConnectionNotEstablished: Access denied for user 'redmine'@'localhost' (using password: YES)
Jeremy Davis's picture

Hi Scott, from a quick glance at what you've done, on face value it seems ok. Also you're probably already aware, but just in case, whilst it's often referred to as "MySQL" and includes the 'mysql' CLI client tool, the "MySQL" DB engine in TurnKey is actually MariaDB. Even if you're moving from actual MySQL, it should load ok. Although please be aware that it may not go back the other way if you want to return to actual MySQL.

Also as a bit of background, by default on TurnKey (and upstream MariaDB - also supported by MySQL, although I don't think it's default there?) the root user is authenticated via unix socket. That is generally considered best practice/best security these days - although as with most technical decisions, there are pros and cons.

When running as the root Linux user, by default you don't need to give a username (if no username given, it defaults to the Linux user it's running as - in this case should be root). A password is also not required because the unix socket authentication is only available when running as that Linux user on localhost (i.e. does not support access via TCP).

If you do wish to stick with password authentication (which is a legitimate option if that works best for you) please be aware that you'll most likely (sorry I don't recall 100%) need to set up a system user (with full root-like privileges) specifically for controlling the MariaDB service - like it used to be before socket auth was an option. Otherwise the MariaDB service will have issues starting/restarting (and probably even stopping). The biggest downside of using a system user for maintenance (inc service start/stop) is that the password needs to be stored in plain text (in /etc). So be sure to make the file only readable by root. I'm sorry I can't offer any specific. I'm not sure, but that's perhaps the root of your issue?

It's also worth being aware (if you're not already) that even when using a password, the root user can override MariaDB/MySQL authentication anyway - by manually starting it with the '--skip-grant-tables' option. So using unix socket for root user authentication is not really reducing security. FWIW we still use passwords for app user DBs (i.e. as per what you've done for the redmine DB user).

So this should work (essentially the command you used):

mysql redmine_production 

Or more explicitly:

mysql --database=redmine_production 

You can then double check that it's all working as it should by trying to view the DB tables as the redmine user:

mysql -u redmine -ppassword redmine_production -b "SHOW TABLES;"

As you're clearly aware, to input the password interactively just use '-p' instead of '-ppassword'. Although when troubleshooting this sort of thing IMO it's better to actually put it in the command, so then you can ensure no typos (if you're concerned about it being in the bash history, that can be edited after the fact by editing ~/.bash_history (i.e. /root/.bash_history for the root user) and removing the line from the history file.

If that works, but you still have issues with Redmine, please double check that the password and username are correct in the Redmine conf file.

Scott's picture

I found I could see what permissions were granted by using:

show grants for redmine@localhost;

After I dropped the original database and created the new one, the permissions were still assigned and unchanged.  So I just skipped the grant all privileges... command that was causing me trouble.  Database migrated and seems to be working fine.

Jeremy Davis's picture

Great news Scott! Thanks for posting back with your solution. Hopefully that will save someone else some hassles in the future.

Add new comment