Let app teams snapshot, restore and clone their own PostgreSQL databases on CloudNativePG, and run their migration scripts on them or on any other PostgreSQL, without asking the platform team. People sign in with Entra ID and see the databases of the namespaces Kubernetes lets them into; every action runs as a Kubernetes Job next to the database.
- Access is Kubernetes RBAC. A
Databaseresource names its app's namespace in a label (an external database, in the catalog). Who may see it, snapshot it, restore it or run a script on it is what the API server says that person may do in that namespace, asked with a SubjectAccessReview:viewsees,editsnapshots, restores and runs scripts,adminrestores and runs scripts in production. There is no second list of who may do what. - Snapshots on demand.
pg_dumpin the custom format, streamed straight into Azure Blob Storage with who took it and why. Nothing touches a disk, and nothing leaves the cluster. - Restore in place or into a copy, exactly. In one transaction, everything the database's owner
owns is dropped and the snapshot restored: the database ends up as the snapshot was, or, if
anything fails, as it was before. In place, the app's workloads are scaled to 0 first and back
afterwards; into a copy, a second database
<name>_copyappears next to it, created and dropped by CloudNativePG. - Clone across environments, once shared. Someone who may snapshot a database in acc shares a snapshot with a namespace in dev; whoever may restore there can then clone it.
- Scripts, checked or run. Paste or open a
.sqlfile: it runs as the database's owner in one transaction. A check runs it, shows what it said and rolls back; a run commits, after a snapshot if asked. One failing statement, or a lock it can't get within 15 seconds, changes nothing. - Any PostgreSQL. A server outside CloudNativePG goes in the catalog with its host, a login and that login's password Secret: it is listed, snapshotted and scripted like the others.
- Production is careful. A restore or a script in a production environment waits for a second person, who reads the script before approving it; snapshots taken there can't be downloaded, and a script's output there names its statements but shows no rows.
- Database users: the owner roles of the databases you see, and the Secrets with their passwords.
- An audit log of who asked for what, who approved it and how it ended, per environment.
- No superuser. Each Job connects as the database's owner role, with the password from that managed role's Secret. The server itself reads no Secret.
sequenceDiagram
participant P as Person
participant U as databases-ui
participant K as Kubernetes API
participant J as Job (in the database namespace)
participant D as PostgreSQL
participant B as Blob Storage
P->>U: sign in (Entra ID: the app the API server trusts)
P->>U: snapshot payments_dev
U->>K: Database resource: namespace label, owner, Cluster's managed roles
U->>K: SubjectAccessReview: may oidc:ada get secrets in dev-payments?
U->>K: create a Job (owner role, its password Secret, the CA certificate)
K->>J: start the pod
J->>D: pg_dump --format=custom
J->>B: stream the dump into a block blob, commit it with its metadata
J->>B: append the outcome to the audit log
P->>U: restore it
U->>K: create a Job
J->>K: scale the app's workloads to 0
J->>B: stream the snapshot through pg_restore
J->>D: BEGIN; DROP OWNED BY CURRENT_USER; the snapshot; COMMIT
J->>K: scale the workloads back
People sign in to the app registration the cluster's API server trusts, so their ID token carries the claims the API server builds a user from. The server builds the same user and groups (with the same claims and prefixes) and asks the API server about them in a SubjectAccessReview; the answers are kept for a minute. It needs no token of theirs at the API server, and stores none.
The Jobs run this app's own image, which carries the PostgreSQL client, as
python -m databases_ui job. A Job's pod reads the owner's password from the kubelet, verifies the
server's certificate against the cluster's CA (verify-full), and talks to storage with its own
workload identity.
One instance runs in each cluster. Its catalog lists the environments: the one in its own cluster
runs Jobs, the others are where shared snapshots come from or go to (see
examples/catalog.yaml):
environments:
- name: dev
# The CloudNativePG Cluster (default: the environment's name) and its namespace (default:
# database), which holds the Database resources, the role Secrets and the Jobs.
cluster: dev
namespace: database
storage:
url: https://<account>.blob.core.windows.net/dev-snapshots
cloneFrom: [acc] # environments whose shared snapshots may be restored here
- name: acc
kubernetes: none # another cluster's
storage:
url: https://<account>.blob.core.windows.net/acc-snapshots
- name: prd
kubernetes: none
production: true # restores wait for a second person; no downloads
cluster: prod
storage:
url: https://<other account>.blob.core.windows.net/prd-snapshotsWhat each action takes in the database's namespace, per environment, can be changed with
access; these are the defaults:
access:
read: {verb: list, resource: pods} # see a database, its snapshots, Jobs, audit
snapshot: {verb: get, resource: secrets} # snapshot, download, share
restore: {verb: get, resource: secrets} # restore, clone into, drop a copy
# in a production environment:
# restore: {verb: create, group: rbac.authorization.k8s.io, resource: rolebindings}A snapshot holds all of a database's data, so taking one takes what reading the namespace's Secrets takes. Check the file in CI before it reaches the server, which refuses to start with a catalog that does not hold together:
docker run --rm -v "$PWD/catalog.yaml:/catalog.yaml:ro" ghcr.io/magmamoose/databases-ui:<tag> check /catalog.yamlThe server finds a database's namespace and workloads on its Database resource:
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
name: payments-dev
namespace: database
labels:
databases-ui/namespace: dev-payments # the label key is config.namespaceLabel
annotations:
databases-ui/workloads: deployment/payments-api,statefulset/payments-worker
spec:
cluster: {name: dev}
name: payments_dev
owner: payments_dev # a managed role with a passwordSecretWithout the label, a database belongs to its own namespace (database): only who may act there,
usually the platform team, sees it. Without the annotation, a restore in place scales nothing; the
owner's other sessions are still ended first.
A PostgreSQL server that isn't a CloudNativePG Database (a VM, a managed service, another
operator's cluster) goes under its environment's external in the catalog:
environments:
- name: acc
storage:
url: https://<account>.blob.core.windows.net/acc-snapshots
external:
- name: billing # what this app calls it, unique in the environment
namespace: acc-billing # whose people may act on it; empty: the environment's own
host: billing-db.example.internal
port: 5432 # the default
database: billing # the database on that server
user: billing_owner # the login the Jobs use
passwordSecret: billing-db # a Secret in the environment's namespace, key `password`
caSecret: billing-db-ca # its `ca.crt` verifies the server
tls: verify-full # the default; or require, or disable- The login is what a script can do. Give the Jobs the schema's owner, as migration scripts need, not a superuser: a superuser's script can do anything to the server.
verify-fullwithout acaSecretchecks the certificate against the image's public roots (sslrootcert=system), for a server with a public certificate.requireencrypts without checking who answers;disableis for a server without TLS on a network you trust.- No restores. A restore drops everything its login owns first, which on a server this app
doesn't manage could be anything. Its snapshots are ordinary
pg_dumparchives: download one outside production, or restore it by hand. - The Jobs must reach it. If the namespace's NetworkPolicies limit egress, admit the Jobs' pods
(
app.kubernetes.io/component: job) to the server's address and port. The admission policy admits exactly the catalog's host, port, TLS mode and password Secret, so the catalog has to be in the chart's values (catalog.contentorcatalog.text).
- One transaction, every statement or none.
psqlrunsBEGIN,SET LOCAL lock_timeout = '15s', the script withON_ERROR_STOP, thenCOMMIT(run) orROLLBACK(check). At the first error psql stops before either and PostgreSQL rolls everything back, so a check shows exactly what a run would do. - No BEGIN, COMMIT or psql commands. A script that opens or ends a transaction of its own, or
holds a backslash command (
\!,\connect,\o), is refused before a Job exists, with the line. Statements that can't run in a transaction (CREATE INDEX CONCURRENTLY,VACUUM) fail. - A lock it can't get within 15 seconds fails the script, rather than queueing the app's own
queries behind an
ALTER TABLEthat waits for a long one. - A snapshot first, when asked. A run can take a snapshot before it starts; if the snapshot fails, the script doesn't run.
- What it said. The last lines of psql's output (statement tags, notices, errors with the
script's line numbers, and rows outside production) show under Activity. The script itself is
kept beside the snapshots (
scripts/<database>/<id>.sql) and in the Job, with its sha256 in the audit log. - In production, a second person reads it first. The request waits, suspended; whoever approves sees the script the Job holds, which can't change after it is created.
- One transaction, the snapshot exactly.
psqlrunsBEGIN,DROP OWNED BY CURRENT_USER, then the scriptpg_restore --clean --if-exists --no-owner --no-aclmakes of the snapshot, andCOMMITonly oncepg_restorehas finished without an error. Tables created after the snapshot go too, so an app's migrations find the state they expect. On any error, or if the download breaks off, nothing changes. DROP OWNEDalso revokes privileges granted to the owner role, on objects in this database and on shared objects such as other databases. Owners that own their databases, as CloudNativePG sets them up, lose nothing.- No owners or grants from the snapshot. Everything restored belongs to the database's owner role, so a snapshot moves between environments whose roles differ.
- Extensions stay as they are. Snapshots leave extensions out (
--exclude-extension=*): the owner role can't drop one the operator created. The target database needs the same extensions; a copy gets those itsDatabaseresource lists. - In place, the workloads go first. Each workload in the annotation is scaled to 0, its replica
count kept in the annotation
databases-ui/replicas-before-restore, and other sessions of the owner role are ended. Afterwards it gets its count back, also when the restore failed; if the Job itself dies halfway, the next restore restores the count from that annotation. A GitOps tool that setsreplicasmay scale the app back up during the restore; the transaction keeps the data right either way. - The owner must read everything.
pg_dumpruns as the owner role, so objects in the database that belong to other roles and that it can't read stop the snapshot.
A container per environment, for example in a storage account per environment, with:
- a lifecycle rule that deletes blobs under
snapshots/after as many days as snapshots should live; the audit log is underaudit/, one append blob per month; - network rules that admit the clusters' egress addresses: every transfer happens inside a cluster, the browser's downloads included, which the server streams;
- Storage Blob Data Contributor on it for the storage identity below, and, where an environment clones from this one, for that environment's storage identity too: its server lists what was shared with it and its Jobs read the snapshots.
- Sign-in: the app registration whose ID tokens the cluster's API server accepts (its client id
is the API server's audience). Platform Web, redirect URI
https://<host>/auth/callback. The sign-in asks foropenid profile; nothing else. Its credential is a federated credential, audienceapi://AzureADTokenExchange, issuer the cluster's service-account issuer, subjectsystem:serviceaccount:<release namespace>:<release name>; or a client secret. - The claims the API server uses (
config.kubernetesUser,config.kubernetesGroups): the username and groups claims and their prefixes, exactly as in its authentication configuration. A person in more groups than fit in the token is refused at sign-in, with the reason. - Storage (
azure.storageClientId, default the sign-in app): a managed identity or app with federated credentials for the server and for the Jobs in each namespace ofrbac.namespaces(system:serviceaccount:<namespace>:databases-ui-job).
The chart installs:
- a ClusterRole that lets the server create SubjectAccessReviews;
- in each namespace of
rbac.namespaces(where the environment's CloudNativePG Cluster is): a Role for the server (Database resources: get, list, create and delete, for copies; Clusters: get; Jobs: get, list, create, patch, delete; pods: list; no Secrets), the Jobs' service accountdatabases-ui-job, and withnetworkPolicy.databases.enableda NetworkPolicy that lets the Jobs reach PostgreSQL on the CloudNativePG pods; - in each namespace of
jobs.scaleNamespaces: the right for the Jobs to get and patch Deployments and StatefulSets and list pods, for restores in place; - a ValidatingAdmissionPolicy (
jobPolicy, Kubernetes 1.30+) that holds the Jobs the server creates to what it builds: this image, run asjob; one owner's password and never a*-superuserSecret; the CA certificate and no other Secret file;verify-fullTLS to a-rwservice in the Job's own namespace; and only the catalog's storage. Creating Jobs in a namespace would otherwise let a client mount any Secret there.
helm install databases-ui oci://ghcr.io/magmamoose/charts/databases-ui \
--namespace databases-ui --create-namespace \
--set azure.tenantId=<tenant> --set azure.clientId=<the API server's app> \
--set azure.storageClientId=<storage identity> \
--set config.publicOrigin=https://databases.example.com \
--set-file catalog.text=catalog.yaml \
--set 'jobs.scaleNamespaces={dev-payments}' \
--set ingress.enabled=trueEvery value is described in charts/databases-ui/values.yaml.
The catalog can also be a map in catalog.content or your own ConfigMap
(catalog.existingConfigMap); the server reads it at startup, and the chart rolls the pods when
its own copy changes.
| Variable | |
|---|---|
AZURE_TENANT_ID, AZURE_CLIENT_ID |
the sign-in app registration |
AZURE_FEDERATED_TOKEN_FILE or AZURE_CLIENT_SECRET[_FILE] |
its credential |
AZURE_AUTHORITY_HOST |
a sovereign cloud's login host; default https://login.microsoftonline.com |
DATABASES_UI_STORAGE_CLIENT_ID |
the storage identity; default the sign-in app |
DATABASES_UI_PUBLIC_ORIGIN |
where people open it, https://host |
DATABASES_UI_SESSION_KEY[_FILE] |
at least 32 characters, the same on every replica |
DATABASES_UI_SESSION_HOURS |
1 to 24, default 8 |
DATABASES_UI_CATALOG |
the catalog file |
DATABASES_UI_NAMESPACE_LABEL |
the label that names a database's namespace; default databases-ui/namespace |
DATABASES_UI_USERNAME_CLAIM, DATABASES_UI_USERNAME_PREFIX |
as the API server builds the user; default preferred_username, oidc: |
DATABASES_UI_GROUPS_CLAIM, DATABASES_UI_GROUPS_PREFIX |
as it builds the groups; default groups, oidc: |
DATABASES_UI_JOB_IMAGE |
the image the Jobs run: this app's own |
DATABASES_UI_JOB_SERVICE_ACCOUNT |
the Jobs' service account; default databases-ui-job |
DATABASES_UI_STORAGE_SAS[_FILE] and DATABASES_UI_STORAGE_SAS_SECRET |
instead of Entra ID: a SAS for the server, and <secret>/<key> of the same SAS in each Job namespace (for Azurite and tests) |
DATABASES_UI_BRAND_ACCENT, _ACCENT_TEXT, _ACCENT_DARK, _ACCENT_TEXT_DARK |
your colours as #rrggbb |
python -m databases_ui [serve | check <catalog> | job]: the server (default), the catalog check,
and a Job's work.
- Backend for frontend. The browser holds no token it can read: the session is an AES-GCM
sealed,
__Host-,HttpOnly,Secure,SameSite=Strictcookie, and every write also needs a CSRF token and this origin. No inline script or style, under a strict Content Security Policy. - Kubernetes decides. Every call asks the API server whether the person may do what it takes in the database's namespace. Outside the namespaces someone may see, a database does not exist to them: not in a list, not in an answer. If the claims or prefixes differ from the API server's, the reviews ask about nobody and refuse everything.
- No passwords in the server. It reads no Secret; a Job's pod gets the owner's password from the kubelet, and the admission policy keeps the server's Jobs to what it builds.
- A production restore or script takes two people. It is created suspended and starts only when someone else who may restore (or run scripts) there approves it; a Job's pod template can't change after creation, so what runs is what was asked for. Unapproved requests expire after a day.
- A script runs as the database's owner, never as a superuser the app chose. For an external database the catalog names the login, and the admission policy holds a script Job to that host, port, TLS mode and password Secret.
- Data leaves an environment only when someone who may snapshot it says so: a share names one namespace in one environment, and is in the audit log. Downloads, outside production only, stream through the server and are recorded too.
uv sync
uv run pytest
uv run python -m databases_ui check examples/catalog.yaml
cd frontend && npm install && npm run dev # the UI on 5173, the API proxied to 8080tests/e2e/run.py runs every action against a real cluster with CloudNativePG, Azurite and
RBAC like an OIDC-backed API server's; CI runs it on kind:
k3d cluster create dbui
helm install cnpg cloudnative-pg --repo https://cloudnative-pg.github.io/charts --version 0.29.1 \
--namespace cnpg-system --create-namespace --wait
docker buildx bake app-local && k3d image import databases-ui:dev -c dbui
uv run python tests/e2e/run.pyApache-2.0, see LICENSE.