-- DMS permission V2.3 database regression.
|
-- Prerequisite: V2.2 schema followed by upgrade_dms_permission_v2_2_to_v2_3_20260817.sql.
|
-- The entire test runs in one transaction and always rolls back its test data.
|
|
\set ON_ERROR_STOP on
|
|
SET lock_timeout = '5s';
|
SET statement_timeout = '60s';
|
|
BEGIN;
|
|
DO $dms_v23_schema_assertions$
|
DECLARE
|
required_object TEXT;
|
BEGIN
|
FOREACH required_object IN ARRAY ARRAY[
|
'public.dms_access_exclusions',
|
'public.dms_permission_revisions',
|
'public.idx_dms_folders_parent_name',
|
'public.idx_dms_documents_folder_name',
|
'public.idx_dms_file_versions_document_id'
|
] LOOP
|
IF to_regclass(required_object) IS NULL THEN
|
RAISE EXCEPTION 'required V2.3 object % is missing', required_object;
|
END IF;
|
END LOOP;
|
|
IF EXISTS (
|
SELECT 1
|
FROM pg_catalog.pg_index i
|
WHERE i.indexrelid IN (
|
'public.idx_dms_folders_parent_name'::REGCLASS,
|
'public.idx_dms_documents_folder_name'::REGCLASS,
|
'public.idx_dms_file_versions_document_id'::REGCLASS
|
)
|
AND (NOT i.indisvalid OR NOT i.indisready)
|
) THEN
|
RAISE EXCEPTION 'one or more DMS V2.3 paging indexes are invalid';
|
END IF;
|
END;
|
$dms_v23_schema_assertions$;
|
|
INSERT INTO public.dms_folders (
|
id, tenant_id, parent_id, name, created_by
|
) VALUES (
|
'019d0000-0000-7000-8000-000000000001',
|
'dms-v23-regression',
|
NULL,
|
'V2.3 regression root',
|
'dms-v23-test'
|
);
|
|
INSERT INTO public.dms_permission_resources (
|
id, tenant_id, resource_type, source_type, source_id, registered_by
|
) VALUES (
|
'019d0000-0000-7000-8000-000000000001',
|
'dms-v23-regression',
|
'FOLDER',
|
'MIGRATION',
|
'dms-v23-regression-folder',
|
'dms-v23-test'
|
);
|
|
INSERT INTO public.dms_documents (
|
id, tenant_id, folder_id, name, created_by
|
) VALUES (
|
'019d0000-0000-7000-8000-000000000002',
|
'dms-v23-regression',
|
'019d0000-0000-7000-8000-000000000001',
|
'V2.3 regression document',
|
'dms-v23-test'
|
);
|
|
INSERT INTO public.dms_permission_resources (
|
id, tenant_id, resource_type, source_type, source_id, registered_by
|
) VALUES (
|
'019d0000-0000-7000-8000-000000000002',
|
'dms-v23-regression',
|
'DOCUMENT',
|
'MIGRATION',
|
'dms-v23-regression-document',
|
'dms-v23-test'
|
);
|
|
INSERT INTO public.dms_file_versions (
|
id, tenant_id, document_id, version_no, file_name, created_by
|
) VALUES (
|
'019d0000-0000-7000-8000-000000000003',
|
'dms-v23-regression',
|
'019d0000-0000-7000-8000-000000000002',
|
1,
|
'v2.3-regression.txt',
|
'dms-v23-test'
|
);
|
|
INSERT INTO public.dms_permission_resources (
|
id, tenant_id, resource_type, source_type, source_id, registered_by
|
) VALUES (
|
'019d0000-0000-7000-8000-000000000003',
|
'dms-v23-regression',
|
'FILE_VERSION',
|
'MIGRATION',
|
'dms-v23-regression-version',
|
'dms-v23-test'
|
);
|
|
INSERT INTO public.dms_file_permissions (
|
id, tenant_id, resource_type, resource_id, resource_scope,
|
principal_type, principal_id, include_child_orgs, action_code,
|
source_type, source_id, max_use_count, grant_reason,
|
granted_by_type, granted_by_id
|
) VALUES (
|
'019d0000-0000-7000-8000-000000000100',
|
'dms-v23-regression',
|
'DOCUMENT',
|
'019d0000-0000-7000-8000-000000000002',
|
'SELF',
|
'USER',
|
'dms-v23-user',
|
FALSE,
|
'READ',
|
'MIGRATION',
|
'dms-v23-regression-permission',
|
5,
|
'DMS V2.3 database regression',
|
'SERVICE',
|
'dms-v23-test'
|
);
|
|
DO $dms_v23_permission_insert_revision$
|
BEGIN
|
IF (SELECT revision FROM public.dms_permission_revisions
|
WHERE tenant_id = 'dms-v23-regression') <> 1 THEN
|
RAISE EXCEPTION 'one permission INSERT statement must initialize tenant revision to 1';
|
END IF;
|
END;
|
$dms_v23_permission_insert_revision$;
|
|
-- Empty statements fire statement-level triggers but must not change the tenant revision.
|
INSERT INTO public.dms_file_permissions (
|
tenant_id, resource_type, resource_id, resource_scope,
|
principal_type, principal_id, action_code, source_type, source_id,
|
granted_by_type, granted_by_id
|
)
|
SELECT
|
'dms-v23-regression',
|
'DOCUMENT',
|
'019d0000-0000-7000-8000-000000000002',
|
'SELF',
|
'USER',
|
'nobody',
|
'READ',
|
'MIGRATION',
|
'dms-v23-empty-permission',
|
'SERVICE',
|
'dms-v23-test'
|
WHERE FALSE;
|
|
DO $dms_v23_empty_permission_revision$
|
BEGIN
|
IF (SELECT revision FROM public.dms_permission_revisions
|
WHERE tenant_id = 'dms-v23-regression') <> 1 THEN
|
RAISE EXCEPTION 'empty permission INSERT must not change tenant revision';
|
END IF;
|
END;
|
$dms_v23_empty_permission_revision$;
|
|
-- A usage changes the grant row revision but must not change the tenant permission revision.
|
INSERT INTO public.dms_permission_usages (
|
tenant_id, permission_id, user_id, action_code,
|
actual_resource_type, actual_resource_id, operation_id
|
) VALUES (
|
'dms-v23-regression',
|
'019d0000-0000-7000-8000-000000000100',
|
'dms-v23-user',
|
'READ',
|
'FILE_VERSION',
|
'019d0000-0000-7000-8000-000000000003',
|
'019d0000-0000-7000-8000-000000000200'
|
);
|
|
DO $dms_v23_usage_revision$
|
BEGIN
|
IF (SELECT revision FROM public.dms_permission_revisions
|
WHERE tenant_id = 'dms-v23-regression') <> 1 THEN
|
RAISE EXCEPTION 'permission usage must not change tenant revision';
|
END IF;
|
IF (SELECT used_count FROM public.dms_file_permissions
|
WHERE id = '019d0000-0000-7000-8000-000000000100') <> 1 THEN
|
RAISE EXCEPTION 'permission usage did not increment used_count';
|
END IF;
|
END;
|
$dms_v23_usage_revision$;
|
|
-- Two exclusions in one statement must bump the tenant revision only once.
|
INSERT INTO public.dms_access_exclusions (
|
id, tenant_id, resource_type, resource_id, user_id, action_code,
|
source_type, source_id, exclude_reason, excluded_by_type, excluded_by_id
|
) VALUES
|
(
|
'019d0000-0000-7000-8000-000000000301',
|
'dms-v23-regression',
|
'DOCUMENT',
|
'019d0000-0000-7000-8000-000000000002',
|
'dms-v23-user',
|
'READ',
|
'MIGRATION',
|
'dms-v23-regression-exclusions',
|
'Document exclusion regression',
|
'SERVICE',
|
'dms-v23-test'
|
),
|
(
|
'019d0000-0000-7000-8000-000000000302',
|
'dms-v23-regression',
|
'FILE_VERSION',
|
'019d0000-0000-7000-8000-000000000003',
|
'dms-v23-user',
|
'READ',
|
'MIGRATION',
|
'dms-v23-regression-exclusions',
|
'FileVersion exclusion regression',
|
'SERVICE',
|
'dms-v23-test'
|
);
|
|
DO $dms_v23_exclusion_insert_revision$
|
BEGIN
|
IF (SELECT revision FROM public.dms_permission_revisions
|
WHERE tenant_id = 'dms-v23-regression') <> 2 THEN
|
RAISE EXCEPTION 'one exclusion INSERT statement must bump tenant revision exactly once';
|
END IF;
|
END;
|
$dms_v23_exclusion_insert_revision$;
|
|
INSERT INTO public.dms_access_exclusions (
|
tenant_id, resource_type, resource_id, user_id,
|
source_type, source_id, excluded_by_type, excluded_by_id
|
)
|
SELECT
|
'dms-v23-regression',
|
'DOCUMENT',
|
'019d0000-0000-7000-8000-000000000002',
|
'nobody',
|
'MIGRATION',
|
'dms-v23-empty-exclusion',
|
'SERVICE',
|
'dms-v23-test'
|
WHERE FALSE;
|
|
DO $dms_v23_empty_exclusion_revision$
|
BEGIN
|
IF (SELECT revision FROM public.dms_permission_revisions
|
WHERE tenant_id = 'dms-v23-regression') <> 2 THEN
|
RAISE EXCEPTION 'empty exclusion INSERT must not change tenant revision';
|
END IF;
|
END;
|
$dms_v23_empty_exclusion_revision$;
|
|
UPDATE public.dms_access_exclusions
|
SET revoked_at = CURRENT_TIMESTAMP,
|
revoked_by_type = 'SERVICE',
|
revoked_by_id = 'dms-v23-test',
|
revoke_reason = 'DMS V2.3 regression revocation',
|
revision = revision + 1
|
WHERE tenant_id = 'dms-v23-regression';
|
|
DO $dms_v23_exclusion_revoke_revision$
|
BEGIN
|
IF (SELECT revision FROM public.dms_permission_revisions
|
WHERE tenant_id = 'dms-v23-regression') <> 3 THEN
|
RAISE EXCEPTION 'one exclusion revocation statement must bump tenant revision exactly once';
|
END IF;
|
END;
|
$dms_v23_exclusion_revoke_revision$;
|
|
UPDATE public.dms_file_permissions
|
SET revoked_at = CURRENT_TIMESTAMP,
|
revoked_by_type = 'SERVICE',
|
revoked_by_id = 'dms-v23-test',
|
revoke_reason = 'DMS V2.3 regression revocation',
|
revision = revision + 1
|
WHERE id = '019d0000-0000-7000-8000-000000000100';
|
|
DO $dms_v23_permission_revoke_revision$
|
BEGIN
|
IF (SELECT revision FROM public.dms_permission_revisions
|
WHERE tenant_id = 'dms-v23-regression') <> 4 THEN
|
RAISE EXCEPTION 'permission revocation must bump tenant revision exactly once';
|
END IF;
|
END;
|
$dms_v23_permission_revoke_revision$;
|
|
DO $dms_v23_immutable_assertions$
|
BEGIN
|
BEGIN
|
UPDATE public.dms_file_permissions
|
SET id = '019d0000-0000-7000-8000-000000000101',
|
revision = revision + 1
|
WHERE id = '019d0000-0000-7000-8000-000000000100';
|
RAISE EXCEPTION 'permission id update unexpectedly succeeded';
|
EXCEPTION
|
WHEN SQLSTATE '55000' THEN NULL;
|
END;
|
|
BEGIN
|
UPDATE public.dms_access_exclusions
|
SET revoked_at = CURRENT_TIMESTAMP,
|
revoked_by_type = 'SERVICE',
|
revoked_by_id = 'dms-v23-test',
|
revoke_reason = 'Repeated revocation',
|
revision = revision + 1
|
WHERE id = '019d0000-0000-7000-8000-000000000301';
|
RAISE EXCEPTION 'repeated exclusion revocation unexpectedly succeeded';
|
EXCEPTION
|
WHEN SQLSTATE '55000' THEN NULL;
|
END;
|
|
BEGIN
|
DELETE FROM public.dms_access_exclusions
|
WHERE tenant_id = 'dms-v23-regression';
|
RAISE EXCEPTION 'physical exclusion delete unexpectedly succeeded';
|
EXCEPTION
|
WHEN SQLSTATE '55000' THEN NULL;
|
END;
|
|
BEGIN
|
TRUNCATE TABLE public.dms_access_exclusions;
|
RAISE EXCEPTION 'physical exclusion truncate unexpectedly succeeded';
|
EXCEPTION
|
WHEN SQLSTATE '55000' THEN NULL;
|
END;
|
|
BEGIN
|
DELETE FROM public.dms_permission_revisions
|
WHERE tenant_id = 'dms-v23-regression';
|
RAISE EXCEPTION 'physical permission revision delete unexpectedly succeeded';
|
EXCEPTION
|
WHEN SQLSTATE '55000' THEN NULL;
|
END;
|
END;
|
$dms_v23_immutable_assertions$;
|
|
DO $dms_v23_constraint_assertions$
|
BEGIN
|
BEGIN
|
INSERT INTO public.dms_access_exclusions (
|
tenant_id, resource_type, resource_id, user_id,
|
source_type, source_id, excluded_by_type, excluded_by_id
|
) VALUES (
|
'dms-v23-regression',
|
'FOLDER',
|
'019d0000-0000-7000-8000-000000000001',
|
'dms-v23-user',
|
'MIGRATION',
|
'dms-v23-invalid-folder-exclusion',
|
'SERVICE',
|
'dms-v23-test'
|
);
|
RAISE EXCEPTION 'FOLDER exclusion unexpectedly succeeded';
|
EXCEPTION
|
WHEN check_violation THEN NULL;
|
END;
|
|
BEGIN
|
INSERT INTO public.dms_access_exclusions (
|
tenant_id, resource_type, resource_id, user_id,
|
source_type, source_id, excluded_by_type, excluded_by_id
|
) VALUES (
|
'another-tenant',
|
'DOCUMENT',
|
'019d0000-0000-7000-8000-000000000002',
|
'dms-v23-user',
|
'MIGRATION',
|
'dms-v23-cross-tenant-exclusion',
|
'SERVICE',
|
'dms-v23-test'
|
);
|
RAISE EXCEPTION 'cross-tenant exclusion unexpectedly succeeded';
|
EXCEPTION
|
WHEN foreign_key_violation THEN NULL;
|
END;
|
END;
|
$dms_v23_constraint_assertions$;
|
|
ROLLBACK;
|
|
SELECT 'DMS permission V2.3 database regression passed' AS result;
|
