From 63db9395831cc4f8bbd368fea71bbd4bd4857908 Mon Sep 17 00:00:00 2001
From: Michael Hoennig <michael@hoennig.de>
Date: Mon, 17 Oct 2022 12:18:12 +0200
Subject: [PATCH] hoist `select queryAccessibleObjectUuidsOfSubjectIds(...)` into WITH CTE for better performance
---
src/main/resources/db/changelog/058-rbac-generators.sql | 14 ++++++++------
1 files changed, 8 insertions(+), 6 deletions(-)
diff --git a/src/main/resources/db/changelog/058-rbac-generators.sql b/src/main/resources/db/changelog/058-rbac-generators.sql
index af5422d..f2214a9 100644
--- a/src/main/resources/db/changelog/058-rbac-generators.sql
+++ b/src/main/resources/db/changelog/058-rbac-generators.sql
@@ -143,15 +143,17 @@
/*
Creates a restricted view based on the 'view' permission of the current subject.
*/
- -- TODO.refa: hoist `select queryAccessibleObjectUuidsOfSubjectIds(...)` into WITH CTE for performance
sql := format($sql$
set session session authorization default;
create view %1$s_rv as
- select target.*
- from %1$s as target
- where target.uuid in (select queryAccessibleObjectUuidsOfSubjectIds('view', '%1$s', currentSubjectsUuids()))
- order by %2$s;
- grant all privileges on %1$s_rv to restricted;
+ with accessibleObjects as (
+ select queryAccessibleObjectUuidsOfSubjectIds('view', '%1$s', currentSubjectsUuids())
+ )
+ select target.*
+ from %1$s as target
+ where target.uuid in (select * from accessibleObjects)
+ order by %2$s;
+ grant all privileges on %1$s_rv to restricted;
$sql$, targetTable, orderBy);
execute sql;
--
Gitblit v1.9.3