Build a Repeatable Lab with Roles, Databases, Schemas, Extensions, and Sample Workloads

Learning outcomes

The first four lessons established PostgreSQL’s architecture, release lifecycle, installation, and client workflow. Now ServiceHub needs a stable disposable environment that later chapters can mutate without touching unrelated databases. A good lab is more than “create a database and use postgres as everything.” It has explicit ownership, a separate application login, controlled schema privileges, deterministic seed/reset behavior, and a rule for extensions and destructive experiments.

The goal is not to design a perfect production platform in Chapter 01. It is to create a small environment whose security and lifecycle boundaries are visible enough to support later MVCC, WAL, replication, VACUUM, indexing, RLS, and failure-recovery exercises.

1. Design identities before objects

PostgreSQL uses roles for both login identities and groups/ownership. A role with LOGIN can authenticate; a role without LOGIN can still own database objects and receive privileges. Separating ownership from application login reduces the chance that an exploited application account can redefine its own schema.

The course will later add specialized migration, monitoring, replication, and backup roles when their responsibilities become necessary. Do not create dozens of speculative roles now.

2. Safety gate: prove the target before creating anything

Connect to the local administrative database through the service profile from Lesson 4. Before creating cluster-wide roles, confirm that this is the intended disposable server.

psql "service=servicehub-lab-admin" -W\conninfo

SELECT current_database() AS database_name,       session_user AS login_role,       current_user AS effective_role,       inet_server_addr() AS server_address,       inet_server_port() AS server_port,       version() AS server_build;SHOW data_directory;

Stop if the server, port, data directory, or role is not the disposable environment you expect. Role creation is cluster-wide, so an accidental production connection would create identities outside the intended lab database.

3. Create the owner and application roles

Run the following as a local administrator with permission to create roles. Do not put the application password in the SQL file. Create the login without a password literal, then set its password interactively with psql’s \password command.

CREATE ROLE servicehub_owner  NOLOGIN  NOSUPERUSER  NOCREATEDB  NOCREATEROLE  NOREPLICATION;CREATE ROLE servicehub_app  LOGIN  NOSUPERUSER  NOCREATEDB  NOCREATEROLE  NOREPLICATION;

\password servicehub_app

\password prompts and sends an appropriate ALTER ROLE without echoing a plaintext password into your SQL script. For a real application deployment, credentials should come from an approved secret-management and rotation workflow rather than a human-maintained tutorial password.

Verify role attributes from the operator interface and catalog view:

\du

SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole, rolreplicationFROM pg_catalog.pg_rolesWHERE rolname IN ('servicehub_owner', 'servicehub_app')ORDER BY rolname;

4. Create a dedicated database owned by the non-login role

CREATE DATABASE cannot run inside a normal transaction block, so do not wrap this step in --single-transaction. The owner role does not need LOGIN to own the database.

CREATE DATABASE servicehub_lab  OWNER servicehub_owner;

The database inherits the cluster's locale/encoding defaults. The course baseline expects UTF-8; verify SHOW server_encoding; after connecting. If your cluster uses another encoding, do not force a mismatched encoding onto template1; use the official CREATE DATABASE/locale guidance or initialize a dedicated UTF-8 lab cluster. After creation, tighten the broad default database privileges for this disposable lab and grant only what the application login needs at the database boundary:

REVOKE ALL ON DATABASE servicehub_lab FROM PUBLIC;GRANT CONNECT ON DATABASE servicehub_lab TO servicehub_app;

Revoking PUBLIC privileges is a deliberate course convention, not a claim that every production database must use this exact grant set. Production role design must account for administrators, monitoring, migrations, backups, background jobs, and provider constraints.

5. Update the service profile and reconnect

Change the Lesson 4 service entry so its database is now servicehub_lab. Keep an administrator service and later add a separate application service; do not silently repurpose one name if team automation already depends on it.

[servicehub-lab-admin]host=127.0.0.1port=55432dbname=servicehub_labuser=postgresapplication_name=bda_servicehub_adminconnect_timeout=5[servicehub-lab-app]host=127.0.0.1port=55432dbname=servicehub_labuser=servicehub_appapplication_name=bda_servicehub_appconnect_timeout=5

Reconnect with the admin service and verify current_database() before schema work.

6. Create controlled schemas as the owner

The application should use an explicit app schema. An extensions schema gives approved relocatable extension objects a distinct namespace when the extension supports that placement. The admin role can temporarily SET ROLE to the owner role only if it is permitted to do so; a superuser in this disposable lab can perform that administrative transition.

SET ROLE servicehub_owner;CREATE SCHEMA app AUTHORIZATION servicehub_owner;CREATE SCHEMA extensions AUTHORIZATION servicehub_owner;RESET ROLE;REVOKE CREATE ON SCHEMA public FROM PUBLIC;GRANT USAGE ON SCHEMA app TO servicehub_app;

The application receives USAGE so it can resolve objects in app, but it does not receive CREATE. Later security lessons explain why writable schemas combined with search_path can create privilege-escalation risks.

Set a controlled default search path for the application role in this database:

ALTER ROLE servicehub_app IN DATABASE servicehub_lab  SET search_path = pg_catalog, app;

This is convenient for the lab, but administrative and SECURITY DEFINER code should not blindly trust caller-controlled namespace resolution. Production-safe search_path design gets its own treatment later.

7. Create the initial ServiceHub schema

The schema deliberately uses familiar relational features. Advanced PostgreSQL-specific types and index methods are introduced only when the course teaches their semantics.

SET ROLE servicehub_owner;SET search_path = pg_catalog, app;CREATE TABLE app.customers (  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  email text NOT NULL UNIQUE,  display_name text NOT NULL,  created_at timestamptz NOT NULL DEFAULT now());CREATE TABLE app.technicians (  technician_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  display_name text NOT NULL,  active boolean NOT NULL DEFAULT true);CREATE TABLE app.work_orders (  work_order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  customer_id bigint NOT NULL REFERENCES app.customers(customer_id),  assigned_technician_id bigint REFERENCES app.technicians(technician_id),  status text NOT NULL DEFAULT 'open'    CHECK (status IN ('open', 'scheduled', 'in_progress', 'completed', 'cancelled')),  priority smallint NOT NULL DEFAULT 3    CHECK (priority BETWEEN 1 AND 5),  summary text NOT NULL,  opened_at timestamptz NOT NULL DEFAULT now(),  scheduled_for timestamptz);RESET ROLE;

Identity columns use underlying sequence machinery, foreign keys establish referential integrity, and check constraints enforce the small status/priority domains. Chapter 04 examines those mechanisms in depth; here they simply give later labs a coherent dataset.

8. Grant runtime privileges, including sequences

The application needs DML privileges on current tables and the privileges required to obtain identity values from sequences. It should not be able to drop or redefine tables.

GRANT SELECT, INSERT, UPDATE, DELETEON ALL TABLES IN SCHEMA appTO servicehub_app;GRANT USAGE, SELECTON ALL SEQUENCES IN SCHEMA appTO servicehub_app;

Those statements affect existing objects. To keep future objects created by servicehub_owner consistent, define default privileges for that creating role:

ALTER DEFAULT PRIVILEGES FOR ROLE servicehub_owner IN SCHEMA appGRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO servicehub_app;ALTER DEFAULT PRIVILEGES FOR ROLE servicehub_owner IN SCHEMA appGRANT USAGE, SELECT ON SEQUENCES TO servicehub_app;

A frequent mistake is to run ALTER DEFAULT PRIVILEGES as the wrong role and assume it applies globally. Default privileges are tied to the role that will create future objects. Chapter 20 returns to this detail during full least-privilege design.

9. Seed deterministic sample data

Seed data should be small enough to understand now and deterministic enough that later labs can reset to the same business state. Use an explicit transaction and schema-qualified names.

BEGIN;INSERT INTO app.customers (email, display_name) VALUES  ('northwind@example.test', 'Northwind Workshop'),  ('cedar@example.test', 'Cedar Clinic'),  ('metro@example.test', 'Metro Library');INSERT INTO app.technicians (display_name) VALUES  ('Ari Chen'),  ('Mina Patel'),  ('Omar Reyes');INSERT INTO app.work_orders  (customer_id, assigned_technician_id, status, priority, summary, scheduled_for)SELECT c.customer_id, t.technician_id, 'scheduled', 2,       'Replace failed temperature sensor', now() + interval '1 day'FROM app.customers cCROSS JOIN app.technicians tWHERE c.email = 'northwind@example.test'  AND t.display_name = 'Ari Chen';INSERT INTO app.work_orders  (customer_id, status, priority, summary)SELECT customer_id, 'open', 4, 'Investigate intermittent gateway outage'FROM app.customersWHERE email = 'cedar@example.test';COMMIT;

The .test domain prevents examples from pointing at real mailboxes. The timestamp values are intentionally relative; exact displayed times depend on the session time zone. Later temporal lessons will make those assumptions explicit.

10. Positive authorization test: the application can do its job

Disconnect and connect using servicehub-lab-app. Verify the role and search path, then read and insert through the permitted tables.

\c servicehub_lab servicehub_app 127.0.0.1 55432\conninfo

SELECT current_database(), session_user, current_user;SHOW search_path;SELECT work_order_id, status, priority, summaryFROM app.work_ordersORDER BY work_order_id;INSERT INTO app.work_orders (customer_id, status, priority, summary)SELECT customer_id, 'open', 3, 'Inspect backup power unit'FROM app.customersWHERE email = 'metro@example.test'RETURNING work_order_id, status, priority, summary;

The insert should succeed if table and sequence privileges are correct. RETURNING makes the generated identifier observable without a second query.

11. Negative authorization test: the application cannot redefine the schema

A security design is not verified by successful operations alone. Prove that an operation outside the application role’s contract fails:

-- Run as servicehub_app. This should fail.CREATE TABLE app.should_not_exist (  id integer PRIMARY KEY);

The expected result is a permission error for schema app. If the table is created, stop and inspect ownership/grants; do not continue with a lab whose privilege boundary is already wrong.

SELECT has_schema_privilege('servicehub_app', 'app', 'USAGE') AS can_use_app,       has_schema_privilege('servicehub_app', 'app', 'CREATE') AS can_create_in_app;

For this lab, the desired result is true for USAGE and false for CREATE.

12. Extension policy: available, approved, installed

PostgreSQL installations often make additional supplied extensions available. The course will not auto-install every extension it can see. Use a three-state policy:

For Chapter 01, pg_trgm is on the approved optional list because later lessons can use trigram similarity/indexing. It remains optional now so the mandatory lab works even when a minimal platform package has not installed contrib modules.

SELECT name, default_version, installed_version, commentFROM pg_catalog.pg_available_extensionsWHERE name = 'pg_trgm';

If and only if the row exists and you want the optional exercise, install it through the owner context into the dedicated extensions schema:

SET ROLE servicehub_owner;CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA extensions;RESET ROLE;SELECT extname, extversion, extnamespace::regnamespace AS extension_schemaFROM pg_catalog.pg_extensionWHERE extname = 'pg_trgm';

If the extension package is unavailable, do not download an arbitrary binary from an untrusted site to make the tutorial “pass.” Follow the platform’s official PostgreSQL/contrib packaging guidance or skip the optional exercise.

13. Reset conventions: destructive by design, guarded by identity

Later concurrency, MVCC, indexing, and planner labs will mutate data. A reset script should refuse to run outside the intended database. Use a server-side guard before destructive statements:

Run the reset script through an administrator/owner-capable lab connection, never through servicehub_app, because the application role intentionally lacks TRUNCATE privilege.

\set ON_ERROR_STOP onDO $$BEGIN  IF current_database() <> 'servicehub_lab' THEN    RAISE EXCEPTION 'Refusing reset in database %', current_database();  END IF;END$$;BEGIN;TRUNCATE TABLE app.work_orders, app.technicians, app.customers  RESTART IDENTITY CASCADE;INSERT INTO app.customers (email, display_name) VALUES  ('northwind@example.test', 'Northwind Workshop'),  ('cedar@example.test', 'Cedar Clinic'),  ('metro@example.test', 'Metro Library');INSERT INTO app.technicians (display_name) VALUES  ('Ari Chen'), ('Mina Patel'), ('Omar Reyes');INSERT INTO app.work_orders  (customer_id, assigned_technician_id, status, priority, summary, scheduled_for)SELECT c.customer_id, t.technician_id, 'scheduled', 2,       'Replace failed temperature sensor', now() + interval '1 day'FROM app.customers cCROSS JOIN app.technicians tWHERE c.email = 'northwind@example.test'  AND t.display_name = 'Ari Chen';INSERT INTO app.work_orders (customer_id, status, priority, summary)SELECT customer_id, 'open', 4, 'Investigate intermittent gateway outage'FROM app.customersWHERE email = 'cedar@example.test';COMMIT;

TRUNCATE is intentionally destructive and bypasses row-by-row deletion. The guard reduces accidental misuse but is not a complete production safety system. Store reset scripts only with disposable lab resources, keep production credentials separate, and require stronger deployment controls for real data.

14. Suggested lab file layout

The course does not add these support scripts to the repository in this prompt; the required deliverable remains exactly five lesson HTML files. If you reproduce the lab locally, keep your scripts organized so each later chapter can build on them:

servicehub-postgresql-lab/  README.md  connection/    pg_service.conf.example  sql/    00-verify-target.sql    10-create-roles.sql    20-create-database.sql    30-create-schema.sql    40-grants.sql    50-seed.sql    90-reset.sql  backups/    .gitkeep  notes/    baseline.txt

Never commit a real .pgpass file or real secrets. An example service file may omit passwords entirely.

15. Create a logical safety snapshot before destructive chapters

A logical dump is not the only PostgreSQL backup strategy and is not sufficient for every recovery objective, but it is a useful Chapter 01 safety artifact. Create a custom-format dump after the baseline seed is verified:

pg_dump --dbname="service=servicehub-lab-admin" \  --format=custom \  --file=servicehub_ch01_baseline.dump

Record the tool version used with pg_dump --version. A file existing on disk is not proof of recoverability; Chapter 13 requires restore drills and backup verification. For now, the dump is a convenient rollback point before you intentionally damage the disposable dataset in later lessons.

16. Baseline observability record

Capture a small baseline that future chapters can compare after schema or server changes:

SELECT version() AS server_build;SELECT current_database(), current_user;SHOW data_checksums;SHOW server_encoding;SHOW TimeZone;SHOW search_path;SELECT rolname, rolcanloginFROM pg_catalog.pg_rolesWHERE rolname LIKE 'servicehub_%'ORDER BY rolname;SELECT nspnameFROM pg_catalog.pg_namespaceWHERE nspname IN ('app', 'extensions')ORDER BY nspname;SELECT schemaname, tablename, tableownerFROM pg_catalog.pg_tablesWHERE schemaname = 'app'ORDER BY tablename;SELECT extname, extversionFROM pg_catalog.pg_extensionORDER BY extname;

Do not compare every future environment byte-for-byte. Configuration and version changes will be intentional. The value of a baseline is that changes become explainable rather than mysterious.

17. A deliberately wrong approach: make the application the owner

The fastest way to make permission errors disappear is often to run the application as a superuser or make it own all objects. That also grants the compromised application far more power than its runtime behavior needs. It can redefine schema objects, bypass intended ownership boundaries, and make later least-privilege analysis meaningless.

The repair is the design you just built: ownership sits in servicehub_owner, a non-login role; servicehub_app logs in and receives the narrow database/schema/table/sequence privileges needed for ordinary runtime work. Schema migration tooling can receive a separate controlled path later instead of borrowing the application password.

18. Hands-on lab: rebuild ServiceHub from an empty state

To prove repeatability, do not merely inspect the database you created interactively. Use the following acceptance sequence against the disposable local server:

1. Verify the target server and data directory.
2. Create servicehub_owner and servicehub_app with the declared attributes.
3. Create servicehub_lab owned by servicehub_owner.
4. Create app and extensions schemas.
5. Create the three initial tables as servicehub_owner.
6. Apply current and default privileges to servicehub_app.
7. Seed deterministic sample rows.
8. Connect as servicehub_app and prove SELECT/INSERT succeed.
9. Prove CREATE TABLE app.should_not_exist... fails.
10. Check pg_trgm availability; install only if explicitly choosing the optional exercise.
11. Create the custom-format baseline dump.
12. Run the baseline inventory and save it with client/server version evidence.

Verification query

SELECT  (SELECT count(*) FROM app.customers) AS customers,  (SELECT count(*) FROM app.technicians) AS technicians,  (SELECT count(*) FROM app.work_orders) AS work_orders;SELECT has_schema_privilege('servicehub_app', 'app', 'USAGE') AS app_usage,       has_schema_privilege('servicehub_app', 'app', 'CREATE') AS app_create;SELECT table_name, privilege_typeFROM information_schema.role_table_grantsWHERE grantee = 'servicehub_app'  AND table_schema = 'app'ORDER BY table_name, privilege_type;

The exact work-order count can be one higher if you retained the positive application INSERT exercise. What matters is that the seed state is understood, the application can perform intended DML, and app_create is false.

19. Cleanup policy

Keep this lab through the PostgreSQL course unless a chapter explicitly asks for a fresh topology. When cleanup is genuinely required, create/verify any backup you intend to keep, connect to a different database such as postgres, ensure no important clients are using servicehub_lab, then perform destructive cleanup consciously.

-- Connect to a database other than servicehub_lab first.-- Verify this is the disposable local server.DROP DATABASE servicehub_lab;DROP ROLE servicehub_app;DROP ROLE servicehub_owner;

PostgreSQL will refuse DROP DATABASE from a session connected to that same database. Active sessions can also block ordinary drop behavior. Do not reach for forced termination until you understand which sessions exist and why.

20. Chapter 01 summary and bridge to server internals

You now have the foundations required for deeper PostgreSQL work. You can distinguish the server instance from its database cluster, databases, and schemas; distinguish client tools from server versions; install and verify PostgreSQL 18.x; connect through psql/libpq without hard-coding secrets; and build a repeatable least-privilege ServiceHub lab.

The key habit is evidence-first operation. Query the server for its identity and settings, query catalogs for object/extension state, test both allowed and denied operations, and keep destructive work scoped to a disposable environment.

Chapter 02 moves beneath these logical boundaries. It will trace the supervising server, client backends, auxiliary processes, shared memory, data directory/configuration files, GUC scope, logging, and restart-versus-reload behavior so you can explain what the running PostgreSQL instance is doing rather than merely issue commands to it.

Authoritative references

• Database Roles
• CREATE ROLE
• CREATE DATABASE
• Schemas and search_path
• Privileges
• ALTER DEFAULT PRIVILEGES
• pg_available_extensions
• Additional Supplied Modules and Extensions
• pg_dump