Securing GeoPackage Files for Field Use

Encrypt the .gpkg container with SQLCipher, apply OS-level read-only permissions, verify a SHA-256 checksum, and rebuild spatial indexes — in that order — before pushing a GeoPackage to any disconnected field device.

Why This Matters #

A GeoPackage is an ordinary SQLite database. Any process with file-read access — including a two-line Python script — can extract all geometries, attributes, and spatial reference metadata without credentials or a connection string. When a field tablet or phone is lost, that exposure becomes a data-governance incident.

The Security Boundaries & Access Controls model for spatial SQLite requires three non-negotiable layers before a .gpkg leaves your staging environment: cryptographic wrapping at the file layer, permission enforcement at the runtime layer, and checksum verification at the sync layer. This page shows how to implement all three in a repeatable Python pipeline targeted at offline-first GIS deployments.

Prerequisites #

• Python 3.9+ with pysqlcipher3 (SQLCipher-compiled SQLite driver) installed
• SQLCipher 4.x — pysqlcipher3 links against it; verify with python -c "from pysqlcipher3 import dbapi2 as s; c=s.connect(':memory:'); print(c.execute('PRAGMA cipher_version').fetchone())"
• A source .gpkg that passes OGC GeoPackage schema validation (mandatory tables gpkg_contents, gpkg_geometry_columns, gpkg_spatial_ref_sys must exist)
• File-system permissions allowing write access to the destination directory on the staging host, and R-tree index awareness from SpatiaLite Metadata Tables Explained
• hashlib (stdlib) — no extra install

pip install pysqlcipher3

Security Layer Architecture #

The diagram below shows the three-layer model applied to a GeoPackage deployment pipeline.

Primary Method: Encrypting a GeoPackage with SQLCipher #

Standard Python sqlite3 does not include encryption. You need pysqlcipher3, which wraps SQLCipher and exposes the PRAGMA key directive and sqlcipher_export() function required for in-place GeoPackage migration.

The ATTACH DATABASE pattern copies all tables, triggers, and spatial indexes into a new encrypted container while preserving the full GeoPackage Specification Deep Dive schema — including gpkg_extensions, gpkg_tile_matrix_set, and every geometry column registration.

# GeoPackage context: encrypts a .gpkg using SQLCipher 4 AES-256.
import os
from pysqlcipher3 import dbapi2 as sqlcipher


def secure_geopackage_for_field(src_path: str, dst_path: str, passphrase: str) -> None:
    """Encrypt a GeoPackage using SQLCipher and verify schema integrity.

    Args:
        src_path:   Path to unencrypted source .gpkg file.
        dst_path:   Destination path for the encrypted output file.
        passphrase: AES-256 encryption key (provision from OS Keychain in production).
    """
    if not os.path.exists(src_path):
        raise FileNotFoundError(f"Source GPKG not found: {src_path}")
    if os.path.exists(dst_path):
        raise FileExistsError(f"Destination already exists — remove first: {dst_path}")

    # Open the unencrypted source; WAL mode reduces lock contention during export.
    src = sqlcipher.connect(src_path)
    src.execute("PRAGMA journal_mode=WAL")
    src.execute("PRAGMA synchronous=NORMAL")

    # Attach the new encrypted destination.
    # SQLCipher 4 derives AES-256-CBC with PBKDF2 (SHA-512, 256k iterations)
    # at key-derivation time. Any cipher_* PRAGMA adjustments must come BEFORE
    # the PRAGMA key to take effect; here we rely on v4 defaults which match
    # QGIS 3.28+, ArcGIS Pro 3.x, and modern Android GIS SDKs.
    src.execute("ATTACH DATABASE ? AS encrypted KEY ?", (dst_path, passphrase))

    # Copy all schema, data, and GeoPackage spatial metadata tables atomically.
    src.execute("SELECT sqlcipher_export('encrypted')")
    src.execute("DETACH DATABASE encrypted")
    src.close()

    # Verify encryption: opening WITHOUT the key must fail.
    # If it succeeds, the container was not encrypted — raise immediately.
    try:
        test = sqlcipher.connect(dst_path)
        test.execute("SELECT count(*) FROM sqlite_master;")
        test.close()
    except sqlcipher.DatabaseError:
        pass  # Expected: unkeyed access correctly rejected.
    else:
        raise RuntimeError("Encryption failed: file opened without passphrase.")

Key points:

• sqlcipher_export() carries across gpkg_spatial_ref_sys, gpkg_geometry_columns, and all R-tree virtual tables.
• Store passphrases in OS Keychain, AWS KMS, or HashiCorp Vault — never in plaintext config files or version-controlled scripts.
• If you must target SQLCipher 3 compatibility (older QGIS or custom mobile builds), add PRAGMA cipher_compatibility = 3; before PRAGMA key on the destination connection.

Step-by-step Walkthrough #

1. Validate the source file before encryption #

Run schema assertions to confirm the mandatory GeoPackage metadata tables are intact. Encrypting a malformed file does not fix it.

# GeoPackage context: validate mandatory metadata tables before encryption.
import sqlite3


REQUIRED_TABLES = {
    "gpkg_contents",
    "gpkg_geometry_columns",
    "gpkg_spatial_ref_sys",
}


def assert_gpkg_schema(path: str) -> None:
    """Raise ValueError if mandatory GeoPackage tables are missing."""
    conn = sqlite3.connect(path)
    rows = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table'"
    ).fetchall()
    conn.close()

    present = {r[0] for r in rows}
    missing = REQUIRED_TABLES - present
    if missing:
        raise ValueError(f"Source .gpkg is missing required tables: {missing}")

2. Encrypt the file #

Call secure_geopackage_for_field() from the primary method above. After encryption, the source file should remain in place on the staging host; only the encrypted output travels to the field device.

3. Rebuild spatial indexes on the encrypted file #

sqlcipher_export() copies R-tree virtual table definitions but does not guarantee that the index is populated on the encrypted copy. Rebuild it using SpatiaLite’s CreateSpatialIndex() before shipping. As detailed in SpatiaLite Metadata Tables Explained, the R-tree backing tables (*_node, *_parent, *_rowid) must be current for sub-second spatial queries.

# SpatiaLite context: rebuild R-tree spatial index after encryption.
# table_name and geom_column are trusted, developer-supplied identifiers;
# they cannot be bound as SQL parameters.
def rebuild_spatial_indexes(
    conn,  # pysqlcipher3 connection with mod_spatialite loaded
    table_name: str,
    geom_column: str,
) -> None:
    """Rebuild R-Tree spatial index; run after sqlcipher_export."""
    conn.execute(f"SELECT CreateSpatialIndex('{table_name}', '{geom_column}');")
    # Truncate the WAL file to reclaim storage on constrained field devices.
    conn.execute("PRAGMA wal_checkpoint(TRUNCATE);")
    conn.commit()

4. Apply OS-level read-only permissions #

Deploy the encrypted file to a read-only location before it reaches the device. This prevents accidental overwrites by the field app even if the passphrase is available.

# Python context: apply read-only file permissions before sync.
import os
import stat


def make_readonly(path: str) -> None:
    """Remove all write bits (owner/group/other) from the file."""
    current = os.stat(path).st_mode
    readonly = current & ~(stat.S_IWUSR | stat.S_IWGRP | stat.S_IWOTH)
    os.chmod(path, readonly)

On Android, push to the app’s internal files/ directory (not external storage) and use Context.openFileInput() — this sandboxes the file from other apps automatically.

5. Generate and distribute the integrity manifest #

# Python context: SHA-256 checksum for offline integrity verification.
import hashlib
import json
import os


def generate_gpkg_checksum(file_path: str) -> str:
    """Return SHA-256 hex digest of the encrypted .gpkg file."""
    sha256 = hashlib.sha256()
    with open(file_path, "rb") as f:
        for chunk in iter(lambda: f.read(8192), b""):
            sha256.update(chunk)
    return sha256.hexdigest()


def write_manifest(gpkg_path: str, manifest_path: str) -> None:
    """Write a JSON manifest with file name and SHA-256 hash."""
    checksum = generate_gpkg_checksum(gpkg_path)
    manifest = {
        "file": os.path.basename(gpkg_path),
        "sha256": checksum,
    }
    with open(manifest_path, "w", encoding="utf-8") as f:
        json.dump(manifest, f, indent=2)

Distribute the manifest over a separate secure channel — not bundled in the same archive as the .gpkg. Field apps compare the computed hash against the manifest value on every launch; a mismatch triggers a quarantine and re-download request rather than silent data exposure.

6. Restrict attribute access with SQLite views #

Before shipping, create column-filtered views so field technicians query only the data relevant to their role. This pattern integrates naturally with the connection pooling layer in automated sync pipelines.

# GeoPackage context: create role-filtered view on encrypted connection.
def create_field_view(conn, table_name: str) -> None:
    """Expose only geometry and operational columns; mask PII/financials."""
    conn.execute(f"""
        CREATE VIEW IF NOT EXISTS {table_name}_field_view AS
        SELECT
            fid,
            geometry,
            feature_type,
            status,
            last_updated
        FROM {table_name}
        -- Columns omitted: owner_id, valuation, internal_notes
    """)
    conn.commit()

Verification #

After completing all steps, confirm the encrypted file is correctly locked:

# Python context: verify encrypted GeoPackage rejects unkeyed access.
from pysqlcipher3 import dbapi2 as sqlcipher


def verify_encryption(path: str) -> bool:
    """Return True when unkeyed access raises DatabaseError (file is encrypted)."""
    try:
        conn = sqlcipher.connect(path)
        conn.execute("SELECT count(*) FROM sqlite_master;")
        conn.close()
        return False  # No error = not encrypted.
    except sqlcipher.DatabaseError:
        return True


assert verify_encryption("field_survey_encrypted.gpkg"), "Encryption check failed"

Also confirm the spatial metadata survived encryption:

# Verify gpkg_contents is intact via ogrinfo (GDAL 3.4+)
# Set GPKG_SQLITE_CIPHER_PASSPHRASE if your GDAL build links SQLCipher.
ogrinfo -al -so field_survey_encrypted.gpkg

Alternative Approaches and Edge Cases #

CLI approach with sqlcipher binary: If Python tooling is unavailable in your deployment pipeline, the sqlcipher CLI (available via Homebrew or system packages) can perform the same export:

# Shell context: encrypt an existing .gpkg using sqlcipher CLI.
sqlcipher field_survey.gpkg \
  "ATTACH DATABASE 'field_survey_enc.gpkg' AS enc KEY 'your-passphrase'; \
   SELECT sqlcipher_export('enc'); DETACH DATABASE enc;"

Android safe temp-file handling: Never write an intermediate decrypted file to external storage or the system /tmp directory. On Android, use the app’s getCacheDir() exclusively, set to mode 0600, and wipe it immediately after any in-memory operation completes. External SD cards bypass Android’s per-app sandbox and expose unencrypted content to other applications.

Read-only WAL mode for distributed field copies: When multiple devices receive the same reference .gpkg (e.g. a base map), open it with immutable=1 in the URI to prevent WAL file creation entirely:

# GeoPackage context: open encrypted file as immutable read-only replica.
import urllib.parse
from pysqlcipher3 import dbapi2 as sqlcipher

uri = "file:{}?immutable=1".format(urllib.parse.quote(path))
conn = sqlcipher.connect(uri, uri=True)
conn.execute("PRAGMA key='your-passphrase'")

This eliminates WAL file creation and write-lock acquisition, which is critical when the file lives on a read-only mount or a network-shared volume.

Troubleshooting #

DatabaseError: file is not a database Cause: You opened the encrypted .gpkg with standard sqlite3 (no key supplied), or with a SQLCipher version that uses different cipher defaults. Fix: Use pysqlcipher3 and supply the passphrase immediately via PRAGMA key. If the file was encrypted with SQLCipher 3 defaults, add conn.execute("PRAGMA cipher_compatibility = 3;") before the key PRAGMA.

OperationalError: no such function: CreateSpatialIndex Cause: mod_spatialite is not loaded on the encrypted connection before calling rebuild_spatial_indexes(). Fix: Load the extension first: conn.enable_load_extension(True); conn.execute("SELECT load_extension('mod_spatialite')");. The correct .so/.dylib/.dll path for your platform is covered in Extension Compatibility in Spatial SQLite.

Spatial queries slow after encryption despite index rebuild Cause: PRAGMA journal_mode reverted to DELETE during export, preventing WAL-mode concurrent reads. Fix: After opening the encrypted file with the correct key, execute PRAGMA journal_mode=WAL; before any spatial queries. Also run PRAGMA cache_size=-32000; (32 MB page cache) on low-RAM field tablets to avoid repeated disk I/O for large geometry BLOBs.

Deployment Checklist for Field Teams #

Before pushing .gpkg files to disconnected environments, verify the following:

• AES-256 encryption applied via SQLCipher with cipher_compatibility = 4
• Passphrases distributed via OS Keychain or secure enclave (not email or Slack)
• gpkg_contents, gpkg_geometry_columns, and gpkg_spatial_ref_sys tables intact post-encryption
• R-tree spatial indexes rebuilt with CreateSpatialIndex() on the encrypted copy
• SHA-256 manifest generated and stored separately from the .gpkg
• File marked read-only (chmod 444 / SetFileAttributes) before sync
• Field app configured to query encrypted handle only — no plaintext fallback on disk
• Delta sync writes use transactional batches with rollback on checksum mismatch

Related #

• Security Boundaries & Access Controls — parent guide covering the full three-layer security model for spatial SQLite
• GeoPackage Specification Deep Dive — mandatory table schemas and OGC compliance requirements
• SpatiaLite Metadata Tables Explained — R-tree index internals and metadata table contracts
• Extension Compatibility in Spatial SQLite — platform-specific mod_spatialite loading and version pinning
• Connection Pooling & Lifecycle Management — managing encrypted connection handles in multi-threaded sync pipelines