Tutorial: Database Content Engine Part I

Part 1 of 2: Installation of Vault, DCE Plugins and Database

Hashicorp Vault is a state-of-the-art security software. It manages and protects passwords, keys, and other sensitive secrets in a open-cloud environment. Vault allows 3rd-party plugin, to serve customized auth method or secret engine. Beneath the surface, vault is a httpd server.

In this article, I will introduce a special vault plugin, called Database Content Engine (DCE), to serve database content as a REST API service using the vault infrastructure.

1. Features of DCE API Service

There are many database API management services nowadays, for example CData API Service, MuleSoft Anypoint, PostgREST etc. Compared to them, this DCE vault plugin is very unique in the following areas.

1.1) Multiple Teams

• DEC creates multiple role-based teams from database, each of them accessing own domain of information in REST APIs. For example, an online e-commerce database could consist of roles like customer, product vendor, and customer support.
• Authentication to team could be verified by about 20 built-in auth methods in Vault such as LDAP, AWS and Oauth2. DCE has also provided a new auth method, graphauth, which authenticates against membership or credential table in database.
• API access by team could be further filtered by Access-Control-List (ACL), making fine-tuning authorization controls.

1.2) Code Automation and No-code Programming

• DCE can be initialized through a process known as genesis. During genesis, authentication teams and backend programs are created for the first time. These programs are encapsulated in no-code JSON files, which can be downloaded to a local disk for editing.
• The modified no-code can be re-submitted to the vault to serve customized REST APIs. For instance, in an e-commerce application, we can GET a user’s order to include line-items, shipping, and payment information, using multiple table joins. The changes are made in the no-code file, not through actual programming language code.
• In addition to JSON, DCE can also utilize the HCL format for no-code files.

1.3) Security Foundation and Row-Level-Security

• Due to the security-first principle of Hashicorp products, DCE is one of the most secure API data services available on the internet. The robust security foundation of Hashicorp also safeguards DCE.
• A team member will be restricted to accessing only their own data by Row Level Security in DCE, even if the database does not support this feature. For example, an e-commerce customer can only access her line items in the order details table, despite the table being shared by all customers.

2 Installations

2.1) Download Hashicorp Vault

Follow up this instruction by Hashicorp to download and install Vault in Windows, macOS and Linux. After installation, please not run the program or any startup script yet.

2.2) Config Vault

In our current directory, assumed to be /home/user1, create a working directory, working, and populate it with 4 sub directories:

$ cd
$ mkdir -p working/{config,file,plugins,data}
$ cd working
$ ls
# config data file plugins

Populate this config file vault.json in config:

$ cat > config/vault.json << EOF
{
  "storage": {
    "file": {
      "path": "/home/user1/working/file"
    }
  },
  "listener": {
    "tcp":{
      "address": "0.0.0.0:8200",
      "tls_disable": 1
    }
  },
  "plugin_directory": "/home/user1/working/plugins",
  "ui": true,
  "disable_mlock": true
}
EOF

Here we assume a vault service on port 8200, to accept plugins in directory plugins, and to adopt backend storage called file storage.

Change the home directory name, /home/user1, to yours.

2.3) Download Database Content Engine

Here download pre-compiled plugins, graph and graphauth, and CLI program dcp. The binaries’ sha256sum are:

$ git clone git@github.com:genelet/medium_dce.git
$ cd medium_dce
$ sha256sum dcp* graph*

bbb37b3932201e735d9af344a44cd3eb03b69073ce7c6934e0f6b37905b1bb2d  dcp-386-linux
c40bbe84f3f421d7edb8c6856a19eec27e3d452b780df1be5b0fa78ee943290a  dcp-386-windows.exe
8ccfcfad6145213fbef2116b4786cda6de087071d0e518951f14b68ebc27c819  dcp-amd64-darwin
7eea7c0a184bb9205fa10af0f3ed5a929cc5787f02b8efded3c57882baaefa9d  dcp-amd64-linux
766bed34cd8f575119243bc6faa4817b57c7cda26c927f686ab640bb81013695  dcp-amd64-windows.exe
db3a1f45c24a48a3056bc231ba99eb7a9956e85c22b709a297ed7a1340d3fd1f  graph-386-linux
de960a1df14af74da8f3868a8d3ac9f82663841e558115f42e9bc88624dc0f09  graph-386-windows.exe
5fc2f18d35a6d94d6cd1d67f4cfacb79128889b3f184b0de63d6a9258cef523a  graph-amd64-darwin
2026ce85c7dc98d6a233a6fb1c218053a7f6bdd670d414376291a3d44825539a  graph-amd64-linux
64a13c058191d7946c23a98d8d0ceecf99d2213e41082826e1820dc5205e5a61  graph-amd64-windows.exe
4d5ac0288b80da06a0e767e119e045a65e08fc0de3f72c0d6ebb292cd5e26f6c  graphauth-386-linux
4ee3c9667283bfc566f8308030ae79c15bd72bb1ec2c7bd8a92585f36ff9226c  graphauth-386-windows.exe
2c310822af74317006ea1fb7346ff57125ffe5e1e4fa1768c98cbec299fe77a5  graphauth-amd64-darwin
886543157bdb17d49c15f3dc3551b6e7d0653570ab2322ac2f935b928a940037  graphauth-amd64-linux
da84e7e0262505b07e44ee1d815d8f2af2088f66b68663c63fc54d569eba3af0  graphauth-amd64-windows.exe

Assume that we are on Linux so the plugin binaries are graph-amd64-linux and graphauth-amd64-linux . Copy them to working/plugins, and make the be executable:

$ cd /home/user1/working
$ cp medium_dce/graph-amd64-linux plugins/graph
$ cp medium_dce/graphauth-amd64-linux plugins/graphauth
$ chmod a+rx plugins/*

Run the plugins:

$ ./plugins/graph
$ ./plugins/graphauth

We should get the output:

This binary is a plugin. These are not meant to be executed directly.
Please execute the program that consumes these plugins, which will
load any plugins automatically

2.4) CLI

Copy dcp-amd64-linux to working and run it:

$ cp medium_dce/dcp-amd64-linux dcp
$ chmod u+x dcp
$ ./dcp
# Usage: PROGRAM genesis|push|pull

2.5) Install Relational Database

If you already have a MySQL, just use it. If not yet, install one for example this tutorial.

By the end, we will have a running database and a DBA who manages it. The information is wrapped up into 3 strings defined by Go database/sql:

• driver: e.g. mysql
• data source name: e.g. DB_USER:DB_PASS@/classicmodels
• name of database: e.g. classicmodels

In this demo, we are assumed to have the MySQL database, classicmodel, downloaded from https://www.mysqltutorial.org/mysql-sample-database.aspx :

MySQL Sample Database

3. Set Up Vault and DCE

3.1) Start up Vault

$ vault server -config=config/vault.json

After the start up, we should get the UI at http://localhost:8200/.

3.2) Initialize Vault

Claim the ownership of vault by initialization. After the ownership is identified, no one can break in the vault barrier. Only will you be able to access and start the httpd service.

Initialize vault for the first time using commands curl and jq in container:

$ curl --request POST \
  --data '{"secret_shares": 5, "secret_threshold": 3}' \
  $VAULT_ADDR/v1/sys/init | jq
 
#
{
  "keys": [
    "ef3724b4f856ada717cee2ff7e4f534799534b968ca4b37f3b1864599e3e36ad57",
    "1e658f7554cb262c4e554cf51cd787a9bca61a2e3bc420382e9ee89d56d342d6ba",
    "5c123f819ac7ec0103bcaa222174de4fe7e5333334df2f4c0a46b406a514ea7f92",
    "7bfdacc85af1f3c23e6a5133b5792455c3080454fd0a617217caee64b6e190954c",
    "203231260fc8487482d46a6c24fd185210261cf1afce687d1d299bf098f94ac5c2"
  ],
  "keys_base64": [
    "7zcktPhWracXzuL/fk9TR5lTS5aMpLN/OxhkWZ4+Nq1X",
    "HmWPdVTLJixOVUz1HNeHqbymGi47xCA4Lp7onVbTQta6",
    "XBI/gZrH7AEDvKoiIXTeT+flMzM03y9MCka0BqUU6n+S",
    "e/2syFrx88I+alEztXkkVcMIBFT9CmFyF8ruZLbhkJVM",
    "IDIxJg/ISHSC1GpsJP0YUhAmHPGvzmh9HSmb8Jj5SsXC"
  ],
  "root_token": "hvs.6AreB0rVR8SXD8GIKBTKY9JG"
}

Please save the 5 restoration keys and the root token in a safe place (yours will be definitely different.) The keys are for system operations and root_token for vault management.

3.3) Unseal Vault

Vault is sealed. We need to unseal it using 3 out of the 5 keys.

$ curl --request POST \
  --data '{"key":"HkOSkE7tlVEpz8sfqus0L1rmxySmdNOccQnOL/RyJMeQ"}' \
  $VAULT_ADDR/v1/sys/unseal | jq

# {
  "type": "shamir",
  "initialized": true,
  "sealed": true,
  "t": 3,
  "n": 5,
  "progress": 1,
  "nonce": "c2c3944e-8ba3-30e8-aff8-ed6aab3b2076",
  "version": "1.15.4",
  "build_date": "2023-12-04T17:45:28Z",
  "migration": false,
  "recovery_seal": false,
  "storage_type": "file"
}

$ curl --request POST \
--data '{"key":"xa7f2d3t6rKT6bt/3GSQ730e7ADYJYNk2thWZfHIxSfc"}' \
$VAULT_ADDR/v1/sys/unseal | jq
 
# {
  "type": "shamir",
  "initialized": true,
  "sealed": true,
  "t": 3,
  "n": 5,
  "progress": 2,
  "nonce": "c2c3944e-8ba3-30e8-aff8-ed6aab3b2076",
  "version": "1.15.4",
  "build_date": "2023-12-04T17:45:28Z",
  "migration": false,
  "recovery_seal": false,
  "storage_type": "file"
}

$ curl --request POST \
  --data '{"key":"tv8zQbnW80YTTIT4DU2hMrQO5QFzJaKKsmZffbevdA28"}' \
  $VAULT_ADDR/v1/sys/unseal | jq

# {
  "type": "shamir",
  "initialized": true,
  "sealed": false,
  "t": 3,
  "n": 5,
  "progress": 0,
  "nonce": "",
  "version": "1.15.4",
  "build_date": "2023-12-04T17:45:28Z",
  "migration": false,
  "cluster_name": "vault-cluster-8a027631",
  "cluster_id": "ea05b13d-431a-8616-dc7d-1fecf4c1b5b8",
  "recovery_seal": false,
  "storage_type": "file"
}

By the end, we should get sealed: false . Now the vault is ready for use.

Let’s put the root token into environment VAULT_TOKEN:

$ export VAULT_TOKEN=hvs.LttSx7rwFQljGKXFoX78RdRw
# your token will be different

3.4) Load Plugins into Vault

Install graph as Secret Engine; and graphauth as Auth Method:

$ curl --header "x-vault-token: ${VAULT_TOKEN}" \
  --request POST \
  --data '{"sha256": "2026ce85c7dc98d6a233a6fb1c218053a7f6bdd670d414376291a3d44825539a","command": "graph"}' \
  $VAULT_ADDR/v1/sys/plugins/catalog/secret/graph

$ curl --header "x-vault-token: ${VAULT_TOKEN}" \
  --request POST \
  --data '{"sha256": "886543157bdb17d49c15f3dc3551b6e7d0653570ab2322ac2f935b928a940037","command": "graphauth"}' \
  $VAULT_ADDR/v1/sys/plugins/catalog/auth/graphauth

To use the plugins, we should mount them to URL paths. Let’s choose the same path names as the plugin names:

# this mounts graph to endpoint $VAULT_ADDR/v1/graph , as Secret Engine
$ curl --header "x-vault-token: ${VAULT_TOKEN}" \
  --request POST \
  --data '{"type": "graph"}' \
  $VAULT_ADDR/v1/sys/mounts/graph

# this mount graphauth to endpoint $VAULT_ADDR/v1/graphauth , as Auth Method
$ curl --header "x-vault-token: ${VAULT_TOKEN}" \
  --request POST \
  --data '{"type": "graphauth"}' \
  $VAULT_ADDR/v1/sys/auth/graphauth

Now, the vault is ready to delivery:

• Database REST APIs at VAULT_ADDR/v1/graph
• Authentication at VAULT_ADDR/v1/auth/graphauth

3.5) Security and Restart

Information like vaulted secrets and keys are encrypted, stored and protected persistently in directory vault-file .

Hence, if vault restarts, we shall unseal it using 3 of the stored keys, so as to verify the ownership. There is no need to initialize nor to enable plugins again, because they are already loaded into the system.

4. Project Automation

4.1) Config DCE to Serve httpd

The next step is to config DCE, using the following endpoints:

• database connection: /graph/config
• Definition of teams: /graph/config
• generation of no-code backend: /graph/config/generate
• login and authentication: /auth/graphauth/login

These tasks are summarized in the OpenAPI Description:

$ curl -H "X-VAULT-TOKEN: ${VAULT_TOKEN}" \
  -X GET $VAULT_ADDR/v1/graph/config/openapi

# {
    "openapi": "3.1.0",
    "info": {
        "version": "1.0.0",
        "title": "OpenAPI for Vault Database Configuration",
        "description": "HTTP API that gives you full access to Config and Authentication. All API routes are prefixed with /v1",
        "license": {
            "name": "MIT"
        }
    },
    "servers": [
        {
            "url": "/v1",
            "description": "Database Authentication and Configuration"
        }
    ],
    "paths": {
        "/auth/graphauth/login": {
...
        },
        "/graph/config": {
...
        },
        "/graph/config/generate": {
...

4.2) Code Automation

dcp is a CLI wrapping config APIs. We should use it to generate the first set of no-code programs.

No-code consists of many JSON files on server side. These JSON files will be interpreted as real Golang programs when push to server. Alternative, we can use Hashicorp’s HCL to represent no-code.

$ dcp
# Usage: PROGRAM genesis|push|pull

dcp has three sub-commands, genesis, pull and push. Command genesis generates no-code and pull them to working directory:

$ dcp genesis
# Usage: PROGRAM genesis
  -call_name string
     Auth statement e.g. 'SELECT userid, name FROM users WHERE email=? AND passwd=?'
  -data_source string
     data source name. REQUIRED
  -database string
     database name. REQUIRED
  -dir string
     Directory to save auto-generated config files. REQUIRED
  -driver string
     database driver name. REQUIRED
  -help
     Print this usage
  -hide
     If set, encrypt database parameters
  -launch
     If set, mounts plugins graph and graphauth.
  -meta string
     User meta data fields e.g. 'firstname, lastname, user_id'
  -team string
     Team name e.g. 'member'
  -usejson
     If set, use json format for configuration files.
  -user_id string
     User ID column e.g. 'user_id'
  -user_table string
     User table e.g. 'users'
  -vault_addr string
     Vault address, default to VAULT_ADDR in ENV
  -vault_token string
     Vault token, default to VAULT_TOKEN in ENV

Let’s run genesis without bells and whistles. Simply pass database driver type: mysql, user: root, password: change-me, host: host.docker.internal, port: 13306, and database name: classicmodels to server, and pull the generated no-code to the working directory /vault/data.

$ dcp genesis \
  -dir ./data \
  -driver mysql \
  -data_source "root:change-me@/classicmodels" \
  -database classicmodels \
  -vault_addr $VAULT_ADDR \
  -vault_token $VAULT_TOKEN \
  -usejson 

# &api.Secret{RequestID:"11bbd628-624b-3703-882d-3d6c4201dd3e", LeaseID:"", LeaseDuration:0, Renewable:false, Data:map[string]interface {}{"genesis":true}, Warnings:[]string(nil), Auth:(*api.SecretAuth)(nil), WrapInfo:(*api.SecretWrapInfo)(nil)}

A successful run should return {“config”:true} .

Let’s see the pulled files in data:

$ ls data/
#
config.json       customers  offices       orders    productlines
config.json.spec  employees  orderdetails  payments  products

4.3) OpenAPIs for Admin and Public

By default, two special teams are always generated during genesis: admin, a super-user team to access all endpoints with root token, and public, a public team without authentication. The OpenAPI path of team is VAULT_ADDR/v1/graph/model/{team}/openapi .

So the OpenAPI Description for admin can be retrieved from

$ curl -H "X-VAULT-TOKEN: ${VAULT_TOKEN}" \
  -X GET $VAULT_ADDR/v1/graph/model/admin/openapi

# {
    "openapi": "3.1.0",
    "info": {
        "version": "1.0.0",
        "title": "OpenAPI for Vault Database Content",
        "description": "HTTP API that gives you full access to Database.",
        "license": {
            "name": "MIT"
        }
    },
    "servers": [
        {
            "url": "/v1/graph/model/admin",
            "description": "Database Content Engine for team admin"
        }
    ],
    "paths": { 
        "/": {
            "get": {
                "summary": "List of components in admin",
                "operationId": "listadmin",
...
                }
            }
        },
        "/customers": {
            "$ref": "./customers/openapi_landing"
        },
        "/employees": {
            "$ref": "./employees/openapi_landing"
        },
        "/offices": {
            "$ref": "./offices/openapi_landing"
        },
...
    },
    "components": {
...
}

And here is the OpenAPI for component customers:

$ curl -H "X-VAULT-TOKEN: ${VAULT_TOKEN}" \
  $VAULT_ADDR/v1/graph/model/admin/customers/openapi_landing
  
# {
...
    "servers": [
        {
            "url": "/v1/graph/model/admin/customers",
            "description": "Database Content Engine for component customers of team admin"
        }
    ],
    "paths": { 
        "/": {
...
       "/{customerNumber}": {
...
}

4.4) Examples of Admin APIs

Let’s run the APIs to see how they work.

• LIST of tables

$ curl -H "X-VAULT-TOKEN: ${VAULT_TOKEN}" \
  -X GET $VAULT_ADDR/v1/graph/model/admin | jq

# {
  "generics": null,
  "landings": [
    "customers",
    "employees",
    "offices",
    "orderdetails",
    "orders",
    "payments",
    "productlines",
    "products"
  ]
}

• LIST of all rows in table customers

$ curl -H "X-VAULT-TOKEN: ${VAULT_TOKEN}" \
  -X GET $VAULT_ADDR/v1/graph/model/admin/customers | jq
# too long to display

• GET details of customerNumber=496

$ curl -H "X-VAULT-TOKEN: ${VAULT_TOKEN}" \
  -X GET $VAULT_ADDR/v1/graph/model/admin/customers/496 | jq

# {
  "action": "edit",
  "component": "customers",
  "data": [
    {
      "addressLine1": "Arenales 1938 3'A'",
      "city": "Auckland  ",
      "contactFirstName": "Tony",
      "contactLastName": "Snowden",
      "country": "New Zealand",
      "creditLimit": "110000.00",
      "customerName": "Kelly's Gift Shop",
      "customerNumber": 496,
      "phone": "+64 9 5555500",
      "salesRepEmployeeNumber": 1612
    }
  ],
  "incoming": {
    "customerNumber": "496"
  },
  "team": "admin"
}

Therefore, by passing in table names and their primary keys, we can get all table contents using the admin path.

4.5) Examples of Public APIs

Public team is open to all visitors without authentication. Because there is no role definition in genesis, public will get the same endpoints as admin. Try to run the same APIs as above but without token:

$ curl -X GET $VAULT_ADDR/v1/graph/model/public | jq
$ curl -X GET $VAULT_ADDR/v1/graph/model/public/customers | jq
$ curl -X GET $VAULT_ADDR/v1/graph/model/public/customers/496 | jq

5. Example of No-code Programming

Later, we decide to make following changes in the public APIs.

• only offices could be open to public.
• in offices, only is GET allowed. No POST, PUT nor DELETE, .
• LIST of offices shows only columns city, state and office code.

To do these, we make the following changes in the no-code JSON set.

5.1) Edit data/config.json.spec

This file controls what component can be served.

Words component and table could be mutually used, except that one table can have multiple components.

$ cat data/config.json.spec
...
         "public":  {
            "objectName":  "Team",
            "fields":  {
              "Colorfuls":  {
                "listStruct":  {
                  "listFields":  [
                    {
                      "objectName":  "Colorful",
                      "serviceName":  "public-customers"
                    },
                    {
                      "objectName":  "Colorful",
                      "serviceName":  "public-employees"
                    },
                    {
                      "objectName":  "Colorful",
                      "serviceName":  "public-offices"
                    },
                    {
                      "objectName":  "Colorful",
                      "serviceName":  "public-orderdetails"
                    },
                    {
                      "objectName":  "Colorful",
                      "serviceName":  "public-orders"
                    },
                    {
                      "objectName":  "Colorful",
                      "serviceName":  "public-payments"
                    },
                    {
                      "objectName":  "Colorful",
                      "serviceName":  "public-productlines"
                    },
                    {
                      "objectName":  "Colorful",
                      "serviceName":  "public-products"
                    }
                  ]
                }
...

Delete all the other components in public, except for public-offices,

$ cat data/config.json.spec
...
         "public":  {
            "objectName":  "Team",
            "fields":  {
              "Colorfuls":  {
                "listStruct":  {
                  "listFields":  [
                    {
                      "objectName":  "Colorful",
                      "serviceName":  "public-offices"
                    }
                  ]
                }
...

5.2) Edit data/offices/public-offices.json

This file controls which actions and parameter can be applied to offices for public.

Edit and leave only LIST (string topics) and GET (string edit). When LIST, show columns officeCode, city and state:

"actions": [
      {
        "actionName": "topics",
        "picked": ["officeCode", "city", "state"]
      },
      {
        "actionName": "edit"
      }
    ]

5.3) Push to Server

Use sub-command push to submit the modified no-code to server:

$ dcp push
Usage: PROGRAM genesis|push|pull
  -dir string
     Directory to save auto-generated config files. REQUIRED
  -help
     Print this usage
  -launch
     If set, mounts plugins graph and graphauth.
  -usejson
     If set, use json format for configuration files.
  -vault_addr string
     Vault address, default to VAULT_ADDR in ENV
  -vault_token string
     Vault token, default to VAULT_TOKEN in ENV

Let’s push:

$ dcp push \
  -dir ./data \
  -vault_addr $VAULT_ADDR \
  -vault_token $VAULT_TOKEN \
  -usejson

In the first terminal where vault is running, we should see numerous write to backyard messages.

Let’s confirm that the APIs have been updated so that public serves offices only:

$ curl -X GET \
  $VAULT_ADDR/v1/graph/model/public | jq

# {
  "generics": null,
  "landings": [
    "offices"
  ]
}

LIST of offices becomes:

$ curl -X GET \
  $VAULT_ADDR/v1/graph/model/public/offices | jq

#{
  "action": "topics",
  "component": "offices",
  "data": [
    {
      "city": "San Francisco",
      "officeCode": "1",
      "state": "CA"
    },
    {
      "city": "Boston",
      "officeCode": "2",
      "state": "MA"
    },
    {
      "city": "NYC",
      "officeCode": "3",
      "state": "NY"
    },
    {
      "city": "Paris",
      "officeCode": "4"
    },
    {
      "city": "Tokyo",
      "officeCode": "5",
      "state": "Chiyoda-Ku"
    },
    {
      "city": "Sydney",
      "officeCode": "6"
    },
    {
      "city": "London",
      "officeCode": "7"
    }
  ],
  "incoming": {},
  "team": "public"
}

which are expected.

6. Summary

We will discuss how to run genesis to build authentication teams and to serve customized REST APIs in the second part of this article.