Making Redmine read-only for part of the day

Added by Cyber Owl over 3 years ago

Hello.

I work with a company that has multiple remote sites.

We use Redmine in our office. Our office is in a certain network zone. Everyone in our office can access Redmine because we are all on the same network (NW1).

We have a remote office in China. They are on a different network (NW2). They do not have network access to our network (NW1). And we do not have access to their network zone (NW2).

Now, our company IT will not allow us to have access to each other's network zone so a network solution to this is out of the question. Any way to circumvent the network "partition" via networking configuration (using route commands, etc.) is also out of the question.

The only way we can "transmit" between each network zone is via our repository (SVN). If we check in our DB to SVN, the people in the China office can get our DB via SVN, and vice-versa.

Both our offices want to use Redmine for project and bug tracking though.

We handled this by having two Redmines - one Redmine in our NW1 zone and another Redmine in China's NW2 zone.

Using Cron jobs and SVN, we make copies of each other's Redmine MySQL database at certain times and transmit them via SVN.

For example, at 5PM our time, we commit to SVN. Their Redmine imports the database. Then at 5AM, they commit to SVN, we check out the database from SVN, and our Redmine imports the database.

I know, this is convoluted but we are stuck with very peculiar networking security rules here and this is a workaround.

There is one problem - there are some gaps in this implementation (like any shared resource problems).

What if someone at my office makes changes to Redmine after 5PM? Then at 5AM, we copy over the Redmine DB from the China office and his changes are lost because we only backup/transmit at 5PM.

To fix this, we decided to make our Redmine "read-only" from 5PM to 5AM. However, when I set the Redmine MySQL database "read-only", using MySQL statements, this causes the Redmine webserver to display an error. So I can't use the MySQL statement.

Is there a way to make Redmine "read-only" but not by making the MySQL database "read-only"?

Replies (11)

RE: Making Redmine read-only for part of the day - Added by Pavel Potcheptsov over 3 years ago

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'@'%'

Hope this help.

RE: Making Redmine read-only for part of the day - Added by Cyber Owl over 3 years ago

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

RE: Making Redmine read-only for part of the day - Added by Pavel Potcheptsov over 3 years ago

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.

RE: Making Redmine read-only for part of the day - Added by Cyber Owl over 3 years ago

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"?

RE: Making Redmine read-only for part of the day - Added by Pavel Potcheptsov over 3 years ago

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.
нихт ферштейн?)

RE: Making Redmine read-only for part of the day - Added by Cyber Owl over 3 years ago

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?

RE: Making Redmine read-only for part of the day - Added by Pavel Potcheptsov over 3 years ago

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

RE: Making Redmine read-only for part of the day - Added by Cyber Owl over 3 years ago

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.

RE: Making Redmine read-only for part of the day - Added by Pavel Potcheptsov over 3 years ago

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.

RE: Making Redmine read-only for part of the day - Added by Cyber Owl over 3 years ago

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

RE: Making Redmine read-only for part of the day - Added by Cyber Owl over 3 years ago

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. спасибо!

(1-11/11)