Add initial work on 'database' machine.

Development deployment is working; production deployment is untested so
far.

5 files changed, 245 insertions, 0 deletions

diff --git a/README.mdwn b/README.mdwn
index d25dfa2f..a7e277e2 100644
--- a/README.mdwn
+++ b/README.mdwn
@@ -12,6 +12,11 @@ should be contained in this Git repository, with the exception of certain
 private tokens (which should be simple to inject at deploy time). Each
 service should be provided by a system which services only one function.
 
+I apologise if it's a bit over-complicated! Part of the goal for this work
+has been to learn Ansible and Packer, and see how they can be helpful for
+Baserock. Feel free to discuss simplifying things which appear overengineered
+or needlessly confusing!
+
 Front-end
 ---------
 
@@ -29,6 +34,47 @@ To deploy this system:
 
 Full HAProxy 1.5 documentation: <https://cbonte.github.io/haproxy-dconv/configuration-1.5.html>.
 
+Database
+--------
+
+Baserock uses MariaDB (a fork of MySQL). Storyboard has database migration
+files which are tied to this database, so we do not have the choice of using
+others at this time.
+
+The Packer build only creates an image with MariaDB installed. To deploy, you
+will need to set up and attach a volume and then start the 'mariadb' service.
+Also, you must create or have access to an Ansible playbook which will set up
+the user accounts. For development deployments you can use the 'develop.sh'
+script which sets up all the necessary accounts using dummy passwords.
+
+To deploy this system to production:
+
+    packer build -only=production database/packer_template.json
+    nova create \
+        --flavor 2 --image 'database-mariadb' \
+        --key-name=<your-keypair> database-mariadb
+    nova volume-create \
+        --display-name database-volume \
+        --display-description 'Database volume' \
+        10G
+    nova volume-attach database-mariadb <volume ID> auto
+
+    nova floating-ip-associate database-mariadb <some floating IP>
+
+    # Set up the volume inside the machine
+    ansible <IP> --user=fedora --sudo -m shell \
+        -a "mkfs.ext4 /dev/vdb -L database-volume"
+    ansible <IP> --user=fedora --sudo -m lineinfile \
+        -a "dest=/etc/fstab create=yes line='LABEL=database-volume /var/lib/mysql ext4 defaults 1 2'"
+
+    # FIXME: here we start the service before setting the root password!!!!
+    ansible <IP> --user=fedora --sudo -m service \
+        -a "name=mariadb enabled=true state=started"
+
+    # edit 'hosts' line of database/user_accounts.yml to point to the server's IP
+    ansible-playbook database/user_accounts.yml
+
+
 Deployment to DataCentred
 -------------------------
 
diff --git a/database/develop.sh b/database/develop.sh
new file mode 100755
index 00000000..140092b1
--- /dev/null
+++ b/database/develop.sh
@@ -0,0 +1,70 @@
+#!/bin/sh
+
+# Start up a development instance of 'database', which will be accessible on
+# the local machine. (To stop it again, use `docker stop baserock-database`).
+
+# Note that this container works in a different way to the official Docker
+# MariaDB image (<https://registry.hub.docker.com/_/mariadb/>). That's
+# intentional: the official image is for use when Docker is being used as a
+# production environment and the official Docker images are considered trusted.
+# Here I am using Docker as a tool to locally test out trusted(ish) images that
+# I create with Packer, before deploying them to an OpenStack cloud.
+
+set -eu
+
+# These lines of SQL are needed to authorize the container host for accessing
+# the database remotely. (It actually grants access to any host, but since
+# this is a development instance that's OK!)
+CREATE_REMOTE_ROOT_USER_SQL="CREATE USER 'root'@'%' IDENTIFIED BY 'insecure' ;"
+ALLOW_REMOTE_ROOT_USER_SQL="GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION ;"
+
+docker run --detach \
+    --name=baserock-database \
+    --publish=127.0.0.1:3306:3306 \
+    baserock/database \
+    /bin/sh -c " \
+      echo \"$CREATE_REMOTE_ROOT_USER_SQL\" > /tmp/mariadb-init.sql && \
+      echo \"$ALLOW_REMOTE_ROOT_USER_SQL\" >> /tmp/mariadb-init.sql && \
+      /usr/libexec/mariadb-prepare-db-dir mariadb && \
+      /usr/bin/mysqld_safe --basedir=/usr --init-file=/tmp/mariadb-init.sql"
+
+trap 'docker rm -f baserock-database > /dev/null' ERR
+
+# Create some dummy accounts (in production deployments, this is done using the
+# 'service-config.yml' Ansible playbook). We expect that there exists a 'root'
+# user with no password set already.
+
+create_without_overwriting() {
+    target_file="$1"
+    content="$2"
+    if [ -e "$target_file" -a "$(cat "$target_file")" != "$content" ]; then
+        echo >&2 "Not overwriting existing file $target_file"
+        # Don't let the user create a development environment using files that
+        # could contain the real passwords, to avoid them being used in an
+        # insecure deployment.
+        exit 1
+    fi
+    echo "$content" > "$target_file"
+}
+
+create_without_overwriting "database/root.database_password.yml" "root_password: insecure"
+create_without_overwriting "database/baserock_openid_provider.database_password.yml" "baserock_openid_provider_password: openid_insecure"
+
+# Ouch! Would be nice if you could get the 'docker run' command to wait until
+# the database server is ready, or poll somehow until it is.
+echo "Waiting 30 seconds for database server to be ready"
+sleep 30
+
+# Note that the Python 'mysqldb' module is required on the machine Ansible
+# connects to for this playbook. For development deployments that is *your*
+# machine (since we cannot and should not SSH into the Docker container). On
+# Red Hat OSes the package you need is called 'MySQL-python'.
+ansible-playbook database/user_config.yml
+
+echo "You have a container named 'baserock-database' listening on port 3306."
+echo
+echo "Pass '--link baserock-database:mysql' to 'docker run' when starting "
+echo "other containers if you want to give them access to this instance."
+echo
+echo "Run 'docker stop baserock-database; docker rm baserock-database' when "
+echo "you are done with it (all data will then be lost)."
diff --git a/database/local.yml b/database/local.yml
new file mode 100644
index 00000000..71ec333b
--- /dev/null
+++ b/database/local.yml
@@ -0,0 +1,18 @@
+# System configuration for Baserock database server.
+#
+# Packer runs this playbook inside the system at 'build' time, using the
+# command `sudo ansible-playbook`.
+---
+- hosts: localhost
+  tasks:
+  - name: enable persistant journal
+    shell: mkdir /var/log/journal
+    args:
+      creates: /var/log/journal
+
+  - name: install MariaDB
+    yum: name={{ item }} state=latest
+    with_items:
+        - mariadb
+        - mariadb-server
+        - MySQL-python
diff --git a/database/packer_template.json b/database/packer_template.json
new file mode 100644
index 00000000..0e65a435
--- /dev/null
+++ b/database/packer_template.json
@@ -0,0 +1,48 @@
+{
+    "builders": [
+        {
+            "name": "development",
+            "type": "docker",
+            "image": "fedora:20",
+            "commit": true,
+            "run_command": ["-d", "-i", "-t", "{{.Image}}", "/bin/sh"]
+        },
+        {
+            "name": "production",
+            "type": "openstack",
+            "image_name": "database-mariadb",
+            "flavor": "f0577618-9125-4948-b450-474e225bbc4c",
+            "source_image": "742e0414-c985-4994-b307-4aafade942b3",
+            "networks": ["d079fa3e-2558-4bcb-ad5a-279040c202b5"],
+            "floating_ip": "85.199.252.164",
+            "use_floating_ip": true,
+            "ssh_username": "fedora"
+        }
+    ],
+    "provisioners": [
+        {
+            "type": "shell",
+            "inline": [ "sudo yum install -y ansible"]
+        },
+        {
+            "type": "ansible-local",
+            "playbook_file": "database/local.yml",
+            "command": "sudo ansible-playbook"
+        },
+        {
+            "type": "shell",
+            "inline": [ "sync; sync; sleep 10; sync" ],
+            "only": ["production"]
+        }
+    ],
+    "post-processors": [
+        [
+            {
+                "type": "docker-tag",
+                "repository": "baserock/database",
+                "tag": "latest",
+                "only": ["development"]
+            }
+        ]
+    ]
+}
diff --git a/database/user_config.yml b/database/user_config.yml
new file mode 100644
index 00000000..069b66d1
--- /dev/null
+++ b/database/user_config.yml
@@ -0,0 +1,63 @@
+# User account configuration for Baserock database server.
+#
+# If you're setting up a production deployment, you'll need to temporarily give
+# the database instance a public floating IP, then edit 'hosts' in this file
+# to point to that IP and run:
+#
+#   ansible-playbook database/user_config.yml
+#
+# The relevant .database_password.yml files will need to be available too.
+# You should then remove the floating IP from the instance (you can re-add one
+# any time you want to remotely administer the database).
+---
+- hosts: localhost
+  vars_files:
+    - root.database_password.yml
+    - baserock_openid_provider.database_password.yml
+  tasks:
+    - name: configuring the root database user
+      mysql_user: |
+        name=root
+        password={{ root_password }}
+        login_host=127.0.0.1
+        login_user=root
+        login_password={{ root_password }}
+        check_implicit_admin=yes
+
+    - name: remove the MySQL test database
+      mysql_db:
+        name=test state=absent
+        login_host=127.0.0.1
+        login_user=root
+        login_password={{ root_password }}
+
+    - name: adding databases
+      mysql_db: |
+        name={{ item.name }}
+        state=present
+        login_host=127.0.0.1
+        login_user=root
+        login_password={{ root_password }}
+      with_items:
+        - { name: baserock_openid_provider }
+
+    # We could probably restrict the privileges of these users further...
+    #
+    # I feel like setting 'host="%"' (i.e. not enforcing that the account can
+    # only be used by IPs within the cloud's local network, or even a single
+    # known IP adress) is kind of bad practice, but since the database server
+    # is not exposed to the internet anyway I don't think it's important right
+    # now.
+    - name: adding other database users
+      mysql_user: |
+        name="{{ item.name }}"
+        host="%"
+        password={{ item.password }}
+        priv={{ item.priv }}
+        login_host=127.0.0.1
+        login_user=root
+        login_password={{ root_password }}
+      with_items:
+        - name: openid
+          password: "{{ baserock_openid_provider_password }}"
+          priv: baserock_openid_provider.*:ALL;