Skip to content

PostgreSQL Permissions Model

A comprehensive, standalone PostgreSQL database model for multi-tenant user, group, and permissions management.

Production-ready and used in real-world projects of all sizes, this pure-SQL framework gives any PostgreSQL application complete handling of multi-tenancy, user management, hierarchical permissions, permission sets, and role-based access control -- without external dependencies.

Feature Highlights

  • Multi-Tenancy -- Isolated tenant contexts with per-tenant permission sets, user access, and preferences.
  • Hierarchical Permissions -- Unlimited nesting using PostgreSQL's ltree extension (e.g. users.create_user.admin_level).
  • Flexible Group Types -- Internal, external (mapped to identity providers), and hybrid membership models.
  • Identity Providers -- Works with any provider: AzureAD, Google, Windows Auth, LDAP, KeyCloak, Facebook, and more.
  • Resource-Level ACL -- Per-resource access control with grants, denies, and group inheritance via list-partitioned tables.
  • Language & Translation -- Full-text and accent-insensitive search across languages with copy and overwrite support.
  • API Key Management -- Inbound and outbound API keys with a technical-user pattern for uniform permission handling.
  • Audit Logging -- Multi-key journal entries, security event trail, request context tracking, and monthly partitioning.
  • Permission Caching -- Automatic permission cache with configurable TTL and automatic invalidation on changes.
  • Real-Time Notifications -- PostgreSQL LISTEN/NOTIFY pushes permission changes to backends instantly after COMMIT.
  • App Bootstrapping -- Idempotent ensure_* functions for declaring permissions, groups, and mappings at startup.
  • User Blacklist -- Block re-creation of deleted or banned users by username and provider identity.

Quick Start

Set up a complete permissions database in minutes:

# Configure connection in debee.env
# PGHOST=localhost  PGPORT=5432  PGUSER=postgres  PGPASSWORD=...  DBDESTDB=your_db

# Full database setup (recreate, restore, and update)
./debee.ps1 -Operations fullService

# Full database setup
./debee.sh -o fullService

# Full database setup
python debee.py -o fullService

Then use the permission system in your application:

-- check user permission (throws exception if denied)
perform auth.has_permission(_tenant_id, _user_id, 'orders.cancel_order');

-- silent permission check (returns boolean)
if auth.has_permission(_tenant_id, _user_id, 'orders.view', _throw_err := false) then
    -- user has permission
end if;

-- create a hierarchical permission
select auth.create_permission_by_path('orders.cancel_order', 'Cancel Order');

-- assign permission to a user
select auth.assign_permission(_created_by, _user_id, _tenant_id, _target_user_id, null, 'orders.cancel_order');

Documentation Sections

Getting Started

Learn what this project is, how to install it, and understand the architecture.

Concepts

Deep dives into the design and behavior of each subsystem.

Function Reference

Complete API documentation for every public function in the system.

Error & Event Codes

Complete reference of all event codes (10xxx--21xxx) and error codes (30xxx--37xxx).