Skip to content

PLANETSCALE 2022-07-27 Tier 3

Read original ↗

PlanetScale — Behind the scenes: How we built Password Roles

Summary

Phani Raju (PlanetScale) describes how PlanetScale shipped per-password roles (read-only, write-only, read/write, admin) on top of Vitess's native table-ACL mechanism — and why the native mechanism, designed for statically-configured Vitess deployments, was a bad fit for a multi-tenant managed-database product. Vitess's out-of-the-box authorization reads a JSON configuration file from each vttablet pod's local disk, with periodic reload controlled by --table-acl-config-reload-interval. The file maps MySQL usernames to readers / writers / admins groups over table-name prefixes. That model assumes a known, slow-moving set of users, a small number of ACL files (one per Vitess cluster, not one per customer database), and a maintenance-scheduled update cadence — none of which apply when every PlanetScale customer mints and revokes their own passwords on demand. Rather than push per-customer ACL state onto every vttablet pod, PlanetScale keeps the ACL file identical across every customer database — hard-coding only three synthetic usernames: planetscale-reader, planetscale-writer, planetscale-admin. Customer passwords, their roles, and both SHA-1 and SHA-2 hashes live in an external credential database. Incoming connections hit a user query frontend service that authenticates the password, looks up the role, and then rewrites the security principal on the fly to one of the three synthetic usernames before the query reaches vttablet. The static ACL file never changes; the dynamism lives entirely in the credential store and the frontend's rewrite step.

Key takeaways

  1. Vitess table-ACL is a static-config mechanism — vttablet reads a JSON file from disk at boot and optionally reloads it on --table-acl-config-reload-interval. The file declares table_groups, each with table_names_or_prefixes, readers, writers, and admins arrays of usernames. "% means all tables." This file is the single source of truth for who can do what to which tables, and MySQL-wire-protocol usernames are what get checked against it. (Source: sources/2026-04-21-planetscale-behind-the-scenes-how-we-built-password-roles)

  2. Four orthogonal reasons the native mechanism fails for a managed-database product: (a) user story breaks under timed refresh — "Our user story of 'Customers can create passwords with roles and they work immediately' prevents the dependence on a timed refresh loop on the vttablets." Refresh-interval latency ≥ time-to-credential-works, which is unacceptable if the guarantee is immediate. (b) per-pod race condition — "might not be able to update all vttablet pods at once, leading to a race condition where a given credential might be admin on one pod and reader on another." Inconsistent ACL state across replicas of the same logical database. (c) pod-restart has no authoritative source — "If a pod goes down and needs to be restarted, we don't have an external ACL store to figure out what the ACL state for each database should be before bringing it up again." Local-file state is lost with the pod. (d) per- customer state explosion — "We'd need to maintain a separate set of state for each customer database that cannot be common to all PlanetScale databases." The scalar-value of a single static file per cluster doesn't survive multi- tenancy. All four are framed as reasons not to push ACL state to the pod.

  3. Canonical fit-for-static-config profile. The post states the three customer profiles the static-file approach is great for — and by negation identifies PlanetScale's anti- profile: "Pre-defined set of users" (well-known, slow-moving), "Small set of ACL configuration files that are custom to each of their Vitess clusters" (not per-customer), "Can plan for a maintenance schedule when updating this file on the pod/shared volume." This is a rare case of a post explicitly drawing the line where a native mechanism's assumptions break — useful for the wiki because it names the axis (customer cardinality × update cadence × maintenance-schedule tolerance) along which file-based authz stops scaling.

  4. External credential store as canonical fix. Per password the credential DB stores four fields: Display name, Role, password_sha1_hash, password_sha2_hash (both hash flavours kept presumably for MySQL client-auth-plugin backward compatibility — mysql_native_password uses SHA-1, caching_sha2_password uses SHA-256; the post doesn't spell this out but the field presence is unambiguous). The Role column selects which of the three synthetic Vitess usernames the principal gets rewritten to. Creates and revocations touch only this table; no pod ever reloads a file. (Source: sources/2026-04-21-planetscale-behind-the-scenes-how-we-built-password-roles)

  5. user query frontend as the rewrite chokepoint. "If you create a write-only password and connect to your PlanetScale database, the query hits the user query frontend, which is a service responsible for all user-facing functionality for PlanetScale databases." This is the service that knows about the credential DB; vttablet only ever sees one of three fixed principals. The ACL file is therefore universal across all PlanetScale databases — one file, three usernames, shipped identically to every pod — and per-customer dynamism is entirely decoupled from the data-plane authz check. This shape is the external-credential-store-with-principal-rewrite pattern.

  6. Four corresponding architectural wins map 1:1 to the four failure modes. (i) Instant credential effect — "a dynamic user credential store allows us to create/delete user mappings to roles instantly, without the need for a refresh interval." (ii) Pod-independent state — "By mapping all PlanetScale users' roles to the username from the Vitess ACL configuration, we can do an 'on the fly' rewrite of the security principal so that connections to the database get the right access levels." (iii) Restart- safe ACL — "Since all authentication and authorization data is stored on an external data store, all pods that we create for a database will have the same ACL state." (iv) Universal ACL config — "Since the base ACL configuration is the same across all PlanetScale databases, debugging and fixing any issues with ACL enforcement is simplified." Static file becomes an invariant, not a knob.

Systems extracted

  • systems/vitess — host platform whose table-ACL mechanism is being consumed. New axis: authorization / table ACL is a seventh distinct Vitess-internals subsystem covered on the wiki (after expression eval, data motion, query routing / transactional writes, load admission / throttler, fork management).
  • systems/vitess-table-acl — canonical wiki page for the static-config table-ACL mechanism on vttablet, including the JSON schema (table_groups, table_names_or_prefixes, readers, writers, admins) and the --table-acl-config-reload-interval refresh knob.
  • systems/planetscale-user-query-frontend — new canonical wiki page for the PlanetScale-side service that authenticates per-customer passwords, looks up roles in the credential DB, and rewrites the security principal to one of three synthetic Vitess usernames before the query reaches vttablet.
  • systems/planetscale — extended with the per-password roles product capability and the external-credential-store shape underneath.
  • systems/mysql — MySQL wire protocol carries the principal that gets rewritten; client-auth-plugin backward-compat is the likely explanation for storing both SHA-1 and SHA-256 hashes per password.

Concepts extracted

  • concepts/table-group-acl — the Vitess model of grouping tables (by name or prefix) and attaching read / write / admin user lists to each group, as a function evaluated on each query against the connection's authenticated principal.
  • concepts/on-the-fly-security-principal-rewrite — the design primitive of translating a dynamic per-customer credential into a small fixed set of synthetic principals before the query reaches the authz check, so that the downstream check can stay static.
  • concepts/external-credential-store — hosting authentication + authorization state in a dedicated database rather than on the data-plane pods, decoupling credential lifecycle from data-plane restart / rolling update.

Patterns extracted

  • patterns/external-credential-store-with-principal-rewrite — canonicalise this post's architectural shape as a reusable pattern for managed-multi-tenant deployments of systems with static-config authz: keep the downstream authz file invariant across tenants, store per-tenant credential + role state in an external DB, and rewrite the principal at an ingress service before the data-plane authz check runs.

Operational numbers

The post is a design-disclosure piece — no production numbers (pod count, credential-DB QPS, frontend p99 rewrite overhead, number of passwords per database, failure-mode telemetry) are quoted. The architecture claims are therefore mechanism-level, not performance-level.

Caveats

  • Refresh-interval failure mode asserted, not measured. The post argues file-based refresh is incompatible with "credentials work immediately", but doesn't quote a measured refresh-latency distribution or a specific user story where file-based ACL fell short.
  • Hash-pair rationale implicit. Both password_sha1_hash and password_sha2_hash are stored, but the post doesn't explain why. The natural reading is mysql_native_password (SHA-1) vs caching_sha2_password (SHA-256) client-auth-plugin backward compatibility, but this is inference.
  • user query frontend is named, not architected. Its placement in the request path, its relationship to VTGate, its replication / failover model, and how the credential-DB lookup plays against cache coherence or connection-pooling are all left unspecified.
  • No revocation-latency disclosure. The post says create/revoke is "instant" because state lives in the credential DB, but doesn't describe how in-flight connections (which are long-lived MySQL sessions) are affected when a password's role changes or the password is revoked. Does the frontend re-check on every query, or is the principal computed at connection-open and cached for the session's lifetime? This matters for the revocation SLA.
  • 2022 product-launch post. Architecturally substantive but written at the "here's what we built" altitude. Later PlanetScale posts would likely have more on the user query frontend's deep structure; none have been ingested yet that describe it directly.

Source

Last updated · 319 distilled / 1,201 read