What is a Postgres schema per module, and is it a security boundary?

A Postgres schema is a namespace inside a single database. Tables, views, functions and sequences live in a schema, and an unqualified name such as orders is resolved by walking the session's search_path in order. A schema-per-module layout gives each module its own namespace, for example billing, catalog and identity, all in one database and one cluster.

A schema does not enforce ownership by default. If the application connects as a single role that owns every object, any module can query billing.invoices by its fully qualified name, and search_path won't stop it. search_path controls name lookup, not access. The boundary only becomes real when roles and privileges are added on top, which section 2 covers.

How it compares

Approach Isolation enforced by Postgres Cross-module joins Operational cost
Table-name prefixes (billing_invoices) None. It is a naming convention only. Trivial Lowest
Schema per module Yes, once each module has its own role and grants Possible with qualified names, and can be restricted by privileges One database to back up, tune and monitor
Database per module Strong. Sessions are bound to one database. Not possible without extensions such as postgres_fdw or dblink Multiple databases, connection targets and migration runs

Who it suits

This design fits teams running a modular monolith on one Postgres instance who want clear module data ownership without operating several databases. They keep a single backup, a single connection target and the option of a local transaction when one is justified. In return, each module's tables sit behind a named boundary that can be locked down with grants and checked in CI. Because each module's data is already grouped and its outside dependencies are visible, extracting a module into its own database later is mostly a matter of moving one schema.

How do per-module roles and privileges enforce data ownership?

Each module gets two roles. orders_owner owns the orders schema and every object in it, and only migrations run as this role. orders_app is what the application connects as, and it owns nothing. The split matters because an owner bypasses table privileges and can ALTER or DROP its objects. A runtime role that owns its tables can do anything to them. A runtime role that only holds grants can do only what you granted.

orders_app receives USAGE on the orders schema, which lets it look up names there. It also receives SELECT, INSERT, UPDATE and DELETE on the tables, plus USAGE on sequences that back identity columns. It gets nothing on billing, so a query against billing.invoices fails with a permission error instead of returning data.

Revoke CREATE on public from PUBLIC. Newer Postgres releases do this by default, but older or upgraded clusters may not. If you skip it, any role can create tables in a shared schema that no module owns.

Grants apply only to objects that already exist. Run ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders so tables and sequences created by future migrations are granted to orders_app automatically.

Finally, pin search_path per role, for example orders, api. An unqualified customers then resolves to the module's own table or the published contract. It can never silently resolve into another module's schema.

Module Schema Owner role (migrations) Runtime role Can read
Orders orders orders_owner orders_app orders, api
Billing billing billing_owner billing_app billing, api
Shared api (published views) owned per view by the publishing module none granted to all *_app roles
-- Run once as a superuser or admin role, per module.

-- Close the shared schema so no role can create unowned tables in it.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Owner role: owns the schema and runs migrations. Runtime role owns nothing.
CREATE ROLE orders_owner LOGIN;
CREATE ROLE orders_app LOGIN;

CREATE SCHEMA orders AUTHORIZATION orders_owner;

-- Runtime role can resolve names in its own schema and nothing else.
GRANT USAGE ON SCHEMA orders TO orders_app;

-- Grants for objects that already exist.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA orders TO orders_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA orders TO orders_app;

-- Grants for objects that future migrations create as orders_owner.
ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO orders_app;
ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders
  GRANT USAGE ON SEQUENCES TO orders_app;

-- Published contract: modules publish views into api, every app role reads it.
CREATE SCHEMA IF NOT EXISTS api;
GRANT CREATE, USAGE ON SCHEMA api TO orders_owner;
GRANT USAGE ON SCHEMA api TO orders_app;

-- Views orders_owner publishes in api become readable by all app roles.
ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA api
  GRANT SELECT ON TABLES TO orders_app, billing_app;

-- Unqualified names resolve only to the module's own schema or the contract.
ALTER ROLE orders_app SET search_path = orders, api;
ALTER ROLE orders_owner SET search_path = orders;

-- As orders_app: SELECT * FROM billing.invoices;
-- ERROR:  permission denied for schema billing

Provisioning the orders module: an owner role and a runtime role, schema and table grants, default privileges for future migrations, a shared api schema, and a pinned search_path. Assumes billing_app already exists.

Should each module get its own connection pool or use SET ROLE?

Each module's *_app role needs a path from the application to the database. There are two common ways to provide it, and they differ in what the database can enforce.

Per-module pool and credentials Shared pool with SET ROLE
Who the database sees A distinct login role per module One login role that is a member of every module role
Can module code become another module? Only by obtaining another module's credentials Yes, through SET ROLE or RESET ROLE
Connection count Modules × instances × pool size Instances × pool size
Pooler compatibility Works with any pooling mode Needs SET LOCAL ROLE inside each transaction

Per-module credentials make ownership a hard guarantee. A bug in billing code cannot read orders tables, because the billing connection has no privilege to do so. The cost is connection count. As an illustrative estimate, 6 modules, 10 instances and a pool of 10 per module produce 600 connections, which can exceed a typical max_connections budget. This approach usually requires smaller per-module pools or a pooler in front of the database.

A shared pool keeps connection counts flat but relies on the application to behave. The login role must be able to assume every module role, so any code path can switch roles or reset to the login role. Session-level SET ROLE also persists on the server connection. Behind a transaction-mode pooler, that role can leak into the next client's transaction. SET LOCAL ROLE limits the change to the current transaction. Any statement that runs outside an explicit transaction then runs as the login role.

Check the following against your own stack:

  • Whether your ORM wraps every query in a transaction.
  • How it handles prepared statements.
  • Which role your migration and admin tooling connects as.
-- Option A: one login role per module, with its own credentials.
-- The database enforces ownership because billing_svc only inherits billing_app.
CREATE ROLE billing_svc LOGIN PASSWORD 'change-me-billing' IN ROLE billing_app;
CREATE ROLE orders_svc  LOGIN PASSWORD 'change-me-orders'  IN ROLE orders_app;

-- Cap each module so the total stays inside max_connections.
ALTER ROLE billing_svc CONNECTION LIMIT 20;
ALTER ROLE orders_svc  CONNECTION LIMIT 20;

-- Option B: one shared login role that is a member of every module role.
-- NOINHERIT means it has no module privileges until it switches role.
CREATE ROLE app_gateway LOGIN NOINHERIT PASSWORD 'change-me-gateway';
GRANT billing_app, orders_app TO app_gateway;

-- Per request, scope the role to the transaction. SET LOCAL reverts at
-- COMMIT or ROLLBACK, so a transaction-mode pooler does not hand the
-- role to the next client.
BEGIN;
SET LOCAL ROLE billing_app;
SELECT current_user;              -- billing_app
SELECT count(*) FROM billing.invoices;
COMMIT;

SELECT current_user;              -- app_gateway again

-- What Option B does not prevent: any code on this connection can switch
-- to another module or back to the login role.
BEGIN;
SET LOCAL ROLE billing_app;
RESET ROLE;                       -- back to app_gateway
SET LOCAL ROLE orders_app;        -- allowed, because app_gateway is a member
COMMIT;

Option A shows per-module login roles with connection limits, and Option B shows a shared NOINHERIT login role that scopes each module role with SET LOCAL ROLE, along with the role switching that the shared approach cannot prevent.

How should modules read data they do not own?

Ordered from most to least coupled, these are the four realistic options.

Pattern Consumer depends on Main risk
Direct SELECT grant on another module's tables Physical table layout Any column rename breaks other modules
Read-only view in the provider's api schema A named, owned projection Views drift into a second schema nobody versions
SECURITY DEFINER function A function signature Privilege escalation if search_path is not pinned
In-process call through the module's interface Application types only Joins move into application code

Avoid direct grants. They make the provider's internal tables part of its public contract, which defeats the point of separate schemas.

Views are the cheapest real boundary. By default Postgres checks privileges on the underlying tables as the view owner, so the consumer needs only USAGE on the api schema and SELECT on the view. The providing module owns and migrates the view.

SECURITY DEFINER functions run with the owner's privileges. If search_path is not pinned in the function definition, a caller who can create objects in a schema on the path can shadow a table, function or operator, and that code then runs with the owner's privileges. Set an explicit path in the definition and put pg_temp last.

In-process calls remove database coupling entirely.

Versioning. Treat views and functions as a versioned contract. Adding a column is safe. Removing or retyping one requires a new version, such as an api_v2 schema, and a deprecation window.

Reporting. Admin and reporting joins get more expensive under every pattern except direct grants. That cost creates pressure to add a reporting role with SELECT on everything, and once application code starts connecting as that role, the boundary is gone. Grant the reporting role access only to api objects, keep its credentials out of application config, and audit which hosts use it.

-- Run as billing_owner, the role that owns the billing module's schemas.
-- billing holds private tables; billing_api holds the published contract.
CREATE SCHEMA billing_api AUTHORIZATION billing_owner;

-- Read-only view: a named projection, not the physical table layout.
-- The _v1 suffix makes the contract version explicit.
CREATE VIEW billing_api.invoice_summary_v1 AS
SELECT id AS invoice_id, customer_id, total_cents, status, issued_at
FROM billing.invoices;

-- The consumer gets the api schema and the view. It gets nothing on schema billing.
GRANT USAGE ON SCHEMA billing_api TO orders_app;
GRANT SELECT ON billing_api.invoice_summary_v1 TO orders_app;

-- SECURITY DEFINER function with a pinned search_path.
-- pg_temp goes last so a caller cannot shadow objects with temp tables.
CREATE FUNCTION billing_api.open_balance_v1(p_customer_id bigint)
RETURNS bigint
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = billing, pg_temp
AS $$
  SELECT COALESCE(SUM(total_cents), 0)::bigint
  FROM billing.invoices
  WHERE customer_id = p_customer_id AND status = 'open'
$$;

-- New functions are executable by PUBLIC by default. Restrict that to named consumers.
REVOKE EXECUTE ON FUNCTION billing_api.open_balance_v1(bigint) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION billing_api.open_balance_v1(bigint) TO orders_app;

-- Consumer side, as orders_app. Both statements work.
-- SELECT * FROM billing.invoices would fail with permission denied.
SELECT invoice_id, total_cents FROM billing_api.invoice_summary_v1 WHERE customer_id = 42;
SELECT billing_api.open_balance_v1(42);

The billing module publishes a versioned read-only view and a SECURITY DEFINER function with a pinned search_path in its billing_api schema, and grants the orders module access to those objects only, not to the billing tables.

When should you avoid cross-module foreign keys and transactions?

A foreign key from orders.order to customers.customer looks harmless, but it hands the orders module a veto over the customers module. The orders role needs the REFERENCES privilege on a table it does not own. Customers can no longer drop, rename or retype its primary key without coordinating a migration with orders. Delete behavior also becomes a cross-module decision: ON DELETE RESTRICT blocks customer erasure because of rows orders owns, and CASCADE lets one module silently delete another module's data.

You have three alternatives:

  • Store the ID and reconcile it. Orders keeps customer_id as a plain column, with no constraint.
  • Keep a module-local replica. Orders maintains a small orders.customer_ref table, fed by customer events, and can put a local FK on it.
  • Accept the FK as recorded coupling. If you keep it, list it in an architecture decision record and in your boundary checks, so it is a known exception and not an accident.

The first two give up database-enforced referential integrity, so orphaned rows will happen. Decide what each module does when it finds one: tolerate it, tombstone it, or flag it with a scheduled reconciliation query.

Transactions that span schemas have the same tradeoff. One commit across orders and inventory is a legitimate convenience while both modules share a deployment. It also hides an assumption that breaks the day either module is extracted. A per-schema outbox avoids that. Orders writes its state change and an orders.outbox row in one local transaction, and a relay publishes the event after commit.

There is no universal rule here. Write down a team policy that says which pairs of modules may share FKs or transactions, and enforce it in CI.

-- Module-local replica of customer reference data, fed by customer events
CREATE TABLE orders.customer_ref (
  customer_id bigint PRIMARY KEY,
  deleted_at  timestamptz            -- tombstone set when a customer.deleted event arrives
);

-- Orders stores customer_id as a plain column: no FK into the customers schema
CREATE TABLE orders."order" (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,       -- reconciled against orders.customer_ref, not enforced
  status      text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Per-schema outbox: a relay reads unpublished rows after commit and publishes them
CREATE TABLE orders.outbox (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_type   text NOT NULL,
  payload      jsonb NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz           -- NULL until the relay has published the event
);

-- State change and event are written in one local transaction inside the orders schema
BEGIN;
WITH new_order AS (
  INSERT INTO orders."order" (customer_id, status)
  VALUES (42, 'placed')
  RETURNING id, customer_id
)
INSERT INTO orders.outbox (event_type, payload)
SELECT 'order.placed', jsonb_build_object('order_id', id, 'customer_id', customer_id)
FROM new_order;
COMMIT;

-- Scheduled reconciliation: orders whose customer is unknown or tombstoned
SELECT o.id, o.customer_id, r.deleted_at
FROM orders."order" o
LEFT JOIN orders.customer_ref r ON r.customer_id = o.customer_id
WHERE r.customer_id IS NULL OR r.deleted_at IS NOT NULL;

The orders schema keeps customer_id without a cross-module foreign key and writes each state change together with an outbox row in one local transaction, while a scheduled query against a module-local replica flags orphaned or tombstoned customer references.

How do you run migrations per module and catch boundary violations in CI?

Give each module its own migration directory and run it while connected as that module's owner role, not as a superuser. Point the migration tool's history table at the module's own schema, for example billing.schema_migrations. Each module then tracks its own version independently. If a migration tries to create objects in another schema, it fails with a permission error at the moment it runs, instead of slipping through.

Ordering is the hard part. Suppose reporting defines a view over billing's published view. Then billing must migrate first. Later, when billing drops a column the view uses, Postgres rejects the change because the view depends on it. Treat that error as the boundary showing up. Declare module dependencies explicitly, run migrations in topological order, and when a published view must change, give consumers a deprecation window.

Add catalog checks to CI so these edges are visible in the codebase, not discovered in production:

  • Views that cross schemas: join pg_depend to pg_rewrite (on objid, with classid = 'pg_rewrite'::regclass). Compare the namespace of ev_class, the view, with the namespace of refobjid, the table it reads.
  • Foreign keys that cross schemas: look for rows in information_schema.referential_constraints where constraint_schema differs from unique_constraint_schema. Run this as a role that can see every schema, because information_schema hides objects the current role has no privileges on. Fail the build on any edge your policy does not allow.

Then test privileges as they actually apply at runtime. For each runtime role:

  • Connect as that role and select from one table in every schema the role does not own.
  • Assert SQLSTATE 42501 (insufficient privilege).
  • Also assert has_schema_privilege returns false for those schemas.

Back this up with a static check on raw SQL in each module's code. Flag schema-qualified references to other modules' schemas, and flag reliance on a broad search_path.

Grants drift, often through a manual GRANT applied during an incident and never reverted. These tests catch that on the next run. Reviewers then no longer need to inspect every migration and query for boundary leaks, and can spend their time on design.

How do you migrate an existing monolith out of the public schema, and is it worth the cost?

Start with an inventory, not a migration. Use query logs or pg_stat_statements to map every table to the module whose code reads or writes it. Tables touched by several modules are the real work: each needs one owner, and the other modules move to that owner's API or a read view.

Then move one module at a time. ALTER TABLE ... SET SCHEMA changes only catalog metadata. Indexes, constraints and owned sequences move with the table, and no rows are rewritten. It still takes an ACCESS EXCLUSIVE lock, so set a short lock_timeout and retry. Otherwise the statement can queue behind a long query and block all traffic on the table. Leave a simple view in public under the old name. Simple single-table views are auto-updatable, so unconverted code keeps working. Drop each view once logs show it is no longer used. Tighten grants last, after the code paths are clean.

Approach Ongoing cost What it enforces
Schemas + CI lint Low Ownership at review time
Schemas + per-module roles Credentials, pools, grant upkeep Ownership at runtime
Separate databases/services Ops, network calls, data sync Ownership plus resource isolation

Small teams often get most of the value from the first row.

Schemas share one cluster. They do not isolate lock contention, vacuum pressure, WAL volume, heavy analytical queries, or a long migration that saturates I/O. If one module's load or release cadence keeps hurting the others, move that module to a separate database or service.

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 modular monolith database schema, 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.