Quiz 2.2: Ansible Playbooks

[defaults]

# Basic Configuration
inventory = ./inventory
remote_user = jpcabral-tip
private_key_file = ./private.key

# Priviege Escalation
[privilege_escalation]
become = True
become_method = sudo
become_user = root
become_ask_pass = False
# NOTE: Configure remote host/s to allow remote_user to execute sudo withoout password using sudo visudo. Append `<remote_user> ALL=(ALL) NOPASSWD:ALL`

[ubuntu]
192.168.254.135

---
  - name: Quiz 2.2 Ansible Playbooks
    hosts: ubuntu
    
    tasks:
    - name: Include db authentication
      include_vars:
        file: vars/db_config_vars.yaml


    - name: Install Apache2
      apt:
        name: apache2
        state: latest
        update_cache: yes
    
    - name: Allow HTTP on Port 80
      ufw:
        rule: allow
        port: "80"
        proto: tcp

    - name: Start and enable Apache service
      service:
        name: apache2
        state: started
        enabled: yes

    - name: Install MySQL
      apt:
        name: mysql-server
        state: present
        update_cache: yes
   
    - name: Start MySQL server
      service:
        name: mysql
        state: started
        enabled: yes

    - name: Install PHP
      apt:
        name:
          - php
          - libapache2-mod-php
          - php-mysql
        state: present
        update_cache: yes
    
    - name: Create /var/www/jpcabral-tip/ for domain config
      file:
        path: /var/www/jpcabral-tip
        state: directory
        owner: jpcabral-tip
        group: jpcabral-tip
        mode: '0755'

    - name: Copy jpcabral-tip.conf to /etc/apache2/sites-available/jpcabral-tip.conf
      copy:
        src: jpcabral-tip.conf
        dest: /etc/apache2/sites-available/jpcabral-tip.conf
        owner: jpcabral-tip
        group: jpcabral-tip
        mode: '0755'

    - name: a2ensite jpcabral-tip
      shell: "a2ensite jpcabral-tip; a2dissite 000-default"
      notify:
      - Restart Apache

    - name: New index.html
      copy:
        src: index.html
        dest: /var/www/jpcabral-tip/index.html
        owner: jpcabral-tip
        group: jpcabral-tip
        mode: '0755'
      notify:
      - Restart Apache
    
    - name: Create info.php on /var/www/jpcabral-tip/
      copy:
        content: "<?php phpinfo();"
        dest: /var/www/jpcabral-tip/info.php
        owner: jpcabral-tip
        group: jpcabral-tip
        mode: '0755'
      notify:
      - Restart Apache

    # OPTIONAL PHP MYSQL TASKS??
    - name: Install MySQL module requirements
      apt:
        name:
          - python3
          - python3-pymysql
        state: latest
        update_cache: yes
    
    - name: Install pip requirements for MySQL module
      pip:
        name:
          - pymysql
    
    - name: Create MySQL user
      shell: mysql -e "CREATE USER IF NOT EXTSTS ''@'localhost' IDENTIFIED WITH mysql_native_password BY ''"; mysql -e "CREATE USER IF NOT EXISTS ''@'%' IDENTIFIED WITH mysql_native_password BY ''"
    
    - name: Escalate created MySQL user
      shell: set -f; mysql -e "GRANT ALL PRIVILEGES ON *.* TO ''@'localhost'"; mysql -e "GRANT ALL PRIVILEGES ON *.* TO ''@'%'"

    - name: Create example_database
      mysql_db:
        login_host: ""
        login_user: ""
        login_password:  ""
        name: example_database
        state: present
    
    - name: Copy the SQL script on remote host
      copy:
        src: db.sql
        dest: "~"
        owner: jpcabral-tip
        group: jpcabral-tip
        mode: '0775'
      register: sqlFile

    - name: Import SQL
      mysql_db:
        login_host: ""
        login_user: ""
        login_password: ""
        state: import
        name: all
        target: "~/db.sql"
      when: sqlFile.changed

    - name: Copy todo_list.php
      copy:
        src: todo_list.php
        dest: /var/www/jpcabral-tip/todo_list.php
        owner: jpcabral-tip
        group: jpcabral-tip
        mode: '0755'
      notify:
      - Restart Apache

    handlers:
      - name: Restart Apache
        service:
          name: apache2
          state: restarted

CREATE SCHEMA IF NOT EXISTS example_database;

USE example_database;


CREATE TABLE IF NOT EXISTS example_database.todo_list(
	item_id INT AUTO_INCREMENT,
	content VARCHAR(255),
	PRIMARY KEY(item_id)
);

-- SAMPLE ENTRIES
INSERT INTO example_database.todo_list(content) VALUES("My first important item");
INSERT INTO example_database.todo_list(content) VALUES("My second important item");
INSERT INTO example_database.todo_list(content) VALUES("My third important item");
INSERT INTO example_database.todo_list(content) VALUES("and this one more thing");

<html>
	<head>
		<title>jpcabral-tip website</title>
	</head>
	<body>
		<h1>Hello World!</h1>

		<p>This is the landing page of <strong>jpcabral-tip</strong>.</p>
	</body>
</html>

<VirtualHost *:80>
	ServerName jpcabral-tip
	ServerAlias www.jpcabral-tip
	DocumentRoot /var/www/jpcabral-tip
	ErrorLog ${APACHE_LOG_DIR}/error.log
	CustomLog ${APACHE_LOG_DIR}/access.log combined
</VirtualHost>

<?php
$user = "jpcabral-tip";
$password = "password";
$database = "example_database";
$table = "todo_list";

try {
	$db = new PDO("mysql:host=localhost;dbname=$database", $user, $password);
	echo "<h2>TODO</h2><ol>";
	foreach($db->query("SELECT content FROM $table") as $row){
		echo "<li>" . $row['content'] . "</li>";
	}
	echo "</ol>";
} catch (PDOExecption $e){
	print "Error!: " . $e->getMessage() . "<br/>";
	die();
}

# Directory Summary

**Author:** Jose Paulo Cabral

## Prequisites

* Ansible (installed on local machine)
* SSH (installed on both local and remote machine/s)

## Requirements

* SSH private key file for authentication placed on working directory.
* Declare MySQL database user for local and remote hosts. (to be supplied in ``vars/db_config_vars.yaml``
Note: ``private.key`` and ``vars/db_config_vars.yaml`` are placed on ``.gitignore`` and must be supplied before executing the playbook.

## Directory Structure

```
quiz22
	files/
		db.sql
		index.html
		jpcabral-tip.conf
		todo_list.php	
	vars/
		db_config_vars.yaml*
ansible.cfg
inventory
playbook.yaml
private.key*
.gitignore
README.md
```

Note: Files marked with asterisk (*) at the end are declared inside ``.gitignore``.

## Content Structure for Files Declared in .gitgnore
* ``private.key``
	The localmachine generated SSH private key (named ``id_rsa`` by default inside ``~/.ssh/``
* ``vars/db_config_vars.yaml``
	```
	db_host: <database IP>
	db_user: <database user>
	db_pass: <database user password>
	```

localhost:~/1811023/quiz22# ansible-playbook playbook.yaml