summaryrefslogtreecommitdiffstats
path: root/contrib/sepgsql/sql/misc.sql
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /contrib/sepgsql/sql/misc.sql
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'contrib/sepgsql/sql/misc.sql')
-rw-r--r--contrib/sepgsql/sql/misc.sql45
1 files changed, 45 insertions, 0 deletions
diff --git a/contrib/sepgsql/sql/misc.sql b/contrib/sepgsql/sql/misc.sql
new file mode 100644
index 0000000..bd5b6e2
--- /dev/null
+++ b/contrib/sepgsql/sql/misc.sql
@@ -0,0 +1,45 @@
+--
+-- Regression Test for Misc Permission Checks
+--
+
+LOAD '$libdir/sepgsql'; -- failed
+
+--
+-- Permissions to execute functions
+--
+CREATE TABLE t1 (x int, y text);
+INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(1,100) x);
+
+CREATE TABLE t1p (o int, p text) PARTITION BY RANGE (o);
+CREATE TABLE t1p_ones PARTITION OF t1p FOR VALUES FROM ('0') TO ('10');
+CREATE TABLE t1p_tens PARTITION OF t1p FOR VALUES FROM ('10') TO ('100');
+INSERT INTO t1p (SELECT x, md5(x::text) FROM generate_series(0,99) x);
+
+SET sepgsql.debug_audit = on;
+SET client_min_messages = log;
+
+-- regular function and operators
+SELECT * FROM t1 WHERE x > 50 AND y like '%64%';
+SELECT * FROM t1p WHERE o > 50 AND p like '%64%';
+SELECT * FROM t1p_ones WHERE o > 50 AND p like '%64%';
+SELECT * FROM t1p_tens WHERE o > 50 AND p like '%64%';
+
+-- aggregate function
+SELECT MIN(x), AVG(x) FROM t1;
+SELECT MIN(o), AVG(o) FROM t1p;
+SELECT MIN(o), AVG(o) FROM t1p_ones;
+SELECT MIN(o), AVG(o) FROM t1p_tens;
+
+-- window function
+SELECT row_number() OVER (order by x), * FROM t1 WHERE y like '%86%';
+SELECT row_number() OVER (order by o), * FROM t1p WHERE p like '%86%';
+SELECT row_number() OVER (order by o), * FROM t1p_ones WHERE p like '%86%';
+SELECT row_number() OVER (order by o), * FROM t1p_tens WHERE p like '%86%';
+
+RESET sepgsql.debug_audit;
+RESET client_min_messages;
+--
+-- Cleanup
+--
+DROP TABLE IF EXISTS t1 CASCADE;
+DROP TABLE IF EXISTS t1p CASCADE;