Skip to content

Show a nice error message when connecting to the database with a read-only user

Marek Szuba requested to merge experimental/ro_user into master

Created by: muffato

beekeeper.pl, like other scripts needs a read+write connection to the database. As I often (by mistake) use ensro, I'm a bit tired of seeing this stack trace:

mattxps:~ $ beekeeper.pl -url 'mysql://ensro@localhost/matthieu_test_db_master'
Pipeline name: long_mult
Default meadow: LOCAL/mattxps

DBD::mysql::st execute failed: INSERT command denied to user 'ensro'@'localhost' for table 'beekeeper' at /home/matthieu/workspace/src/hive/master/modules/Bio/EnsEMBL/Hive/DBSQL/StatementHandle.pm line 119.
DBD::mysql::st execute failed: INSERT command denied to user 'ensro'@'localhost' for table 'beekeeper' at /home/matthieu/workspace/src/hive/master/modules/Bio/EnsEMBL/Hive/DBSQL/StatementHandle.pm line 119.
 at /home/matthieu/workspace/src/hive/master/modules/Bio/EnsEMBL/Hive/DBSQL/StatementHandle.pm line 145.
        Bio::EnsEMBL::Hive::DBSQL::StatementHandle::__ANON__('Bio::EnsEMBL::Hive::DBSQL::StatementHandle=HASH(0x3a61b18)', 0, 'ANALYSIS_FAILURE', 'mattxps', 'LOCAL/mattxps', 'matthieu', '-url mysql://ensro@localhost/matthieu_test_db_master', 11026, 1, ...) called at /home/matthieu/workspace/src/hive/master/modules/Bio/EnsEMBL/Hive/DBSQL/BaseAdaptor.pm line 528
        Bio::EnsEMBL::Hive::DBSQL::BaseAdaptor::class_specific_execute('Bio::EnsEMBL::Hive::DBSQL::BeekeeperAdaptor=HASH(0x3a4a680)', 'Bio::EnsEMBL::Hive::Beekeeper=HASH(0x3a4a6e0)', 'Bio::EnsEMBL::Hive::DBSQL::StatementHandle=HASH(0x3a61b18)', 'ARRAY(0x3a4e8c0)') called at /home/matthieu/workspace/src/hive/master/modules/Bio/EnsEMBL/Hive/DBSQL/BaseAdaptor.pm line 578
        Bio::EnsEMBL::Hive::DBSQL::BaseAdaptor::store('Bio::EnsEMBL::Hive::DBSQL::BeekeeperAdaptor=HASH(0x3a4a680)', 'Bio::EnsEMBL::Hive::Beekeeper=HASH(0x3a4a6e0)') called at /home/matthieu/workspace/src/hive/master/scripts/beekeeper.pl line 471
        main::register_beekeeper('Bio::EnsEMBL::Hive::Valley=HASH(0x35907a8)', 'HASH(0x23bfcb8)') called at /home/matthieu/workspace/src/hive/master/scripts/beekeeper.pl line 285
        main::main() called at /home/matthieu/workspace/src/hive/master/scripts/beekeeper.pl line 32

And I though I could make the error message a bit nicer.

In this commit I introduce has_write_access in DBConnection, which returns a boolean, and requires_write_access, which dies or does nothing. Each script that needs to write to the database now calls requires_write_access. There is one exception: beekeeper, as it still can print the status of the pipeline, a warning message about the database user and exit with the error code 1. Do you think beekeeper should just die like the other scripts ? Here is how it looks like with my changes

mattxps:~ $ runWorker.pl -url 'mysql://ensro@localhost/matthieu_test_db_master'
It appears that ensro doesn't have INSERT/UPDATE/DELETE privileges on this database (matthieu_test_db_master). Please check the credentials
mattxps:~ $ beekeeper.pl -url 'mysql://ensro@localhost/matthieu_test_db_master' -debug 9
Pipeline name: long_mult

**********************************************************************
* It appears that ensro doesn't have INSERT/UPDATE/DELETE privileges
* on this database (matthieu_test_db_master). Please check the credentials
*
**********************************************************************

Default meadow: LOCAL/mattxps

take_b_apart (  1)       READY, jobs(                       2r ), avg:  0.0 ms , workers(Running:0, Est.Required:1)   h.cap:-  a.cap:1  (sync'd 2789 sec ago)
part_multiply(  2)       EMPTY, jobs(                       =0 ), avg:  0.0 ms , workers(Running:0, Est.Required:0)   h.cap:-  a.cap:1  (sync'd 0 sec ago)
add_together (  3)       EMPTY, jobs(                       =0 ), avg:  0.0 ms , workers(Running:0, Est.Required:0)   h.cap:-  a.cap:1  (sync'd 0 sec ago)

total over 3 analyses :   0.00% complete (< 0.00 CPU_hrs) (2 to_do + 0 done + 0 failed + 0 excluded = 2 total)

===== Stats of active Roles as recorded in the pipeline database: ======
         ======= TOTAL ======= : 0 active Roles

**********************************************************************
* beekeeper.pl is running in read-only mode, i.e. it only
* prints the current status of the pipeline.
*
**********************************************************************

My other question is about the implementation of has_write_access. I couldn't find a clean way of checking the permissions of the current user. It looks like in theory, the permissions of a given user could vary per host it's connecting from, per database and per table ? I didn't feel like parsing the output of SHOW GRANTS, e.g.

| GRANT SELECT, RELOAD, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, SHOW VIEW ON *.* TO 'ensro'@'%' |                                                                                                                                                                 
| GRANT SELECT ON `%\_%`.* TO 'ensro'@'%'                                                                     |

and I reckon my implementation is simplistic. Is there a real risk that the user has no write permissions in general, but has write permissions enabled at the database level ? I think it's fine not to capture all the cases where there is no write permissions (as DBI would just fail anyway when trying to write stuff) but it'd be bad preventing accessing the database if there is no real reason. Also, I haven't searched how to check those in PostreSQL

Merge request reports