SpatiaLite & GeoPackage Automation with Python

How to Encrypt a GeoPackage with SQLCipher in Python

Install the sqlcipher3 package, open a connection, run PRAGMA key = '<secret' as the very first statement, and export your plaintext .gpkg into the keyed…

Install the sqlcipher3 package, open a connection, run PRAGMA key = '<secret>' as the very first statement, and export your plaintext .gpkg into the keyed handle — SQLCipher then transparently AES-256 encrypts every page written to disk.

This page drills into the cryptographic mechanics that sit underneath the Security Boundaries & Access Controls guide: the exact PRAGMA sequence, page-size tuning, in-place key rotation, and where the passphrase actually comes from. For the wider deployment pipeline — checksums, read-only mounts, and role views — see the companion walkthrough on securing GeoPackage files for field use.

Why This Matters

A GeoPackage is a plain SQLite database with a documented header, so the bytes on disk are self-describing. Anyone who copies the file can read the GeoPackage Binary geometry blobs, the attribute rows, and the spatial reference metadata with nothing more than the standard library. Encryption at rest closes that gap: without the key, the file is indistinguishable from random bytes, and even the SQLite magic string in the first 16 bytes is scrambled.

SQLCipher is the de-facto standard for encrypted SQLite. It re-encrypts each database page with AES-256 and stores a per-page HMAC so tampering is detectable. Because it operates below the SQL layer, your queries, triggers, and the R-tree spatial index all keep working unchanged once the key is supplied. The trade-off is a real but modest CPU cost per page and one hard constraint: only SQLCipher-aware builds of GDAL or QGIS can open the result.

Prerequisites

  • Python 3.9+ with the sqlcipher3 wheel installed (pip install sqlcipher3-binary, which bundles the SQLCipher amalgamation)
  • SQLCipher 4.x runtime — confirm with python -c "import sqlcipher3; print(sqlcipher3.connect(':memory:').execute('PRAGMA cipher_version').fetchone())"
  • A source .gpkg that already passes OGC GeoPackage schema validation
  • A secret store for the passphrase — OS keychain, environment variable injected by a secrets manager, or a hardware enclave — never a plaintext config file
  • Basic familiarity with SQLite WAL mode and PRAGMA statements

Primary Method

The clean way to encrypt an existing GeoPackage is to open the plaintext file, ATTACH a keyed destination, and call sqlcipher_export() to stream every table into it. The critical detail is statement order: PRAGMA key and any cipher_* PRAGMA on the destination must run before the first byte is read or written, otherwise SQLCipher falls back to defaults.

python
# GeoPackage context: AES-256 encrypt a .gpkg at rest with the sqlcipher3 package.
import os
import sqlcipher3


def encrypt_geopackage(src_path: str, dst_path: str, passphrase: str) -> None:
    """Produce an AES-256 encrypted copy of a plaintext GeoPackage.

    Args:
        src_path:   Path to the unencrypted source .gpkg.
        dst_path:   Path for the encrypted output (must not already exist).
        passphrase: Encryption key, sourced from a keychain — never hard-coded.
    """
    if not os.path.exists(src_path):
        raise FileNotFoundError(f"Source GeoPackage not found: {src_path}")
    if os.path.exists(dst_path):
        raise FileExistsError(f"Refusing to overwrite existing file: {dst_path}")

    # Open the PLAINTEXT source. No key is set on this handle.
    conn = sqlcipher3.connect(src_path)
    try:
        # Attach the encrypted destination with its key. SQLCipher 4 derives
        # AES-256 via PBKDF2-HMAC-SHA512 (256k iterations) from this passphrase.
        conn.execute("ATTACH DATABASE ? AS enc KEY ?", (dst_path, passphrase))

        # Page size is a schema-time property; set it on the fresh container
        # BEFORE any table is written. 4096 matches GDAL's default expectation.
        conn.execute("PRAGMA enc.cipher_page_size = 4096")

        # Stream all tables, indexes, triggers and GeoPackage metadata across.
        conn.execute("SELECT sqlcipher_export('enc')")
        conn.execute("DETACH DATABASE enc")
    finally:
        conn.close()

sqlcipher_export() copies the complete schema — gpkg_contents, gpkg_geometry_columns, gpkg_spatial_ref_sys, every user layer, and the R-tree virtual tables — into the encrypted container in one pass, so the output is a fully valid GeoPackage that merely happens to be unreadable without the key.

Step-by-Step Walkthrough

1. Resolve the passphrase from a secret store

Load the key at runtime; do not accept it as a literal in the script. The keyring library reads the OS keychain (macOS Keychain, Windows Credential Locker, or Secret Service on Linux), with an environment variable as a CI fallback.

python
import os
import keyring


def resolve_passphrase(service: str = "gpkg-field", account: str = "default") -> str:
    """Fetch the encryption key from the OS keychain, falling back to env."""
    secret = keyring.get_password(service, account) or os.environ.get("GPKG_KEY")
    if not secret:
        raise RuntimeError("No encryption key available in keychain or GPKG_KEY")
    return secret

2. Choose the cipher page size deliberately

cipher_page_size controls how many bytes SQLCipher encrypts per block. Larger pages reduce per-page HMAC overhead for scan-heavy spatial queries; 4096 is the safe default that matches GDAL’s compiled expectations. It must be fixed at creation time — you cannot change it later without a full re-export.

python
# GeoPackage context: page size is set once, before the first write.
conn.execute("PRAGMA enc.cipher_page_size = 4096")

3. Open the encrypted GeoPackage for querying

To read the file back, supply the key as the first statement after connecting. Any query issued before PRAGMA key returns file is not a database.

python
import sqlcipher3

conn = sqlcipher3.connect("field_survey_enc.gpkg")
conn.execute("PRAGMA key = ?", (resolve_passphrase(),))
# Only now is the database readable.
row = conn.execute("SELECT table_name FROM gpkg_contents").fetchone()
print(row)

4. Rotate the key in place with PRAGMA rekey

When a passphrase is suspected compromised, PRAGMA rekey re-encrypts every page with a new key without producing an intermediate plaintext copy. Open with the current key, then rekey.

python
# GeoPackage context: rotate the encryption key without decrypting to disk.
def rotate_key(path: str, old_key: str, new_key: str) -> None:
    conn = sqlcipher3.connect(path)
    conn.execute("PRAGMA key = ?", (old_key,))
    conn.execute("PRAGMA rekey = ?", (new_key,))
    conn.close()

Rekeying rewrites the whole file, so it briefly needs free space equal to the database size and should run while no other process holds the file open.

Verification

Confirm the output is genuinely encrypted by proving that an unkeyed connection fails and a keyed one succeeds.

python
# Python context: an encrypted GeoPackage must reject unkeyed access.
import sqlcipher3


def is_encrypted(path: str, passphrase: str) -> bool:
    # Unkeyed read must fail.
    bare = sqlcipher3.connect(path)
    try:
        bare.execute("SELECT count(*) FROM sqlite_master")
        return False  # Readable without a key — NOT encrypted.
    except sqlcipher3.DatabaseError:
        pass
    finally:
        bare.close()
    # Keyed read must succeed.
    keyed = sqlcipher3.connect(path)
    keyed.execute("PRAGMA key = ?", (passphrase,))
    keyed.execute("SELECT count(*) FROM gpkg_contents")
    keyed.close()
    return True


assert is_encrypted("field_survey_enc.gpkg", resolve_passphrase())

You can also inspect the raw header: on a plaintext GeoPackage the first bytes read SQLite format 3, while an encrypted file shows random bytes there.

bash
# Shell context: plaintext SQLite starts with a known magic string; encrypted does not.
head -c 16 field_survey_enc.gpkg | xxd

Alternative Approaches or Edge Cases

Reading the file from GDAL or QGIS. Standard GDAL and QGIS binaries link plain SQLite and cannot open a SQLCipher file — ogrinfo reports not a database. You need a build compiled against SQLCipher, after which GDAL exposes the key through the OGR_SQLITE_PRAGMA open option or an environment variable. The diagram below shows which consumers can and cannot open the encrypted container.

SQLCipher GeoPackage client compatibilityAn encrypted GeoPackage in the center is readable by sqlcipher3 in Python and by SQLCipher-aware GDAL and QGIS builds, but is rejected by stock GDAL, stock QGIS, and the standard library sqlite3 module, all of which report that the file is not a database.Encrypted .gpkgAES-256 pagessqlcipher3 (Python) — opensSQLCipher-aware GDAL — opensSQLCipher-aware QGIS — opensstock GDAL — rejectedstock QGIS — rejectedstdlib sqlite3 — rejected

SQLCipher 3 compatibility. If a legacy mobile build uses SQLCipher 3 defaults, add PRAGMA cipher_compatibility = 3 immediately after PRAGMA key on every connection, since the KDF iteration count and HMAC algorithm changed between major versions.

Performance overhead. Expect roughly 5–15% added latency on read-heavy spatial workloads because each page is decrypted and HMAC-verified on access. Raise the page cache (PRAGMA cache_size = -32000 for 32 MB) so hot geometry pages stay decrypted in memory rather than being re-verified on every query.

Troubleshooting

DatabaseError: file is not a database Cause: a query ran before PRAGMA key, the key is wrong, or a stock sqlite3 build opened a SQLCipher file. Fix: connect with sqlcipher3, make PRAGMA key the first statement, and confirm the passphrase resolved correctly from the keychain.

OperationalError: An error occurred with PRAGMA cipher_page_size Cause: cipher_page_size was set after a table already existed on the encrypted handle. Fix: set the page size on the attached destination before sqlcipher_export(), or re-run the export into a fresh file.

ogrinfo reports not a recognized file format on the encrypted file Cause: the GDAL build is not linked against SQLCipher. Fix: install a SQLCipher-aware GDAL, or decrypt to a secure temporary handle for tools that cannot supply a key — never leave that plaintext copy on shared storage.