Database Provisioning

When a new instance that uses MariaDB (PHOTOPRISM_DATABASE_DRIVER: "mysql") joins the cluster without a database name, user, and password of its own, the PhotoPrism® Portal creates a database and a database user for it and passes the credentials on at registration. This guide explains how to set up the account the Portal uses for this, how MariaDB versions and MaxScale affect it, and how to troubleshoot provisioning errors.

How Provisioning Works

When an instance registers for the first time, the Portal connects with its provisioning account and runs these statements:

1. CREATE DATABASE IF NOT EXISTS for the instance’s database, with utf8mb4 and utf8mb4_unicode_ci.
2. CREATE USER IF NOT EXISTS for the instance’s database user, with the host '%', followed by ALTER USER to set a new password.
3. GRANT ALL PRIVILEGES on that database to that user.
4. FLUSH PRIVILEGES.

Database and user names are derived from the cluster and instance identifiers and look like cluster_d3xk7qp2mfa5 and cluster_u6tzb4whn3ck. The prefix is set with PHOTOPRISM_DATABASE_PROVISION_PREFIX (default cluster_); use a different prefix per environment if staging and production share one database server. Because the names are stable, a registration that is repeated after an error reuses the database and user created by the earlier attempt.

Creating the Provisioning Account

Create a dedicated, password-authenticated account rather than using root. You can replace '%' with a host pattern that covers the network the Portal connects from:

CREATE USER 'photoprism_provision'@'%' IDENTIFIED BY '<provision-password>';

-- Manage the per-instance database users.
GRANT CREATE USER, RELOAD ON *.* TO 'photoprism_provision'@'%';

-- Privileges the Portal passes on to each instance account (MariaDB 11.3 and later).
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER,
      CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE, SHOW CREATE ROUTINE, EVENT, TRIGGER,
      DELETE HISTORY
  ON *.* TO 'photoprism_provision'@'%' WITH GRANT OPTION;

On MariaDB 11.2 and earlier, remove SHOW CREATE ROUTINE from the list, as these versions do not have this privilege and reject the statement. See MariaDB Versions for details.

Three properties of this grant set are easy to get wrong:

• Every privilege in the second statement is required. MariaDB only allows an account to grant ALL PRIVILEGES on a database if it holds every database-level privilege itself, with grant option. If even one is missing, registration fails with Error 1044 (42000): Access denied after the database and user have already been created.
• The privileges are server-wide, not restricted to a name pattern such as `cluster\_%`. MariaDB only lets an account pass on privileges for a database it holds them on by exact name, and the database names are generated at registration time.
• RELOAD is required, as the Portal runs FLUSH PRIVILEGES and treats a failure as an error.

Then configure the account’s DSN on the Portal:

PHOTOPRISM_DATABASE_PROVISION_DSN: "photoprism_provision:<provision-password>@tcp(mariadb:3306)/"

The DSN is used only by the Portal and is never passed on to instances. Store it only in the Portal’s secret, and protect it like the database administrator’s credentials. If it is not set and the Portal’s own database is MariaDB, the Portal provisions with its own database account instead, which usually lacks the required privileges.

Authentication Plugins

Use a password-based authentication plugin the database driver supports for the provisioning account, the Portal’s own account, and any pre-created instance accounts:

• mysql_native_password or ed25519.
• caching_sha2_password, which is available from MariaDB Community Server 12.1 and MariaDB Enterprise Server 11.8 and must be installed explicitly.

Do not assign unix_socket or parsec to these accounts, as the driver cannot authenticate with them. On many Linux distributions, root authenticates via unix_socket and can only be used from a local shell, which is another reason to create a dedicated account.

Verifying the Account

Before starting the Portal, run the statements the Portal runs, logged in as the provisioning account over TCP from a host that is allowed to connect:

CREATE DATABASE `cluster_selftest` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'cluster_selftest'@'%' IDENTIFIED BY '<any-password>';
GRANT ALL PRIVILEGES ON `cluster_selftest`.* TO 'cluster_selftest'@'%';
FLUSH PRIVILEGES;
DROP USER 'cluster_selftest'@'%';
DROP DATABASE `cluster_selftest`;

Every statement must succeed. An Error 1044 on the GRANT means a privilege is missing, which you can find by comparing SHOW GRANTS FOR 'photoprism_provision'@'%'; with the list above. A failed run leaves the test database and user behind, so remove them with DROP USER and DROP DATABASE, as an administrator if the account lacks the privileges to do so. Changed privileges apply to new connections only, so run the test again in a new session after correcting them.

MariaDB Versions

The required privileges depend on the MariaDB version, as MariaDB 11.3 added the SHOW CREATE ROUTINE privilege:

GRANT SHOW CREATE ROUTINE ON *.* TO 'photoprism_provision'@'%' WITH GRANT OPTION;

The same privileges apply to MariaDB Community Server and MariaDB Enterprise Server. For the recommended server versions and running MariaDB Enterprise under its Kubernetes operator, see MariaDB Enterprise.

MaxScale & Database Proxies

With MariaDB MaxScale, set the Portal’s PHOTOPRISM_DATABASE_SERVER and the server in PHOTOPRISM_DATABASE_PROVISION_DSN to the MaxScale listener. Instances receive this endpoint at registration, so they connect through MaxScale as well. Keep the following in mind:

• Use the host '%' for the provisioning account and any pre-created accounts. MaxScale connects to the database servers with the client’s credentials, so the servers see the connection coming from MaxScale’s address rather than the client’s.
• New accounts are picked up automatically. MaxScale loads the user accounts from the database servers when it starts and reloads them when a login fails, so a newly provisioned instance can connect without further steps. Reloads are rate-limited by MaxScale’s users_refresh_time setting.
• No account synchronization is needed. Leave PHOTOPRISM_DATABASE_PROVISION_PROXY_DSN empty.

With ProxySQL, set PHOTOPRISM_DATABASE_SERVER to the ProxySQL frontend, point PHOTOPRISM_DATABASE_PROVISION_DSN at the primary database server, and set PHOTOPRISM_DATABASE_PROVISION_PROXY_DSN to the ProxySQL admin interface (for example provision:<proxy-admin-password>@tcp(proxysql:6032)/). The Portal then adds each provisioned account to ProxySQL as well. The Portal assigns provisioned accounts to hostgroup 10, so ProxySQL must define that hostgroup before deployment, as the Portal does not create hostgroups or query rules.

Encrypted Connections

Without a tls parameter, the Portal connects to the database without encryption. To encrypt the provisioning connection, add one to PHOTOPRISM_DATABASE_PROVISION_DSN. A DSN that contains ? is used as it is, so include the default parameters as well:

PHOTOPRISM_DATABASE_PROVISION_DSN: "photoprism_provision:<provision-password>@tcp(mariadb:3306)/?tls=true&charset=utf8mb4,utf8&collation=utf8mb4_unicode_ci&parseTime=true&timeout=15s"

• tls=true verifies the server certificate against the certificate authorities the container trusts. The hostname in the DSN must appear in the certificate’s Subject Alternative Name.
• tls=skip-verify encrypts the connection without verifying the certificate, for example the one MariaDB 11.4 and later generate automatically. tls=preferred also encrypts, but connects without encryption if the server does not support it.

To verify a certificate issued by a private certificate authority, mount its PEM-encoded certificate into the container and add the directory to SSL_CERT_DIR, keeping the system directory first so that public certificate authorities stay trusted:

services:
  portal:
    environment:
      SSL_CERT_DIR: "/etc/ssl/certs:/certs"
    volumes:
      - "/path/to/root_ca.crt:/certs/root_ca.crt:ro"

The Portal’s own connection is configured the same way, with a complete PHOTOPRISM_DATABASE_DSN. Instances that receive provisioned credentials connect with their database name, user, and password, which carry no TLS parameter. To encrypt an instance’s connection, pre-create its database and set a complete PHOTOPRISM_DATABASE_DSN with a tls parameter on the instance.

Pre-Created Databases

If a privileged provisioning account is not acceptable, create a database and a user for each instance in advance, for example:

CREATE DATABASE `photoprism_team_a` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'photoprism_team_a'@'%' IDENTIFIED BY '<instance-password>';
GRANT ALL PRIVILEGES ON `photoprism_team_a`.* TO 'photoprism_team_a'@'%';

Then set PHOTOPRISM_DATABASE_SERVER, PHOTOPRISM_DATABASE_NAME, PHOTOPRISM_DATABASE_USER, and PHOTOPRISM_DATABASE_PASSWORD explicitly on each instance. An instance with its own database settings does not request a provisioned database when it registers. The cost is that each database must be prepared before an instance can join.

Troubleshooting

Provisioning errors are logged by the Portal when an instance registers. Compare the Portal and instance logs for the same time period.

Portal log: register › ensure database with Error 1044 (42000): Access denied for user '…' to database 'cluster_d…'

The provisioning account is missing at least one privilege. Compare SHOW GRANTS with the list above, keeping the MariaDB version in mind, and add what is missing. Then restart the Portal: changed privileges apply to new database connections only, and the Portal keeps its provisioning connections open for up to 30 minutes. If the user named in the message is the Portal’s own database user, PHOTOPRISM_DATABASE_PROVISION_DSN is not set; set it to a dedicated provisioning account.

Portal log: register › ensure database with Error 1045 (28000): Access denied for user '…' (using password: YES)

The Portal could not log in with the provisioning account. Check the user name, password, and host in the DSN, the account’s host pattern, which must match the address the Portal (or MaxScale) connects from, and its authentication plugin.

The instance log shows waiting for the database or connection attempts to [::1]:3306

The instance did not receive database credentials, usually because its registration failed. The instance log then shows failed to join the configured cluster, and the Portal log has the details.

The instance uses SQLite instead of MariaDB

Provisioning is only requested by instances that use MariaDB. Set PHOTOPRISM_DATABASE_DRIVER to mysql on the instance and leave its database name, user, and password empty.

An instance attempts to join only when it starts. After fixing the cause and restarting the Portal, restart the instance so it registers again. The database and user created by an earlier, failed attempt are reused.

To list the provisioned databases and users, run the following as a database administrator:

SHOW DATABASES LIKE 'cluster\_d%';
SELECT User, Host FROM mysql.user WHERE User LIKE 'cluster\_u%';

Adjust the pattern if you use a different prefix.

PhotoPrism® Documentation

For more information on specific features, services and related resources, please refer to the other documentation available in our Knowledge Base and User Guide:

• Portal Config Options
• MariaDB Enterprise
• Portal on Kubernetes
• Portal on OpenShift
• PhotoPrism® Pro Knowledge Base