313 lines
15 KiB
PL/PgSQL
313 lines
15 KiB
PL/PgSQL
-- 0005 rollback: put the four tenant tables back in `public`, one shared table again.
|
|
--
|
|
-- This file **runs**. It is not the commented-out sketch at the end of a
|
|
-- migration, because a rollback for a data migration that exists only as a comment
|
|
-- is a rollback nobody runs.
|
|
--
|
|
-- psql -d <database> -v ON_ERROR_STOP=1 -f 0005_tenant_schemas_rollback.sql
|
|
--
|
|
-- It undoes 0005 completely:
|
|
--
|
|
-- 1. recreate `public.projects`, `public.project_authors`,
|
|
-- `public.project_publishers` and `public.invitations` with their
|
|
-- `organization_id` columns, constraints, indexes, policies and trigger;
|
|
-- 2. copy every organization's rows back, recovering `organization_id` from the
|
|
-- schema name - the name was derived from the identifier, so this is exact and
|
|
-- not a guess;
|
|
-- 3. restore the three foreign keys that pointed into `projects`, and the two
|
|
-- `app` functions that read it;
|
|
-- 4. drop the tenant schemas, the trigger and the two provisioning functions;
|
|
-- 5. remove the ledger row.
|
|
--
|
|
-- ## What it cannot restore
|
|
--
|
|
-- Three things, stated rather than discovered afterwards:
|
|
--
|
|
-- * **Rows written into a tenant schema since the migration.** They are copied
|
|
-- back, so they survive; but a row whose `created_by` names a user deleted in
|
|
-- the meantime will fail the foreign key and stop the copy. That is the
|
|
-- constraint doing its job, and the fix is to resolve the user, not to drop the
|
|
-- constraint.
|
|
-- * **The column order and any constraint added by a later migration** that
|
|
-- referenced the tenant tables. This rollback is written against 0001-0005 and
|
|
-- nothing after them.
|
|
-- * **The gate it reopens.** The shared tables have no `organization_id`
|
|
-- filtering unless the policies below are enforced, and they are not enforced
|
|
-- for the table owner, which is the application. Rolling back returns the
|
|
-- deployment to gate **G2** and to shared-tables-plus-RLS, both of which §5.0
|
|
-- records as measured failures. Rolling forward is the direction; this file
|
|
-- exists so that the decision is reversible, not because it is better.
|
|
|
|
BEGIN;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 1. The shared tables, as they were
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS public.projects (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
organization_id uuid NOT NULL REFERENCES public.organizations(id),
|
|
name text NOT NULL,
|
|
slug public.citext NOT NULL,
|
|
status text NOT NULL DEFAULT 'active'
|
|
CHECK (status IN ('active', 'on_hold', 'released', 'archived')),
|
|
created_by uuid REFERENCES public.users(id),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
-- Referenced by `invitations` and `user_roles` as `(project_id,
|
|
-- organization_id)`, so the pair has to be unique for those foreign keys to
|
|
-- exist again.
|
|
CONSTRAINT projects_id_organization_id_key UNIQUE (id, organization_id),
|
|
CONSTRAINT projects_organization_id_slug_key UNIQUE (organization_id, slug)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.project_authors (
|
|
project_id uuid NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
|
|
author_id uuid NOT NULL REFERENCES public.authors(id),
|
|
credit_role text NOT NULL
|
|
CHECK (credit_role IN ('author', 'translator', 'editor', 'narrator',
|
|
'cover_artist', 'illustrator')),
|
|
lang text NOT NULL,
|
|
"position" smallint NOT NULL DEFAULT 1,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
created_by uuid REFERENCES public.users(id),
|
|
CONSTRAINT project_authors_pkey PRIMARY KEY (project_id, author_id, credit_role, lang)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.project_publishers (
|
|
project_id uuid NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
|
|
publisher_id uuid NOT NULL REFERENCES public.publishers(id),
|
|
lang text NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
created_by uuid REFERENCES public.users(id),
|
|
CONSTRAINT project_publishers_pkey PRIMARY KEY (project_id, publisher_id, lang)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.invitations (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
organization_id uuid NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
|
|
project_id uuid,
|
|
email public.citext NOT NULL,
|
|
role_id uuid NOT NULL REFERENCES public.roles(id) ON DELETE RESTRICT,
|
|
token_hash text NOT NULL UNIQUE,
|
|
invited_by uuid NOT NULL REFERENCES public.users(id),
|
|
expires_at timestamptz NOT NULL,
|
|
accepted_at timestamptz,
|
|
revoked_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT invitations_project_id_organization_id_fkey
|
|
FOREIGN KEY (project_id, organization_id)
|
|
REFERENCES public.projects(id, organization_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS projects_organization_idx ON public.projects (organization_id);
|
|
CREATE INDEX IF NOT EXISTS invitations_email_idx ON public.invitations (email);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS project_authors_order_idx
|
|
ON public.project_authors (project_id, credit_role, COALESCE(lang, ''), "position");
|
|
CREATE UNIQUE INDEX IF NOT EXISTS project_publishers_once_idx
|
|
ON public.project_publishers (project_id, publisher_id, COALESCE(lang, ''));
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 2. The rows, back into the shared tables
|
|
-- ---------------------------------------------------------------------------
|
|
-- `organization_id` is recovered by parsing the schema name, which is the
|
|
-- definition `app.tenant_schema_name` used to build it. That is why the name was
|
|
-- derived rather than stored: a rollback can rebuild the column from the schema,
|
|
-- and a stored name could have disagreed with the rows.
|
|
DO $restore$
|
|
DECLARE
|
|
tenant record;
|
|
organization_id uuid;
|
|
BEGIN
|
|
FOR tenant IN SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant\_%' ORDER BY nspname LOOP
|
|
BEGIN
|
|
organization_id := substring(tenant.nspname FROM 8)::uuid;
|
|
EXCEPTION WHEN others THEN
|
|
-- A schema that matches the pattern but is not one of ours. Skipped
|
|
-- loudly rather than dropped: something else made it, and guessing at
|
|
-- its contents is how a rollback destroys data.
|
|
RAISE WARNING 'skipping %: its name is not an organization identifier', tenant.nspname;
|
|
CONTINUE;
|
|
END;
|
|
|
|
EXECUTE format(
|
|
'INSERT INTO public.projects (id, organization_id, name, slug, status, created_by, created_at, updated_at) '
|
|
'SELECT id, %L, name, slug, status, created_by, created_at, updated_at FROM %I.projects '
|
|
'ON CONFLICT DO NOTHING',
|
|
organization_id, tenant.nspname
|
|
);
|
|
EXECUTE format(
|
|
'INSERT INTO public.project_authors (project_id, author_id, credit_role, lang, "position", created_at, created_by) '
|
|
'SELECT project_id, author_id, credit_role, lang, "position", created_at, created_by FROM %I.project_authors '
|
|
'ON CONFLICT DO NOTHING',
|
|
tenant.nspname
|
|
);
|
|
EXECUTE format(
|
|
'INSERT INTO public.project_publishers (project_id, publisher_id, lang, created_at, created_by) '
|
|
'SELECT project_id, publisher_id, lang, created_at, created_by FROM %I.project_publishers '
|
|
'ON CONFLICT DO NOTHING',
|
|
tenant.nspname
|
|
);
|
|
EXECUTE format(
|
|
'INSERT INTO public.invitations (id, organization_id, project_id, email, role_id, token_hash, invited_by, '
|
|
'expires_at, accepted_at, revoked_at, created_at) '
|
|
'SELECT id, %L, project_id, email, role_id, token_hash, invited_by, expires_at, accepted_at, revoked_at, created_at '
|
|
' FROM %I.invitations ON CONFLICT DO NOTHING',
|
|
organization_id, tenant.nspname
|
|
);
|
|
END LOOP;
|
|
END
|
|
$restore$;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 3. The policies, and the trigger
|
|
-- ---------------------------------------------------------------------------
|
|
-- Restored verbatim from the 0001-0004 schema. They are not decoration: without
|
|
-- them the four tables sit in `public` with no tenant filter at all, which is
|
|
-- strictly worse than either state - the tables are shared *and* unprotected.
|
|
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS projects_select ON public.projects;
|
|
CREATE POLICY projects_select ON public.projects FOR SELECT USING (app.can_access_project(id));
|
|
DROP POLICY IF EXISTS projects_insert ON public.projects;
|
|
CREATE POLICY projects_insert ON public.projects FOR INSERT
|
|
WITH CHECK (app.is_platform_admin() OR app.is_organization_admin(organization_id));
|
|
DROP POLICY IF EXISTS projects_update ON public.projects;
|
|
CREATE POLICY projects_update ON public.projects FOR UPDATE
|
|
USING (app.is_platform_admin() OR app.is_organization_admin(organization_id))
|
|
WITH CHECK (app.is_platform_admin() OR app.is_organization_admin(organization_id));
|
|
DROP POLICY IF EXISTS projects_delete ON public.projects;
|
|
CREATE POLICY projects_delete ON public.projects FOR DELETE
|
|
USING (app.is_platform_admin() OR app.is_organization_admin(organization_id));
|
|
|
|
ALTER TABLE public.project_authors ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS project_authors_tenant ON public.project_authors;
|
|
CREATE POLICY project_authors_tenant ON public.project_authors
|
|
USING (app.is_platform_admin() OR EXISTS (
|
|
SELECT 1 FROM public.projects p
|
|
WHERE p.id = project_authors.project_id
|
|
AND app.is_organization_member(p.organization_id)));
|
|
|
|
ALTER TABLE public.project_publishers ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS project_publishers_tenant ON public.project_publishers;
|
|
CREATE POLICY project_publishers_tenant ON public.project_publishers
|
|
USING (app.is_platform_admin() OR EXISTS (
|
|
SELECT 1 FROM public.projects p
|
|
WHERE p.id = project_publishers.project_id
|
|
AND app.is_organization_member(p.organization_id)));
|
|
|
|
ALTER TABLE public.invitations ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS invitations_manage ON public.invitations;
|
|
CREATE POLICY invitations_manage ON public.invitations
|
|
USING (app.is_platform_admin() OR app.is_organization_admin(organization_id))
|
|
WITH CHECK (app.is_platform_admin() OR app.is_organization_admin(organization_id));
|
|
DROP POLICY IF EXISTS invitations_select ON public.invitations;
|
|
CREATE POLICY invitations_select ON public.invitations FOR SELECT USING (
|
|
app.is_platform_admin()
|
|
OR app.is_organization_admin(organization_id)
|
|
OR (email = (SELECT u.email FROM public.users u WHERE u.id = app.current_user_id())));
|
|
|
|
DO $$
|
|
BEGIN
|
|
EXECUTE 'DROP TRIGGER IF EXISTS projects_set_updated_at ON public.projects';
|
|
EXECUTE 'CREATE TRIGGER projects_set_updated_at BEFORE UPDATE ON public.projects '
|
|
'FOR EACH ROW EXECUTE FUNCTION app.set_updated_at()';
|
|
END
|
|
$$;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 4. The foreign keys and functions that 0005 removed
|
|
-- ---------------------------------------------------------------------------
|
|
-- Guarded on the catalogue rather than written plainly, because PostgreSQL has no
|
|
-- `ADD CONSTRAINT IF NOT EXISTS`. It is not decoration: every other statement in
|
|
-- this file is safe to run twice, and a rollback that fails on the second attempt
|
|
-- is one that cannot be used to recover from a failure part-way through itself -
|
|
-- and a rollback of a per-tenant migration on a database with a few hundred
|
|
-- tenants is exactly where that happens.
|
|
DO $keys$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'artifacts_project_id_fkey') THEN
|
|
EXECUTE 'ALTER TABLE public.artifacts ADD CONSTRAINT artifacts_project_id_fkey '
|
|
'FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE';
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'jobs_project_id_fkey') THEN
|
|
EXECUTE 'ALTER TABLE public.jobs ADD CONSTRAINT jobs_project_id_fkey '
|
|
'FOREIGN KEY (project_id) REFERENCES public.projects(id)';
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'user_roles_project_id_organization_id_fkey') THEN
|
|
EXECUTE 'ALTER TABLE public.user_roles ADD CONSTRAINT user_roles_project_id_organization_id_fkey '
|
|
'FOREIGN KEY (project_id, organization_id) REFERENCES public.projects(id, organization_id) '
|
|
'ON DELETE CASCADE';
|
|
END IF;
|
|
END
|
|
$keys$;
|
|
|
|
-- The original bodies, which read `projects.organization_id` and could not survive
|
|
-- the table leaving `public`. Restored verbatim so that a rolled-back database is
|
|
-- byte-for-byte the schema 0005 found rather than an approximation of it.
|
|
CREATE OR REPLACE FUNCTION app.can_access_project(target_project_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
STABLE SECURITY DEFINER
|
|
SET search_path TO 'public', 'pg_temp'
|
|
AS $body$
|
|
SELECT app.is_platform_admin()
|
|
OR EXISTS (
|
|
SELECT 1
|
|
FROM projects p
|
|
WHERE p.id = target_project_id
|
|
AND app.is_organization_admin(p.organization_id)
|
|
)
|
|
OR EXISTS (
|
|
SELECT 1
|
|
FROM user_roles ur
|
|
WHERE ur.user_id = app.current_user_id()
|
|
AND ur.project_id = target_project_id
|
|
AND ur.scope = 'project'
|
|
AND ur.status = 'active'
|
|
)
|
|
$body$;
|
|
|
|
CREATE OR REPLACE FUNCTION app.is_organization_admin_for_project(target_project_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
STABLE SECURITY DEFINER
|
|
SET search_path TO 'public', 'pg_temp'
|
|
AS $body$
|
|
SELECT app.is_platform_admin()
|
|
OR EXISTS (
|
|
SELECT 1
|
|
FROM projects p
|
|
WHERE p.id = target_project_id
|
|
AND app.is_organization_admin(p.organization_id)
|
|
)
|
|
$body$;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 5. The tenant schemas, the trigger, the functions, the ledger row
|
|
-- ---------------------------------------------------------------------------
|
|
-- Dropped last, one schema per statement, because each of the four tables in a
|
|
-- schema holds a foreign key and a few hundred of them in one transaction will
|
|
-- exhaust the lock table - which is a failure that looks like a PostgreSQL defect
|
|
-- rather than a rollback that got too big.
|
|
--
|
|
-- When it does happen, the answer is to **run this file again**. Every statement
|
|
-- above it is safe to run twice, the constraints are guarded on the catalogue
|
|
-- precisely so that a re-run does not stop at the first one, and the schema drops
|
|
-- use `IF EXISTS`. So the recovery is another run, not a hand-written fix.
|
|
DO $drop$
|
|
DECLARE
|
|
tenant text;
|
|
BEGIN
|
|
FOR tenant IN SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant\_%' LOOP
|
|
EXECUTE format('DROP SCHEMA IF EXISTS %I CASCADE', tenant);
|
|
END LOOP;
|
|
END
|
|
$drop$;
|
|
|
|
DROP TRIGGER IF EXISTS organizations_provision_tenant_schema ON public.organizations;
|
|
DROP FUNCTION IF EXISTS app.provision_tenant_schema_for_new_organization();
|
|
DROP FUNCTION IF EXISTS app.provision_tenant_schema(uuid);
|
|
COMMENT ON FUNCTION app.tenant_schema_name(uuid) IS NULL;
|
|
|
|
DELETE FROM public.schema_migrations WHERE version = '0005_tenant_schemas';
|
|
|
|
COMMIT; |