SQLite Database File Permissions Ignoring POSIX ACLs: Causes and Fixes

Understanding SQLite’s File Permission Behavior with POSIX ACL Inheritance

Issue Overview: SQLite Ignores Parent Directory ACLs During Database Creation

When creating new database files in directories with POSIX Access Control Lists (ACLs), SQLite’s default behavior may override inherited group permissions defined by the directory’s ACLs. This occurs even when the directory is configured with default ACLs that explicitly grant rwx access to a designated group. For example, a ZFS file system directory with the following ACL:

# file: destinationFolder/
# owner: userName
# group: userName
user::rwx
group::---
group:project:rwx
mask::rwx
other::---
default:user::rwx
default:group::---
default:group:project:rwx
default:mask::rwx
default:other::---

…should propagate these permissions to new database files. However, SQLite creates files with reduced group permissions:

# file: destinationFolder/lock.sqlite
# owner: userName
# group: userName
user::rw-
group::---
group:project:rwx		#effective:r--
mask::r--
other::---

The mask::r-- reduces the effective group permissions to read-only, despite the parent directory’s ACL specifying rwx for the project group. This discrepancy arises because SQLite explicitly sets file permissions during creation using its internal defaults, bypassing the inheritance logic of POSIX ACLs. The root cause lies in SQLite’s file creation logic, which uses hardcoded permissions unless overridden at compile time or runtime.

Diagnosing Permission Conflicts Between SQLite and POSIX ACLs

The conflict between SQLite’s default permissions and POSIX ACL inheritance stems from three primary factors:

1. SQLite’s Compile-Time Permission Defaults
   SQLite uses the SQLITE_DEFAULT_FILE_PERMISSIONS compile-time flag to set initial file permissions. The default value is 0644 (owner read/write, group/others read). This setting takes precedence over the umask and directory ACLs. Even if the parent directory’s ACLs grant broader permissions, SQLite’s explicit open() call with mode=0644 overrides them.

2. POSIX ACL Mask Interaction
   The ACL mask acts as a permissions ceiling for groups and others. SQLite’s 0644 default sets the file’s base permissions to rw-r--r--. The ACL mask (mask::r--) then restricts the effective group permissions to r--, even if the directory’s default ACL grants rwx to the project group. This occurs because the mask is derived from the base permissions set by SQLite, not the directory’s ACLs.

3. Umask Misconfiguration Myths
   A common misconception is that the umask is responsible for restricting permissions. However, the umask only subtracts permissions from the mode passed to open(). If SQLite uses 0644 and the umask is 0000, the resulting permissions remain 0644. The umask cannot add permissions that SQLite’s open() call does not include in the mode parameter.

Resolving SQLite-POSIX ACL Conflicts: Solutions and Workarounds

Solution 1: Recompile SQLite with Custom Default Permissions

Modify the SQLITE_DEFAULT_FILE_PERMISSIONS compile-time flag to align with your ACL requirements. For example, 0660 grants read/write to the owner and group:

# Download SQLite amalgamation
wget https://www.sqlite.org/2023/sqlite-amalgamation-3420000.zip
unzip sqlite-amalgamation-3420000.zip
cd sqlite-amalgamation-3420000

# Compile with custom permissions
gcc -DSQLITE_DEFAULT_FILE_PERMISSIONS=0660 -o sqlite3 shell.c sqlite3.c -ldl -lpthread

This ensures new databases inherit rw-rw---- permissions, allowing the project group to write. However, this approach is inflexible if different directories require varying permissions.

Solution 2: Post-Creation Permission Adjustment

Use a wrapper script to modify permissions after database creation. For example:

#!/bin/bash
DB_FILE="$1"
sqlite3 "$DB_FILE" "VACUUM;"  # Create the database
chmod 0660 "$DB_FILE"          # Adjust permissions

This method avoids recompiling SQLite but requires manual intervention or script integration.

Solution 3: Custom VFS Implementation

Develop a custom Virtual File System (VFS) that respects POSIX ACLs during file creation. Override the xOpen method to retrieve and apply the parent directory’s default ACL:

static int aclVfsOpen(
  sqlite3_vfs *pVfs,
  const char *zName,
  sqlite3_file *pFile,
  int flags,
  int *pOutFlags
){
  if (flags & SQLITE_OPEN_CREATE) {
    // Retrieve parent directory's default ACL
    acl_t dirAcl = acl_get_file(zName, ACL_TYPE_DEFAULT);
    // Apply ACL to new file
    acl_set_file(zName, ACL_TYPE_ACCESS, dirAcl);
    acl_free(dirAcl);
  }
  return pOriginalVfs->xOpen(pOriginalVfs, zName, pFile, flags, pOutFlags);
}

Register this VFS as the default using sqlite3_vfs_register(). This approach requires C programming expertise and thorough testing.

Solution 4: Leverage Umask and ACL Mask Synergy

Configure the umask to 006 to strip "others" permissions while allowing SQLite’s 0660 default to grant group access:

umask 006
sqlite3 newdb.sqlite

This ensures files are created with rw-rw----, aligning with the directory’s ACLs. However, this affects all file creations in the shell session, not just SQLite.

Solution 5: Filesystem-Level ACL Propagation

Configure ZFS to enforce ACL inheritance aggressively. On ZFS, set:

zfs set aclinherit=passthrough zpool/dataset

This forces new files to inherit parent directory ACLs regardless of the creating application’s permissions. However, this may not override SQLite’s explicit open() mode.

Final Recommendation

For most users, recompiling SQLite with -DSQLITE_DEFAULT_FILE_PERMISSIONS=0660 (Solution 1) provides a balance between simplicity and effectiveness. Advanced users with multiple permission profiles should implement a custom VFS (Solution 3) or filesystem-level ACL propagation (Solution 5).