What is a Postgres schema per module, and why does it need roles and grants?
A Postgres schema per module means the whole application still runs against one physical database, and a single transaction can still span several modules, but each module's tables, sequences and views live in their own schema: billing.invoices, catalog.products, identity.users. You keep one deployment, one connection pool and ACID guarantees across modules, while gaining a visible line between them.
On its own, a schema is only a namespace. It qualifies names and controls what search_path resolves. It does not stop anything. If the application connects as one role that owns every table, which is the common default, then billing code can join against identity.users and nothing at the database level will object. At that point the schema is a naming convention, and the boundary exists only as long as code review catches violations.
The boundary becomes enforced when each module gets two roles:
- An owner role that owns the schema and its objects. Migrations run as this role.
- A runtime role that the module's code connects as, with
USAGEon its own schema and only the table privileges it needs.
Every cross-module access then needs an explicit grant. Without one, the query fails with a permission error at the database, in tests and in production.
The main benefit is explicit ownership. The set of grants is a written list of which modules depend on which data. That list is the work plan if you later move a module into its own database.
The pattern suits modular monoliths where several teams or modules share one Postgres instance and want boundaries that the database enforces.
+-----------------------------------------------------------------------+
| Modular monolith: one deployment, one connection pool |
| +----------------+ +----------------+ +----------------+ |
| | billing code | | catalog code | | identity code | |
| +-------+--------+ +-------+--------+ +-------+--------+ |
+-----------+---------------------+---------------------+---------------+
| billing_app | catalog_app | identity_app
v (runtime role) v v
+-----------------------------------------------------------------------+
| One Postgres database, one transaction scope |
| +----------------+ +----------------+ +----------------+ |
| | schema billing | | schema catalog | | schema identity| |
| +----------------+ +----------------+ +----------------+ |
| | owner role: | | owner role: | | owner role: | |
| | billing_owner | | catalog_owner | | identity_owner| |
| +----------------+ +----------------+ +----------------+ |
| | invoices | | products | | users | |
| +----------------+ +----------------+ +----------------+ |
+-----------------------------------------------------------------------+
Cross-module access by a runtime role:
billing_app --SELECT--> catalog.products OK: explicit GRANT exists
billing_app --JOIN----> identity.users ERROR: permission denied
Migrations run as *_owner; module code connects as *_app.
Each module's tables live in their own schema inside one database, owned by a per-module owner role and accessed through a per-module runtime role, so a cross-module query works only when an explicit grant allows it and otherwise fails with a permission error.
How do you create schemas, owner roles and runtime roles per module?
Take a billing module as the example. Repeat each step for every module.
Step by step
- Create an owner role with
NOLOGIN.billing_ownerowns every object in the schema, and nobody connects as it directly. Migrations connect as a deploy user that is a member ofbilling_ownerand runSET ROLE billing_ownerfirst, so the objects they create get the right owner. - Create the schema with
CREATE SCHEMA billing AUTHORIZATION billing_owner. Ownership controls who can alter or drop objects, so it should belong to one role per module. - Close the
publicschema withREVOKE CREATE ON SCHEMA public FROM PUBLIC. Defaults differ between Postgres versions and managed services, so don't assume yours. Check with\dn+and with queries such ashas_schema_privilege('billing_app', 'public', 'CREATE'). - Create a runtime
LOGINrole.billing_appgetsUSAGEonbillingplus only the table privileges it needs, such asSELECT, INSERT, UPDATE. Leave outDELETEif the module never deletes rows. - Set default privileges with
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing. Grant table privileges, andUSAGEon sequences, tobilling_app. These defaults only apply to objects thatbilling_ownercreates, which is one more reason migrations must run under that role. - Set the search path with
ALTER ROLE billing_app SET search_path = billing. Unqualified queries in application code then resolve to the module's own schema.
Migrations should still use qualified names like billing.invoices. A migration session's search_path depends on the tool, the connecting user and any session settings. An unqualified CREATE TABLE can land in the wrong schema without raising an error.
| Role | Login | Owns objects | Typical privileges |
|---|---|---|---|
billing_owner |
No | Yes | All, via ownership |
billing_app |
Yes | No | USAGE on the schema, DML on tables |
-- Run as a superuser or a role with CREATEROLE.
-- 1. Owner role: owns objects, never logs in directly.
CREATE ROLE billing_owner NOLOGIN;
GRANT billing_owner TO deploy; -- deploy user runs migrations
-- 2. Schema owned by the module's owner role.
CREATE SCHEMA billing AUTHORIZATION billing_owner;
-- 3. Stop every role from creating objects in public.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- 4. Runtime role: can log in, can't create or drop anything.
CREATE ROLE billing_app LOGIN PASSWORD 'replace-with-generated-secret';
GRANT USAGE ON SCHEMA billing TO billing_app;
-- Covers tables that already exist. No DELETE: the module never deletes rows.
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA billing TO billing_app;
-- 5. Grants for future objects. These apply only to objects billing_owner creates.
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
GRANT SELECT, INSERT, UPDATE ON TABLES TO billing_app;
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
GRANT USAGE ON SEQUENCES TO billing_app;
-- 6. Unqualified names in application queries resolve to billing.
ALTER ROLE billing_app SET search_path = billing;
-- Migration session: switch roles first and qualify every name.
SET ROLE billing_owner;
CREATE TABLE billing.invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount_cents bigint NOT NULL
);
RESET ROLE;
-- Check the privileges directly instead of assuming the defaults.
SELECT has_schema_privilege('billing_app', 'public', 'CREATE') AS public_create,
has_schema_privilege('billing_app', 'billing', 'CREATE') AS billing_create,
has_table_privilege('billing_app', 'billing.invoices', 'DELETE') AS can_delete;
SQL that sets up a NOLOGIN owner role, a schema owned by it, a least-privilege runtime role, default privileges and a search_path for the billing module, then creates a table under the owner role and checks the resulting privileges with has_schema_privilege and has_table_privilege.
How should modules share data across schema boundaries?
Once runtime roles can only touch their own schema, other modules need a deliberate way in. Granting orders_app SELECT on billing.invoices would work, but it makes the table's column layout a public interface that billing can no longer change freely. A better approach is for each providing module to publish a small billing_api schema, owned by billing_owner. It contains views and functions shaped for consumers. Billing then grants USAGE on that schema and SELECT or EXECUTE on specific objects to named consumer roles such as orders_app, and to nobody else.
Views work well for reads. Postgres checks the underlying tables against the view owner's privileges, so consumers never need grants on billing itself. When a consumer needs logic, such as filtering by tenant or computing a balance, use a SECURITY DEFINER function. It runs with the owner's privileges, which is why it must pin its lookup path with SET search_path = billing, pg_temp and schema-qualify the objects it references. Without the pin, the function resolves unqualified names using the caller's search_path. A caller who can create objects in any schema on that path can shadow a table or operator, and their code then runs as billing_owner. Also revoke the default EXECUTE from PUBLIC on these functions.
Cross-schema foreign keys are possible, but the referencing table's owner needs the REFERENCES privilege on the target table. They also bind both modules' migrations and locking behavior together, which is the coupling you set out to remove. If you drop them, referential integrity moves somewhere else. It becomes an existence check through the API at write time, or a reconciliation job that finds and repairs orphaned references.
-- Run as billing_owner. billing_api is the only surface other modules may touch.
CREATE SCHEMA billing_api AUTHORIZATION billing_owner;
-- Read contract: a stable shape over billing.invoices. Postgres checks billing.invoices
-- against the view owner (billing_owner), so consumers need no grants on billing.
CREATE VIEW billing_api.invoice_summary AS
SELECT i.id, i.order_id, i.status, i.total_cents
FROM billing.invoices AS i;
-- Logic contract: runs with billing_owner's privileges, so name lookup is locked down.
-- pg_temp goes last so a caller's temporary objects cannot shadow billing objects.
CREATE FUNCTION billing_api.outstanding_balance(p_tenant_id bigint)
RETURNS bigint
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = billing, pg_temp
AS $$
SELECT coalesce(sum(i.total_cents - i.paid_cents), 0)::bigint
FROM billing.invoices AS i -- schema-qualified as well as pinned
WHERE i.tenant_id = p_tenant_id
AND i.status <> 'void';
$$;
-- New functions are executable by PUBLIC by default; remove that before granting.
REVOKE ALL ON FUNCTION billing_api.outstanding_balance(bigint) FROM PUBLIC;
-- Grant the contract to named consumers only. orders_app gets nothing on billing.*.
GRANT USAGE ON SCHEMA billing_api TO orders_app;
GRANT SELECT ON billing_api.invoice_summary TO orders_app;
GRANT EXECUTE ON FUNCTION billing_api.outstanding_balance(bigint) TO orders_app;
Billing publishes a billing_api schema with a view and a SECURITY DEFINER function that has a pinned search_path, revokes the default PUBLIC execute, and grants access only to orders_app.
How do you wire per-module roles into the application and test isolation?
Once each module has its own runtime role, the application has to actually connect as that role. There are two common ways to do this.
Pool per module. Each module gets its own connection pool that logs in as its runtime role. A connection can only ever hold that module's privileges, so there is no state to manage. The downside is connection count: total connections are roughly modules × pool size × application instances, and that number reaches the server's max_connections sooner than you might expect.
Shared pool with role switching. One login role is granted membership in every runtime role. Each unit of work runs SET LOCAL ROLE inside its transaction. Postgres reverts the role at commit or rollback, which makes this much safer than a plain SET ROLE. A plain SET ROLE stays on the session until something runs RESET ROLE, so a missed reset leaves the role on the connection and the next request inherits another module's privileges without any error. Also note that the shared login role can assume any module's role. Injected SQL can therefore switch roles too, so the boundary depends on application discipline.
| Approach | Connections | Isolation risk | Operational cost |
|---|---|---|---|
| Pool per module | Multiplies with modules | Low: role fixed at login | More pools to size and monitor |
Shared pool + SET LOCAL ROLE |
One pool | Leaked or unreset role weakens isolation silently | Every transaction must set the role |
Testing the boundary
Add an integration test that connects as the orders runtime role, runs SELECT against a billing table, and asserts that the query fails with SQLSTATE 42501 (insufficient_privilege). Write one such test for each pair of modules that must not touch each other. When someone adds an accidental grant or a query that crosses a module boundary, CI fails. Reviewers then no longer have to look for these mistakes by reading SQL in every pull request.
# Integration tests: the orders runtime role must not read billing tables.
# Run against a database migrated with the real roles and grants.
import os
import psycopg
import pytest
from psycopg import errors
# Example: postgresql://orders_rt:placeholder@localhost:5432/app
ORDERS_DSN = os.environ["ORDERS_DSN"]
# Shared login role that is a member of every runtime role
APP_LOGIN_DSN = os.environ["APP_LOGIN_DSN"]
def test_orders_role_cannot_read_billing():
# Pool-per-module style: the connection logs in as orders_rt directly.
with psycopg.connect(ORDERS_DSN) as conn:
with pytest.raises(errors.InsufficientPrivilege) as exc:
conn.execute("SELECT 1 FROM billing.invoices LIMIT 1")
assert exc.value.sqlstate == "42501"
def test_set_local_role_blocks_billing_and_reverts():
# Shared-pool style: switch roles only for the duration of one transaction.
with psycopg.connect(APP_LOGIN_DSN, autocommit=True) as conn:
with pytest.raises(errors.InsufficientPrivilege) as exc:
with conn.transaction():
conn.execute("SET LOCAL ROLE orders_rt")
conn.execute("SELECT 1 FROM billing.invoices LIMIT 1")
assert exc.value.sqlstate == "42501"
# After rollback the role must not leak to the next unit of work.
user = conn.execute("SELECT current_user").fetchone()[0]
assert user != "orders_rt"
A pytest suite using psycopg 3 that asserts the orders runtime role gets SQLSTATE 42501 on a billing table, both when it logs in directly and when the shared login role uses SET LOCAL ROLE, and that the role does not persist after the transaction ends.
How do you run migrations per module?
The same discipline applies to schema changes. If a superuser or a shared deploy account runs every migration, that account owns every table, and the ownership model from earlier sections stops matching what is actually in the database. Each module's migrations should run as that module's owner role. You can do this in two ways: connect with a dedicated migration login and issue SET ROLE billing_owner at the start of the run, or grant the migration login membership in the owner role and set the role in the tool's connection settings. In both cases, new objects are owned by billing_owner and not by whoever happened to deploy.
Store each module's migration history in a table inside that module's own schema, for example billing.schema_migrations. A module can then be versioned, rolled back and eventually extracted without depending on a global ledger. Order the runs so that a module's contract objects, such as the views and functions in its api schema, exist before any consuming module's migrations reference them.
Ownership also matters for grants. ALTER DEFAULT PRIVILEGES applies only to objects created by the role named in that statement, or by the current role if none is named. If a table is created by any other role, for example during a manual hotfix as postgres, it silently misses the grants. Runtime roles then fail with permission errors that look unrelated to the change.
Many ORMs, migration tools and admin tools assume a single schema or public. You may need to configure them explicitly to use schema-qualified models, a per-module history table location and a per-run search path.
How do you retrofit schema isolation into an existing single-schema monolith?
Start with an inventory, not a migration. List every table in public and assign each to exactly one owning module, based on which code writes to it. Tables written by several modules are the real coupling in your system. Mark them as unresolved and leave them in place for now.
Then move tables one module at a time with ALTER TABLE ... SET SCHEMA. It is a catalog change, not a data copy, so it is fast even on large tables. Indexes, constraints and owned sequences move with the table. For every moved table, create a view with the same name in public that selects from the new location. Simple single-table views are automatically updatable in Postgres, so most existing reads and writes keep working. You can then update call sites gradually instead of in the same deploy.
Next, introduce the per-module runtime roles. Give them broad grants at first: usage on every module schema and DML on every table. Nothing breaks, but each connection now identifies which module issued a query. In staging, revoke one cross-module grant at a time. Postgres logs the statement that caused an error when log_min_error_statement is at ERROR or lower, which is the default. Each "permission denied" entry therefore shows you the exact query that crosses a boundary. Fix that access path, then revoke the next grant.
This approach spreads the cost over many small, reversible changes. Each one can ship alongside feature work. Once nothing reads through a compatibility view anymore, drop it.
When should you avoid a schema per module, and what are the alternatives?
Schema isolation is only as strong as the role that connects. Superusers skip privilege checks entirely. Owners hold every privilege on their own tables. Roles with BYPASSRLS skip any row-level security policies you layer on top. If the application connects as postgres or as a module's owner role, every boundary in this tutorial disappears.
Schemas also share one instance. One module's long transaction holds back vacuum for everyone. Its lock queue can stall a migration, and its bloat competes for the same buffer cache and I/O. A slow report in billing is still a slow report in orders.
The social limits matter as much as the technical ones. A cross-schema join is one grant away, and "just give us SELECT on everything" is an easy request to approve. Without review, grants accumulate until they no longer match the intended dependencies. Periodically diff the actual privileges against a declared list.
| Option | Enforcement | Operational isolation | Transaction scope | Cost |
|---|---|---|---|---|
| Single schema, naming conventions | Code review only | None | Global | Lowest |
| Schema per module with grants | Database privileges | None | Global | Low to moderate |
| Database per module | No cross-database queries | Partial (shared instance) or full | Per module | Moderate |
| Separate services and databases | Network and API | Full | Per service | Highest |
Naming conventions suit small teams that can review every query. Schema per module fits when you want enforced boundaries and still need atomic cross-module writes. Choose a database per module, or full services, when you need resource isolation or independent scaling. Those options replace shared transactions with sagas, outbox processing and duplicated data. You pay for that every time a feature crosses a module boundary.
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.
- Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.
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.
- Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
- 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.