On 23.05.25 10:43, Feike Steenbergen wrote:
Attached is a sample exploit, that achieves this, key components:
- the GENERATED column uses a user defined immutable function
- this immutable function cannot ALTER ROLE (needs volatile)
- therefore this immutable function calls a volatile function
- the volatile function can contain any security exploit
I propose to address this by not allowing the use of user-defined
functions in generation expressions for now. The attached patch
implements this. This assumes that all built-in functions are
trustworthy, for this purpose, which seems likely true and likely desirable.
I think the feature is still useful like that, and this approach
provides a path to add new functionality in the future that grows this
set of allowed functions, for example by allowing some configurable set
of "trusted" functions or whatever.
From 0a6fd83bc1b96a0a6a465d64ef06bec4f0a3e824 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 5 Jun 2025 12:35:38 +0200
Subject: [PATCH] Restrict virtual columns to use built-in functions
Just like selecting from a view is exploitable (CVE-2024-7348),
selecting from a table with virtual generated columns is exploitable.
Users who are concerned about this can avoid selecting from views, but
telling them to avoid selecting from tables is less practical.
To address this, this changes it so that generation expressions for
virtual generated columns are restricted to using built-in functions.
We assume that built-in functions cannot be exploited for this
purpose.
In the future, this could be expanded by some new mechanism to declare
other functions as safe or trusted for this purpose, but that is to be
designed.
Reported-by: Feike Steenbergen <feikesteenber...@gmail.com>
Discussion:
https://www.postgresql.org/message-id/flat/CAK_s-G2Q7de8Q0qOYUR%3D_CTB5FzzVBm5iZjOp%2BmeVWpMpmfO0w%40mail.gmail.com
---
doc/src/sgml/ddl.sgml | 9 ++++++++
doc/src/sgml/ref/create_table.sgml | 8 +++++++
src/backend/catalog/heap.c | 17 +++++++++++++++
.../regress/expected/generated_virtual.out | 21 +++++++++----------
src/test/regress/sql/generated_virtual.sql | 15 ++++++-------
5 files changed, 52 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index fcd1cb85352..f442769b9ca 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -419,6 +419,15 @@ <title>Generated Columns</title>
<varname>tableoid</varname>.
</para>
</listitem>
+ <listitem>
+ <para>
+ The generation expression of a virtual generated column must not
+ reference user-defined functions, that is, it can only reference
+ built-in functions. This applies also indirectly, such as for functions
+ underlying operators or casts. (This restriction does not exist for
+ stored generated columns.)
+ </para>
+ </listitem>
<listitem>
<para>
A generated column cannot have a column default or an identity
definition.
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index 4a41b2f5530..d5ffe548d1b 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -929,6 +929,14 @@ <title>Parameters</title>
not other generated columns. Any functions and operators used must be
immutable. References to other tables are not allowed.
</para>
+
+ <para>
+ The generation expression of a virtual generated column must not
+ reference user-defined functions, that is, it can only reference
+ built-in functions. This applies also indirectly, such as for functions
+ underlying operators or casts. (This restriction does not exist for
+ stored generated columns.)
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index fbaed5359ad..aef9711f532 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -3214,6 +3214,12 @@ check_nested_generated(ParseState *pstate, Node *node)
check_nested_generated_walker(node, pstate);
}
+static bool
+contains_user_functions_checker(Oid func_id, void *context)
+{
+ return (func_id >= FirstNormalObjectId);
+}
+
/*
* Take a raw default and convert it to a cooked format ready for
* storage.
@@ -3253,6 +3259,17 @@ cookDefault(ParseState *pstate,
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("generation expression is not
immutable")));
+
+ /*
+ * Virtual generated columns are restricted to using built-in
+ * functions for security reasons.
+ */
+ if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL &&
+ check_functions_in_node(expr,
contains_user_functions_checker, NULL))
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("generation expression uses
user-defined function"),
+ errdetail("Virtual generated columns
that make use of user-defined functions are not yet supported."));
}
else
{
diff --git a/src/test/regress/expected/generated_virtual.out
b/src/test/regress/expected/generated_virtual.out
index 6300e7c1d96..7e14e4f8ad4 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -604,9 +604,11 @@ INSERT INTO gtest11 VALUES (1, 10), (2, 20);
GRANT SELECT (a, c) ON gtest11 TO regress_user11;
CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE
LANGUAGE SQL;
REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
-CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS
(gf1(b)) VIRTUAL);
-INSERT INTO gtest12 VALUES (1, 10), (2, 20);
-GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
+CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS
(gf1(b)) VIRTUAL); -- fails, user-defined function
+ERROR: generation expression uses user-defined function
+DETAIL: Virtual generated columns that make use of user-defined functions are
not yet supported.
+--INSERT INTO gtest12 VALUES (1, 10), (2, 20);
+--GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
SET ROLE regress_user11;
SELECT a, b FROM gtest11; -- not allowed
ERROR: permission denied for table gtest11
@@ -619,15 +621,12 @@ SELECT a, c FROM gtest11; -- allowed
SELECT gf1(10); -- not allowed
ERROR: permission denied for function gf1
-INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- allowed (does not actually
invoke the function)
-SELECT a, c FROM gtest12; -- currently not allowed because of function
permissions, should arguably be allowed
-ERROR: permission denied for function gf1
+--INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- allowed (does not actually
invoke the function)
+--SELECT a, c FROM gtest12; -- currently not allowed because of function
permissions, should arguably be allowed
RESET ROLE;
-DROP FUNCTION gf1(int); -- fail
-ERROR: cannot drop function gf1(integer) because other objects depend on it
-DETAIL: column c of table gtest12 depends on function gf1(integer)
-HINT: Use DROP ... CASCADE to drop the dependent objects too.
-DROP TABLE gtest11, gtest12;
+--DROP FUNCTION gf1(int); -- fail
+DROP TABLE gtest11;
+--DROP TABLE gtest12;
DROP FUNCTION gf1(int);
DROP USER regress_user11;
-- check constraints
diff --git a/src/test/regress/sql/generated_virtual.sql
b/src/test/regress/sql/generated_virtual.sql
index b4eedeee2fb..ae5c1c5f8f8 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -290,20 +290,21 @@ CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int
GENERATED ALWAYS AS (b * 2
CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE
LANGUAGE SQL;
REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
-CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS
(gf1(b)) VIRTUAL);
-INSERT INTO gtest12 VALUES (1, 10), (2, 20);
-GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
+CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS
(gf1(b)) VIRTUAL); -- fails, user-defined function
+--INSERT INTO gtest12 VALUES (1, 10), (2, 20);
+--GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
SET ROLE regress_user11;
SELECT a, b FROM gtest11; -- not allowed
SELECT a, c FROM gtest11; -- allowed
SELECT gf1(10); -- not allowed
-INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- allowed (does not actually
invoke the function)
-SELECT a, c FROM gtest12; -- currently not allowed because of function
permissions, should arguably be allowed
+--INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- allowed (does not actually
invoke the function)
+--SELECT a, c FROM gtest12; -- currently not allowed because of function
permissions, should arguably be allowed
RESET ROLE;
-DROP FUNCTION gf1(int); -- fail
-DROP TABLE gtest11, gtest12;
+--DROP FUNCTION gf1(int); -- fail
+DROP TABLE gtest11;
+--DROP TABLE gtest12;
DROP FUNCTION gf1(int);
DROP USER regress_user11;
base-commit: b87163e5f3847730ee5f59718d215c6e63e13bff
--
2.49.0
