Package Service

09 Nov 2020 - Pentti

This assignment H2 is all about Salt package-file-service; how to install, configure and manage different demons using Salt.

In this exercise I first tested how to install and configure LAMP stack locally and then configured salt using mysql formula and pillar to achieve similar result on my vagrant instance. The Main goal was to serve test data from mariadb to .php page which running on top of apache.

For this task I used similar setup as I did in the previous assignment.

To get started

Install MariaDB

$ sudo apt-get update && apt-get upgrade  
$ apt-get install mariadb-server  

After installing mariadb I had the same issue I have had before with accessing initially mysql with password. The Problem occurs when default value of root has auth plugin set to unix_socket which denies accessing to the root with password. Fortunately the auth plugin can be changedpretty easily.

I was able to log in without password using command sudo mysql and then I just had to change the auth plugin with following command:

mysql> UPDATE mysql.user SET plugin = 'mysql_native_password', authentication_string = PASSWORD('changeme') WHERE User = 'root';  
mysql> FLUSH PRIVILEGES;  

By doing that I was able to gain access as a root with using password:

$ mysql -u root -p  
$ <password>  

Then I set up simple database with simple test data:

CREATE DATABASE phptest;  
CREATE TABLE users(id int, name varchar(30));  
INSERT INTO users(id, name) VALUES (1, Jane);  
INSERT INTO users(id, name) VALUES (2, Jack);  

and checked that table data is as should:

MariaDB [phptest]> select * from users;
+------+------+
| id   | name |
+------+------+
|    1 | Jack |
|    2 | Jane |
+------+------+
2 rows in set (0.000 sec)

sources:
https://blog.ndk.name/change-mysql-server-authentication-plugin-for-root-user/
https://thishosting.rocks/install-mysql-mariadb-ubuntu/

Install apache2 && php

$ sudo apt install apache2  
$ sudo apt install php libapache2-mod-php php-mysql -y
...
$ echo this is a testpage | sudo tee /var/www/html/index.html  
this is a testpage

sources:
https://phoenixnap.com/kb/how-to-install-lamp-stack-on-ubuntu
https://linuxize.com/post/how-to-install-php-on-ubuntu-18-04/

Combine these all!

I created following php script inside index.php page for querying data from the database:

<?php
  $dbname = 'phptest';
  $dbuser = 'phpuser';
  $dbpass = 'phptest';
  $dbhost = 'localhost';
  try {
    $dbh = new PDO('mysql:host='.$dbhost.';dbname='.$dbname, $dbuser, $dbpass);

    foreach($dbh->query('SELECT * from users') as $row) {
        print_r($row);
    }
    $dbh = null;
  }
  catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
  }
?>
$ curl localhost
...
Array
(
    [id] => 1
    [0] => 1
    [name] => jane
    [1] => jane
)
Array
(
    [id] => 2
    [0] => 2
    [name] => Jack
    [1] => Jack
)
...

source:
https://www.w3schools.com/php/php_mysql_connect.asp

Installing to Slave through Salt

Now that I had tested the configuration locally, it was time to try to do the same thing with Salt

Instead of doing all the configuring by myself I thought that using state formula to handle mysql configuring would be best way to achieve the wanted result. Additionally I’m emptying the /var/www/html directory and sending the previously created index.php file to the slave.

Even though it sounded easy it was still quite tricky to get everything to work as intended. Firstly I had to modify following configure files to achieve wanted result:

/etc/salt/master

file_roots:
  base:
    - /srv/salt
    - /srv/formulas/mysql-formula

pillar_roots:
  base:
    - /srv/pillar

/srv/salt/apache/init.sls

apacheinstalled:
  pkg.installed:
    - name: apache2

/var/www/html/:
  file.directory:
    - clean: True

/var/www/html/index.php:
  file.managed:
    - source: salt://apache/index.php

/srv/salt/php/init.sls

php:
  pkg.installed:
    - pkgs:
        - php
        - libapache2-mod-php
        - php-mysql

apache2:
  service.running:
    - enable: True
    - reload: True
    - watch:
      - pkg: php

and link to the mysql pillar

I noticed that on the initial salt run everything else work like is should, but couple of the states fail due error InternalError: (1698, u"Access denied for user 'root'@'localhost'") but when I ran it again, everything works flawlessly. Problem seemed to lie in the wrongly imported module, but this needed further investigation.

EDIT 12.11.2020

I was able to workaround the access denied problem by manually adding - connection_unix_socket: /var/run/mysqld/mysqld.sock line to every failing state in the concerning files as instructed here. By doing this the connection unix socket will always be found at the first run and you dont have to run the state twice.

The user phpuser and the database phptest were created in the mysql pillar configuration file:

  user:
    phpuser:
      password: 'phptest'
      host: localhost
      databases:
        - database: phptest
          grants: ['all privileges']

I wasn’t able to find configuration how to create the test table and test data initially, but adding the data manually worked just like in the start of this assignment.

EDIT 11.11.2020

solution for initial data

I found a solution to create initial data for the database by using schemas and after couple hours of trial and error I was able to initally set up the database, table users and data to the table.

/srv/pillar/mysql.sls

...
 database:
    - initial

  schema:
    initial:
      load: true
      source: salt://schemas/initial.schema.tmpl
      template: jinja
...

/srv/salt/schemas/initial.schema.tmpl

CREATE DATABASE IF NOT EXISTS `phptest`
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

USE `phptest`;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(30) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Initial data for table `users`
--

INSERT INTO users (id, name) VALUES (1, 'Bob');
INSERT INTO users (id, name) VALUES (2, 'Cindy');

source:
https://docs.saltstack.com/en/latest/ref/returners/all/salt.returners.mysql.html

After these steps I was able to run state.highstate command and test that everything works:

$ sudo salt '*' state.highstate 
...
Summary for slave032e1cee
-------------
Succeeded: 22 (changed: 5)
Failed:     0
-------------
Total states run:       22
Total run time:     36.225 s
$ curl localhost
...
Array
(
    [id] => 1
    [0] => 1
    [name] => Bob
    [1] => Bob
)
Array
(
    [id] => 2
    [0] => 2
    [name] => Cindy 
    [1] => Cindy 
)
...

sources:
https://docs.saltstack.com/en/latest/topics/tutorials/pillar.html
https://github.com/saltstack-formulas/mysql-formula#mysql-database
https://docs.saltstack.com/en/3000/ref/states/all/salt.states.pkg.html
https://stackoverflow.com/questions/34969893/how-do-i-implement-a-pillar-example-from-a-saltstack-formula