1322754 - reevaluate balrog database users

Status: RESOLVED WONTFIX

(Cloud Services :: Operations: Miscellaneous, task)

Description

[[e-mail] (:bhearsum)]

Balrog's history is meant to be an audit trail and historical record, and as such we treat it as write-once data. There's no way for rows in history tables to be UPDATEd or DELETEd that I'm aware of, but it would be nice to enforce this at the database-level, and simply not allow the admin app to do it at all. This would protect us against bugs or attacks that try to cause history to be removed.

Ben, we talked about this a bit and you said you'd need to create separate credentials for the cleanup cronjob, because it does need to have the ability to DELETE history rows in releases_history.

Comment 2

[Benson Wong [:mostlygeek]]

This bug used to be revoke update/delete on history tables for the balrog admin user. Let's expand the scope a bit since the list of users is growing. 

I think we need 3 users: 

1. balrog_ro
  - GRANT SELECT ON balrogdb.* to balrog_ro@'%' IDENTIFIED BY 'password';
  - has read access to everything

2. balrog_history_cleanup
  -  CREATE USER balrog_history_cleanup IDENTIFIED BY 'password';
     GRANT select,delete ON balrogdb.permissions_history TO balrog_history_cleanup@'%';
     GRANT select,delete ON balrogdb.permissions_req_signoffs_scheduled_changes_conditions_history 
        TO balrog_history_cleanup@'%';
     ... repeat for each history table
  - select,delete on *ONLY* history tables

3. balrog_admin
  -  CREATE USER balrog_admin IDENTIFIED BY 'password';
     GRANT select,insert,update,delete ON balrogdb.permissions TO balrog_admin@'%';
     GRANT select,insert ON balrogdb.permissions_history TO balrog_admin@'%';
     ... repeat for each table/history pair 
  - select, update, insert, delete on all non history tables
  - select on all history tables

AFAIK mysql doesn't allow a GRANT *.*, and then a REVOKE <specific permissions>. So we have to be very explicit on what we grant / revoke access to. This would mean when we do schema changes we will also need to do grant changes.

Comment 3

[[e-mail] (:bhearsum)]

Just to spell it out fully:

(In reply to Benson Wong [:mostlygeek] from comment #2)
> I think we need 3 users: 
> 
> 1. balrog_ro
>   - GRANT SELECT ON balrogdb.* to balrog_ro@'%' IDENTIFIED BY 'password';
>   - has read access to everything

This user is for the public webheads (ie: the ones powering aus5.mozilla.org).

> 2. balrog_history_cleanup
>   -  CREATE USER balrog_history_cleanup IDENTIFIED BY 'password';
>      GRANT select,delete ON balrogdb.permissions_history TO
> balrog_history_cleanup@'%';
>      GRANT select,delete ON
> balrogdb.permissions_req_signoffs_scheduled_changes_conditions_history 
>         TO balrog_history_cleanup@'%';
>      ... repeat for each history table
>   - select,delete on *ONLY* history tables

This is not used by any wsgi app - just by cron.

> 3. balrog_admin
>   -  CREATE USER balrog_admin IDENTIFIED BY 'password';
>      GRANT select,insert,update,delete ON balrogdb.permissions TO
> balrog_admin@'%';
>      GRANT select,insert ON balrogdb.permissions_history TO balrog_admin@'%';
>      ... repeat for each table/history pair 
>   - select, update, insert, delete on all non history tables
>   - select on all history tables

And this one is used by aus4-admin

> AFAIK mysql doesn't allow a GRANT *.*, and then a REVOKE <specific
> permissions>. So we have to be very explicit on what we grant / revoke
> access to. This would mean when we do schema changes we will also need to do
> grant changes.

That is...painful. For you, mostly. If you're willing to go ahead with it, I can add some notes to our instructions for when we do schema upgrades to make sure that we remind you to update the grants. But if you think it's too much of a pain, I can live with that.

Comment 4

[Benson Wong [:mostlygeek]]

We decided this is a WONT FIX after our ops/dev meeting today.