[Enterprise Gap] Define GA deployment profiles, tenant isolation, SSO, and lifecycle provisioning

[seonghobae]

Buyer-visible problem

pg-erd-cloud has project membership, OIDC token verification, API keys, encrypted DSNs, and public share links. Those are necessary controls, but they do not by themselves prove that a shared hosted service is multi-tenant safe. Current work such as PR #855 improves OIDC organization binding while explicitly leaving broader multi-tenant isolation and lifecycle provisioning unresolved.

A complete product must make a truthful deployment claim:

• either a single-tenant/self-hosted GA profile with explicit boundaries; or
• a multi-tenant SaaS profile with enforced tenant ownership on every persisted and cached object.

It must never imply multi-tenancy merely because projects have members.

Required product decision

Publish and implement two explicit deployment profiles behind the same modular product contracts.

Profile A — single_tenant_managed

• one customer organization per deployment/database;
• Keyverse or external OIDC issuer with organization binding;
• project RBAC within that organization;
• Compose/Podman and production ingress profile;
• customer-owned backup, restore, secret, and network policies;
• no cross-customer claim.

This is the minimum viable GA profile if proven end to end.

Profile B — multi_tenant_saas

Do not mark this profile GA until all requirements below pass.

1. Add normalized tenant authority:

tenant_account
tenant_domain
tenant_membership
tenant_role_assignment
identity_link_record
provisioning_request
provisioning_receipt
data_residency_policy
tenant_audit_event

2. Every authority-bearing object must carry or derive one immutable tenant_account_uuid, including projects, members, connections, snapshots, snapshot data, jobs, views, annotations, share links, API keys, migration plans/runs, connector artifacts, audit events, caches, object-storage keys, metrics labels, and queue signals.
3. Enforce tenant ownership in PostgreSQL with tenant-qualified foreign keys and RLS or an equivalently strong repository layer plus database tests. Application filters alone are not sufficient evidence.
4. Prevent cross-tenant references before insert/update and reject ambiguous legacy rows during migration.
5. Bind OIDC issuer/audience/organization claims to a tenant through reviewed mapping; never merge accounts by email alone.
6. Add SCIM 2.0 provisioning/deprovisioning through Keyverse or a dedicated adapter. Removing employment/access must revoke sessions, API keys, connector grants, and future project access without rewriting audit history.
7. Provide tenant-scoped encryption context and rotation. A tenant compromise must not expose other tenants' DSNs or connector secrets.
8. Partition/cache/queue keys must include tenant authority and resist hot-tenant starvation.
9. Share links are tenant-owned, project-scoped, expiring/revocable, rate-limited, and auditable; public access must never infer another tenant's existence.
10. Define data residency, export, deletion, legal hold, backup, and restore semantics per profile.
11. OTel traces/metrics/logs must avoid raw DSNs, comments, schema values, tokens, and high-cardinality tenant PII while preserving authorized auditability.

Required authorization matrix

At minimum:

Action	Viewer	Editor	Project owner	Tenant admin	Deployer
Read approved snapshot	yes	yes	yes	policy	policy
Create connection/snapshot	no	policy	yes	policy	policy
Edit view/annotation	no	yes	yes	policy	no
Create/revoke share link	no	no	yes	policy	no
Create/revoke API key	self/policy	self/policy	self/policy	policy	policy
Approve/apply migration	no	no	no	policy	yes + maker-checker
Export/delete tenant data	no	no	no	governed	no

The final matrix may be more granular, but every API, worker command, connector, and UI action must derive from one versioned policy source.

Adversarial and lifecycle tests

• same UUID/object name in two tenants;
• cross-tenant FK, URL, share link, API key, cache key, queue signal, export, and connector reference;
• issuer/audience/org claim confusion;
• email reuse across issuers;
• tenant-admin demotion, user deprovisioning, session expiry, and API-key revocation;
• restore one tenant without exposing or overwriting another;
• hot-tenant workload fairness;
• RLS bypass attempts through owner/service roles;
• multi-tenant migration from current rows with fail-closed ambiguity handling;
• production statement/branch coverage 100% and public API docstrings 100%.

Acceptance evidence

• ADR selecting the initial GA profile and defining the non-GA claim boundary;
• PRD/TRD/data model/threat model/UML/operability and privacy/retention docs;
• Keyverse OIDC/SCIM contract tests and standalone fallback behavior;
• clean-install and upgrade rehearsal on real PostgreSQL;
• exact-current-head CI/security/tenant-isolation/browser tests and independent review;
• docs/product-technical-gap-baseline.md, README deployment claims, SECURITY, and CHANGELOG updated.

References — APA 7th

Internet Engineering Task Force. (2015). System for Cross-domain Identity Management: Protocol (RFC 7644). https://www.rfc-editor.org/rfc/rfc7644

OpenID Foundation. (2025). OpenID Continuous Access Evaluation Profile 1.0 final specification. https://openid.net/specs/openid-caep-1_0-final.html

National Institute of Standards and Technology. (2020). Zero trust architecture (NIST Special Publication 800-207). https://doi.org/10.6028/NIST.SP.800-207