What is a Postgres schema per module, and why isn't a naming convention enough?

A Postgres schema per module means each module in a modular monolith gets its own schema, such as billing, catalog or identity, and each schema is owned by a dedicated role. The billing module's application code connects as a billing role. That role holds privileges only on the billing schema and on whatever other modules explicitly grant it.

Teams often try to get the same result with conventions instead: table prefixes like billing_invoices, one migrations folder per module, and a review rule against cross-module joins. These help people read the code, but the database does not check any of them. If every module connects as the same role, any query can join billing_invoices to catalog_products, and Postgres will run it. A reviewer has to notice that join in a large diff, under deadline, every time. That includes joins buried in an ORM relation or a reporting query.

Moving tables into separate schemas does not close this gap. A schema on its own is only a namespace. A role that has USAGE on the schema and SELECT on its tables can still query them with a qualified name. Data ownership becomes enforceable only when each module runs as its own role and that role lacks privileges on schemas it does not own. Then an unauthorized cross-module query fails with a permission error.

This matters most for teams where several groups own different modules in one codebase, or where a module may later be extracted into its own service. Because the boundary fails loudly, violations show up as failing tests close to the commit that introduced them. Without it, the hidden dependencies tend to surface only during the extraction, when they are much more expensive to untangle.

Who should use a Postgres schema per module?

The pattern fits teams running a modular monolith on a single Postgres database who want module boundaries that the database enforces rather than boundaries that depend on review discipline. In practice that means:

  • Organizations where several teams own different modules in one codebase. Each team can change its own tables and migrations. Other teams can depend only on what that team publishes in its contract schema, so a schema change in billing cannot silently break a query written by the orders team.
  • Teams that expect to extract a module into a separate service. The grants become an inventory of who depends on what. Extraction starts from a known list of consumers instead of a search through the codebase.
  • Teams that have already had incidents from accidental coupling, such as a report that joined into another module's tables and broke when those tables changed, or a module that wrote rows it did not own.
  • Systems where limiting which code can read sensitive tables matters. If only the identity runtime role can read credential tables, a bug or injection in another module cannot reach them through its own connection.

It also needs someone who will own the automation: role creation, default privileges, the expected-grants manifest and the CI checks. Without that owner, the grants drift. For small single-team codebases, or when the real problem is resource contention, see the section on when to avoid the pattern below.

How does it work? The schema and role boundary

In short, each module's data sits in schemas owned by a role that only that module's migrations use. The module's application connects as a separate runtime role with data access but no DDL rights. Other modules get access only to a published contract schema. Postgres checks these privileges on every query, so the boundary is enforced by the database rather than by convention.

Each module gets two schemas: orders for internal tables and orders_api for the views or functions other modules may read. Both are created with AUTHORIZATION orders_owner, a NOLOGIN role. Because that role cannot log in, migrations reach it through SET ROLE from a deploy identity. Every object they create is then owned by the module rather than by whoever ran the deploy. At runtime the module connects as orders_app, a login role that has DML rights on its own tables but cannot alter them. Add a shared application role only when one process truly spans modules, and grant it as little as possible.

 billing module                          orders module
 billing_owner (NOLOGIN) ──owns──┐       orders_owner (NOLOGIN) ──owns──┐
 billing_app (LOGIN)             │       orders_app (LOGIN)             │
                                 ▼                                      ▼
 [billing]   [billing_api]  ◄────────┐   [orders]   [orders_api]
     ▲                               │       ▲          ▲
 billing_app: USAGE + DML            │   orders_app: USAGE + DML
                                     │                  │
             orders_app ── USAGE + SELECT ─┘   billing_app ── USAGE + SELECT ──┘
                (on billing_api)                  (on orders_api)

 [public]: no tables; CREATE revoked from PUBLIC

Schema access and table access are checked separately, and a query needs both:

Privilege Granted on What it allows Missing it gives
USAGE schema resolving names inside the schema "permission denied for schema"
SELECT/INSERT/UPDATE/DELETE table or view operating on rows "permission denied for table"

USAGE alone does not expose any data. Table grants cannot be used if the role cannot reach the schema that holds the table.

Keep public empty and revoke CREATE on it from PUBLIC wherever that default still applies. Otherwise it becomes an unowned place where cross-module tables collect.

These checks only work while connections use ordinary roles. Superusers skip privilege checks entirely. The database owner can create schemas outside this layout. Predefined roles that read all data, or a broad GRANT ... ON ALL TABLES, get past the boundary without any error. BYPASSRLS skips any row-level policies you add on top.

┌─ orders module ──────────────────┐   ┌─ billing module ─────────────────┐
│ orders_owner (NOLOGIN)            │   │ billing_owner (NOLOGIN)           │
│   │ owns; migrations via SET ROLE │   │   │ owns; migrations via SET ROLE │
│   ├────────────┐                  │   │   ├────────────┐                  │
│   ▼            ▼                  │   │   ▼            ▼                  │
│ ┌────────┐   ┌────────────┐       │   │ ┌─────────┐  ┌─────────────┐      │
│ │ orders │   │ orders_api │       │   │ │ billing │  │ billing_api │      │
│ └────────┘   └────────────┘       │   │ └─────────┘  └─────────────┘      │
│   ▲                               │   │   ▲                               │
│   │ USAGE + DML (no DDL)          │   │   │ USAGE + DML (no DDL)          │
│ orders_app (LOGIN)                │   │ billing_app (LOGIN)               │
└───────────────────────────────────┘   └───────────────────────────────────┘

 Cross-module paths (USAGE on the *_api schema + SELECT on its views):
   billing_app ──USAGE+SELECT──▶ orders_api    allowed
   orders_app  ──USAGE+SELECT──▶ billing_api   allowed
   billing_app ───────╳────────▶ orders        denied: no USAGE on schema

┌─ public ─────────────────────┐  ┌─ bypass the boundary ──────────────────┐
│ no tables; CREATE revoked    │  │ superuser; database owner;             │
│ from PUBLIC                  │  │ roles with BYPASSRLS or broad grants   │
└──────────────────────────────┘  └────────────────────────────────────────┘

Each module's NOLOGIN owner role owns its internal and api schemas, and its LOGIN runtime role gets USAGE plus DML on its own tables. Other modules reach only the api schema through USAGE plus SELECT. Superusers and broadly privileged roles sit outside this boundary.

How do you implement schema permissions per module step by step?

Start by closing the default door. Run REVOKE CREATE ON SCHEMA public FROM PUBLIC so no role can drop tables into public and sidestep the layout. Some installations already default to this, but stating it explicitly makes the intent visible.

Roles, then the schema

For each module, create two roles. One is a NOLOGIN owner, for example billing_owner, that owns the objects. The other is a login runtime role, for example billing_app, that the service connects as. Then run CREATE SCHEMA billing AUTHORIZATION billing_owner. The runtime role does not own the tables, so it cannot drop or alter them, even if the application is compromised.

Grant USAGE ON SCHEMA billing to billing_app, followed by SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA billing. That grant only covers tables that exist at that moment. To cover tables that migrations create later, run ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing, granting the same table privileges plus USAGE, SELECT ON SEQUENCES.

Default privileges apply only to objects created by the named role. If a migration runs as a superuser or a deploy account, its tables get no automatic grants, and the runtime role fails with permission errors. Run migrations as the owner role, either directly or via SET ROLE.

Name resolution

Set ALTER ROLE billing_app SET search_path = billing. In migrations, schema-qualify every name, as in billing.invoices. An unqualified name resolves through whatever search path is in effect at that moment, so it can silently point at another module's table of the same name.

Shared objects

Extensions, shared enum or domain types, and standalone sequences need a named owner too. Put them in a shared schema owned by a platform role or a designated module, for example with CREATE EXTENSION ... SCHEMA shared. Grant other modules USAGE on that schema and nothing more. Without a named owner, these objects turn into a module that nobody is responsible for changing.

-- Run as an admin role. 1) Close the default door.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 2) A NOLOGIN owner for the objects and a login role for the service.
CREATE ROLE billing_owner NOLOGIN;
CREATE ROLE billing_app LOGIN PASSWORD 'replace-with-secret-from-vault';
CREATE SCHEMA billing AUTHORIZATION billing_owner;

-- 3) Runtime access to objects that already exist.
GRANT USAGE ON SCHEMA billing TO billing_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA billing TO billing_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA billing TO billing_app;

-- 4) Future objects. This applies ONLY to objects created by billing_owner.
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO billing_app;
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
  GRANT USAGE, SELECT ON SEQUENCES TO billing_app;

-- 5) Pin name resolution for the runtime role.
ALTER ROLE billing_app SET search_path = billing;

-- 6) Shared objects get an explicit owner instead of living in public.
CREATE ROLE platform_owner NOLOGIN;
CREATE SCHEMA shared AUTHORIZATION platform_owner;
GRANT USAGE ON SCHEMA shared TO billing_owner, billing_app;
-- Extension objects are placed in shared (owned by whoever runs CREATE EXTENSION).
CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA shared;
CREATE TYPE shared.currency_code AS ENUM ('USD', 'EUR', 'GBP');
ALTER TYPE shared.currency_code OWNER TO platform_owner;

-- 7) Migrations run as the owner and schema-qualify every name.
-- (The admin running this must be a member of billing_owner.)
SET ROLE billing_owner;
CREATE TABLE billing.invoices (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  currency shared.currency_code NOT NULL
);
RESET ROLE;

End-to-end setup for one module: the owner and runtime roles, schema grants, default privileges scoped to the owner role, a pinned search_path, a shared schema with a named owner, and a schema-qualified migration run as the owner.

How should modules share data on purpose?

Give each module a second schema that is its public contract, for example billing_api_v1, separate from the private billing schema. It holds only views and functions. Grant USAGE on it, plus SELECT or EXECUTE on specific objects, to named consumer roles such as orders_rw. Never grant those roles anything on billing itself.

A view like billing_api_v1.invoice_summary runs with its owner's rights, so consumers read the columns it exposes without touching the base tables. A function like billing_api_v1.mark_invoice_paid(invoice_id) declared SECURITY DEFINER with SET search_path = pg_catalog, pg_temp can write on their behalf. Every change to the contract then shows up as a reviewable migration. A breaking change ships as billing_api_v2 while v1 stays in place for existing callers.

The search_path pin is required. Without it, a caller who can create objects in a schema that appears earlier on the path can shadow an operator or function, and that code then runs with the owner's privileges.

Mechanism Explicit contract Planner cost Extraction cost
API view Yes Simple views are inlined; aggregates or DISTINCT can block predicate pushdown Low: becomes an endpoint
SECURITY DEFINER function Yes Not inlined; opaque to the planner Low
Cross-schema foreign key Partial None High
Shared transaction No None High

A foreign key from orders to billing requires the REFERENCES privilege on the referenced table. It also lets orders block deletes and schema changes inside billing. Atomic transactions across modules are a real advantage of keeping one database, so use them where consistency matters. But each one, like each foreign key, is something you will have to replace with a saga or an outbox before you can split the modules into separate services.

-- Run as the role that owns the billing schema, so views and functions execute with its rights.
SET ROLE billing_owner;

-- The contract schema. It holds only views and functions, never tables.
CREATE SCHEMA billing_api_v1;

-- Read contract: expose chosen columns. Consumers never reference billing.invoices directly.
CREATE VIEW billing_api_v1.invoice_summary AS
SELECT id AS invoice_id, order_id, total_cents, status
FROM billing.invoices;

-- Write contract: runs with the owner's privileges.
-- The pinned search_path stops callers from shadowing functions or operators,
-- so every table reference inside the body is schema-qualified.
CREATE FUNCTION billing_api_v1.mark_invoice_paid(p_invoice_id bigint)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
BEGIN
  UPDATE billing.invoices
     SET status = 'paid', paid_at = now()
   WHERE id = p_invoice_id AND status = 'open';
  RETURN FOUND;  -- false if the invoice was missing or not open
END;
$$;

-- New functions are executable by PUBLIC by default. Restrict execution to named consumers.
REVOKE ALL ON FUNCTION billing_api_v1.mark_invoice_paid(bigint) FROM PUBLIC;

-- Grant the contract to a specific consumer role. Nothing is granted on schema billing.
GRANT USAGE ON SCHEMA billing_api_v1 TO orders_rw;
GRANT SELECT ON billing_api_v1.invoice_summary TO orders_rw;
GRANT EXECUTE ON FUNCTION billing_api_v1.mark_invoice_paid(bigint) TO orders_rw;

RESET ROLE;

A billing_api_v1 contract schema with a view and a SECURITY DEFINER function that has a pinned search_path. Both are granted only to the orders_rw consumer role, and the private billing schema stays ungranted.

Wiring connections and migrations per module

There are two ways to connect each module to its role. You can give every module its own connection string and pool that logs in as its role. Or you can use one shared pool whose sessions switch identity with SET ROLE.

Approach Boundary strength Connection cost Main risk
Pool per module role Enforced at login Multiplies with modules Exhausting max_connections
Shared pool + SET ROLE Only as strong as the calling code One pool per instance Role escape and role leakage

The shared pool is weaker than it looks. The login role must be a member of every module role. Any code path that can issue SET ROLE or RESET ROLE can therefore step out of its boundary, including a careless query helper. Transaction-mode poolers add a second risk. A session-level SET ROLE stays on the server connection, so if the pooler does not reset session state, the next client can inherit that role. SET LOCAL ROLE inside each transaction limits the damage but does not remove it.

Pool-per-role costs connections. Illustrative estimate: assume 8 modules, 6 app instances and a pool size of 5. That gives 8 × 6 × 5 = 240 connections, well above the default max_connections of 100. A shared pool of 15 per instance needs 90.

Keep migrations per module. Give each schema its own directory and its own history table, and run them as that module's owner role. When one module's api view reads another module's tables, the provider's migrations must run first. The migration runs then form a dependency graph that fresh environments must follow in order. Rollbacks get harder too. Postgres refuses to drop or retype a column that a view depends on, so the consumer's view must be dropped before the provider can roll back, and recreated afterwards.

-- Shared-pool setup: the login role must be a member of every module role
CREATE ROLE app_login LOGIN PASSWORD 'placeholder-not-a-secret';
CREATE ROLE orders_rw NOLOGIN;
CREATE ROLE billing_rw NOLOGIN;
GRANT orders_rw, billing_rw TO app_login;

-- Intended per-request pattern: scope the role to one transaction
BEGIN;
SET LOCAL ROLE orders_rw;
SELECT current_user;                 -- orders_rw
SELECT count(*) FROM orders.order_line;
COMMIT;                              -- role reverts to app_login here

-- The escape: any code running in the same transaction can switch roles
BEGIN;
SET LOCAL ROLE orders_rw;
RESET ROLE;                          -- back to app_login, a member of every module role
SET LOCAL ROLE billing_rw;
SELECT * FROM billing.invoice;       -- succeeds; the orders boundary is gone
COMMIT;

-- The leak: a session-level SET ROLE survives COMMIT on the server connection
SET ROLE orders_rw;
COMMIT;                              -- warns that no transaction is open; role still set
SELECT current_user;                 -- still orders_rw for the next client
                                     -- if the pooler reuses this connection as-is

-- What a pooler must run before handing the connection to another client
DISCARD ALL;                         -- resets role and other session state
SELECT current_user;                 -- app_login

With a shared pool, SET LOCAL ROLE scopes a module role to one transaction, but RESET ROLE lets code escape that role. A session-level SET ROLE also remains on the server connection until something such as DISCARD ALL clears it.

How do you verify the boundary in CI and catch privilege drift?

The boundary only holds if something checks it on every change. Without that, each new module adds roles and grants that nobody reviews, and the cleanup later costs more than the automation would have.

Negative tests per runtime role

For each module, open a connection as its runtime role, not the migration role and not a superuser, and query another module's internal tables. Assert that the query fails with SQLSTATE 42501 (insufficient_privilege). Do not just assert that the query raised an error, because a missing table or a typo would also pass. Also include a positive case, such as reading the provider's published views. That way the suite fails if the grants are too tight as well as too loose.

Run these tests with ordinary roles. Superusers skip permission checks entirely, and table owners can usually access their own tables. Local setups often connect as postgres, and admin tools often connect as an owner. In both cases a missing REVOKE goes unnoticed until production, where the runtime role finally hits it.

Detecting privilege drift

Keep an expected-grants manifest in the repository and compare the live catalog against it:

  • information_schema.role_table_grants for table privileges. This view only shows rows where the grantor or grantee is a role that the current user has enabled, so run the check as an audit role that can see every module.
  • pg_namespace.nspacl for USAGE and CREATE on schemas, expanded with aclexplode().
  • pg_class.relacl for sequences and views.
  • pg_default_acl for default privileges. These matter most, because a stray default grant gives access to every table created after it, without anyone writing a new grant.

Fail the build on any difference between the catalog and the manifest. If the difference is intentional, the fix is a reviewed edit to the manifest.

How can a schema per module save engineering time?

The pattern costs setup time up front: roles, default privileges, a grants manifest and CI tests. It pays that back by moving work that people would otherwise do by hand, late and under pressure, into checks that run automatically.

  • Less review effort spent policing boundaries. Reviewers no longer need to scan every diff for cross-module joins hidden in ORM relations or reporting queries. The database rejects them, and the negative tests in CI catch them. Review time goes to design questions instead.
  • Coupling is found at commit time, not during extraction. A forbidden query fails with a permission error in the test run of the change that introduced it, while the author still has the context to fix it. Without enforcement, the same dependency is typically discovered months later by someone else, who first has to work out why it exists.
  • Dependency inventories come from the catalog. Before extracting billing, you can list the grants on billing_api_v1 and the views and functions in it. That tells you which modules consume billing data and through which objects. You do not have to search the codebase, the ORM models and ad hoc scripts to build the same list.
  • Fewer cross-team coordination loops for schema changes. A team can rename or restructure tables in its private schema without asking other teams, because nothing outside the module is allowed to reference them. Only changes to the contract schema need coordination, and those are visible as migrations, with v2 schemas for breaking changes.
  • Smaller blast radius during incidents. A runtime role cannot drop or alter tables and cannot read other modules' data. A bad deploy or a compromised module is therefore confined to its own schema, which narrows what responders have to investigate.
  • Grants are reproducible. Because default privileges and the manifest live in the repository, new environments get the same permissions without manual fixes. That avoids "works locally, permission denied in staging" debugging.

Illustrative estimate, with assumed inputs only: assume a team plans to extract one module and has accumulated 4 undocumented cross-module queries. Assume each costs 2 engineer-days to find and replace during the extraction, but about half a day if caught at the commit that added it. Under these assumptions, late discovery costs 8 engineer-days and early discovery costs 2. Your own numbers will differ. The point is that the cost of a hidden dependency grows with how long it stays hidden, and enforcement makes that period short.

The savings depend on the automation existing. If grants are maintained by hand, the time saved in review is spent instead on chasing permission errors and drift.

When should you avoid a Postgres schema per module, and what are the alternatives?

Skip the pattern when one team owns the whole codebase and it is small enough that everyone knows which module owns which table. The roles, grants and CI checks then cost more than the accidental coupling they prevent.

Skip it too when the real problem is resource contention. Schemas do not isolate CPU, locks, vacuum pressure or long transactions. A runaway report in one module still holds back vacuum and blocks DDL for every other module.

Finally, do not adopt it without someone to automate grants. Hand-maintained privileges drift, and a boundary nobody trusts is worse than none.

Option Setup effort Grant maintenance Migration complexity Later extraction
Naming conventions only Minimal None Low Hard: hidden joins surface late
Single schema with row-level security Moderate Policies per table Medium: policies change with columns Hard: RLS separates rows, not modules
Schema per module with roles Moderate Default privileges plus a manifest Medium: one migration stream per module Moderate: dependencies are explicit
Database per module, same instance Moderate Low Medium: separate connections and migrations Easier, but cross-module reads need a foreign data wrapper or the application
Separate instances or services High Low High: distributed changes Already done

Adopt schemas per module when several teams own modules, when extracting a module into its own service is a realistic plan, or when accidental cross-module joins have already caused incidents. In those cases the boundary is worth what it costs.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres schema per module, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.