warkanum
4c8e1066d4
Replace the ad-hoc tables/procedures install layout with versioned, ordered SQL migrations tracked in broker_schema_migrations. Add optional least-privilege role provisioning (--with-roles), multi-tenant row-level security, lease-based job claiming with stale-lease recovery, and job dependencies -- both by job id and by fan-in job group. Add Docker/Compose support for running the broker and its test suite.
61 lines
2.9 KiB
SQL
61 lines
2.9 KiB
SQL
-- Reference role/grant setup for pgsql-broker.
|
|
--
|
|
-- Applied via `pgsql-broker install --with-roles` (run once per cluster;
|
|
-- the schema/grant statements are safe to re-run per database). The
|
|
-- __BROKER_*_PASSWORD__ placeholders are substituted by the installer at
|
|
-- render time -- never edit this file to hardcode a real password. Each
|
|
-- CREATE ROLE is guarded so re-running this (e.g. against a second
|
|
-- configured database) rotates the password via ALTER ROLE instead of
|
|
-- failing on an already-existing role.
|
|
--
|
|
-- Roles:
|
|
-- broker_admin -- schema owner, runs migrations (`pgsql-broker install`).
|
|
-- Needs BYPASSRLS so broker_recover_stale_jobs (SECURITY
|
|
-- DEFINER, owned by this role) can sweep all tenants.
|
|
-- broker_runtime -- the role the running broker process connects as.
|
|
-- No BYPASSRLS, no ownership, SEARCH_PATH=broker so the
|
|
-- broker's unqualified table/function references resolve.
|
|
-- broker_enqueue -- narrow role for services that only need to add jobs.
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'broker_admin') THEN
|
|
CREATE ROLE broker_admin LOGIN PASSWORD __BROKER_ADMIN_PASSWORD__ BYPASSRLS;
|
|
ELSE
|
|
ALTER ROLE broker_admin WITH LOGIN PASSWORD __BROKER_ADMIN_PASSWORD__ BYPASSRLS;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'broker_runtime') THEN
|
|
CREATE ROLE broker_runtime LOGIN PASSWORD __BROKER_RUNTIME_PASSWORD__;
|
|
ELSE
|
|
ALTER ROLE broker_runtime WITH LOGIN PASSWORD __BROKER_RUNTIME_PASSWORD__;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'broker_enqueue') THEN
|
|
CREATE ROLE broker_enqueue LOGIN PASSWORD __BROKER_ENQUEUE_PASSWORD__;
|
|
ELSE
|
|
ALTER ROLE broker_enqueue WITH LOGIN PASSWORD __BROKER_ENQUEUE_PASSWORD__;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
ALTER ROLE broker_runtime SET search_path = broker, public;
|
|
ALTER ROLE broker_enqueue SET search_path = broker, public;
|
|
|
|
-- Run once broker.broker_jobs etc. already exist (i.e. after `pgsql-broker install`
|
|
-- as broker_admin), so schema ownership/grants land on the right objects.
|
|
|
|
ALTER SCHEMA broker OWNER TO broker_admin;
|
|
GRANT USAGE ON SCHEMA broker TO broker_runtime, broker_enqueue;
|
|
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA broker TO broker_runtime;
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA broker TO broker_runtime;
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA broker TO broker_runtime;
|
|
|
|
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA broker FROM broker_enqueue;
|
|
GRANT EXECUTE ON FUNCTION broker.broker_add_job TO broker_enqueue;
|
|
GRANT EXECUTE ON FUNCTION broker.broker_add_job_simple TO broker_enqueue;
|
|
GRANT EXECUTE ON FUNCTION broker.broker_set_tenant TO broker_enqueue;
|
|
GRANT INSERT, SELECT ON broker.broker_jobs, broker.broker_job_dependency TO broker_enqueue;
|
|
GRANT USAGE ON broker.broker_jobs_id_broker_jobs_seq TO broker_enqueue;
|