Across a system of a dozen services sharing one relational database cluster, how would you decide how many distinct database accounts to create and how narrowly to scope each one?
answer
- walls cost maintenance - too fine degrades to wildcards
- split on classification, ownership, capability, environment
- schema-per-service is the prerequisite for scoping
- process boundary makes an account split meaningful
- rotation cost dominates; short-lived credentials change it
basics
~20 sCut accounts along blast-radius boundaries, not along code structure: one runtime account per service (and per data-sensitivity tier within it), separate migration and read-only identities, and a separate schema or database wherever the data classification differs. Finer scoping costs grant maintenance and credential rotation, so stop where the marginal containment stops paying.
solid answer
~60 sI decide with three questions per candidate boundary. **What is the blast radius if this credential leaks?** If two services' data have different classifications - payments versus feature flags - they get different accounts, and preferably different schemas so grants are cheap to express. **Can I even enforce the boundary?** If both services share the same tables at row level, separate accounts buy nothing; the boundary has to exist in the schema first. **What does it cost to run?** Every account is a secret to store, rotate, and monitor; every scope is grants to maintain in migrations. My default: per service, one runtime account scoped by schema, one migration/owner identity used only by the pipeline, one read-only role for humans and reporting. Sub-service splits only where a genuinely more sensitive table exists, ideally isolated behind a service that owns it rather than granted more finely. The failure mode to avoid is scoping so fine that nobody maintains the grants, and everything drifts to a wildcard grant that is never reviewed.
go deeper
At minimum know that each service should have its own account rather than everyone sharing one, and that migrations and reporting use separate identities.
Explain the per-service baseline (owner, runtime, read-only) and why schema-per-service makes those grants expressible and stable.
Argue the boundaries in terms of blast radius and enforceability, and cover the maintenance costs - grants in migrations, CI verification, rotation without downtime.
Own the tradeoff explicitly: which boundaries earn their keep, what schema restructuring they presuppose, when separate clusters beat separate schemas, and how short-lived dynamic credentials change the calculus.
## The real question 'How many accounts?' is really 'where do I want a wall, given that walls cost money to maintain?'. There is no maximum-security answer worth having, because a scheme too fiddly to maintain degrades into wildcard grants within two quarters - which is worse than a coarser scheme that stays honest. ## Boundaries worth paying for **Data classification.** The strongest reason to split. Payment data, credentials, and personal data belong behind accounts that ordinary services cannot use. If the feature-flag service and the payments service share one account, a leak in the least-defended service exposes the most sensitive data. **Ownership.** One team's service should not be able to silently write another team's tables. Separate accounts turn 'please don't' into 'cannot', and they surface accidental coupling immediately - a permission error at integration time is far cheaper than discovering a cross-service write in an incident. **Capability class.** Within a service: DDL (migration), read-write (runtime), read-only (reporting/humans). This split is nearly free and pays back every time, so it is the baseline everywhere. **Environment.** Never share credentials across environments, and never let a non-production identity authenticate against production. ## Boundaries usually not worth paying for **Per-endpoint or per-module accounts.** Splitting one service's own account into 'the checkout code path account' and 'the profile code path account' rarely helps: the process holds both secrets in the same memory, so a compromise gets both. Enforcement at the process boundary is what makes an account split meaningful. **Per-end-user database accounts** for consumer-scale systems. Pooling collapses and account lifecycle becomes a second identity system. **Column-by-column grant sprawl** as the primary control. If a table mixes classifications, the durable fix is usually a view or a split table, not thirty grants that a future migration will silently outgrow. ## The prerequisite nobody mentions Account granularity is bounded by schema granularity. If twelve services all read one shared schema, there is nothing to scope to. So the first move is often schema-per-service (or database-per-service) - then `GRANT ... ON SCHEMA` is a one-line, stable expression of the boundary, and new tables inherit the right shape via default privileges. Trying to express service boundaries as per-table grant lists over a shared schema is the version that rots. ## Cost model Per account you pay: a secret in the store, a rotation procedure that must work without downtime, monitoring for its use, an entry in the access review, and grant statements in migrations. Rotation is the sharpest cost - if rotating a credential requires a coordinated restart, teams will avoid it, and unrotated credentials undermine the whole scheme. Dynamic short-lived credentials (a secrets manager issuing per-instance logins, or cloud IAM-based database auth) change this calculus substantially: when credentials are minutes-lived and issued automatically, finer scoping becomes cheap and the leak scenario weakens on its own. ## How I'd actually stage it 1. **Baseline everywhere**: per service - migration/owner, runtime, read-only. Schema per service. No `PUBLIC` grants. 2. **Classify data.** Anything regulated or credential-bearing moves behind its own schema and, ideally, its own owning service; other services reach it through that service's API rather than through a grant. 3. **Add per-human identities** for anything a person runs, so auditing names people. 4. **Automate the grants** in migrations and verify them in CI (connect as each runtime role; assert it can reach its own schema and cannot reach others). 5. **Only then** consider finer splits, and only where step 4's automation makes them free. ## Judgment calls to be able to defend - Separate cluster versus separate schema for the most sensitive data: separate clusters give real isolation of resources and blast radius, at the cost of cross-store consistency and operational overhead. - Whether the read-only human path is worth per-person accounts against the overhead of provisioning them - usually yes, because attribution is exactly what you need during an incident. - Whether to model tenants as roles at all, or to keep tenancy purely a data concern enforced in the application and by policies.
- Two services need the same table. Do you grant both accounts on it, or make one service own it and expose an API?Default to single ownership with an API, because shared write access couples the two services' schemas forever and makes migrations a cross-team negotiation. Grant a second account direct read access only when the coupling is genuinely read-only, stable, and performance-critical - and then grant it on a view so the owning team can evolve the base table.
- How do you keep this from decaying once it is set up?Put grants in migrations so they are reviewed with the schema, and add a CI check that connects as each runtime role and asserts both what it can reach and what it must not. Pair that with periodic access review of accounts and their grants, and with credential rotation that works without downtime - if rotation is painful, the scheme quietly stops being maintained.
saying these in an interview costs you the question
- Designing accounts around code modules inside one process, where a compromise yields every secret that process holds anyway.
- Proposing very fine-grained per-table grants over a single shared schema, which is the arrangement most likely to rot into a wildcard grant.
- Ignoring credential rotation cost when arguing for many accounts.
- Treating separate database accounts as sufficient isolation when the underlying tables are shared row-for-row.