=========================
Sample mysql environment:
=========================

.. _mysql_env:

Overview:
=========

I was involved in a discussion about the wisdom and process for
granting developer access to azure keyvaults in dev.  The details
are irrelevant; however, in order to facilitate the discussion,, I 
designed a sample environment in my personal tenant.

I set up two automation processes - one using azcli and the other
using terraform.

Technical details:
==================

* All assets created within a single resource group.  Makes
  deletion (and therefore cost control) much easier.
* Virtual network created:

  * 2 subnets
  * 1 NSG applied both both subnets
  * All traffic from internet blocked except for ssh/mysql 
    from ${myip}
  * service endpoints created for storage and keyvaults.

* 2 keyvaults created with access restricted to ${myip} and
  the two subnets.

  * doldbadmin: contains dbroot secret which contains the db root
    password,
  * doldbapp: contains the appid secret which contains the db password
    for the appid

* 1 storage account, sa0x003, with a container, dbaccess, containing
  scripts and sample database data.  Access to the account and container
  restricted to ${myip} and the two subnets.

* 1 ubuntu virtual machine using image alias Ubunt2204.  Created with a 
  public IP on the vms subnet and a system assigned managed identity.

* Permissions provisioned for system assigned managed identity:

  * Storage blob data reader for blob access
  * Get, List to each keyvault for secrets access

* vm configuration files/scripts:

  * cdata: user data script to:

    * install az cli
    * update ~/.bashrc
    * create ~/.vimrc
    * create script, dlall, to download files from blob.

  * dbdata.txt:  sample data from internet with 5m rows.
  * instdb: simple/crude script to handle:

    * mysql db installation and initial configuration
    * create the ~/.mycnf file
    * create the content db and organizations table

Command line env creation:
==========================

There's nothing real special about the bash script.  It's just
a succession of az cli calls to create the environment documented
above.  

::

  $ grep '^[A-z]*()' mkenv
  confighost()
  mksecrets()
  mkkv()
  mkrg()
  mknsg()
  mkvnet()
  mksa()
  mkvm()
  mkblob()

  $ tail -16 cli/mkenv
  
  mkrg        || exit
  mknsg       || exit
  mkvnet      || exit
  mkkv        || exit
  mksecrets   || exit
  mksa        || exit
  mkvm        || exit
  mkblob      || exit
  
  # List ips:
  az vm list-ip-addresses --query "[].{
    VMName:virtualMachine.name, 
    PublicIP:virtualMachine.network.publicIpAddresses[0].ipAddress}" \
    -o table
  
The script assumes ``az login`` has been run before execution.

The script can be found via :download:`this link <cli/mkenv>`.

Terraform env creation:
=======================

I'm not an expert with terraform by any stretch.  One of the goals of this
exercise was to get more familiarity with the tool. I ran into some 
interesting lessons learned in this effort:

* I had to provision keyvault permissions specifically.  Using azcli,
  the calling user had key administrator functionality by default.  
  This includes adding 'Purge' to the list of key privs.
* terraform purges keyvaults upon deletion,  That's what I wanted, so
  all is good; however, it'd be interesting to see the issues if 
  purge_protection_enabled is set to true.
* There were some squirrely dependency issues.  I ended up using 
  depends_on probably more than I needed; but it works.
* I found intresting syntax for generating lists/arrays that worked
  **really** well.  Made the code much more succinct and readable.
  Details in the main.tf file below.

locals.tf:
----------

* :download:`link to locals.tf <tform/locals.tf>`
* only real thing of note is the use of a data resource
  to create a local variable, myip, automaticaly rather
  than me updating it every time.

main.tf:
--------

* :download:`link to main.tf <tform/main.tf>`
* Interesting item of note: Use of for loops.  Not something
  I've run into in reading about terraform::

    # lock storage account to $myip and vnet/subnets
    resource "azurerm_storage_account_network_rules" "example" {
      depends_on = [azurerm_subnet.subnet]
      storage_account_id         = azurerm_storage_account.sa.id
      default_action             = "Deny"
      ip_rules                   = [local.myip]
      bypass                     =  ["AzureServices"]
      virtual_network_subnet_ids = [for s in azurerm_subnet.subnet : s.id]
    }
    
  There were a couple of examples of that in the file which, again, made
  it much more readable and maintainable.

outputs.tf:
-----------

* :download:`link to output.tf <tform/outputs.tf>`
* Nothing terribly noteworthy.  Maybe the multi-line 
  output used for ~/.ssh/config updates::

    output "vm_details" {
      value = "host ${azurerm_linux_virtual_machine.vm.name}\n  hostname ${azurerm_public_ip.vm_pip.ip_address}"
    }

variables.tf:
-------------


* :download:`link to variables.tf <tform/variables.tf>`
* Nothing out of the ordinary.