User Management
Breedbase contains a system for managing user accounts, authentication information, roles (authorization information) and meta information, such as the information in user generated lists and datasets.
The user information is stored in the sgn_people schema, in the sp_person table, which contains username, password, first and last name, and similar information.
The password is stored encrypted with the crypt function, including salt (using gen_salt('bf')).
Roles can be defined in the sp_roles tables and associated to users through the sp_person_roles table. The system has 4 standard roles defined that are required for basic operation:
-
curator- the super user -
submitter- a user with read/write privileges -
user- a user with read-only privileges -
sequencer- a deprecated user (only used in the SGN database)
For each breeding program, and additional role is defined, usually with the breeding program short name. These breeding program specific roles are used to enforce breeding program level authorizations.
User defined lists are stored in the list table, with the individual list elements stored in the list_item table.
User defined datasets are stored in the sp_dataset table. The dataset info is stored as a jsonb string.
Forum topics are stored in forum_topic and individual forum posts are stored in forum_post.
A DBIx::Class interface is available for the sgn_people schema at CXGN::People::Schema. Most functionality is available through the second-level CXGN::People::Person object (in the cxgn-corelibs repo).
In Catalyst, the logged in user is available from $c->user(). To obtain user data, the get_object() function can be called on the returned user object. It returns a CXGN::People::Person object. For example, to obtain the first name and the last name of a user:
$c->user()->get_object()->get_first_name()." ".$c->user()->get_object()->get_last_name();
To test if the user has a specific role, the has_role() function can be used:
if ($c->user()->has_role("curator")) {
do_powerful_stuff();
}
The login is performed though a RESTful interface at /ajax/user/login/. It takes two parameters, a username and password. A successful login returns a login token. The token is stored in the sgn_people.sp_person table in the cookie_string field, along with a timestamp (last_access_time field). The timestamp is updated every time the user accesses a web page. If the last_access_time is larger than 2 hours, the token is invalidated.
The following web interfaces are available:
- user account creation
- user account editing
- user password reset
- login
There is also a web interface for curators to create new users. The curator form also allows for the user type to be set. A user generated account will always be of type user and will have to be upgraded later, after careful examination of the user account.
The user functionality is implemented in the controllers SGN::Controller::User and SGN::Controller::AJAX::User, based on CXGN::People::Person and other second-level objects.
Users can be managed through the command line interface using psql. In the database, the users are managed using the sp_person table in the sgn_people schema.
Passwords are stored hashed and salted in the password column. The encryption depends on the pg_crypto extension.
When connected to the database, you can change the password of a user using an SQL update command. Before issuing the command, the default schema has to be set to sgn, using:
set search_path=sgn
This is because the pg_crypto extension is loaded in the sgn schema.
Then, issue the update command:
begin; -- always run commands that modify the database in a transaction!
update sgn_people.sp_person set password=crypt('the_password', gen_salt('bf')) where sp_person_id= <the sp_person_id> ;
commit;
Deleting and adding users through the command line is not recommended, as users have many complex relationships with other tables. To effectively remove a user from the user interface, the censor column can be set to 1. The advantage of this method over hard deletion is that the relationships of all the data that the user added are conserved for the future.
-
pg_cryptoextension
Catalyst::Authentication::User
