Database accounts and privileges #

This page is for database administrators who create the accounts an Exponential site connects with and who must justify every privilege. It answers: which account the site needs on each engine, which privileges are required and why, and which other accounts (replicas, cluster, backups) a setup may need. The shipped defaults are not safe for production: site.ini [DatabaseSettings] User=root and file.ini [eZDFSClusteringSettings] DBUser=root are placeholders to replace.

Contents #

1. One account per site
2. MySQL and MariaDB
3. PostgreSQL
4. MongoDB
5. Oracle
6. SQLite
7. Other accounts
8. What the project does not document
9. Checklist

One account per site #

Exponential connects with one account, named in site.ini [DatabaseSettings] User (or for SQLite, through the file system). The same account runs page views, publishing, cronjobs and the update files of an upgrade, so it needs the right to change the schema of its own database, and nothing outside it. Never use an administrative account (root, a superuser, SYS). Give each site its own account and its own database or schema.

MySQL and MariaDB #

CREATE USER 'exponential'@'localhost' IDENTIFIED BY '<a strong password>';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
      LOCK TABLES, CREATE TEMPORARY TABLES
   ON exponential.* TO 'exponential'@'localhost';

No global privilege, no SUPER, no right to list databases. Restrict the host part to the web servers. Source: 9.3.

PostgreSQL #

sudo -u postgres createuser --pwprompt exponential
sudo -u postgres createdb --owner=exponential --encoding=UTF8 --template=template0 exponential

Make the account the owner of the database: from PostgreSQL 15 on, other users may no longer create tables in its public schema. As owner it may also create pgcrypto, a trusted extension from PostgreSQL 13 on. It needs no superuser rights, CREATEDB or CREATEROLE. Restrict it in pg_hba.conf to the web servers and this database.

MongoDB #

Create the user in the site's own database, because the adapter uses that database as the authentication database: roles readWrite and dbAdmin on that database only (Setting up MongoDB). A user is required even on a server without access control.

Oracle #

php extension/ezoracle/bin/php/ora-grant.php prints the grants for a DBA to run: the account may create sessions, tables, views, triggers, sequences and procedures. Add SELECT_CATALOG_ROLE if you use the health and report commands; for datapump, READ and WRITE on the directory object (Setting up Oracle).

SQLite #

There are no database accounts. The file system is the access control: every process that uses the site must be able to write the directory and its files, through one common group; nobody else should be able to read them (SQLite files, ownership and permissions).

Other accounts #

What the project does not document #

The project documents and tests one account per site that both runs the site and applies schema changes. Splitting it into a run-time account without DDL rights and a migration account is not described or tested by the project. If your policy requires it, grant the migration account the DDL rights, keep CREATE TEMPORARY TABLES and LOCK TABLES on the run-time account, and test every update file and the setup on staging first.

Checklist #

• To do: No site connects as root, a superuser or SYS.
• To do: Each site has its own account, limited to its own database or schema.
• To do: Host or network restrictions allow only the web servers (and cron hosts).
• To do: The DFS cluster's DBUser is not the shipped root.
• To do: Backup accounts read only what they back up.
• To do: Passwords live in settings/override/ and the cluster file only (Securing the database).

Related pages #

• Securing the database
• Databases and users in hosting panels
• Per-customer users and pools
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.