Design Multi-Team Database and Schema Boundaries with Least-Privilege Ownership

Learning outcomes

A growing ServiceHub organization now has dispatch, billing, and analytics responsibilities. If every team connects as one shared owner role and creates objects in public, the database will work until the first migration collision, accidental drop, or credential compromise. PostgreSQL gives you the building blocks for a much cleaner model: separate owner roles, runtime login roles, schemas as team/application namespaces, explicit membership transitions for migrations, default privileges for future objects, and controlled search paths.

This capstone lesson for Chapter 03 designs the boundaries and then proves them with an access matrix. The emphasis is not “create many roles because more roles are secure.” The emphasis is mapping each privilege to a responsibility and demonstrating both permitted and forbidden actions.

1. Start from responsibilities, not role names

The owner roles are stable object identities even if human/service logins rotate. The migrator must explicitly adopt an owner role when creating objects, so ownership stays deterministic. Runtime roles never receive membership in owner roles; they get ordinary privileges only.

2. Create the role graph deliberately

Run as the disposable lab administrator. Set passwords for LOGIN roles interactively with psql \password rather than embedding plaintext passwords in SQL files.

CREATE ROLE ch03_dispatch_owner NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;CREATE ROLE ch03_billing_owner  NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;CREATE ROLE ch03_migrator LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;CREATE ROLE ch03_dispatch_app LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;CREATE ROLE ch03_billing_app  LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;CREATE ROLE ch03_analyst      LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;GRANT ch03_dispatch_owner TO ch03_migratorWITH INHERIT FALSE, SET TRUE;GRANT ch03_billing_owner TO ch03_migratorWITH INHERIT FALSE, SET TRUE;

\password ch03_migrator\password ch03_dispatch_app\password ch03_billing_app\password ch03_analyst

The migrator can SET ROLE to the owner roles, but does not automatically inherit their privileges. The runtime application roles have no membership path into the owners at all.

3. Database boundary: CONNECT is separate from schema access

A role must first be able to connect to servicehub_lab. Database CONNECT does not grant access to every schema/table; it merely permits the database connection boundary.

GRANT CONNECT ON DATABASE servicehub_labTO ch03_migrator, ch03_dispatch_app, ch03_billing_app, ch03_analyst;

In a real estate of applications, PUBLIC CONNECT/TEMPORARY defaults may be adjusted according to policy. Chapter 01 already tightened ServiceHub's broad database grants, so explicit CONNECT fits the course convention.

4. Create schemas as the correct owners

The administrator can create schemas owned by another role only when allowed to set role appropriately; a superuser in the disposable course lab can perform the setup. Ownership is visible and auditable:

CREATE SCHEMA ch03_dispatch AUTHORIZATION ch03_dispatch_owner;CREATE SCHEMA ch03_billing  AUTHORIZATION ch03_billing_owner;CREATE SCHEMA ch03_analytics AUTHORIZATION servicehub_owner;REVOKE CREATE ON SCHEMA public FROM PUBLIC;REVOKE ALL ON SCHEMA ch03_dispatch, ch03_billing, ch03_analytics FROM PUBLIC;GRANT USAGE ON SCHEMA ch03_dispatch TO ch03_dispatch_app, ch03_analyst;GRANT USAGE ON SCHEMA ch03_billing  TO ch03_billing_app, ch03_analyst;GRANT USAGE ON SCHEMA ch03_analytics TO ch03_analyst;

USAGE lets a role resolve/access objects in a schema subject to object privileges. CREATE lets a role define new objects in that schema. Runtime roles receive USAGE but deliberately do not receive CREATE.

5. Migration workflow: SET ROLE before CREATE

Connect as ch03_migrator. The login itself does not own either schema. It explicitly assumes the target owner identity before DDL:

SELECT session_user, current_user;SET ROLE ch03_dispatch_owner;CREATE TABLE ch03_dispatch.jobs (    job_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    work_order_id bigint NOT NULL,    assigned_at timestamptz NOT NULL DEFAULT now(),    dispatch_state text NOT NULL);RESET ROLE;SET ROLE ch03_billing_owner;CREATE TABLE ch03_billing.invoices (    invoice_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    work_order_id bigint NOT NULL,    amount numeric(12,2) NOT NULL,    invoice_state text NOT NULL DEFAULT 'draft');RESET ROLE;

Now verify that table ownership is the owner roles, not ch03_migrator:

SELECT c.oid::regclass AS relation,       c.relowner::regrole AS ownerFROM pg_catalog.pg_class AS cWHERE c.oid IN ('ch03_dispatch.jobs'::regclass,                'ch03_billing.invoices'::regclass)ORDER BY c.oid::regclass::text;

6. Grant current runtime privileges

The dispatch app should manipulate dispatch jobs, the billing app should manipulate invoices, and the analyst should read both. Identity/sequence-backed inserts also need sequence privileges.

GRANT SELECT, INSERT, UPDATE, DELETEON ch03_dispatch.jobs TO ch03_dispatch_app;GRANT USAGE, SELECTON SEQUENCE ch03_dispatch.jobs_job_id_seq TO ch03_dispatch_app;GRANT SELECT, INSERT, UPDATE, DELETEON ch03_billing.invoices TO ch03_billing_app;GRANT USAGE, SELECTON SEQUENCE ch03_billing.invoices_invoice_id_seq TO ch03_billing_app;GRANT SELECT ON ch03_dispatch.jobs, ch03_billing.invoices TO ch03_analyst;

The analyst has USAGE on the schemas and SELECT on the current tables, but no INSERT/UPDATE/DELETE and no CREATE.

7. Default privileges belong to the future creator role

Current grants do not automatically apply to tables created next month. ALTER DEFAULT PRIVILEGES changes the initial privileges of future objects created by a specific role. Because migrations use SET ROLE ch03_dispatch_owner before creating dispatch objects, the default privileges must be defined for ch03_dispatch_owner, not for the administrator or migrator login.

ALTER DEFAULT PRIVILEGES FOR ROLE ch03_dispatch_ownerIN SCHEMA ch03_dispatchGRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ch03_dispatch_app;ALTER DEFAULT PRIVILEGES FOR ROLE ch03_dispatch_ownerIN SCHEMA ch03_dispatchGRANT USAGE, SELECT ON SEQUENCES TO ch03_dispatch_app;ALTER DEFAULT PRIVILEGES FOR ROLE ch03_dispatch_ownerIN SCHEMA ch03_dispatchGRANT SELECT ON TABLES TO ch03_analyst;ALTER DEFAULT PRIVILEGES FOR ROLE ch03_billing_ownerIN SCHEMA ch03_billingGRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ch03_billing_app;ALTER DEFAULT PRIVILEGES FOR ROLE ch03_billing_ownerIN SCHEMA ch03_billingGRANT USAGE, SELECT ON SEQUENCES TO ch03_billing_app;ALTER DEFAULT PRIVILEGES FOR ROLE ch03_billing_ownerIN SCHEMA ch03_billingGRANT SELECT ON TABLES TO ch03_analyst;

\ddp

8. Search paths should match the runtime contract

Set database-specific role defaults that keep trusted schemas explicit and put pg_catalog first. The runtime role still can use schema-qualified names; the path is convenience, not authorization.

ALTER ROLE ch03_dispatch_app IN DATABASE servicehub_labSET search_path = pg_catalog, ch03_dispatch;ALTER ROLE ch03_billing_app IN DATABASE servicehub_labSET search_path = pg_catalog, ch03_billing;ALTER ROLE ch03_analyst IN DATABASE servicehub_labSET search_path = pg_catalog, ch03_analytics, ch03_dispatch, ch03_billing;

These settings take effect on new sessions. SET ROLE does not reload target-role connection defaults, so migration scripts should explicitly set their DDL search path or use qualified names.

9. Build an access matrix before you call the design finished

Use helper functions to preflight, but prove critical boundaries with actual operations in separate role sessions.

SELECT r.rolname,       has_schema_privilege(r.rolname,'ch03_dispatch','USAGE') AS dispatch_usage,       has_schema_privilege(r.rolname,'ch03_dispatch','CREATE') AS dispatch_create,       has_table_privilege(r.rolname,'ch03_dispatch.jobs','SELECT') AS dispatch_select,       has_table_privilege(r.rolname,'ch03_dispatch.jobs','INSERT') AS dispatch_insert,       has_table_privilege(r.rolname,'ch03_billing.invoices','SELECT') AS billing_select,       has_table_privilege(r.rolname,'ch03_billing.invoices','INSERT') AS billing_insertFROM pg_catalog.pg_roles AS rWHERE r.rolname IN ('ch03_dispatch_app','ch03_billing_app','ch03_analyst','ch03_migrator')ORDER BY r.rolname;

10. Positive and negative tests

As ch03_dispatch_app, this should succeed:

INSERT INTO ch03_dispatch.jobs (work_order_id, dispatch_state)VALUES (1, 'queued')RETURNING job_id, work_order_id, dispatch_state;

These should fail:

-- dispatch_app should not write billing data.INSERT INTO ch03_billing.invoices (work_order_id, amount)VALUES (1, 100.00);-- dispatch_app should not redefine its schema.CREATE TABLE ch03_dispatch.should_not_exist(id integer);

As ch03_analyst, SELECT from both tables should succeed while INSERT should fail. As ch03_migrator, CREATE in the schema should fail before SET ROLE and succeed after explicitly becoming the owner role.

11. Deliberately wrong approach: one shared “app_owner” login for everyone

One shared owner credential makes authorization easy to configure and nearly impossible to audit. Every application instance, migration job, developer shell, and report tool can alter/drop objects. Credential rotation becomes disruptive. Logs show one identity. Default privileges may accidentally reflect whoever created the object. Search paths and temporary objects become harder to reason about.

The multi-role pattern costs more initial design, but creates useful blast-radius boundaries. It also makes incident questions answerable: which login authenticated, which owner owns the object, which membership path allowed a role transition, and which explicit grant allowed the data operation?

12. Cleanup: remove only what this disposable design lab created

Before cleanup, inventory the three prefixed schemas and role graph. Then remove objects first, reverse default privileges if needed, revoke memberships, and drop roles. CASCADE is acceptable for the three teaching schemas because the prefix and inventory prove they are lab-owned.

-- Remove future-object defaults while the schemas/owner roles still exist.ALTER DEFAULT PRIVILEGES FOR ROLE ch03_dispatch_owner IN SCHEMA ch03_dispatchREVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM ch03_dispatch_app;ALTER DEFAULT PRIVILEGES FOR ROLE ch03_dispatch_owner IN SCHEMA ch03_dispatchREVOKE USAGE, SELECT ON SEQUENCES FROM ch03_dispatch_app;ALTER DEFAULT PRIVILEGES FOR ROLE ch03_dispatch_owner IN SCHEMA ch03_dispatchREVOKE SELECT ON TABLES FROM ch03_analyst;ALTER DEFAULT PRIVILEGES FOR ROLE ch03_billing_owner IN SCHEMA ch03_billingREVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM ch03_billing_app;ALTER DEFAULT PRIVILEGES FOR ROLE ch03_billing_owner IN SCHEMA ch03_billingREVOKE USAGE, SELECT ON SEQUENCES FROM ch03_billing_app;ALTER DEFAULT PRIVILEGES FOR ROLE ch03_billing_owner IN SCHEMA ch03_billingREVOKE SELECT ON TABLES FROM ch03_analyst;DROP SCHEMA ch03_dispatch CASCADE;DROP SCHEMA ch03_billing CASCADE;DROP SCHEMA ch03_analytics CASCADE;REVOKE ch03_dispatch_owner FROM ch03_migrator;REVOKE ch03_billing_owner FROM ch03_migrator;ALTER ROLE ch03_dispatch_app IN DATABASE servicehub_lab RESET search_path;ALTER ROLE ch03_billing_app IN DATABASE servicehub_lab RESET search_path;ALTER ROLE ch03_analyst IN DATABASE servicehub_lab RESET search_path;REVOKE CONNECT ON DATABASE servicehub_labFROM ch03_migrator, ch03_dispatch_app, ch03_billing_app, ch03_analyst;DROP ROLE ch03_dispatch_app;DROP ROLE ch03_billing_app;DROP ROLE ch03_analyst;DROP ROLE ch03_migrator;DROP ROLE ch03_dispatch_owner;DROP ROLE ch03_billing_owner;

If DROP ROLE reports remaining dependencies/default-privilege entries, stop and inspect them. Do not add a destructive shortcut blindly. DROP OWNED and REASSIGN OWNED have broad database-local consequences and deserve deliberate use, not automatic cleanup scripts.

13. Chapter 03 summary and bridge to Chapter 04

You now have the namespace/security foundation for the rest of PostgreSQL. A cluster contains databases; schemas partition names within a database; search_path is both resolution policy and a trust boundary. PostgreSQL roles unify users/groups but separate LOGIN, membership, object privileges, ownership, and role attributes. Catalogs make those structures observable, dependency tracking protects schema graphs, and extensions add their own lifecycle membership. Least privilege works only when ownership, CREATE rights, default privileges, and role transitions are designed together.

Chapter 04 moves inside table definitions: PostgreSQL's unusually rich data types, domains, constraints, identity/sequence behavior, generated columns, encoding/collation choices, and how those mechanisms enforce data integrity.

Authoritative references

• PostgreSQL 18 — Schemas
• PostgreSQL 18 — Privileges
• PostgreSQL 18 — Role Membership
• PostgreSQL 18 — ALTER DEFAULT PRIVILEGES
• PostgreSQL 18 — Privilege Inquiry Functions
• PostgreSQL 18 — GRANT
• PostgreSQL 18 — SET ROLE