Skip to content

Introduction

What This Project Is

The PostgreSQL Permissions Model is a standalone, pure-SQL database framework that provides complete multi-tenant user, group, and permissions management for any PostgreSQL project. It is production-ready and used in many real-world applications, both large and small.

You install it by running a set of migration scripts against your PostgreSQL database. Once installed, it gives you:

  • Multi-tenancy with isolated permissions and data per tenant
  • Hierarchical permissions using PostgreSQL's ltree extension
  • Flexible group types -- internal, external (mapped to identity providers), and hybrid
  • Identity provider integration with any provider (AzureAD, Google, Windows Auth, LDAP, KeyCloak, etc.)
  • Permission sets for composable role-based access control
  • Resource-level ACL for per-resource access control with grants, denies, and group inheritance
  • Resource roles -- named flag bundles (e.g., "Editor" = read+write+delete) assigned to users/groups on resources, expanding at check time for instant redefinition
  • Language and translation with context-aware multi-field support (title, description, etc.), materialized view for reads, and full-text/accent-insensitive search
  • API key management (inbound and outbound) with a technical-user pattern
  • User data with structured jsonb columns for settings, preferences, and custom app-specific fields with partial merge support
  • Comprehensive audit logging with multi-key journal entries, event categories, and request context
  • App bootstrapping with idempotent ensure functions and optional _is_final_state declarative sync
  • Auto-lockout on repeated login failures with configurable threshold and time window
  • Multi-factor authentication (TOTP) with enrollment, recovery codes, challenge/verify flow, and policy-based enforcement
  • Invitation system with phases, conditions, templates, and action orchestration
  • User blacklist for blocking re-creation of deleted or banned users
  • Permission caching with automatic invalidation
  • Real-time notifications via PostgreSQL LISTEN/NOTIFY for permission changes
  • Built-in search and pagination with unified _search_criteria jsonb parameter across all search functions

What This Project Is Not

  • Not an ORM. This is a SQL-first framework. You interact with it through PostgreSQL functions and tables, not through an object-relational mapper.
  • Not a web framework. It provides the database layer for authorization. Your application server calls these SQL functions to check permissions, manage users, and handle groups.
  • Not an identity provider. It integrates with external identity providers (AzureAD, Google, LDAP, etc.) but does not implement OAuth or SAML flows itself.
  • Not opinionated about your application schema. It lives in its own set of schemas (auth, internal, unsecure, helpers, etc.) and does not interfere with your application tables.

Key Design Principles

Pure SQL, No External Dependencies

Everything is implemented in PostgreSQL using standard extensions (ltree, uuid-ossp, unaccent, pg_trgm). There is no application code, no middleware, and no external services required. The entire authorization system runs inside the database.

Schema-Based Security Boundaries

The system uses multiple PostgreSQL schemas with strict security contracts:

  • public and auth -- functions that always validate permissions before proceeding
  • internal -- business logic that runs in a trusted context (already permission-checked at a higher level)
  • unsecure -- security system internals only (never for business functions)

See Architecture for a full breakdown of each schema's role.

Fully Qualified Schema Names

All function and table references use fully qualified names (e.g. auth.has_permission, public.__version) to prevent search_path issues. This convention is required in all application code that interacts with the system.

Idempotent Bootstrapping

The ensure_* family of functions lets applications declare their permissions, groups, and mappings at startup. These functions create what is missing, skip what exists, and optionally remove what is no longer defined -- making deployments safe and repeatable.

Who It Is For

This framework is designed for:

  • Backend developers building multi-tenant applications on PostgreSQL who need robust, battle-tested authorization
  • Teams that want a centralized permission model shared across multiple services or microservices connecting to the same database
  • Projects that need hierarchical permissions, flexible group types, and identity provider integration without building it from scratch

It works with any programming language or framework that can talk to PostgreSQL.

How It Integrates With Applications

Direct SQL Integration

The most common integration pattern is calling the permission-checking functions directly from your stored procedures or application queries:

-- inside a stored procedure, check permission before proceeding
if not auth.has_permission(_user_id, _correlation_id, 'orders.cancel_order', _tenant_id, _throw_err := false) then
    perform internal.throw_no_permission(_user_id, 'orders.cancel_order');
end if;

-- or use built-in exception throwing (default behavior)
perform auth.has_permission(_user_id, _correlation_id, 'orders.cancel_order', _tenant_id);

Always use fully qualified schema names

Write auth.has_permission(...), not has_permission(...). Omitting the schema can cause "cannot find function" errors depending on the search_path.

Application Libraries

The keen-auth-permissions Elixir library provides application-level wrappers around these SQL functions, handling connection management, caching, and integration with Phoenix applications.

Additional language libraries are planned.

Service Accounts

The system ships with dedicated service accounts (user IDs 1--999 reserved) so backends never need to use the system superuser at runtime. Each service account has only the permissions required for its specific job:

ID Username Purpose
1 system Seed and migration only -- has has_permissions bypass
2 svc_registrator User registration and email/phone verification
3 svc_authenticator Login and permission resolution
4 svc_token_manager Full token lifecycle
5 svc_api_gateway API key validation
6 svc_group_syncer External group member synchronization
800 svc_data_processor Generic app-level processing

Pass the appropriate service account's user_id when calling auth.* functions from your backend code.

Next Steps

  • Installation & Setup -- prerequisites, environment configuration, and running the migration scripts
  • Architecture -- ER diagram, schema security model, and core entity overview
  • Permission Model -- deep dive into hierarchical permissions, permission sets, and the assignment model