Setting up Servatrice - Cockatrice/Cockatrice GitHub Wiki

By default, servatrice requires no database connection to allow game play. How ever, adding a database and connecting servatrice to it increases the servers functionality. Additional functionality when using a database includes server room / game type management, various server tab description / announcement functionality, user account management allowing for name reservations, customized user information, user banning, server side deck storage, game replay functionality, and chat logging. The various scripts and sample files included in the repository have been tested and used against the mysql/mariadb database server. However, as long as the DB understands the basic database calls made by servatrice and a driver for the db exists you should be able to use any other type of database server. The steps described below assume the use of the mysql database server. If you're using a different database server you will need to translate the various commands into your database server's command syntax.

future sections (not existing yet)
- [Server Requirements and Scaling](https://github.com/Cockatrice/Cockatrice/wiki/Setting-up-Servatrice#server-requirements-and-scaling)

If you're about to run (or are already running) your own Servatrice server for Cockatrice make sure to check these topics as well:

Initial Database Configuration

The first couple of things you will need to setup in order to connect servatrice to a database is the actual database itself as well as a user account able to manage that database. It is recommended that you do not use the default administrator/root account when connecting the server to a database for security reasons. Begin by issuing the following commands with in the mysql client using an account that has the rights to create new users and databases inside mysql.

create database servatrice;
create user 'servatrice'@'localhost' identified by 'foobar';
grant all privileges on servatrice.* to 'servatrice'@'localhost';
flush privileges;

The above commands first create a new database named servatrice and then creates a local user account inside mysql named servatrice with the password of foobar and grants that account full permissions on the newly created database. Next we will need to create the database structure. From the command line run the following command to build the database table structure.

mysql -u servatrice -pfoobar servatrice < servatrice.sql
  • Note: the servatrice.sql file can be located under the servatrice folder from the cockatrice github repo.

Once the command completes you can log into the database server and verify that the database tables were created successfully. Do so by issuing the following commands from with in the mysql client.

use servatrice;
show tables;

You should get a result similar to below. If not the database table creation failed and there will need to be some investigation as to why.

+-----------------------------+
| Tables_in_servatrice        |
+-----------------------------+
| cockatrice_bans             |
| cockatrice_buddylist        |
| cockatrice_decklist_files   |
| cockatrice_decklist_folders |
| cockatrice_games            |
| cockatrice_games_players    |
| cockatrice_ignorelist       |
| cockatrice_log              |
| cockatrice_news             |
| cockatrice_replays          |
| cockatrice_replays_access   |
| cockatrice_rooms            |
| cockatrice_rooms_gametypes  |
| cockatrice_servermessages   |
| cockatrice_servers          |
| cockatrice_sessions         |
| cockatrice_uptime           |
| cockatrice_users            |
+-----------------------------+
18 rows in set (0.00 sec)

Updating Servatrice Configuration to use the Database

Once you have completed the database setup procedures under the "Initial Database Configuration" section above you will need to update your servatrice configuration file to connect to the database. There is a number of sections in the servatrice.ini file that either need to be updated or can be updated to allow various functionality options. This section only describes the portions of the configuration file that are required in order to connect the servatrice application to a database server. Additional configuration options with in the servatrice.ini file will be described in other sections of this wiki as time allows the additions of the information.

Locate the section of the servatrice.ini labeled "[database]". Notice there are a number of options and a description of each. Change the following values to the settings shown below.

type=mysql
prefix=cockatrice
hostname=localhost
database=servatrice
user=servatrice
password=foobar

  • Note: If your servatrice server and database server are not on the same machine, update the hostname= line to specify the name of the database server you would like servatrice to connect to. Make sure if this is the case that connectivity is properly configured allowing such things like ports on firewalls to be open.

  • Note: If you have changed the password that the mysql servatrice user account uses, update the password= line to reflect the correct password.

Once you have updated the configuration information listed above you should be able to start/restart your servatrice application and it will now be connected to the database defined. Something to mention at this point. Once the database information has been placed into the servatrice configuration file as described above you will want to make sure you have the correct file permissions defined on the servatrice.ini file for who can access it. Since the mysql user account information is stored in clear text anyone with access to the file will be able to see the account information and potentially gain access to the database information.

User Authentication, Registration and Management

By default, even if you have connected the servatrice application to a database by following the various sections of this wiki page users are not authenticated against any user information stored in the database. This may sound odd but you can configure the servatrice application to connect to a database and use only the functionality of something like room / game type management and disregard user authentication against the database. How ever, most people configuring a database connection are looking to actually authenticate and manage users using a database as a central storage for account information. It's fairly simple to configure servatrice to authenticate users against the database. Make sure you have completed the "Initial Database Configuration" section listed above as well as the "Updating Servatrice Configuration to use the Database" to connect servatrice to a database. Once those two sections are completed successfully edit the servatrice.ini configuration file and locate the section labeled "[authentication]". Notice the various options and descriptions and then change the following configuration option to the settings shown below.

method=sql

Once you have updated the configuration information listed above you should be able to start/restart your servatrice application and it will now be connected to the database and begin authenticating users against the database.

  • Note: There is no built in client user management functionality when it comes to registering users or managing user account information. The only thing available for this type of functionality is a web management interface (Servatrice Web Admin UI) listed under the "Additional References" section below.

Now that the servatrice applications is connected to the database and user authentication has been configured to authenticate users against the database the following command can be run from with in the mysql client to add the first admin user into the database with the username of "servatrice" and the password of "password".

insert into servatrice.cockatrice_users (admin,name,password_sha512,active) values (1,'servatrice','jbB4kSWDmjaVzMNdU13n73SpdBCJTCJ/JYm5ZBZvfxlzbISbXir+e/aSvMz86KzOoaBfidxO0s6GVd8t00qC0TNPl+udHfECaF7MsA==',1);
  • Note: Passwords in the servatrice database are stored using an SHA / salted hash. You can NOT simply add the users password in clear text into the database as it will not properly be hashed and result in user authentication failures. The following PHP based function can be used to convert user passwords to hashed values that can then be used to insert into the database user account information.
function crypt_password($password, $salt = '') {
    if ($salt == '') {
        /* A salt is a 16-char random string that needs to be generated only the first time the
         * password is hashed. If no salt has been externally provided, we generate one here.
         */
        $saltChars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
        for ($i = 0; $i < 16; ++$i) {
            $salt .= $saltChars[rand(0, strlen($saltChars) - 1)];
        }
    }
    // the salt gets prepended to the user-provided password
    $key = $salt . $password;
    for ($i = 0; $i < 1000; ++$i) {
        // 1000 rounds of sha512
        $key = hash('sha512', $key, true);
    }
    /* The hashed password can contain binary, unreadable characters. To avoid problems
     * in storing it, we get its base64 representation. The plaintext salt used is prepended
     * in order to be able to recalculate the same hash when checking the user password.
     */
    return $salt . base64_encode($key);
}

Editing values in mysql

You can update user properties and more through mysql, log in with a privileged account or just use the server account created above on the command line like this:

mysql -u servatrice -pfoobar servatrice
# in mysql giving a user admin(1), moderator(2) and judge(4)
update cockatrice_users set admin=7 where name="[username]";
# checking afterwards can be done with select
select * from cockatrice_users where name="[username]";

Creating a Room

When using the SQL backend, there are no rooms by default. To create your first room, you can run the following SQL command against the Servatrice database:

insert into servatrice.cockatrice_rooms (name,descr,permissionlevel,privlevel,auto_join,join_message,chat_history_size) values ('General room','Play anything here.','NONE','NONE',1,'This message is only here to show that rooms can have a join message.',100);

You can change the permissionlevel above to be one of 'NONE', 'REGISTERED', 'MODERATOR', or 'ADMINISTRATOR'. The privlevel can be one of 'NONE', 'PRIVILEGED', 'VIP', 'DONATOR', and you can disable auto-join by setting that value to 0.

Using Docker

You can use Docker to set up the database and build Servatrice with possibly minimal hassle. Follow these steps:

1. Database setup

1.1. Clone the Cockatrice repository locally

git clone <repo_url>
cd Cockatrice

1.2. Create the containers to run the MySQL database

docker create -v ./mysql-db --name servatrice-db mysql /bin/true
docker run --name servatrice-mysql -e MYSQL_ROOT_PASSWORD=password --volumes-from servatrice-db -d mysql

Don't worry about the MySQL root password being password; this container is only visible internally within Docker.

1.3. Create and run a quick SQL script to set up the database schema

This script uses the servatrice/servatrice.sql schema file, with a header and footer to (respectively) create the database before running it, then populate it with the admin user after it is initialized.

sed '1s/^\xEF\xBB\xBF//' < ./servatrice/servatrice.sql > ./db_setup.sql
echo -e "create database servatrice;\nuse servatrice;\n$(cat db_setup.sql)" > db_setup.sql
echo "
insert into servatrice.cockatrice_users (admin,name,password_sha512,active) values (1,'servatrice','jbB4kSWDmjaVzMNdU13n73SpdBCJTCJ/JYm5ZBZvfxlzbISbXir+e/aSvMz86KzOoaBfidxO0s6GVd8t00qC0TNPl+udHfECaF7MsA==',1);
" >> db_setup.sql

Then, run it using this command:

docker run -i --link servatrice-mysql:mysql --rm mysql \
    bash -c 'mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD" --wait' \
    < db_setup.sql

If it fails with MySQL status 111, wait a few seconds. The MySQL server may have still been starting up.

2. Servatrice server build and setup

Once you have the database going, you can look at Servatrice itself.

2.1. Build Servatrice

docker build -t servatrice .

This takes a LONG time.

2.2 Configure Servatrice

cp ./servatrice/servatrice.ini.example servatrice.ini

Then, edit servatrice.ini to your pleasure. Most importantly, make sure your database settings are as below:

hostname=servatrice-mysql
username=root
password=password

2.3 Run Servatrice

docker run -it -d --link servatrice-mysql \
    --mount type=bind,source=$PWD/servatrice.ini,target=/tmp/servatrice.ini \
    -p 4748:4748 -p 4747:4747 \
    --name servatrice \
    servatrice --config /tmp/servatrice.ini --log-to-console

That's it!

Hosting servatrice on PaaS platforms like Heroku

Servatrice will respect the PORT and DATABASE_URL environmental variables, using them instead of a config file if present. When the DATABASE_URL variable is present, servatrice also assumes the cockatrice table prefix as well as sql mode for authentication and room creation. DATABASE_URL should be a full mysql connection string: ex. mysql://user:[email protected]/database

Additional Servatrice Configuration Options

Configuration file can be passed to the servatrice excutable with the '--config' parameter

servatrice --config /path/to/servatrice.ini

Additional References

Feel free to contact and connect with us on Discord, Gitter and Reddit!

⚠️ **GitHub.com Fallback** ⚠️