Open in app

Sign in

Write

Sign in

Tutorial: Database Content Engine Part II

Part 2 of 2: Serving Database REST APIs

Peter Bi
10 min readJul 19, 2023

In Part 1 of the article, we discussed that Hashicorp Vault is a httpd server so we can use a special plugin, Database Content Engine (DCE), to serve database REST APIs. The benefit is that the service is very secure and simple.

By the end of Part 1, we have installed Hashicorp Vault, the secret engine graph, the auth method graphauth, and MySQL database classicmodels. We can serve the default Admin and Public team APIs, described by the OpenAPI schema.

Here is the database schema:

database schema

In this second part, we will demo how to build different teams and their REST APIs.

7. Build Multiple Teams

An important feature in software development is to build role-based teams so each of them can login and access APIs in own domain.

Let’s build two teams, cust for customers and empl for employees:

  • cust: authenticated by column pair {contactLastName, postalCode} in table customers. A cust member is uniquely identified by customerNumber.
  • empl: authenticated by column pair {email, extension} in table employees. An empl member is uniquely identified by employeeNumber.

In real use case, we will have columns username and password in customers and employees.

7.1) No-Code Automation

Generate teams cust and empl, as well as the defaults admin and public, by

$ dcp genesis \
  -dir ./data \
  -driver mysql \
  -data_source "root:change-me@classicmodels" \
  -database classicmodels \
  -vault_addr $VAULT_ADDR \
  -vault_token $VAULT_TOKEN \
  -team cust/empl \
  -user_table customers/employees \
  -user_id customerNumber/employeeNumber \
  -call_name "SELECT customerNumber, customerName FROM customers WHERE contactLastName=? AND postalCode=?/SELECT employeeNumber, lastName, firstName FROM employees WHERE email=? AND extension=?" \
  -meta "customerNumber,customerName/employeeNumber,lastName,firstName" \
  -usejson

# &api.Secret{RequestID:"04effc88-04d1-58a7-9103-4cce5f0042bb", LeaseID:"", LeaseDuration:0, Renewable:false, Data:map[string]interface {}{"genesis":true}, Warnings:[]string(nil), Auth:(*api.SecretAuth)(nil), WrapInfo:(*api.SecretWrapInfo)(nil)}

where

  • dir: directory for pulled no-code programs. Note that dcp is working in container, so this is the current working directory.
  • team: name of team. Multiple names are separated by “/”.
  • user_table: table for user registration.
  • user_id: unique column, usually the primary key of the user table.
  • data_source: connection string specifying user name, password and host name etc. of database.
  • call_name; SQL statement for login.
    Case A. If SQL is a stored procedure, there needs to be 2 IN Parameters as username and password, and multiple OUT Parameters. Values of OUT Parameters will be assigned to variables listed in meta.
    Case B. If SQL is a SELECT statement, IN are search constraints and OUT are selected fields.
  • meta: list of variable names separated by comma.

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

7.2) ACL Policies

ACL (Access Control List) policy defines URL paths and capabilities that a team can access. Capabilities list, create, update and delete are mapped to http methods GET, POST, PUT, and DELETE. read is GET for single id.

A default set of ACL policies will be created during genesis.

Check ACL policies cust_policy.hcl and empl_policy.hcl, for cust and empl respectively, in directory data:

$ cat data/cust_policy.hcl
#
path "/graph/model/cust" {
 capabilities = ["read","list"]
}
path "/graph/model/cust/+" {
 capabilities = ["read","list","create","update"]
}
path "/graph/model/cust/+/*" {
 capabilities = ["read","delete"]
}
path "/auth/token/lookup-self" {
    capabilities = ["read"]
}
path "/auth/token/revoke-self" {
    capabilities = ["create"]
}

$ cat data/empl_policy.hcl
#
path "/graph/model/empl" {
 capabilities = ["read","list"]
}
path "/graph/model/empl/+" {
 capabilities = ["read","list","create","update"]
}
path "/graph/model/empl/+/*" {
 capabilities = ["read","delete"]
}
path "/auth/token/lookup-self" {
    capabilities = ["read"]
}
path "/auth/token/revoke-self" {
    capabilities = ["create"]
}

7.3) Team Login

Try to access the top URL endpoint of team cust:

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

# {"errors":["permission denied"]}

It is denied because of no authentication.

Now login to customer of contactLastName Schmitt and postalCode 44000:

$ curl -X POST \
  --data '{"team":"cust","username":"Schmitt","password":"44000"}' \
  $VAULT_ADDR/v1/auth/graphauth/login | jq

# {
  "request_id": "d89d46b2-7522-4655-fcd5-4c7f42efb154",
  "lease_id": "",
  "renewable": false,
  "lease_duration": 0,
  "data": null,
  "wrap_info": null,
  "warnings": null,
  "auth": {
    "client_token": "hvs.CAESICOog3bNQnUudkEoOD6i0x1EFtPjgepl2v4kCq4T_wpxGh4KHGh2cy5INkNtcWw1cWNITjg1QjMxMlNoQnpIdEY",
    "accessor": "7cpVy6k29ghKAyFI8ZsRSJ0x",
    "policies": [
      "cust_policy",
      "default"
    ],
    "token_policies": [
      "cust_policy",
      "default"
    ],
    "identity_policies": [
      "cust_policy"
    ],
    "metadata": {
      "customerName": "Atelier graphique",
      "username": "Schmitt"
    },
    "lease_duration": 36000,
    "renewable": true,
    "entity_id": "cdffe027-6b1b-ff80-5afc-8adf88d8b19c",
    "token_type": "service",
    "orphan": true,
    "mfa_requirement": null,
    "num_uses": 0
  }
}

The successful login has returned:

  • client_token: authentication token for accessing team APIs
  • cust_policy: ACL policy assigned to the client token
  • metadata: customer information in session
  • lease_duration: 10 hours is token’s TTL (Time To Live)

From now on, we can pass client_token in header X-Vault-Token in http requests, so as to access team-protected APIs.

Client_token is always bound to a unique user id in a team. You can treat it as an expression of user id of the session. You can use it to access your own data but not others, if Row-Level-Security is implemented properly.

7.4) Token Lookup

Later, we want to view token properties like expiration time.

Set up environment variable VAULT_CLIENT_TOKEN :

$ export VAULT_CLIENT_TOKEN=hvs.CAESIKxSpSS656NVvFGfQTSbqoTaZdu6Awk-5TZGzEMxCZEQGh4KHGh2cy40cmhpdFJRMUJLbjZwWjU1VHZGZ1E3Z3M

Run the lookup API to show token properties.

$ curl \
  --header "X-Vault-Token: ${VAULT_CLIENT_TOKEN}" \
  $VAULT_ADDR/v1/auth/token/lookup-self | jq

# return the same information as those in the login

7.5) Logout

To logout from the team, use the following API:

$ curl --header "X-Vault-Token: ${VAULT_CLIENT_TOKEN}" \
  -X POST \
  $VAULT_ADDR/v1/auth/token/revoke-self

8. OpenAPI Description

While ACL policy has defined where we can visit, it is OpenAPI Description that describes what are inside the URLs, and how to make request.

8.1) OpenAPI for Team

GET OpenAPI Description of cust by

$ curl -H "X-VAULT-TOKEN: ${VAULT_CLIENT_TOKEN}" \
  $VAULT_ADDR/v1/graph/model/cust/openapi

# {
...
    "servers": [
        {
            "url": "/v1/graph/model/cust",
            "description": "Database Content Engine for team cust"
        }
    ],
    "paths": { 
        "/": {
            "get": {
...
        },
        "/orders": {
            "$ref": "./orders/openapi_landing"
        },
        "/payments": {
            "$ref": "./payments/openapi_landing"
        },
        "/orderdetails/{ order_id }/{ order_sign }": {
            "$ref": "./orderdetails/openapi_generic"
        }
    },
    "components": {
        "schemas": {
            "Dashboard": {
                "type": "object",
                "properties": {
                    "landings": {
...
                    "generics": {
...
        },
        "securitySchemes": {
            "vaultToken": {
                "type": "apiKey",
                "in": "header",
                "name": "X-VAULT-TOKEN"
            }
        }
    },
...
}

where the LIST verb of cust, /v1/graph/model/cust, has been described explicitly. Please go to Section 9.1) for examples.

It also refers to OpenAPI schemes of components orders, payments and orderdetails:

"/orders": {
    "$ref": "./orders/openapi_landing"
},
"/payments": {
    "$ref": "./payments/openapi_landing"
},
"/orderdetails/{ parent_id }/{ parent_sign }": {
    "$ref": "./orderdetails/openapi_generic"
}

8.2) Landing Components

Components orders and payments are belong to type landings, marked by openapi_landing in the URL. In these components, the user id of team, custNumber, is present. Since user id is uniquely bound to the client token, we can access them directly by constraining the id in SQL.

Check the OpenAPI Description of orders :

$ curl -H "X-VAULT-TOKEN: ${VAULT_CLIENT_TOKEN}" \
  $VAULT_ADDR/v1/graph/model/cust/orders/openapi_landing

# {
...
    "servers": [
        {
            "url": "/v1/graph/model/cust/orders",
            "description": "Database Content Engine for component orders of team cust"
        }
    ],
    "paths": { 
        "/": {
            "get": {
...
            "post": {
...  
        },
        "/{orderNumber}": {
            "get": {
...
}

8.3) Generic Components

Components belong to generics does not have user id in their tables. For example, in database classicmodels, table orderdetails has no customerNumber. We can’t identify user’s rows by user id as in landing components.

In relational database, to guarantees the ownership of rows by users is called Row-Level-Security (RLS). DCE implements RLS by foreign key trust. If column is a foreign key referencing the primary key of a landing table, or of another generics table with verified RLS, we will use it as trusted constraint in REST verbs.

Foreign key trust is automatically generated during genesis. You can manually build it up in no-code files too.

In our demo database, orderdetails is a generics component. Field orderNumber is a foreign key referencing the primary key of orders, which is a landings table. So we can use orderNumber as constraint.

In order to run REST actions on orderdetails, we have to LIST or GET its parent table orders first, where a signature of orderNumber will be returned. We shall pass both the key and signature to REST action in orderdetails.

Without RLS, a malicious customer can pass any orderNumber to view other customer’s orderdetails.

Check OpenAPI of orderdetails by

$ curl -H "X-VAULT-TOKEN: ${VAULT_CLIENT_TOKEN}" \
  $VAULT_ADDR/v1/graph/model/cust/orderdetails/openapi_generic

# {
   ...
    "servers": [
        {
            "url": "/v1/graph/model/cust/orderdetails",
            "description": "Database Content Engine for component orderdetails of team cust"
        }
    ],
    "paths": { 
        "/{orderNumber}/{orderNumber_sign}": {
            "get": {
...
            "post": {
...
        "/{orderNumber}/{orderNumber_sign}/orderNumber/{orderNumber}/productCode/{productCode}": {
            "get": {
...
}

In summary, REST actions on generic components are:

  • LIST: /{parent_id}/{parent_signature}
  • GET and DELET: /{parent_id}/{parent_signature}/{primary id} . If the primary key consists of multiple columns, use format {parent_id}/{parent_signature}/PK-1/{PK-1}/…/PK-n/{PK-n}
  • POST, UPDATE and PATCH: / ; with payload containing {parent_id} and {parent_signature}It tells us that we should use orderNumber for the RLS measurement.

For the orderdetails table, parent_id is orderNumber.

9. Examples of REST APIs

9.1) Team Component List

LIST team-level components using the client token:

$ curl -H "x-vault-token: ${VAULT_CLIENT_TOKEN}" \
  -X GET $VAULT_ADDR/v1/graph/model/cust | jq

# {
  "generics": [
    "orderdetails"
  ],
  "generics_map": {
    "orderdetails": [
      "orderNumber",
      "orderNumber_sign"
    ]
  },
  "landings": [
    "orders",
    "payments"
  ]
}

Here we see that team cust has 2 landings components orders and payments, and one generics component orderdetails.

9.2) Component orders

LIST rows in orders belong to this user id:

$ curl -H "x-vault-token: ${VAULT_CLIENT_TOKEN}" \
  -X GET $VAULT_ADDR/v1/graph/model/cust/orders | jq

# {
  "action": "topics",
  "component": "orders",
  "data": [
    {
      "customerNumber": 103,
      "orderDate": "2003-05-20",
      "orderNumber": 10123,
      "orderNumber_sign": "1b03601f06f85741cf70865c88646385bdabe38f",
      "requiredDate": "2003-05-29",
      "shippedDate": "2003-05-22",
      "status": "Shipped"
    },
    {
      "customerNumber": 103,
      "orderDate": "2004-09-27",
      "orderNumber": 10298,
      "orderNumber_sign": "96eb124dbb293ef4ec5033e667d75c5b8077f08d",
      "requiredDate": "2004-10-05",
      "shippedDate": "2004-10-01",
      "status": "Shipped"
    },
    {
      "customerNumber": 103,
      "orderDate": "2004-11-25",
      "orderNumber": 10345,
      "orderNumber_sign": "3687ebc7a8fd6e9983f1a3d71b3ae24b16f0eded",
      "requiredDate": "2004-12-01",
      "shippedDate": "2004-11-26",
      "status": "Shipped"
    }
  ],
  "incoming": {
    "customerNumber": "103"
  },
  "team": "cust"
}

GET detail of orderNumber 10123:

$ curl -H "x-vault-token: ${VAULT_CLIENT_TOKEN}" \
  -X GET $VAULT_ADDR/v1/graph/model/cust/orders/10123 | jq

# {
  "action": "edit",
  "component": "orders",
  "data": [
    {
      "customerNumber": 103,
      "orderDate": "2003-05-20",
      "orderNumber": 10123,
      "orderNumber_sign": "1b03601f06f85741cf70865c88646385bdabe38f",
      "requiredDate": "2003-05-29",
      "shippedDate": "2003-05-22",
      "status": "Shipped"
    }
  ],
  "incoming": {
    "customerNumber": "103",
    "orderNumber": "10123"
  },
  "team": "cust"
}

9.3) Generic Component orderdetails

The customer has 3 orders with orderNumber and signature:

"orderNumber":10123,"orderNumber_sign":"301782339d8cba83f3b75732d86d1a5a2765d39e"
"orderNumber":10298,"orderNumber_sign":"92027fbefcdd25134cb0d0ead000be7e0ecad7d5"
"orderNumber":10345,"orderNumber_sign":"ef5d2346eed2bda08e69497a46c9a98cd6f83652"

LIST rows of orderNumber 10123:

$ $CURL -H "x-vault-token: ${VAULT_CLIENT_TOKEN}" \
  $VAULT_ADDR/v1/graph/model/cust/orderdetails/10123/301782339d8cba83f3b75732d86d1a5a2765d39e/orderNumber | jq

# {
  "action": "topics",
  "component": "orderdetails",
  "data": [
    {
      "orderLineNumber": 2,
      "orderNumber": 10123,
      "orderNumber_sign": "18935186ad78f446d64ed87bb66f5f25e8413e2f",
      "priceEach": "120.71",
      "productCode": "S18_1589",
      "quantityOrdered": 26
    },
    {
      "orderLineNumber": 3,
      "orderNumber": 10123,
      "orderNumber_sign": "18935186ad78f446d64ed87bb66f5f25e8413e2f",
      "priceEach": "114.84",
      "productCode": "S18_2870",
      "quantityOrdered": 46
    },
    {
      "orderLineNumber": 4,
      "orderNumber": 10123,
      "orderNumber_sign": "18935186ad78f446d64ed87bb66f5f25e8413e2f",
      "priceEach": "117.26",
      "productCode": "S18_3685",
      "quantityOrdered": 34
    },
    {
      "orderLineNumber": 1,
      "orderNumber": 10123,
      "orderNumber_sign": "18935186ad78f446d64ed87bb66f5f25e8413e2f",
      "priceEach": "43.27",
      "productCode": "S24_1628",
      "quantityOrdered": 50
    }
  ],
  "incoming": {
    "customerNumber": "103",
    "orderNumber": "10123",
    "orderNumber_sign": "8fb3d901e19f6698ede44ce9b2a465e7fef65cea"
  },
  "team": "cust"
}

GET detail of single row using the PK of orderdetails, which is multi-column orderNumber 10123 and productCode S18_1589:

$ $CURL -H "x-vault-token: ${VAULT_CLIENT_TOKEN}" \
  $VAULT_ADDR/v1/graph/model/cust/orderdetails/10123/301782339d8cba83f3b75732d86d1a5a2765d39e/orderNumber/10123/productCode/S18_1589 | jq

# {
  "action": "topics",
  "component": "orderdetails",
  "data": [
    {
      "orderLineNumber": 2,
      "orderNumber": 10123,
      "orderNumber_sign": "18935186ad78f446d64ed87bb66f5f25e8413e2f",
      "priceEach": "120.71",
      "productCode": "S18_1589",
      "quantityOrdered": 26
    }
  ],
  "incoming": {
    "customerNumber": "103",
    "orderNumber": "10123",
    "orderNumber_sign": "8fb3d901e19f6698ede44ce9b2a465e7fef65cea",
    "productCode": "S18_1589"
  },
  "team": "cust"
}

POST a new record to the table:

$ curl -H "x-vault-token: ${VAULT_CLIENT_TOKEN}" \
  -X POST \
  --data @post_ordersdetails.json \
  $VAULT_ADDR/v1/graph/model/cust/orderdetails

# {
  "action": "update",
  "component": "orderdetails",
  "data": [
    {
      "orderLineNumber": 99,
      "orderNumber": 10123,
      "orderNumber_sign": "c91646251d925b33caa18fb23a2e20bd76cb81ee",
      "priceEach": "111.11",
      "productCode": "S18_1589",
      "productCode_sign": "5ac3b116965bfd88c033fdd48f0e4325de1e8307",
      "quantityOrdered": 22
    }
  ],
  "incoming": {
    "customerNumber": "103",
    "orderLineNumber": 99,
    "orderNumber": 10123,
    "orderNumber_sign": "70a227724daa79b163457d1cea5075fa87e7cd1b",
    "priceEach": "111.11",
    "productCode": "S18_1589",
    "quantityOrdered": 22
  },
  "team": "cust"
}

with payload post_ordersdetails.json:

{
      "orderNumber": 10123,
      "orderNumber_sign": "8fb3d901e19f6698ede44ce9b2a465e7fef65cea",
      "priceEach": "111.11",
      "productCode": "S18_1589",
      "quantityOrdered": 22
}

9.4) Rich Features in REST APIs

DCE has implemented many advanced features in APIs to help processing database content. Some of the features are already described by the OpenAPI schemes. Others need works in no-code programs.

Here are some examples.

9.4.1) Cross Multiple Tables

By default, REST verbs would act only on the current table. But we can adjust it to act on parent and children components altogether by passing query argument expn=1.

Let’s try to GET orders with expn=1. In the following API, child component orderdetails is now included in every order.

$ curl -H "x-vault-token: ${VAULT_CLIENT_TOKEN}" \
  $VAULT_ADDR/v1/graph/model/cust/orders/10298?expn=1 | jq

# {
  "action": "edit",
  "component": "orders",
  "data": [
    {
      "customerNumber": 103,
      "orderDate": "2004-09-27",
      "orderNumber": 10298,
      "orderNumber_sign": "1e5ac78914e430a525706e8bc10085fc96977087",
      "orderdetails": [
        {
          "orderLineNumber": 1,
          "orderNumber": 10298,
          "orderNumber_sign": "6913d4a2094da3f505b04f0a086804e9510f3160",
          "priceEach": "105.86",
          "productCode": "S10_2016",
          "productCode_sign": "00aaca97924fbb0df297bb315138f43396cfda15",
          "quantityOrdered": 39
        },
        {
          "orderLineNumber": 2,
          "orderNumber": 10298,
          "orderNumber_sign": "6913d4a2094da3f505b04f0a086804e9510f3160",
          "priceEach": "60.57",
          "productCode": "S18_2625",
          "productCode_sign": "636675fde777f526f0606b12f5cba4e284f7337c",
          "quantityOrdered": 32
        }
      ],
      "requiredDate": "2004-10-05",
      "shippedDate": "2004-10-01",
      "status": "Shipped"
    }
  ],
  "incoming": {
    "customerNumber": "103",
    "expn": "1",
    "orderNumber": "10298"
  },
  "team": "cust"
}

DCE internally uses a stopper object to control how to interact with parent and child components.

You are open to write own stoppers in no-code.

9.4.2) Selected Fields

Sometimes, GET or LIST returns too many columns than needed. Client can request a short list by passing query fields =column1,column2,… :

$ curl -H "x-vault-token: ${VAULT_CLIENT_TOKEN}" \
  "$VAULT_ADDR/v1/graph/model/cust/orders/10298?fields=orderDate,orderNumber" | jq

# {
  "action": "edit",
  "component": "orders",
  "data": [
    {
      "orderDate": "2004-09-27",
      "orderNumber": 10298,
      "orderNumber_sign": "1e5ac78914e430a525706e8bc10085fc96977087"
    }
  ],
  "incoming": {
    "customerNumber": "103",
    "fields": "orderDate,orderNumber",
    "orderNumber": "10298"
  },
  "team": "cust"
}

9.4.3) Pagination

Pagination is implemented in every LIST, if passing pagesize and optionally pageno, in query.

LIST with pagesize 2 inorders: (note there are totally 3)

$ curl -H "x-vault-token: ${VAULT_CLIENT_TOKEN}" \
  "$VAULT_ADDR/v1/graph/model/cust/orders?pagesize=2" | jq
 
# {
  "action": "topics",
  "component": "orders",
  "data": [
    {
      "customerNumber": 103,
      "orderDate": "2003-05-20",
      "orderNumber": 10123,
      "orderNumber_sign": "70a227724daa79b163457d1cea5075fa87e7cd1b",
      "requiredDate": "2003-05-29",
      "shippedDate": "2003-05-22",
      "status": "Shipped"
    },
    {
      "customerNumber": 103,
      "orderDate": "2004-09-27",
      "orderNumber": 10298,
      "orderNumber_sign": "1e5ac78914e430a525706e8bc10085fc96977087",
      "requiredDate": "2004-10-05",
      "shippedDate": "2004-10-01",
      "status": "Shipped"
    }
  ],
  "incoming": {
    "customerNumber": "103",
    "pageno": 1,
    "pagesize": "2"
  },
  "team": "cust"
}

And pageno 2:

$ curl -H "x-vault-token: ${VAULT_CLIENT_TOKEN}" \
  "$VAULT_ADDR/v1/graph/model/cust/orders?pagesize=2&pageno=2" | jq

# {
  "action": "topics",
  "component": "orders",
  "data": [
    {
      "customerNumber": 103,
      "orderDate": "2004-11-25",
      "orderNumber": 10345,
      "orderNumber_sign": "8c39193df3f7960ea011dc8efb6d330d64fc30a7",
      "requiredDate": "2004-12-01",
      "shippedDate": "2004-11-26",
      "status": "Shipped"
    }
  ],
  "incoming": {
    "customerNumber": "103",
    "pageno": "2",
    "pagesize": "2"
  },
  "team": "cust"
}

By default, pagination will not reveal total numbers of rows or pages for security reason. You can change the behavior bytotalforce = -1 in no-code.

10. Summary

In this article, we have demonstrated database REST API service using Hashicorp Vault plugin DCE. The service is powerful, yet very easy to use.

DCE allows us to build multiple authentication teams. It protects data to team members not only at authentication but also table row levels.

DCE adopts the security foundation of Hashicorp products. It is one of the most secure API services in internet.

Database
API
Nocode
Hashicorp
Vault

--

--

Peter Bi

Written by Peter Bi

3 Followers

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams