MySQL

Setting the Service mode to MYSQL enables it to operate in application-layer aware MySQL mode. This mode enables you to provide secretless access for Users to the protected upstream MySQL-based server without having to share the upstream's password. This mode also provides you with clear application-layer aware visibility where your MySQL sessions and queries are logged and audited in real-time.

note

Read more about access to MYSQL from a User perspective here. You can also read more detailed guides here for generic databases behind NAT and here for SaaS PlanetScale MySQL-based databases.

Secretless Access

Secretless access enables you to provide secretless access for authorized Users to MYSQL-based Service by automatically injecting passwords to authenticate to the upstream MySQL database, and force the User to connect to the upstream as a specific user, possibly with a certain role corresponding to specific permissions, to a specific database.

First, you need to create a Secret to store the password of your upstream SSH server (read more here) as follows:

octeliumctl create secret mariadb-password
# OR via a --value flag
octeliumctl create secret --value <PASSWORD> mariadb-password
# OR via a --file flag
octeliumctl create secret --file /PATH/TO/PASSWORD mariadb-password

Now, you define your Service as follows:

kind: Service
metadata:
  name: mariadb
spec:
  mode: MYSQL
  port: 3306
  config:
    upstream:
      url: mysql://address-to-db
    mysql:
      user: root
      database: mysql
      auth:
        password:
          fromSecret: mariadb-password

Now, the above configuration forces the User to authenticate as root with the password value set in the Secret mariadb-password and connect to the database mysql regardless of the information supplied by the downstream.

note

For now, authorized Users have to use an empty password value to access the Service.

note

For internal/private MySQL upstreams behind NAT, you need to remotely serve them via a connected octelium client or container as discussed here.

TLS

If the upstream database is serving over TLS, you have to use the isTLS boolean field as follows:

kind: Service
metadata:
  name: mariadb
spec:
  mode: MYSQL
  port: 3306
  config:
    upstream:
      url: mysql://address-to-db
    mysql:
      user: root
      database: mysql
      auth:
        password:
          fromSecret: mariadb-password
      isTLS: true

Access Control

You can control access based on the MySQL request information. Such information are stored in ctx.request.mysql where it contains the username and database. Here is a detailed example of a inline Policy that controls access based on MySQL-specific information:

kind: Service
metadata:
  name: svc1
spec:
  mode: MYSQL
  port: 1234
  config:
    upstream:
      url: mysql://address-to-db
    # rest of the config
  authorization:
    inlinePolicies:
      - spec:
          rules:
            - effect: ALLOW
              condition:
                any:
                  of:
                    - match: ctx.request.mysql.connect.user == "db-user-1"
                    - match: ctx.request.mysql.connect.database == "db-01"

note

You do not actually need to control access by checking against the database user since you already override the database user in your Service configuration as illustrated above regardless of the database user value provided by the User. You can also use dynamic configuration in order to map different databases and/or database users to different Users under different conditions. You can read more about dynamic configuration here

Dynamic Configuration

You can use dynamic configuration (read more about dynamic configuration here) to, for example, route to different upstreams or different database users with different privileges and roles (e.g. more privileged Users can automatically delete tables or databases while other Users cannot do so) based on identity and/or context. Here is an example:

metadata:
  name: example-svc
spec:
  mode: MYSQL
  dynamicConfig:
    configs:
    - name: prod
      upstream:
        url: mysql://address
      mysql:
        user: prod
        database: prod-db
        auth:
          password:
            fromSecret: prod-password
    - name: dev
      upstream:
        url: mysql://address
      mysql:
        user: dev
        database: dev-db
        auth:
          password:
            fromSecret: dev-password
    rules:
    - condition:
        match: '"prod" in ctx.user.spec.groups'
      configName: prod
    - condition:
        matchAny: true
      configName: dev

Visibility

The Service emits access logs in real time to the audit collector. Each log provides MySQL application-layer aware information about the request such as the command type, the query details, etc.... Here is an example:

{
  "apiVersion": "core/v1",
  "kind": "Log",
  "metadata": {
    // Omitted for the sake of brevity of the example
  },
  "entry": {
    "service": {
      "info": {
        "common": {
          "status": "ALLOWED"
          // Omitted for the sake of brevity of the example
        },
        "mysql": {
          "query": {
            "query": "CREATE DATABASE db01;"
          },
          "type": "QUERY"
        }
      }
      // Omitted for the sake of brevity of the example
    }
  }
}

As you can see in the above example, the type of this MYSQL access Log is a QUERY. The MYSQL mode has currently 8 Log types: SESSION_START, SESSION_END, QUERY, PARSE, CLOSE, EXECUTE, BIND, FUNCTION_CALL and some of these types include different detailed information according to their type. You can read more in the API reference.