Motivation

MySQL Router's HTTP Component supports authentication against a local file based credential storage (WL#12503).

User Stories

1. As a user of MySQL InnoDB Cluster I want to manage the accounts of the HTTP interface of all routers of a cluster from one central place.
2. As a user of MySQL InnoDB Cluster I want to use the REST APIs even if the communication to the cluster is later lost.

Goal

Allow to

• manage the accounts of the http component via the MySQL Shell
• authenticate against accounts stored in the metadata-servers of InnoDB Cluster.
• using the REST APIs even through communication to the metadata-servers is lost.

Background

In WL#13386 InnoDB Cluster metadata Version 2.0.0 introduced a table router_rest_accounts:

CREATE TABLE `router_rest_accounts` (
  `cluster_id` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `user` varchar(256) NOT NULL,
  `authentication_method` varchar(64) NOT NULL DEFAULT 'modular_crypt_format',
  `authentication_string` text CHARACTER SET ascii COLLATE ascii_general_ci,
  `description` varchar(255) DEFAULT NULL,
  `privileges` json DEFAULT NULL,
  `attributes` json DEFAULT NULL,
  PRIMARY KEY (`cluster_id`,`user`)
)

user

username of the HTTP client

authentication_method

modular_crypt_format for MCF style password hashes.

authentication_string

authentication string according to authentication_method

privileges

NULL means "all privileges".

attributes

unused

Design Requirements

DesignReq1

A HTTP client MUST be able to authenticate against accounts stored in the innodb-cluster metadata.

DesignReq2

Accounts in the innodb-cluster metadata MUST be manageable through SQL.

DesignReq3

Privileges of Accounts in the innodb-cluster metadata MUST be NULL to signal "all privileges".

DesignReq3

Accounts and their privileges MUST be cached on the router at runtime.

Security Requirements

SecReq1

Account Data MUST be stored securely in the metadata tables.

SeqReq2

Time until the auth-cache is invalidated MUST be configurable.

Functional Requirements

FuncReq1

router MUST authenticate successfully if username and password match the router_rest_accounts according to authentication_method and authentication_string

FuncReq2

router MUST support authenticating against caching_sha2_password style password hashes.

FuncReq3

router MUST refresh the auth-cache perdiodically after auth_cache_refresh_interval

FuncReq4

router MUST treat auth-cache as valid until auth_cache_ttl expires

FuncReq5

router MUST fail if auth_cache_ttl less than auth_cache_refresh_interval

FuncReq6

router MUST fail if auth_cache_refresh_interval is less than ttl

FuncReq7

Authentication failure MUST be reported to HTTP clients with HTTP Status 401.

FuncReq8

Authorization failures MUST be reported to HTTP client with HTTP Status 403.

Failure scenarios

FailReq1

router MUST fail authentication if the auth_cache_ttl expires.

FailReq2

router MUST fail authorization if the innodb-cluster metadata for the successfully authenticated account contains non-NULL privileges.

FailReq3

router MUST fail authentication if the innodb-cluster metadata is in schema-version before 2.0.0.

FailReq4

router MUST fail authentication if the authentication_string is not in caching_sha2_password format.

FailReq5

router MUST fail authentication if the authentication_method is not in modular_crypt_format.

FailReq6

router MUST fail authentication if HTTP username is longer than the stored length of the router_rest_accounts

Configuration

The http_auth_backend gets a new supported value for the backend field:

• metadata_cache

Like:

[http_auth_backend:name]
backend=metadata_cache

metadata_cache

metadata_cache gets new support options:

auth_cache_refresh_interval

• type: double
• default: 2
• range: 1ms..1hr
• description: time in seconds between auth-cache refresh attempts

If auth_cache_refresh_interval is smaller than the ttl, startup fails.

auth_cache_ttl

• type: double
• default: -1 (means infinite)
• range: -1, 1ms..1hr
• description: time in seconds until the cache becomes invalid if not refreshed.

If auth_cache_ttl is smaller than the auth_cache_refresh_interval, startup fails.