[ansible] How do I install _and_ configure sonerezh from command line?

[nodiscc]

Hello, I'm deploying sonerezh using ansible. How do I create and populate the initial settings and users tables? With my current ansible role I get an error:

Missing Database Table
Error: Table settings for model Setting was not found in datasource default.

The role can be found below

$ tree

.
├── files
│   └── etc_apache2_conf-available_sonerezh.conf
├── tasks
│   ├── main.yml
│   └── sonerezh.yml
└── templates
    └── var_www_music_app_Config_database.php

#################################

$ cat files/etc_apache2_conf-available_sonerezh.conf 
<Directory /var/www/music>
    #SetEnv CAKEPHP_DEBUG 2
    Header set Content-Security-Policy "script-src 'self' 'unsafe-inline' 'unsafe-eval'"
    Options -Indexes
    AllowOverride All
    <IfModule mod_authz_core.c>
        Require all granted
    </IfModule>
</Directory>

#################################

$ cat tasks/sonerezh.yml
# SONEREZH #######

- name: install sonerezh required packages
  apt: pkg={{ item }} state=latest
  with_items:
    - php5-gd
    #- libav-tools #required to transcode/convert tracks, disabled as a security precaution

#https://github.com/Sonerezh/sonerezh/releases
#https://github.com/Sonerezh/sonerezh/releases.atom
- name: clone latest sonerezh release
  git: repo=https://github.com/Sonerezh/sonerezh
       dest=/var/www/music
       version=1.1.1
       accept_hostkey=yes

- name: set sonerezh directory owner
  file: path=/var/www/music owner=www-data recurse=yes

- name: create sonerezh mysql database
  mysql_db: name=sonerezh state=present

- name: create sonerezh mysql user
  mysql_user: name=sonerezh host=localhost password="{{ sonerezh_db_pass }}" state=present priv=sonerezh.*:ALL

- name: copy sonerezh mysql configuration
  template: src=var_www_music_app_Config_database.php dest=/var/www/music/app/Config/database.php owner=www-data group=www-data mode=0600

- name: copy sonerezh apache configuration
  copy: src=etc_apache2_conf-available_sonerezh.conf dest=/etc/apache2/conf-available/sonerezh.conf owner=root group=root mode=0600

- name: enable sonerezh apache configuration
  command: a2enconf sonerezh
  notify: restart apache

########################################

$ cat templates/var_www_music_app_Config_database.php 
<?php
class DATABASE_CONFIG {
public $default = array (
  'datasource' => 'Database/Mysql',
  'host' => 'localhost',
  'database' => 'sonerezh',
  'login' => 'sonerezh',
  'password' => '{{ sonerezh_db_pass }}',
  'prefix' => '',
  'persistent' => false,
  'encoding' => 'utf8',
);
}

How do I create the required tables using mysql/sonerezh command line tools?

[nodiscc]

@lGuillaume124 @atomiix ping, please halp

[lGuillaume124]

Hi,

CakePHP provides a command line tool to perform this:

sudo -u www-data ./app/Console/cake schema create sonerezh

[nodiscc]

@lGuillaume124 Thanks,

I added the following ansible task and don't get this error anymore:

- name: install sonerezh schema to database
  sudo: yes
  sudo_user: www-data
  command: chdir=/var/www/music/ ./app/Console/cake schema create --yes sonerezh

However when I access my Sonerezh instance, the login dialog is shown, even though I have not set any username/password yet; so any login attempt fails since no users are defined.

Attempting to manually access /install shows the error Sonerezh is already installed. Remove or rename app/Config/database.php to run the installation again. and I'm redirected to login again.

Is it possible to create a sonerezh account from the cake shell, and how?

[lGuillaume124]

Indeed you cannot create an account from command line. This issue reminds me of https://github.com/Sonerezh/sonerezh/issues/167, especially https://github.com/Sonerezh/sonerezh/issues/167#issuecomment-223702221. We could add the ability to deploy Sonerezh from CLI through a dedicated CakeShell command.

[nodiscc]

I am trying to insert the admin user in the database manually using the MySql command line.

- name: generate password salt
  command: python3 -c "import bcrypt; print(bcrypt.hashpw('{{ sonerezh_admin_password }}', bcrypt.gensalt(log_rounds=10)))"
  register: 'hashed_password'

- name: insert admin user in sonerezh mysql database
  command: mysql -D sonerezh -e "INSERT INTO users ('email', 'password', 'role', 'avatar') VALUES ('{{ sonerezh_admin_user }}@{{ srv01_fqdn }}', '{{ hashed_password.stdout_lines[0] }}', 'admin', NULL)"

Note that his requires python3-bcrypt. The SQL query currently fails with:

fatal: [my.example.com]: FAILED! => {
    "changed": true,
    "cmd": ["mysql", "-D", "sonerezh", "-e", "INSERT INTO users ('email', 'password', 'role', 'avatar') VALUES ('[email protected]', '$2a$10$w10eybqvqArSj8xouSTk6c4JpkRSV51xZh8r9dj6gd7Z6wHxj0z0S', 'admin', NULL)"],
    "delta": "0:00:00.006902",
    "end": "2016-12-06 23:47:26.710535",
    "failed": true,
    "rc": 1,
    "start": "2016-12-06 23:47:26.703633",
    "stderr": "ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''email', 'password', 'role', 'avatar') VALUES ('[email protected]', '$2a$10$' at line 1",
    "stdout": "",
    "stdout_lines": [],
    "warnings": []
}

Any help is welcome.

@lGuillaume124 A CakeShell command would be a nice addition, but a (working :/ ) mysql command would equally satisfy me.

[atomiix]

Hi !

Try to remove the quotes on the fields names like that :

INSERT INTO users (email, password, role, avatar) VALUES ('[email protected]', '$2a$10$w10eybqvqArSj8xouSTk6c4JpkRSV51xZh8r9dj6gd7Z6wHxj0z0S', 'admin', NULL)

[nodiscc]

👏 Thank you, that was it.

Sonerezh 1.1.1 now installs correctly with this ansible role.

[nodiscc]

@atomiix can you explain to me why the quotes caused the error? I'm trying to learn more about RDBMS and SQL syntax. Thanks again.

[atomiix]

Sure, that's because mysql takes quotes for string only, not for a column or whatever. It's like if you do :

SELECT 'column_name' FROM table_name

That will return literally 'column_name'.

Hope I was clear 🙂

[MightyCreak]

When it's a database, a table or a field, it seems to be a good practice to use back quotes, like that:

SELECT `column_name` FROM `table_name`

That's what you see when you play with mysqldump or phpMyAdmin.

[lGuillaume124]

You can also use the back tick character to properly escape the column name (at least on MySQL).

INSERT INTO `users` (`email`, `password`, `role`, `avatar`) VALUES...

[lGuillaume124]

Well.. @MightyCreak your answer appeared on my screen when I clicked on "Comment" :)

[MightyCreak]

BTW @lGuillaume124, have you seen my PR for docs and www?

(Sorry for the off-topic, Github doesn't appear to have a PM system...)

[lGuillaume124]

Yes absolutely thanks for your help, it will be merged as soon as I can :)

[nodiscc]

Thanks for the help. I will start using backticks to escape table/column names.

[nodiscc]

Reopening since my installation procedure is no longer working. The playbook runs until the end but when trying to login I get a Wrong credentials! error message.

These are the tasks responsible for creating the admin user:

- name: hash password with bcrypt
  command: python3 -c "import bcrypt; print(bcrypt.hashpw('{{ sonerezh_admin_password }}'.encode('utf-8'), bcrypt.gensalt(rounds=10)).decode())"
  register: 'hashed_password'

- name: insert admin user in sonerezh mysql database
  command: mysql -D sonerezh -e "INSERT INTO users (email, password, role, avatar) VALUES ('{{ sonerezh_admin_user }}@{{ srv01_fqdn }}', '{{ hashed_password.stdout_lines[0] }}', 'admin', NULL)"

Which gives me a mysql command such as:

INSERT INTO users (email, password, role, avatar) VALUES ('[email protected]', '$2b$10$okp5K0WvTF7gDxAKA7mHm.d1IyWjPhuBzubX7YkXgx8tTTBlUFp1K', 'admin', NULL

I don't get why that doesn't work.

I checked the resulting users table and the values are correctly inserted.

[nodiscc]

I don't use this software anymore and it's been unmaintained since 2019, therefore I'm closing this issue. I can recommend https://jellyfin.org/ and https://apps.nextcloud.com/apps/music as good alternatives.