Database users¶
Reference: the database users the stack uses on each backend, what each one can do, and why. The SQL files are the source, the setup migrations of db/mysql/setup/sync/, db/postgres/setup/sync/, db/mysql/setup/fxa-mysql/ and db/mysql/setup/fxa-mariadb/. How to run them on your own server: Create the databases and users.
MariaDB and MySQL¶
db/mysql/setup/sync/ creates two databases, a user for each, and the admin UI's database and two users.
| User (setting, default) | Grants | What it does with them |
|---|---|---|
tokenserver (SYNC_TOKENSERVER_DB_USER, tokenserver) |
SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, TRIGGER on the tokenserver database |
creates the tables (the migrations), the init container's config table, the CheckUserLimit procedure and the BeforeInsertUser trigger that enforce SYNC_MAX_USERS, the allowlist of SYNC_ALLOWED_ACCOUNTS (sync_allowed_accounts, sync_refused_accounts, the BeforeInsertAllowedUser trigger), and the procedures the admin UI calls: ffs_set_max_users, ffs_delete_user_row, ffs_drop_admin_tables, ffs_allow_account, ffs_remove_allowed_account, ffs_reject_account, ffs_mark_refusal_reported and ffs_resync_node_load |
storage (SYNC_STORAGE_DB_USER, syncstorage) |
the same on the storage database, without TRIGGER |
creates the tables (the migrations; one of them creates, calls and drops a procedure) |
ffs_admin_ui (ADMIN_DB_USER) |
SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP, LOCK TABLES on ffs_admin only |
the admin UI's own data. DROP and LOCK TABLES are for a restore of its dump |
ffs_admin_sync (ADMIN_DB_SYNC_USER) |
SELECT, EXECUTE on the tokenserver database, SELECT, DELETE on the storage database |
reads the Sync data. Changes max users, the allowlist and the nodes' load, and deletes rows of users, only through the procedures above. Deletes stored data for purges and resets |
Notes:
REFERENCESis needed on MySQL 8.0.22 and later, for the tokenserver's foreign keys.- Two databases, not one: the migrations of both keep their state in a table of the same name.
- With the storage database on another server, run there the three
ffs_admin_syncstatements commented at the end of the file. Leave out the storageGRANTon the tokenserver's server.ffs_admin_uiandffs_adminare on the tokenserver's server only. - On MySQL the users have the server's default,
caching_sha2_password.
PostgreSQL¶
Never run the Sync server, or your own psql, pg_dump or pg_restore on the Sync database, as a superuser. Connect as the owner of the Sync tables: SYNC_POSTGRES_APP_USER in the stack's container, SYNC_POSTGRES_USER on your own server.
db/postgres/setup/sync/ creates:
| Role | What it is |
|---|---|
SYNC_POSTGRES_USER (syncserver) |
the owner of the Sync database. It creates the tables, the post_bso type, the check_user_limit() and ffs_check_allowed_account() functions and their triggers, the allowlist's tables, and the ffs_drop_admin_tables(), ffs_allow_account(), ffs_remove_allowed_account(), ffs_reject_account(), ffs_mark_refusal_reported() and ffs_resync_node_load() functions. At every start it gives ffs_admin_sync its grants on them. No superuser, CREATEDB or CREATEROLE |
ffs_admin_ui (ADMIN_DB_USER) |
the owner of ffs_admin, which is closed to every other role. Nothing on the Sync database (no CONNECT) |
ffs_admin_sync (ADMIN_DB_SYNC_USER) |
on the Sync database: CONNECT, SELECT on its tables, UPDATE (config_value) of config (max users), DELETE on users, bsos, user_collections, batches and batch_bsos, and EXECUTE on ffs_drop_admin_tables(), the four functions of the allowlist and ffs_resync_node_load(). Nothing on ffs_admin |
Running the file:
- Run it as a superuser, or as a role with
CREATEROLEandCREATEDB, which is what managed services give you (tested). ItsGRANT ... TO CURRENT_USERlets that role create a database owned by the new one, which PostgreSQL 16 and later require. - Run again,
CREATE ROLEandCREATE DATABASEfail with "already exists" andpsql -fgoes on. TheALTER ROLEsets the new password. - The
REVOKEkeeps the other roles of a shared server out: no connections, no temporary tables. - For a database that already exists:
ALTER DATABASE syncserver OWNER TO syncserver;. - Leave
SYNC_POSTGRES_APP_USERempty on your own server: it is for the stack's container, whereSYNC_POSTGRES_USERis a superuser. -
On PostgreSQL 11 to 14 every role can create objects in the
publicschema of every database. Close it in the Sync database, as a superuser (since PostgreSQL 15 it is the default):psql -h db.example.com -U postgres -d syncserver -c 'REVOKE CREATE ON SCHEMA public FROM PUBLIC; ALTER SCHEMA public OWNER TO syncserver;'
Why: template0, and the superuser of the stack's container
ffs_admin is a copy of template0, not of template1. On PostgreSQL 11 to 14 every role may create tables in template1, so the Sync server's role could leave ffs_* tables with triggers there, to be copied into ffs_admin. Its public schema also gives PUBLIC no CREATE. The admin UI does not start when something there (a table, function, operator, type or schema) is not ffs_admin_ui's, and its setup check says what.
In the stack's container, SYNC_POSTGRES_USER is the image's superuser, and a superuser reaches every database. So the Sync server does not connect as it:
sync_role_initcreatesSYNC_POSTGRES_APP_USER(syncserver_app, no superuser) before the migrations. At every start it gives that role the right to create tables, and every table, function and type of the Sync database (sync_owner.sql does that for an install from before it).- The migrations, the init container's
db_init.sqland the backups run as that role. - As the superuser runs only what executes no code of the Sync tables' owner: creating and changing roles and databases, the grants and the change of owner, with only
pg_catalogin the search path. - Whatever reads or writes those tables does it as their owner, over a login of its own. Not with
SET ROLE, which code can undo. A trigger, rule, view or function the Sync server's role put there runs with its own rights only.
tests/e2e.sh has that role plant code that would make it a superuser: triggers on config and nodes, config itself as a view calling its function, and an operator <> of its own in public. Then it runs the migrations and the init container again, and checks that none of it ran as a superuser.
Without SYNC_POSTGRES_APP_USER (an install from before it), the Sync server stays SYNC_POSTGRES_USER, and the admin UI's setup check of the event log fails, saying so.
The accounts¶
db/mysql/setup/fxa-mysql/ and db/mysql/setup/fxa-mariadb/ differ only in how the accounts' user signs in. FXA_DB picks the file. The database names are fixed (fxa, fxa_oauth, fxa_profile, pushbox, ffs): a server or instance of its own for the accounts is best.
| User (setting, default on your server) | Grants | What it does with them |
|---|---|---|
FXA_MYSQL_USER (fxa) |
ALL PRIVILEGES on fxa, fxa_oauth, fxa_profile and pushbox |
Mozilla's migrations create the four databases, their tables and about 400 procedures. The accounts services use them |
FXA_MYSQL_POLICY_USER (ffs_policy) |
ALL on ffs with GRANT OPTION; SELECT, UPDATE, DELETE, TRIGGER, EXECUTE on fxa and SELECT, DELETE on fxa_oauth, both with GRANT OPTION; SELECT, DELETE on fxa_profile and pushbox |
fxa-policy installs the sign-up policy and the admin UI's views and procedures at every start, and gives the admin UI's user its grants |
ffs_admin (FXA_ADMIN_DB_PASSWORD) |
what fxa-policy gives it: SELECT on a few columns of fxa.accounts and on the views, EXECUTE on the procedures |
the admin UI's user on the accounts database |
How the accounts' user signs in:
- MySQL, with our accounts images: the server's default,
caching_sha2_password. - MySQL, with an image without mozilla/fxa#21709:
mysql_native_password, the file's-- native:lines.prepare_environment.shuses them for such an image. See MySQL 9. - MariaDB:
mysql_native_password, explicitly. MariaDB has nocaching_sha2_password, and both drivers speak it.
What the policy user installs at every start (admin/fxainit.go), and what the admin UI's user ffs_admin gets on it:
| Object | What it is | ffs_admin |
|---|---|---|
ffs.settings |
the sign-up policy, FXA_MAX_ACCOUNTS, and the last policy read from .env |
SELECT, INSERT, UPDATE, DELETE |
ffs.signup_allowlist |
the emails and domains of the allowlist | SELECT, INSERT, UPDATE, DELETE |
ffs.signup_refusal(), ffs.signup_allowed() |
the functions that check an email against the policy and the max accounts | EXECUTE |
a trigger on fxa.accounts |
refuses the new account through ffs.signup_refusal |
none |
views ffs.sessions, ffs.devices, ffs.emails, ffs.account_security, ffs.security_events, ffs.schema_levels, ffs.database_sizes |
what the admin UI lists, without any secret or token id | SELECT |
procedures ffs.disable_account, ffs.enable_account, ffs.sign_out_all, ffs.sign_out_device, ffs.sign_out_session, ffs.remove_totp, ffs.remove_recovery_key, ffs.confirm_email, ffs.confirm_session, ffs.delete_account, ffs.delete_unconfirmed, ffs.prune |
what the admin UI changes, run with the policy user's privileges | EXECUTE |
fxa.accounts |
Mozilla's table | SELECT on uid, email, emailVerified, createdAt and disabledAt only |
ffs_admin has nothing else: no UPDATE or DELETE on Mozilla's tables. fxa-policy revokes its grants first at every start, so grants from older releases go.
The procedures call Mozilla's own, and fxa-policy checks at every start that these exist: revokeAccountTokens_1, deleteTotpToken_4, deleteRecoveryKey_2, verifyEmail_9, verifyTokensWithMethod_3, verifyToken_3, deleteAccount_24 and prune_10. When one is missing (a newer accounts release renamed it, or the policy user has no EXECUTE on fxa), it logs a warning naming it, and the accounts keep working. What needs it in the admin UI then answers with an error: disabling and signing out, turning off two-step authentication, removing a recovery key, confirming an email or a sign-in, deleting accounts and pruning (Sign out and disable).
Why each grant:
EXECUTEonfxa: the procedures call Mozilla's own, listed above.WITH GRANT OPTION: to give the admin UI's user its grants.SELECTonfxa_profileandpushbox: the admin UI shows the size and schema level of all five databases.DELETEthere removes the profile and the push messages of a deleted account.- The grants are on whole databases because the tables don't exist yet when you run the file.
ffs_adminis created by the file, not byfxa-policy.CREATE USERis a server-wide privilege that also lets its holder change the password of every other account (on MariaDB,roottoo), so the policy user does not get it.
ffs_admin is a user, not the admin UI's own database of the same name (ADMIN_DB_NAME). On one server for Sync and the accounts, both exist side by side, and this user has no grant on that database.
The admin UI's user never sees token ids: a session token id signs in as its account.
Installs whose SQL is older than these grants:
-
Without
EXECUTEin the policy user's grant onfxa, run this once, then restartfxa-policy:GRANT EXECUTE ON `fxa`.* TO '@FXA_POLICY_USER@'@'%' WITH GRANT OPTION; -
With only
SELECTonfxa_profileandpushbox, run this once. Otherwise deleting an account in the admin UI fails, and changes nothing:GRANT DELETE ON `fxa_profile`.* TO '@FXA_POLICY_USER@'@'%'; GRANT DELETE ON `pushbox`.* TO '@FXA_POLICY_USER@'@'%';
One user for both the accounts and the policy is possible (tested):
- Leave
FXA_MYSQL_POLICY_USERempty.fxa-policythen connects withFXA_MYSQL_USERandFXA_MYSQL_PASSWORD, andFXA_MYSQL_POLICY_PASSWORDis not used. - In
database-setup-fxa.sql, delete the policy user'sCREATE USERandALTER USERlines, and put the name ofFXA_MYSQL_USERin itsGRANTs.
The accounts services face the internet, and then also hold GRANT OPTION on fxa, fxa_oauth and ffs: they could pass their access to the accounts on to any other user of the server.
The admin UI's own database¶
The admin UI keeps the event log, the audit trail (who did what, on which account), the notes, its metrics, the schema levels seen, its daily jobs and the backup runs in a database of its own: ADMIN_DB_NAME (ffs_admin). It is on the tokenserver's server; with PostgreSQL, on the same server.
The Sync server's users have no grant on it. A Sync server that is broken into can change what it stores, but cannot read or rewrite the audit trail.
The admin UI has two users there, both with ADMIN_DB_PASSWORD. It is not given the Sync users' passwords.
ADMIN_DB_USER(ffs_admin_ui) reachesffs_adminand nothing else.ADMIN_DB_SYNC_USER(ffs_admin_sync) reads the Sync data, changes it only as the tables above say, and has nothing onffs_admin.
These three names (ADMIN_DB_NAME, ADMIN_DB_USER, ADMIN_DB_SYNC_USER) and the accounts' ffs_admin are never one of Sync's or the accounts' users or databases: prepare_environment.sh and db-bootstrap refuse that. What only they lack is a warning at start, so a Sync user of the same name would turn a stop into a warning.
Why two users
The tokenserver user owns its database and can put code there: procedures, functions and views over its tables, with SQL SECURITY INVOKER (PostgreSQL: SECURITY INVOKER, the default). That code runs with the privileges of whoever calls it or reads the view, and on MariaDB and MySQL a session reaches every database by its name.
With one user, the Sync server's user could replace ffs_set_max_users with a procedure that deletes ffs_admin.ffs_events. The next save of max users in the admin UI would run it, with the rights on the audit trail. So the admin UI runs the Sync databases' code only as ffs_admin_sync. tests/e2e.sh plants such a procedure and function, and checks that the event log stays as it was.
Who creates them:
- The stack's MariaDB and MySQL containers: db/mysql/admin-user.sh runs before the image's entrypoint. At every start it has the server run its statements with all privileges (
--init-file): the database, the two users and their grants. That works on the first start and on the volumes of an existing install alike, whose root password is random: no administrator password is needed anywhere. The storage container gets onlyffs_admin_syncand itsSELECT, DELETE. - The stack's PostgreSQL container: the init container, as the superuser, as described in PostgreSQL.
- Your own server:
database-setup.sql, as its administrator, or the stack itself at every start (Let the stack apply them). Without them the start stops indb-bootstrap, which names them.
An install from before ADMIN_DB_PASSWORD keeps its admin UI tables in the tokenserver database. Moving them: The admin UI's own database.