Databases
Last updated
MariaDB and PostgreSQL, per-user databases and DB-users, with SSO into phpMyAdmin (MariaDB) / Adminer (PostgreSQL).
Engines
- MariaDB — primary, installed by default, from the OS packages (11.8 on Debian 13, 10.11 on Ubuntu 24.04). Clients connect over the Unix socket. TCP listens on loopback
127.0.0.1:3306only, because Stalwart’s SQL directory needs TCP; UFW keeps 3306 closed from outside. - PostgreSQL — installed alongside, off by default. Per-user DBs create on demand.
(Both are connected to the panel itself via Unix socket; the panel DB connection string is unix:///run/mysqld/mysqld.sock.)
Per-user DBs
/jabali-panel/databases:
Databases are shown in a tabbed view (MariaDB and PostgreSQL side by side).
- Create database — pick the engine, name (
<user>_<suffix>prefix enforced bydb_adminpolicies), default DB user. - Create DB user — username + password (shown once). The agent provisions the user with
GRANT ALL ON <user>_*.* TO …. For PostgreSQL, a DB-user granted to a database gets usable schema access, not just a bareCONNECT. - phpMyAdmin SSO (MariaDB) — single-use, short-TTL SSO Token (CONTEXT.md). Click “Open phpMyAdmin” → land authenticated as the DB user.
- Adminer SSO (PostgreSQL) — same single-use SSO flow into Adminer (6.2.0, with a ported SSO plugin).
Per-database Backup / Restore
Each database row has Download backup and Restore from file actions, for both MariaDB and PostgreSQL:
- Download streams a dump (
--no-owner --no-privilegesfor Postgres). - Restore from file uploads a dump and replaces the whole database. The upload is chunked and async to beat Cloudflare’s ~100 MB / 524 origin limits, with an upload-progress modal.
- PostgreSQL restore accepts plain-SQL and pgAdmin’s default custom / tar archive formats and surfaces the real loader error, not a generic failure. Uploaded dumps are never loaded as a superuser — they run through a per-database non-superuser scoped role and are built in a throwaway staging database, swapped onto the real name only on success (a bad upload never wipes the live database).
- A restored PostgreSQL database’s tables belong to its first database user.
When no user is granted on it, they belong to a role that can’t sign in,
never to the server’s
postgresrole, and the first user you grant on it takes them over.
See Backups for account- and server-level restore.
Admin DB Ops
/jabali-admin/settings → Database section:
- Root password rotation — agent-dispatched, audited. New password stored encrypted in
panel_db.db_admin_secrets. - Curated config tune — change a whitelisted set of MariaDB / PostgreSQL config keys (
innodb_buffer_pool_size,max_connections,query_cache_size, etc.). The reconciler converges the change (db_tuning_reconcile.go) and the agent applies viadb.config.apply. - Maintenance —
OPTIMIZE TABLE,ANALYZE,CHECK,REPAIRagainst selected DBs. - Processlist — live processlist; one-click
KILL <id>. - Admin phpMyAdmin SSO — privileged shadow account (
__M46_ADMIN_ALL__sentinel — see CONTEXT.md), full server access.
Every privileged DB action is a Privileged DB Admin Action in domain language: agent-dispatched, audited (success + failure), announced via notifications.
SSO Token Resolution
The single security-critical decision (SSO Token Resolution, CONTEXT.md): an SSO Token resolves to either
- the privileged shadow account (admin-scope, sentinel
__M46_ADMIN_ALL__), or - a per-DB-user scoped shadow account (ownership check passes).
The handler in panel-api/internal/api/databases_admin_ops.go is the adapter; the resolution module is what makes the call.
Architecture
- MariaDB on socket and loopback only — TCP listens on
127.0.0.1:3306only (Stalwart’s SQL directory needs it), and UFW keeps 3306 closed. phpMyAdmin, the panel and user apps connect via the socket. - Socket peer auth for the panel itself — the
jabaliLinux user is the DB owner; no password needed for the panel’s own connection. - Root password alongside socket peer auth — root has a password (so
mysql -uroot -pfrom a console still works) plus socket peer auth (so the panel never sends it). - No program execution from phpMyAdmin or Adminer — their PHP pool (
jabali-pma, userwww-data) disables the command-exec functions,putenvandmail, so a flaw in either app cannot start programs that would read other sites’ files. See Security → AppArmor. - Reconciler-converged tuning — never edit
/etc/mysql/mariadb.conf.d/jabali.cnfby hand; the reconciler will overwrite.
CLI
jabali db list [--user <id>]
jabali db create --user <id> --name suffix
jabali db delete <id>
jabali db user create --user <user> --name <name>