Skip to main content

SQLite Adapter for Casbin: Local Policy Storage Guide

· 5 min read
Serhii Hrekov
Serhii Hrekov
software engineer, creator, artist, programmer, projects founder

Using SQLite as the database adapter for Casbin policy storage is the ideal solution for local development, unit testing, and small-scale applications due to its lightweight, file-based nature.

In the Python Casbin ecosystem, this is achieved using the casbin-sqlalchemy-adapter, as SQLAlchemy natively supports SQLite without needing separate driver installations [2].

1. Installation and Dependencies​

To set up Casbin with SQLite, you only need the core Casbin library and the Casbin SQLAlchemy Adapter:

# Install the core Casbin library
pip install pycasbin

# Install the SQLAlchemy Adapter (handles SQLite, PostgreSQL, MySQL, etc.)
pip install casbin-sqlalchemy-adapter

2. Setting Up the Casbin Enforcer with SQLite​

The setup requires defining the SQLAlchemy connection string that points to your SQLite file. If the file doesn't exist, SQLite and the adapter will automatically create it.

Python Code Example​

import casbin
from casbin_sqlalchemy_adapter import Adapter as SQLAlchemyAdapter

# 1. Define the connection string for the SQLite database file.
# The 'sqlite:///' prefix indicates a file path (three slashes).
# 'policy.db' will be created in the current directory if it doesn't exist.
SQLITE_URL = 'sqlite:///policy.db'

# 2. Instantiate the Adapter
# This links Casbin's policy management to the SQLite file.
adapter = SQLAlchemyAdapter(SQLITE_URL)

# 3. Instantiate the Casbin Enforcer
# 'model.conf' defines the rules (e.g., RBAC, ABAC).
# The adapter is passed here, which triggers the LoadPolicy() operation.
e = casbin.Enforcer('model.conf', adapter)

# The Enforcer is now ready to perform checks against the policy stored in 'policy.db'.

# Example Enforcement
sub = "alice"
obj = "/users/101"
act = "edit"

if e.enforce(sub, obj, act):
    print(f"{sub} is permitted to {act} {obj}.")
else:
    print(f"{sub} is denied access to {obj}.")

Annotation: The SQLAlchemyAdapter translates Casbin policy rules (like p, alice, data1, read) into rows in a table named casbin_rule within the policy.db file [1].

3. Policy Management​

When using the Casbin management API, the adapter ensures changes are immediately saved to the SQLite file, providing persistence across application restarts.

Adding and Removing Policies​

# Adding a new policy rule
e.add_policy("bob", "/data/audit", "view")

# Adding a grouping rule (e.g., assigning a role)
e.add_grouping_policy("bob", "auditor")

# Removing a policy rule
e.remove_policy("alice", "/users/101", "edit")

Every successful call to the Casbin policy management API (e.g., add_policy, remove_grouping_policy) automatically calls the adapter's methods to update the SQLite database file.

Key Considerations for SQLite​

  • No Watcher Needed: In a typical local development scenario, where only one instance of the Flask app is running, a Watcher (used for synchronizing policy changes across multiple services) is not necessary.
  • Performance: SQLite is extremely fast for local, single-user access, making it highly efficient for unit and integration tests.
  • Portability: The use of the SQLAlchemy Adapter means you can switch the policy storage to PostgreSQL or MySQL for staging/production simply by changing the connection string (SQLITE_URL) and installing the corresponding driver, without altering your core Casbin or Flask application code.

Sources​

  1. GitHub - pycasbin/sqlalchemy-adapter
  2. Casbin Documentation - Adapters