SQLite Database File Creation Permissions in World/Group-Writable Sticky Directories

Issue: SQLite WAL/SHM File Creation Fails for Group Members Due to Linux O_CREAT Protections

Core Problem Dynamics

When SQLite operates on a database in a directory with world-writable (777) or group-writable (775) permissions combined with the sticky bit (1777/3777), Linux kernel protections introduced in 2018 (via /proc/sys/fs/protected_regular) may block write access to the -wal (Write-Ahead Logging) and -shm (Shared Memory) files for non-owning users in the designated group. This occurs even when:

1. The directory’s group ownership (sqlite in the example) includes users who should have read/write access.
2. The database files (*.db, *.db-wal, *.db-shm) have group-write permissions (664).
3. The SQLite process is executed by a user belonging to the directory’s group.

The Linux kernel enforces restrictions on O_CREAT when opening files in sticky directories (world/group-writable + sticky bit). If a user attempts to open a file with O_CREAT in such a directory, and the file already exists with a different owner, the kernel returns EACCES (Permission Denied). SQLite’s default behavior is to open -wal and -shm files with O_RDWR|O_CREAT during database initialization. When this fails due to EACCES, SQLite falls back to opening the files in read-only mode (O_RDONLY), rendering the database effectively read-only for group members.

Key Components of the Conflict

1. Sticky Bit Semantics:
   Directories with the sticky bit (t in drwxrwsrwt) restrict file deletion to the file’s owner, directory owner, or root. However, this does not inherently restrict file creation or modification.
2. Linux Protected Regular Files:
   When /proc/sys/fs/protected_regular is set to 2 (default in many distributions), the kernel blocks O_CREAT in group-writable sticky directories if:
   • The file already exists.
   • The file is not owned by the directory’s owner.
     This prevents users from creating or hijacking files in shared directories.
3. SQLite’s File Handling Logic:
   • SQLite opens -wal and -shm files with O_RDWR|O_CREAT during database initialization, regardless of whether the main database file was opened with SQLITE_OPEN_CREATE.
   • If open() fails with EACCES, SQLite retries with O_RDONLY, permanently degrading the database connection to read-only mode.

Observable Symptoms

• Users in the sqlite group can read the database but cannot write to it.
• sqlite3 command-line tool or application connections return SQLITE_READONLY errors.
• System logs (dmesg or journalctl) show openat() syscalls failing with EACCES for -wal/-shm files.

Root Causes: SQLite’s O_CREAT Usage vs. Linux Kernel Protections

1. Misalignment Between SQLite’s File Creation Strategy and Sticky Directory Constraints

SQLite assumes that if a user has write permissions on a directory, they can create auxiliary files (-wal, -shm) without restrictions. This conflicts with Linux’s protected regular file mechanism, which imposes additional constraints in sticky directories.

Technical Breakdown:

• SQLite’s robust_open() Function:
  The function attempts to open files with O_RDWR|O_CREAT first. If this fails (e.g., due to EACCES), it retries with O_RDONLY. There is no intermediate retry with O_RDWR (write mode) without O_CREAT.
• Kernel’s protected_regular Enforcement:
  When protected_regular=2, the kernel checks:
  • Whether the directory is group-writable and has the sticky bit.
  • Whether the target file exists and is owned by a different user.
    If both conditions are met, O_CREAT is blocked to prevent file hijacking.

2. Inadequate Group Permissions Propagation for Auxiliary Files

Even if the main database file (1.db) has group-write permissions (664), SQLite’s handling of -wal/-shm files does not enforce consistent group ownership or permissions:

• The -wal/-shm files are created with the effective user ID of the SQLite process, not the directory’s group.
• The setgid bit on the directory ensures new files inherit the directory’s group, but this does not override kernel protections on existing files.

3. Overlooked Interaction Between Sticky Bit and WAL File Persistence

SQLite’s default behavior is to delete -wal/-shm files when the last connection closes. However, if SQLITE_FCNTL_PERSIST_WAL is enabled or connections are not cleanly closed, these files persist. In a sticky directory:

• A persistent -wal file owned by user A blocks user B (in the same group) from reopening the database in write mode.
• User B’s SQLite process cannot recreate the -wal file due to O_CREAT restrictions, forcing read-only mode.

Resolution: Configuring Directory Permissions, Kernel Settings, and SQLite Behavior

Step 1: Adjust Directory Permissions to Avoid Sticky Bit Conflicts

Option 1: Remove the Sticky Bit
If preventing file deletion by non-owners is not required:

chmod -t /path/to/my_dir  # Remove sticky bit  
chmod 0770 /path/to/my_dir  # Set to group-writable without sticky bit  

Pros:

• Bypasses Linux’s protected_regular enforcement, as the directory is no longer sticky.
• Allows SQLite to create -wal/-shm files without O_CREAT conflicts.

Cons:

• Users in the sqlite group can delete each other’s files.

Option 2: Use Subdirectories with User-Specific Ownership
Create per-user subdirectories without sticky bits:

mkdir -p /path/my_dir/user1  
chown user1:sqlite /path/my_dir/user1  
chmod 0770 /path/my_dir/user1  

Each user stores their databases in their own subdirectory.

Pros:

• Avoids sticky bit conflicts entirely.
• Users cannot interfere with each other’s files.

Cons:

• Requires restructuring application logic to use per-user paths.

Step 2: Modify SQLite’s File Opening Logic for Auxiliary Files

Patch robust_open() to Retry Without O_CREAT
Modify SQLite’s robust_open() function to retry with O_RDWR (without O_CREAT) if the initial O_RDWR|O_CREAT attempt fails with EACCES:

static int robust_open(const char *z, int f, mode_t m) {  
  int fd;  
  mode_t m2 = m ? m : SQLITE_DEFAULT_FILE_PERMISSIONS;  
  int has_creat = (f & O_CREAT);  

  while(1) {  
    // Attempt open with O_CREAT first  
    fd = osOpen(z, f | O_CLOEXEC, m2);  
    if (fd < 0) {  
      if (errno == EACCES && has_creat) {  
        // Retry without O_CREAT  
        f &= ~O_CREAT;  
        has_creat = 0;  
        continue;  
      }  
      // Handle other errors...  
    }  
    break;  
  }  
  return fd;  
}  

Validation:

• Test with SQLite’s make test suite to ensure no regressions in file handling.
• Verify that -wal/-shm files are opened in write mode when permissions allow.

Risks:

• Bypassing O_CREAT may allow unintended access to existing files owned by other users.
• Potential security implications if directory permissions are misconfigured.

Step 3: Configure Kernel’s protected_regular Setting

Temporarily set /proc/sys/fs/protected_regular to 0 to disable O_CREAT restrictions:

echo 0 | sudo tee /proc/sys/fs/protected_regular  

Note: This exposes the system to security risks (e.g., file hijacking in shared directories). Use only for testing.

Step 4: Use SQLITE_OPEN_EXCLUSIVE for Database Creation

When creating new databases, use SQLITE_OPEN_EXCLUSIVE to ensure only one process can create the file:

sqlite3_open_v2("1.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_EXCLUSIVE, NULL);  

Effect:

• Prevents concurrent creation attempts.
• Does not resolve -wal/-shm file issues but reduces race conditions.

Step 5: Enable Persistent WAL Mode

Set SQLITE_FCNTL_PERSIST_WAL to retain -wal files across connections:

sqlite3_file_control(db, NULL, SQLITE_FCNTL_PERSIST_WAL, &persist_wal);  

Rationale:

• Persistent -wal files avoid repeated creation attempts, reducing O_CREAT conflicts.

Final Recommendations

1. Avoid Sticky Bits for SQLite Directories: Use subdirectories with strict group permissions instead.
2. Audit File Ownership: Ensure -wal/-shm files are owned by the directory’s group.
3. Monitor Kernel Updates: Future SQLite versions may include built-in handling for protected_regular scenarios.

By aligning directory permissions, SQLite’s file creation logic, and kernel settings, write access for group members can be restored without compromising security.