Server — MariaDB — Users

Posted on 8 2026

The PowerDNS and PowerDNS Admin databases were created in the PowerDNS article with users that have ALL PRIVILEGES on their respective databases. That is a reasonable starting point for getting things running quickly, but ALL PRIVILEGES includes permissions those applications will never use: the ability to drop the database, create new tables, manage indexes, and change other users’ permissions. This article covers the right way to structure user accounts for February’s applications, how to audit what already exists, and how to tighten the permissions that were created earlier in the series.

The principle

Every service that connects to MariaDB gets its own dedicated user account, scoped to its own database, with only the specific privileges that service actually needs. No shared credentials between services. No accounts with broader access than necessary.

The practical benefits are straightforward. If a service is compromised, the attacker gains access to that service’s database and no others. If a user account’s credentials are leaked, the blast radius is limited to one database. When you decommission a service, you drop its user and its database cleanly without affecting anything else.

Naming is worth being consistent about. The pattern used throughout this series is: user named after the service, host localhost, database named after the service. powerdns@localhost on database powerdns. pdnsadmin@localhost on database pdnsadmin. This makes the user list self-documenting.

What privileges applications actually need

Most web applications and services need four things from a database: read rows, write rows, update rows, delete rows. In SQL terms: SELECT, INSERT, UPDATE, DELETE. That is it.

Beyond those four, some applications also need:

CREATE and ALTER — for schema migrations, when the application creates or modifies tables on startup or upgrade. Some applications handle their own schema management; others require the tables to be pre-created. PowerDNS reads from tables created by the imported schema and does not modify them. PowerDNS Admin runs migrations on startup and needs CREATE and ALTER.

INDEX — required alongside CREATE and ALTER for applications that create tables with indexes.

DROP — needed by applications that drop and recreate tables during migrations. Should be granted cautiously: an application bug with DROP privilege can delete an entire table.

LOCK TABLES — needed by some backup and export operations, not by most application runtime activity.

REFERENCES — needed for creating foreign key constraints.

The right approach is to check what the application actually needs, grant that, and deny everything else. For applications you have already set up, the slow query log and audit log will show what operations they actually perform.

Auditing existing users

Before making any changes, see what is currently in place:

sudo mariadb -e "
SELECT user, host, authentication_string != '' AS has_password
FROM mysql.user
ORDER BY user, host;"

This shows every user account and whether it has a password set. On February after following this series, you should see:

root@localhost — using unix_socket authentication, no password
mariadb.sys@localhost — an internal system account, ignore it
powerdns@localhost — the PowerDNS application user
pdnsadmin@localhost — the PowerDNS Admin application user

Any other accounts are worth investigating. An account you cannot explain should be removed.

Check the current privileges for each application user:

sudo mariadb -e "SHOW GRANTS FOR 'powerdns'@'localhost';"
sudo mariadb -e "SHOW GRANTS FOR 'pdnsadmin'@'localhost';"

If either shows GRANT ALL PRIVILEGES ON powerdns.* TO ..., the permissions are broader than they need to be.

Tightening existing accounts

PowerDNS

PowerDNS reads and writes DNS records but does not manage its own schema. The tables were created when the schema was imported during setup. At runtime, PowerDNS needs SELECT, INSERT, UPDATE, DELETE on its database, plus LOCK TABLES for some internal operations.

Revoke the broad grant and replace it with the appropriate privileges:

REVOKE ALL PRIVILEGES ON powerdns.* FROM 'powerdns'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON powerdns.* TO 'powerdns'@'localhost';
FLUSH PRIVILEGES;

Verify the change:

sudo mariadb -e "SHOW GRANTS FOR 'powerdns'@'localhost';"

The output should show only the five privileges listed above.

Restart PowerDNS and confirm it is still working:

sudo systemctl restart pdns
sudo wg show
upsc apc@localhost

Test a DNS query to confirm zone data is being served correctly:

dig february.home.arpa @127.0.0.1

PowerDNS Admin

PowerDNS Admin runs schema migrations on startup, so it needs broader privileges than PowerDNS itself:

REVOKE ALL PRIVILEGES ON pdnsadmin.* FROM 'pdnsadmin'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP, REFERENCES
  ON pdnsadmin.* TO 'pdnsadmin'@'localhost';
FLUSH PRIVILEGES;

Restart the PowerDNS Admin container and confirm it starts cleanly:

sudo systemctl restart powerdns-admin
sudo journalctl -u powerdns-admin -n 30

Watch for any database-related errors in the logs. If the application fails to start with a privilege error, the missing privilege will be named in the error message. Add it and restart.

Creating new users correctly

When adding a new service to February that needs a database, the pattern is always the same:

-- Create the database
CREATE DATABASE newservice CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create the user
CREATE USER 'newservice'@'localhost' IDENTIFIED BY 'strong-password-here';

-- Grant minimum necessary privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON newservice.* TO 'newservice'@'localhost';

-- Apply
FLUSH PRIVILEGES;

Generate the password with openssl rand -base64 24 and store it in the password manager before using it here.

If the application needs schema migration privileges, add CREATE, ALTER, INDEX, DROP to the grant. If it only reads data, use SELECT alone. If you are not sure what it needs, start with SELECT, INSERT, UPDATE, DELETE and add privileges as the application’s startup logs demand them.

Revoking access when a service is decommissioned

When a service is removed from February, clean up its database artefacts completely:

DROP USER 'oldservice'@'localhost';
DROP DATABASE oldservice;
FLUSH PRIVILEGES;

Do this as part of the decommissioning process, not as an afterthought. A user account for a service that no longer runs is an unnecessary attack surface with no value. A database for a decommissioned service takes up space and adds noise to the user list.

Passwords

Every application user should have a strong, randomly generated password stored in the password manager. The pattern throughout this series has been openssl rand -base64 24, which produces 32 characters of high-entropy base64. That is sufficient.

Rotate passwords when you have reason to believe they may have been compromised: a service logs credentials in an accessible location, a backup is exposed, a developer who had access leaves. Routine time-based rotation without a specific reason adds operational burden without meaningful security benefit for a homelab server where the attack surface is already tightly controlled.

To change a user’s password:

ALTER USER 'powerdns'@'localhost' IDENTIFIED BY 'new-password-here';
FLUSH PRIVILEGES;

Update the application’s configuration to use the new password and restart the service. Update the password manager entry. Check the audit log confirms the service reconnects successfully.

The user list as documentation

The output of SELECT user, host FROM mysql.user is a live record of what has access to February’s databases. Keep it clean: every entry should correspond to something actively running, and you should be able to explain each one without looking anything up.

Run this check after any change to what is running on February:

sudo mariadb -e "
SELECT
  u.user,
  u.host,
  GROUP_CONCAT(DISTINCT s.db ORDER BY s.db SEPARATOR ', ') AS databases
FROM mysql.user u
LEFT JOIN mysql.db s ON u.user = s.user AND u.host = s.host
WHERE u.user NOT IN ('root', 'mariadb.sys')
GROUP BY u.user, u.host
ORDER BY u.user;"

This shows each non-system user alongside the databases they have access to. On a correctly configured February it should look like this:

+------------+-----------+-------------+
| user       | host      | databases   |
+------------+-----------+-------------+
| pdnsadmin  | localhost | pdnsadmin   |
| powerdns   | localhost | powerdns    |
+------------+-----------+-------------+

Clean, self-documenting, and easy to audit. Every new service you add should produce one more row. Every decommissioned service should remove one.