Resolving SQLite “Readonly Database” Errors in systemd Services on Linux

Service Configuration Conflicts with Database Write Permissions

The core issue involves a systemd-managed service failing to write to an SQLite database file stored in /usr/local/mine/db/task.db, throwing an "attempt to write a readonly database" error. While manual execution of the service binary (/usr/local/mine/server) succeeds, systemd’s security sandboxing mechanisms and Linux Filesystem Hierarchy Standard (FHS) compliance rules block write access when the service runs under systemd control. The problem is rooted in three intersecting layers:

1. Systemd’s hardening features (e.g., ProtectSystem=full) that restrict write access to system directories like /usr.
2. Filesystem permissions and ownership mismatches between the service’s runtime user (server) and the database directory.
3. SELinux policy enforcement on Red Hat Enterprise Linux (RHEL) derivatives, which restricts non-compliant file paths.

This error exemplifies the tension between legacy directory usage (e.g., placing databases in /usr/local) and modern Linux security practices. Systemd and SELinux enforce strict boundaries to prevent unauthorized writes to system directories, which are often treated as read-only in FHS-compliant distributions. The manual command-line execution works because it bypasses these protections, running with the user’s full privileges rather than the constrained environment imposed by systemd.

Interplay of systemd Security Settings, SELinux Policies, and Directory Structure

1. Systemd’s ProtectSystem=full Directive

When ProtectSystem=full is enabled in a systemd service unit (apiserver.service), it mounts /usr, /boot, and /etc as read-only for the service process. This setting overrides filesystem permissions, rendering even a world-writable SQLite database in /usr/local/mine/db unwriteable. The directive is part of systemd’s namespace hardening features, which isolate services from critical system paths. Developers often misconfigure this by assuming /usr/local is exempt from protection, but it resides under /usr, making it subject to the same restrictions.

2. FHS Compliance and Directory Misplacement

The Filesystem Hierarchy Standard designates /usr for static, read-only data (e.g., installed software), while /var is reserved for variable data like databases. Placing task.db in /usr/local/mine/db violates FHS guidelines, triggering SELinux denials on RHEL-based systems. SELinux contexts for /usr directories are typically labeled as usr_t, which prohibits database writes. In contrast, /var/lib or /opt have contexts (e.g., var_lib_t) that permit such operations.

3. User and Group Permission Mismatches

Even with explicit ownership (User=server, Group=server) and directory permissions (drwxrwxrwx), systemd services may fail to write to the database if:

• The server user lacks execute permissions on parent directories (e.g., /usr/local/mine).
• Access Control Lists (ACLs) or SELinux policies override traditional POSIX permissions.
• The service’s runtime environment (e.g., PrivateTmp=yes) creates an isolated filesystem namespace.

4. SELinux Context Labeling

On RHEL, Fedora, or CentOS, SELinux applies security contexts to files and directories. A database in /usr/local/mine/db inherits the usr_t context, which is not designed for mutable files. SELinux denies write operations unless the context is explicitly allowed (e.g., semanage fcontext -a -t var_lib_t '/usr/local/mine/db(/.*)?').

Diagnosing and Resolving Write Permission Conflicts

Step 1: Audit systemd Service Unit Configuration

• Disable Hardening Directives Temporarily: Comment out ProtectSystem=full, ProtectHome, PrivateTmp, and ReadWritePaths in apiserver.service. Restart the service:

  sudo systemctl daemon-reload
  sudo systemctl restart apiserver
  

  If the error resolves, reintroduce hardening options one-by-one to identify the culprit.

• Verify Service User Context: Ensure the server user exists and the service runs with correct privileges:

  id server  # Check if user/group exists
  ps -ef | grep server  # Confirm runtime user
  

Step 2: Validate Filesystem Permissions and Ownership

• Audit Directory Permissions:

  namei -l /usr/local/mine/db/task.db
  

  Ensure all parent directories (/usr, /usr/local, /usr/local/mine, /usr/local/mine/db) grant execute (x) permissions to the server user.

• Fix Ownership Recursively:

  sudo chown -R server:server /usr/local/mine/db
  sudo chmod -R u+rwX,g+rwX,o= /usr/local/mine/db
  

Step 3: Relocate Database to FHS-Compliant Path

• Move Database to /var/lib/mine:

  sudo mkdir -p /var/lib/mine/db
  sudo chown server:server /var/lib/mine/db
  sudo mv /usr/local/mine/db/task.db /var/lib/mine/db/
  

  Update the service configuration to reference the new path.

• Adjust SELinux Contexts (RHEL Derivatives):

  sudo semanage fcontext -a -t var_lib_t '/var/lib/mine/db(/.*)?'
  sudo restorecon -Rv /var/lib/mine/db
  

Step 4: Diagnose SELinux Denials

• Check Audit Logs:

  sudo ausearch -m avc -ts recent
  

  Look for avc: denied entries related to task.db.

• Generate SELinux Policy Module:

  sudo audit2allow -a -M mine_db
  sudo semodule -i mine_db.pp
  

Step 5: Customize systemd Hardening with Whitelists

If relocation isn’t feasible, modify apiserver.service to whitelist the database path:

[Service]
...
ReadWritePaths=/usr/local/mine/db
ProtectSystem=strict  # Less restrictive than 'full'

Step 6: Test and Enable the Service

• Dry-Run the Service:

  sudo systemd-analyze verify /usr/lib/systemd/system/apiserver.service
  sudo systemctl start apiserver
  journalctl -u apiserver -f  # Monitor logs
  

• Enable Persistent Logging: Replace shell redirection in ExecStart with systemd’s logging:

  [Service]
  ...
  StandardOutput=append:/var/log/apiserver.log
  StandardError=inherit
  

Final Configuration Example

[Unit]
Description=Mine Server with SQLite Database

[Service]
Type=simple
User=server
Group=server
ExecStart=/usr/local/mine/server -c /usr/local/mine/server.json
ReadWritePaths=/var/lib/mine/db
ProtectSystem=strict
SELinuxContext=system_u:system_r:mysqld_t:s0  # Custom SELinux role

[Install]
WantedBy=multi-user.target

Conclusion: The "readonly database" error under systemd stems from conflicts between service hardening, FHS non-compliance, and SELinux policies. By relocating the database to /var/lib, adjusting systemd permissions, and resolving SELinux contexts, services gain write access without compromising security. Developers must align directory choices with FHS and rigorously test systemd units with hardening features incrementally enabled.