Direct MySQL TLS and HTTPS application access

The two interfaces share one MySQL/MariaDB schema, not replicated stores. Production endpoints are intended to be asi-database-node-api.arybit.co.ke:3306 (MySQL wire protocol over TLS) and https://asi-database-node-api.arybit.co.ke:443 (HTTPS JSON). Local disposable testing is complete for the documented subset. Public MySQL production deployment and actual PHP consumer integration remain incomplete.

flowchart LR
    PHP[Authorized PHP service] -->|MySQL protocol + verified TLS :3306| DIRECT[TLS-only execute-only DB identity]
    PHP -->|HTTPS :443| API[FastAPI service identity and scopes]
    DIRECT --> ROUTINES[Tenant-bound stored routines]
    API --> SERVICE[Shared application transaction layer]
    SERVICE --> ROUTINES
    SERVICE --> DB[(Authoritative MySQL / MariaDB schema)]
    ROUTINES --> DB
    ADMIN[Private migration administrator] --> DB

The HTTPS path retains all documented user/authentication/session/reset operations. SQL and transaction orchestration live in core/application_service.py rather than HTTP route handlers. Lookup and profile update use the same stored routines as the direct path. Both observe asi_users and the same unique keys and audit table. A database outage affects both; restarting FastAPI does not restart MySQL.

Direct scope and database authorization

The direct interface deliberately has no base-table grants. Its prepared CALL operations are:

A server-owned asi_direct_accounts row maps an individually authenticated database username to one tenant/service, capabilities, expiry and enabled state. The wrappers use USER(), which identifies the authenticated session login even in a definer routine; CURRENT_USER() would identify the definer and is deliberately not used. No caller tenant, arbitrary routine name or SQL expression is accepted by the PHP client. Wrapper EXECUTE grants do not grant EXECUTE on internal asi_user_lookup or asi_user_rename. Direct users cannot query tables, select another tenant, mutate credentials/scopes, assign roles, issue DDL or access mysql.user.

The routine definer is a retained non-root schema owner. It needs only the reviewed schema/routine/table permissions; it is not a direct consumer account. With MySQL binary logging enabled, installing the audit trigger requires elevated migration privileges. Use a migration administrator and DB_ROUTINE_DEFINER=owner@host; do not disable binary logging or log_bin_trust_function_creators checks to pass. Do not delete/rename the definer account while routines/triggers depend on it.

Lookup/profile routines do not commit caller transactions. The rename UPDATE and its profile-audit trigger are one atomic statement; a PDO transaction can group multiple supported calls and roll back both data and audit. HTTPS groups its mutation and service/request audit in the application pool transaction. Credential reads are explicitly standalone: they commit an audit record before returning material so a caller rollback cannot erase the disclosure event. The PHP client refuses credentialContext within an open transaction; raw SQL callers must respect this documented transaction boundary. Other authentication lifecycle operations are available through HTTPS; they are not advertised as direct stored procedures. No existing PHP SQL has been verified, and unrestricted SQL access is not a workaround.

Accounts, TLS and PHP clients

Use separate direct identities for asi-authenticate-node-api, asi-core-node-api and any other explicitly approved service, plus a separate HTTPS runtime DB principal, service API keys and migration/backup principals. A protected administrator env can provision a new execute-only TLS account without altering existing users:

python deploy-database-api/ops/provision-direct-account.py --env-file /secure/direct-admin.env --username asi_authenticate_direct_v2 --source-ip any --tenant REVIEWED_TENANT_UUID --service asi-authenticate-node-api --allow-update --output /secure/auth-direct-v2.env

--source-ip any deliberately creates a password-authenticated TLS-only % account for public-IP mobility, not an anonymous account. An exact approved IP may instead be supplied. Passwords are random and written exclusively to mode-0600 output. Accounts begin locked and are unlocked only after mapping/grants complete. Failed provisioning can leave a locked account and inert secret file requiring review. Credential context additionally requires --allow-credentials and the dedicated service name. Do not grant that capability to asi-core-node-api.

The CLI provisions new accounts only. Rotate using a new username, switch the consumer, verify it, then disable the old mapping and lock the old DB account under a reviewed administrator change. No existing production password was changed. A disabled/expired mapping rejects subsequent procedure calls; locking the account also prevents new logins. Decide separately whether to terminate existing sessions. Never put passwords into shell command-line arguments, browser assets or logs.

clients/php/DirectDatabase.php uses PDO MySQL with native prepared statements, verified CA/hostname and an explicit negotiated-cipher check. It supports the three named routines and a transaction callback. It validates host, port, database name, timeouts and replica consistency; no unverified read endpoint is used. Configure it from the direct namespace in .env.example. Hostnames have no https:// prefix. Statement/socket limits are bounded; MariaDB uses max_statement_time and MySQL uses MAX_EXECUTION_TIME for SELECT, with a separate lock-wait limit. These are not a universal server execution deadline for every MySQL write.

clients/php/DatabaseApi.php is independent: it accepts only a configured HTTPS origin, sends a separate service token, refuses redirects, validates returned user schemas, bounds response size/timeouts, and emits safe exceptions. Its public methods cover user lookup/rename; other HTTPS endpoints remain documented but are not claimed as PHP client methods. There is no silent direct/HTTP failover and no automatic write retry. Both libraries require PHP 8.1+ with PDO MySQL/cURL; tests used a pinned PHP 8.3 image. The actual consumer repository remains unavailable.

Listener, exposure and certificate operations

See the concrete public network proposal. Public TCP 3306 is explicitly requested; only individual TLS accounts may use it. No new NSG or host rule was applied. Require both server secure transport and account REQUIRE SSL, a full valid chain and a SAN matching the database hostname. A private NIC bind may serve the Azure public NAT address without binding every interface. The API can use the same verified hostname via reviewed split DNS to the private address; changing DB_HOST to an IP without a matching IP SAN is not a valid fix.

Renew certificates through the existing domain-control/ACME workflow. Copy the renewed chain/key into a database-service-owned directory with key mode 0600/0640; do not grant the database service broad access to /etc/letsencrypt private keys. Validate chain, SAN, dates and key pairing with the plan tool before promotion. Keep a protected previous pair for rollback, install both files, then use the selected engine's supported TLS reload or a coordinated database restart. Test new connections with hostname verification and inspect cipher negotiation after renewal. A database restart may interrupt both paths; an API-only restart must not. No certificate was issued, copied or reloaded on production in this task.

Port 3306 is for MySQL wire protocol, never HTTPS. Keep administrator DB accounts local/private, and expose no MySQL administration UI or X protocol port publicly. The generated Nginx /setup-api/ location now allows loopback only and denies other sources; operators need a pinned SSH tunnel. Static docs/health remain public.

Verification and limits

docker build -f tests/dual/Dockerfile.php -t asi-audit-php .
docker build -f tests/dual/Dockerfile.api -t asi-audit-api .
python tools/test-dual-interface.py

The disposable harness installs all three auth migrations, runs operation tests through TLS as a restricted runtime principal, provisions a second service using the actual CLI, and runs PHP against a real MySQL/MariaDB listener and real HTTPS FastAPI listener. It checks direct-to-HTTPS and HTTPS-to-direct changes, commit and rollback, denied base-table/admin access, tenant isolation, parameter injection, invalid credentials, untrusted CA, hostname mismatch, expired DB certificates and plaintext rejection. HTTPS redirect traps verify no credential forwarding; invalid response types are rejected. Test containers/volumes are removed in finally.

Production external valid-user acceptance, genuine PHP application integration, new-schema backup restoration and engine-specific authentication-abuse controls remain prerequisites. No claim that direct SQL inherits HTTP scopes is made: database capabilities/grants enforce the direct path; service scopes enforce HTTPS.