Easy way to install BIGSdb?

[MostafaYA]

Hi, I am sorry if my question looks silly, but I am really stuck with the BIGSdb installation.
Is that possible to have BIGSdb already installed on a virtual machine that can be directly downloaded and used.

[kjolley]

This is something I have considered but not had any time to think seriously about. I don't have any experience of setting up virtual machines/containers and one of my concerns is that maintaining and updating such an image would create more work. Nevertheless, it is something I would like to investigate, but as I can't commit to the near term, if there is anything specific about the installation that is causing you problems, please don't hesitate to contact me directly and I'll see if I can help.

[MostafaYA]

Thanks a lot for your answer. I will go again through the installation instructions and hope it works this time

[joheli]

@kjolley: Dear Keith, I am currently trying to "dockerize" BIGSdb according to 'BIGSdb Documentation', see https://github.com/berlin-mlst/docker-bigsdb (the image is hosted at https://hub.docker.com/r/berlinmlst/bigsdb/). It is far from being ready, as I have a few questions regarding installation before I can proceed. Could you help me with this?

• Under 3.1 you suggest that bigsdb.pl and bigscurate.pl be placed in a subdirectory of cgi-bin, whereas under 3.7 it is stated that bigsdb.pl can directly be accessed at http://website/cgi-bin/bigsdb.pl. Which is correct?

• Where do I best the scripts in subdirectory "scripts"? E.g. you suggest under heading 3.7. on page 14 of the current documentation that 'sync_user_dbase_users.pl' be moved to /usr/local/bin. Does this apply to all scripts in subdirectory "scripts"?

• Could you point me to a template of menu_header.html mentioned under heading 3.4?

Thank you so much!

Johannes

[kjolley]

Hi Johannes,

It doesn't matter where you install them - if you install them in a subdirectory of cgi-bin, then you include that subdirectory in the URL. It will work in any location within cgi-bin or a subdirectory of it.

I put all my local scripts in /usr/local/bin, but again it doesn't matter really as long as they're in your path (or you fully specifiy the path when calling the script).

Default menu_header.html can be found at https://github.com/kjolley/BIGSdb/blob/develop/headers/menu_header.html. For PubMLST, I use the attached (I've had to add a .txt suffix as GitHub does not support attaching .html files)
header.html.txt
.

You'll notice in this example that I open a <div> for the main content. This is then closed in footer.html.

[joheli]

Thank you, Keith!

I am struggling a bit with add_user.pl; I had to (temporarily) abandon the two-container solution as I don't find a way to specify a database host different from localhost.

Also, I have not been successful in setting the password of the admin user:

If I type add_user.pl -a -d berlin_mlst_users -n jelias -p WHATEVER and then try to login at http://localhost/cgi-bin/bigsdb.pl, I get the error message "Invalid username or password entered. Please try again." There is no specific message in /var/log/bigsdb.log.

Is there maybe another way of specifying the password?

Thank you and kind regards from Berlin,

Johannes

[kjolley]

Are you using a site-wide user database (http://bigsdb.readthedocs.io/en/latest/administration.html#setting-a-site-wide-users-database) or storing user information in the individual databases? If storing it within individual databases (which is the usual way if you're only running a few databases), then the value you need for the -d option is this database name.

You also need to make sure that you have added the user details to the users table of the database that you're trying to connect to.

[joheli]

Thanks, Keith.

Yes, I am using the site-wide solution with a site-wide user database called berlin_mlst_users.

Using psql the demo admin user dberlin does indeed appear in table users:

berlin_mlst_users=# select * from users;
 user_name | surname | first_name |         email         |    affiliation    | date_entered | datestamp  |  status   | validate_start 
-----------+---------+------------+-----------------------+-------------------+--------------+------------+-----------+----------------
 dberlin   | Berlin  | Denzel     | [email protected] | Berlin MLST Group | 2018-07-26   | 2018-07-26 | validated |               
(1 row)

None of the test databases (test_isolates and test_seqdef) contains the admin user:

test_isolates=# select * from users;
 id | user_name  | surname | first_name | email | affiliation | status  | submission_emails | account_request_emails | user_db | date_entered | datestamp  | curator 
----+------------+---------+------------+-------+-------------+---------+-------------------+------------------------+---------+--------------+------------+---------
  0 | setup      |         |            |       |             | user    | f                 | f                      |         | 2018-07-26   | 2018-07-26 |       0
 -1 | autotagger | Tagger  | Auto       |       |             | curator | f                 | f                      |         | 2018-07-26   | 2018-07-26 |       0
(2 rows)

test_isolates=# \c test_seqdef 
You are now connected to database "test_seqdef" as user "postgres".
test_seqdef=# select * from users;
 id |  user_name  | surname | first_name | email | affiliation | status  | submission_emails | account_request_emails | user_db | date_entered | datestamp  | curator 
----+-------------+---------+------------+-------+-------------+---------+-------------------+------------------------+---------+--------------+------------+---------
  0 | setup       |         |            |       |             | user    | f                 | f                      |         | 2018-07-26   | 2018-07-26 |       0
 -1 | autodefiner | Definer | Auto       |       |             | curator | f                 | f                      |         | 2018-07-26   | 2018-07-26 |       0
(2 rows)

From within the container, resetting the password manually returns no error:

root@9f0f77960422:/tmp# add_user.pl -d berlin_mlst_users -n dberlin -p NEWPASSWORD
root@9f0f77960422:/tmp#  

... but the login remains unsuccessful.

Questions:

• do I have to enter the admin user in the test databases as well?
• am I missing a dependency that add_user.pl needs but does not complain about?

The only way to reproduce the error would be to start the container as described in https://github.com/berlin-mlst/docker-bigsdb and then enter the container with docker exec -it bigsdb bash.

[kjolley]

do I have to enter the admin user in the test databases as well?

Yes. Every database for which you want that user to be able to log in to must contain an entry in the users table for that user. You will need to populate the user_dbases table first with the details of the user database and use this id number for user_dbase field in the users table.

When setting up databases, it is generally easier to create the first user as a local user, and then use the web interface to populate user_dbases and to import users for the site-wide database. When adding site-wide users to the database user tables manually, the surname, first_name, email and affiliation should be blank as these are retrieved from the site-wide user database.

am I missing a dependency that add_user.pl needs but does not complain about?

No.

[joheli]

Thank you.

I am afraid I still do not seem to be able to make it work.

I have tried two approaches:

1. Referring to existing user dberlin in database berlin_mlst_users by adding entries to tables user_dbases and users test database test_seqdef
2. Adding a fresh local user to database test_isolates

Approach 1

I first entered the details of the site-wide user database into table user_dbases of test database test_seqdef:

test_seqdef=# insert into user_dbases(id, name, dbase_name, dbase_host, dbase_port, dbase_user, dbase_password, list_order, auto_registration, curator, datestamp) values (1, 'main', 'berlin_mlst_users', 'localhost', '5432', 'bigsdb', 'bigsdb', 1, TRUE, 0, now());
INSERT 0 1
test_seqdef=# select * from user_dbases ;
 id | name |    dbase_name     | dbase_host | dbase_port | dbase_user | dbase_password | list_order | auto_registration | curator | datestamp  
----+------+-------------------+------------+------------+------------+----------------+------------+-------------------+---------+------------
  1 | main | berlin_mlst_users | localhost  |       5432 | bigsdb     | bigsdb         |          1 | t                 |       0 | 2018-07-26
(1 row)

Then, I entered the user into table users:

test_seqdef=# insert into users (id, user_name, user_db, date_entered, datestamp, curator) values (1, 'dberlin', 1, date '2018-07-26', now(), 0);
INSERT 0 1
test_seqdef=# select * from users;
 id |  user_name  | surname | first_name | email | affiliation | status  | submission_emails | account_request_emails | user_db | date_entered | datestamp  | curator 
----+-------------+---------+------------+-------+-------------+---------+-------------------+------------------------+---------+--------------+------------+---------
  0 | setup       |         |            |       |             | user    | f                 | f                      |         | 2018-07-26   | 2018-07-26 |       0
 -1 | autodefiner | Definer | Auto       |       |             | curator | f                 | f                      |         | 2018-07-26   | 2018-07-26 |       0
  1 | dberlin     |         |            |       |             |         |                   |                        |       1 | 2018-07-26   | 2018-07-27 |       0
(3 rows)

After a first failed login via http://localhost/cgi-bin/bigsdb.pl?db=test_seqdef I realised that dbase_user bigsdb presently did not have the permission to access to berlin_mlst_users, so I adjusted the rights accordingly (using berlin_mlst_users=# grant all privileges on all tables in schema public to bigsdb;). Nevertheless, to no avail.

Approach 2

I entered a fresh user into the other test database test_isolates:

test_isolates=# insert into users (id,user_name,surname,first_name,email,affiliation,date_entered,datestamp,curator) values (1,'loko','Loko','Very','[email protected]','Loko Inc.',now(),now(),0);
INSERT 0 1
test_isolates=# select * from users;
 id | user_name  | surname | first_name |      email       | affiliation | status  | submission_emails | account_request_emails | user_db | date_entered | datestamp  | curator 
----+------------+---------+------------+------------------+-------------+---------+-------------------+------------------------+---------+--------------+------------+---------
  0 | setup      |         |            |                  |             | user    | f                 | f                      |         | 2018-07-26   | 2018-07-26 |       0
 -1 | autotagger | Tagger  | Auto       |                  |             | curator | f                 | f                      |         | 2018-07-26   | 2018-07-26 |       0
  1 | loko       | Loko    | Very       | [email protected] | Loko Inc.   |         |                   |                        |         | 2018-07-27   | 2018-07-27 |       0
(3 rows)

After exiting psql adding the password with add_user.pl from within the container returned no error:

root@9f0f77960422:/tmp# add_user.pl -a -d test_isolates -n loko -p WHATEVER
root@9f0f77960422:/tmp# 

Nevertheless, login via http://localhost/cgi-bin/bigsdb.pl?db=test_isolates failed.

Is there an obvious error you can spot?

[kjolley]

In approach 1 you can leave all the database settings blank except the dbase_name if the users database is on the same host as the database you are connecting to. This should work providing you have set the password for the dberlin user for the berlin_mlst_users database. Have you checked that the password hash is stored in bigsdb_auth and that the authentication database is set to bigsdb_auth in the bigsdb.conf file?
In the users table of test_seqdef you should set a status for the user (user, curator, admin).

Approach 2 should also work - again you need to set a user status. Check bigsdb_auth to ensure that the password hash has been set.

You seem to be running add_user.pl from the root account. This doesn't have access to the databases by default, so definitely check that the password hashes are being stored in bigsdb_auth.