Files

106 lines
4.9 KiB
PL/PgSQL

-- VA-P26b merge input: internal-user and PortalUser actor invariants.
--
-- Prisma models the nullable foreign-key columns but cannot express these
-- cross-column CHECK contracts. This artifact must be embedded after the
-- approved merge migration's audited legacy backfill. Constraints are first
-- installed NOT VALID (so new writes are protected immediately) and then
-- validated in the same transaction. Any unresolved legacy actor fails closed.
BEGIN;
DO $$
BEGIN
IF NOT current_setting('is_superuser')::boolean
AND NOT pg_has_role(current_user, 'hivegrid_rls_definer', 'MEMBER') THEN
RAISE EXCEPTION 'Actor-contract installer must be superuser or a member of hivegrid_rls_definer';
END IF;
END$$;
GRANT SELECT ON "Form" TO hivegrid_rls_definer;
GRANT CREATE ON SCHEMA public TO hivegrid_rls_definer;
SET LOCAL ROLE hivegrid_rls_definer;
-- FormSubmission cannot reference Form.subtype directly from a plain CHECK.
-- This stable, fail-closed predicate binds the submission to the exact tenant
-- form: authenticated portal_form rows require one actor; standard/public and
-- legacy rows may remain anonymous but may never carry two actors.
CREATE OR REPLACE FUNCTION public.hivegrid_form_submission_actor_contract(
p_form_id text,
p_tenant_id text,
p_internal_user_id text,
p_portal_user_id text
)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, public
SET row_security = off
AS $function$
SELECT COALESCE((
SELECT CASE
-- Compare through text so this reviewed contract can be installed in
-- the same outer transaction that adds the portal_form enum value.
WHEN form."subtype"::text = 'portal_form'
THEN pg_catalog.num_nonnulls(p_internal_user_id, p_portal_user_id) = 1
ELSE pg_catalog.num_nonnulls(p_internal_user_id, p_portal_user_id) <= 1
END
FROM public."Form" AS form
WHERE form."id" = p_form_id
AND form."tenantId" = p_tenant_id
), false)
$function$;
ALTER FUNCTION public.hivegrid_form_submission_actor_contract(text,text,text,text) OWNER TO hivegrid_rls_definer;
REVOKE ALL ON FUNCTION public.hivegrid_form_submission_actor_contract(text,text,text,text)
FROM PUBLIC, hivegrid_portal, hivegrid_authentik_jit;
GRANT EXECUTE ON FUNCTION public.hivegrid_form_submission_actor_contract(text,text,text,text)
TO hivegrid_migrator, hivegrid_app, hivegrid_worker;
RESET ROLE;
REVOKE CREATE ON SCHEMA public FROM hivegrid_rls_definer;
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'File_actor_exactly_one_check' AND conrelid = '"File"'::regclass) THEN
ALTER TABLE "File"
ADD CONSTRAINT "File_actor_exactly_one_check"
CHECK (pg_catalog.num_nonnulls("createdByUserId", "createdByPortalUserId") = 1) NOT VALID;
END IF;
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'FileUpload_actor_exactly_one_check' AND conrelid = '"FileUpload"'::regclass) THEN
ALTER TABLE "FileUpload"
ADD CONSTRAINT "FileUpload_actor_exactly_one_check"
CHECK (pg_catalog.num_nonnulls("createdByUserId", "createdByPortalUserId") = 1) NOT VALID;
END IF;
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'FileEntityLink_actor_exactly_one_check' AND conrelid = '"FileEntityLink"'::regclass) THEN
ALTER TABLE "FileEntityLink"
ADD CONSTRAINT "FileEntityLink_actor_exactly_one_check"
CHECK (pg_catalog.num_nonnulls("createdByUserId", "createdByPortalUserId") = 1) NOT VALID;
END IF;
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'FormSubmission_actor_contract_check' AND conrelid = '"FormSubmission"'::regclass) THEN
ALTER TABLE "FormSubmission"
ADD CONSTRAINT "FormSubmission_actor_contract_check"
CHECK (public.hivegrid_form_submission_actor_contract("formId", "tenantId", "createdByUserId", "createdByPortalUserId")) NOT VALID;
END IF;
END$$;
ALTER TABLE "File" VALIDATE CONSTRAINT "File_actor_exactly_one_check";
ALTER TABLE "FileUpload" VALIDATE CONSTRAINT "FileUpload_actor_exactly_one_check";
ALTER TABLE "FileEntityLink" VALIDATE CONSTRAINT "FileEntityLink_actor_exactly_one_check";
ALTER TABLE "FormSubmission" VALIDATE CONSTRAINT "FormSubmission_actor_contract_check";
-- The migrator needs EXECUTE only while PostgreSQL validates the CHECK. It is
-- revoked before the outer transaction can commit.
SET LOCAL ROLE hivegrid_rls_definer;
REVOKE EXECUTE ON FUNCTION public.hivegrid_form_submission_actor_contract(text,text,text,text)
FROM hivegrid_migrator;
RESET ROLE;
COMMIT;
-- Inverse merge SQL (execute only during an approved rollback):
-- ALTER TABLE "FormSubmission" DROP CONSTRAINT IF EXISTS "FormSubmission_actor_contract_check";
-- ALTER TABLE "FileEntityLink" DROP CONSTRAINT IF EXISTS "FileEntityLink_actor_exactly_one_check";
-- ALTER TABLE "FileUpload" DROP CONSTRAINT IF EXISTS "FileUpload_actor_exactly_one_check";
-- ALTER TABLE "File" DROP CONSTRAINT IF EXISTS "File_actor_exactly_one_check";
-- DROP FUNCTION IF EXISTS public.hivegrid_form_submission_actor_contract(text,text,text,text);