Redmine

Hi, some time ago I faced with similar trouble: I had to create read-only mirror of redmine server in other that main office.
The idea is to give ability for users in second office read their tasks on spare(backup) redmine instance when main redmine is unreachable due network or other problems.
Mysql replication from main to second office has been made, so spare redmine instance is always in sync.
Although it has been announced that spare redmine has different domain name and users have to use it only if main server is not working and users mustn't do any updates but I had to prevent users to make any changes on spare server and force users to wait until main redmine be up.
I faced with same error when I set mysql to read-only and after quick research I found that it is enough to create such permission for database's user:

Grants for rmuser@%     
GRANT USAGE ON *.* TO 'rmuser'@'%' IDENTIFIED BY PASSWORD '****'
GRANT SELECT ON `rm`.* TO 'rmuser'@'%'
GRANT UPDATE (last_login_on) ON `rm`.`users` TO 'rmuser'@'%'
GRANT DELETE ON `rm`.`tokens` TO 'rmuser'@'%'

Pavel,

Does your solution require replication between the main Redmine and the branch office?

The problem I have is that there is no network connection between our office and the other office (пиздец!). No replication possible.

But if I write a script that sets/unsets permissions on both databases, then I can make it work.

~ CyberOwl

Please do not pay attention to replication.
You need to prevent changes in db, right?
You can create two database.yml files with read-write and read-only users and grant permission for such users in db, then in 5PM you need cron job to stop redmine, set database.yml (cp readonly.yml ./config/database.yml) file with read-only user and start redmine. And vice versa at 5AM (cp readwrite.yml ./config/database.yml). Or you can play with "sed" to modify user field in database.yml file.
That's it.

Pavel,

I have taken your suggestions and am working on the changes needed to do this. I have some questions though.

Q1) From your "GRANT USAGE" statement, it looks like you are creating another user named 'rmuser' in the 'user' table.

Then you are Granting SELECT, UPDATE, and DELETE privileges to 'rmuser'

Is my interpretation correct?

Q2)When you issue this command:
GRANT SELECT ON `rm`.* TO 'rmuser'@'%'

The `rm` is Redmine's database, right?

3) I am a little confused about creating a separate read-only user (rmuser). Wouldn't the Redmine application have a problem with this since it connects and manipulates the MySQL database as "redmine" and not a "rmuser"?

Q1) From your "GRANT USAGE" statement, it looks like you are creating another user named 'rmuser' in the 'user' table.

If you meant in 'user' table of 'mysql' database then yes.

Then you are Granting SELECT, UPDATE, and DELETE privileges to 'rmuser'
Is my interpretation correct?

Yes.

Q2)When you issue this command:
GRANT SELECT ON `rm`.* TO 'rmuser'@'%'
The `rm` is Redmine's database, right?

Yes.

3) I am a little confused about creating a separate read-only user (rmuser). Wouldn't the Redmine application have a problem with this since it connects and manipulates the MySQL database as "redmine" and not a "rmuser"?

You need to grant two users for redmine database, one for full access and second for partial as described above. When you start redmine with full_access_user in database.yml then redmine works as expected. When you start redmine with partial_access_user in database.yml then redmine works without problem because of minimal set of permission that needed for him to start and accept users' logins but from users' point of view redmine works not as usual because only R action from CRUD is allowed, i.e. it's not possible to create|update|delete something.
нихт ферштейн?)

Pavel,

I tried your advice.

I did the following:

1) MySQL> create user 'redmine-readonly'@'localhost' identified by '<password>';

2) MySQL> grant usage on *.* to 'redmine-readonly'@'localhost' identified by password '<hashed password I got from doing a SELECT on table users>';

3) MySQL> grant select on `redmine`.* to 'redmine-readonly'@'localhost';

4) MySQL> grant update (last_login_on) ON `redmine`.`users` to 'redmine-readonly'@'localhost';

5) MySQL> grant delete on `redmine`.`tokens` to 'redmine-readonly'@'localhost';

6) Changed the database.yml file under $REDMINE_ROOT/redmine/config. I have two versions of the database.yml file. The readonly version of the database.yml file sets the username for production to 'redmine-readonly', not 'redmine';

7) Reboot redmine by touching the $REDMINE_ROOT/redmine/tmp/restart.txt file

For Steps 1 thru 5, I get a Query OK, and the 'grant select' command shows redmine-readonly has the permissions that you recommended.

However, the Redmine webserver now shows an Internal Error in my Web browser. It doesn't even show the home page. Did I leave something out?

redmine and mysql are on same host?
What is in production.log?

Pavel,

Yes, Redmine and MySQL are on the same host.

The production log shows this error:

Started GET "/redmine/" for 10.193.217.14 at 2016-04-06 16:46:14 -0700
Processing by WelcomeController#index as HTML
Completed 500 Internal Server Error in 2ms (ActiveRecord: 0.6ms)

ActiveRecord::StatementInvalid (Mysql2::Error: UPDATE command denied to user 'redmine'@'localhost' for table 'tokens': UPDATE `tokens` SET `tokens`.`updated_on` = '2016-04-06 16:46:14' WHERE `tokens`.`user_id` = 38 AND `tokens`.`value` = 'e24c1c94a2525e159a95fa6ee0ca6e6d6b0c920b' AND `tokens`.`action` = 'session'):
app/models/user.rb:414:in `verify_session_token'
app/controllers/application_controller.rb:77:in `session_expired?'
app/controllers/application_controller.rb:67:in `session_expiration'

Maybe I need to grant "update" permission to "redmine" for the redmine.tokens table. But will this cause data corruption problems because an UPDATE is a write operation?

I tried something else, another option. I created new roles that only have read-only permission using Redmine's web UI. So I have a "manager" role, and a "manager-readonly" role. I can get into Redmine's MySQL database using the Python MySQLdb module and change a member's role from one to the other, or vice versa. I can change the roles (using SQL UPDATE) relatively easily and I will test this way today.

6) Changed the database.yml file under $REDMINE_ROOT/redmine/config. I have two versions of the database.yml file. The readonly version of the database.yml file sets the username for production to 'redmine-readonly', not 'redmine';

ActiveRecord::StatementInvalid (Mysql2::Error: UPDATE command denied to user 'redmine'@'localhost' for table 'tokens': UPDATE `tokens` SET `tokens`.`updated_on` = '2016-04-06 16:46:14' WHERE `tokens`.`user_id` = 38 AND `tokens`.`value` = 'e24c1c94a2525e159a95fa6ee0ca6e6d6b0c920b' AND `tokens`.`action` = 'session'):

Strange that you set 'redmine-readonly' user but redmine starts with 'redmine' user.
Yes, grant Update too.

I'll take a look at this and investigate further and get back to you.

Pavel,

I got Redmine to be "read-only" by changing the user in the database.yml file from "redmine" to "redmine-readonly".

The redmine-readonly user only has some privileges. I had to give the user "UPDATE" and "DELETE" privileges on the redmine.tokens table. I think this table keeps track of the tokens for the user.

After doing this, Redmine will not allow anyone to add/edit/delete anything. If they do try to add/edit/delete anything, the web browser shows an Internal Error (500). I might have to do a redirect to show something else but for now, this works.

Thanks for your help Pavel. спасибо!