Writing the sql migration scripts that are run by CREATE EXTENSION and
ALTER EXTENSION UPDATE are security minefields for extension authors.
One big reason for this is that search_path is set to the schema of the
extension while running these scripts, and thus if a user with lower
privileges can create functions or operators in that schema they can do
all kinds of search_path confusion attacks if not every function and
operator that is used in the script is schema qualified. While doing
such schema qualification is possible, it relies on the author to never
make a mistake in any of the sql files. And sadly humans have a tendency
to make mistakes.
This patch adds a new "owned_schema" option to the extension control
file that can be set to true to indicate that this extension wants to
own the schema in which it is installed. What that means is that the
schema should not exist before creating the extension, and will be
created during extension creation. This thus gives the extension author
an easy way to use a safe search_path, while still allowing all objects
to be grouped together in a schema. The implementation also has the
pleasant side effect that the schema will be automatically dropped when
the extension is dropped.
One way in which certain extensions currently hack around the
non-existence of this feature is by using the approach that pg_cron
uses: Setting the schema to pg_catalog and running "CREATE SCHEMA
pg_cron" from within the extension script. While this works, it's
obviously a hack, and a big downside of it is that it doesn't allow
users to choose the schema name used by the extension.
PS. I have never added fields to pg_catalag tables before, so there's
a clear TODO in the pg_upgrade code related to that. If anyone has
some pointers for me to look at to address that one that would be
helpful, if not I'll probably figure it out myself. All other code is
in pretty finished state, although I'm considering if
AlterExtensionNamespace should maybe be split a bit somehow, because
owned_schema skips most of the code in that function.
From cca2fc5a820822a29d9bc3b810a811adb1d081e1 Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <jelte.fenn...@microsoft.com>
Date: Fri, 31 May 2024 02:04:31 -0700
Subject: [PATCH v1] Add support for extensions with an owned schema
Writing the sql migration scripts that are run by CREATE EXTENSION and
ALTER EXTENSION UPDATE are security minefields for extension authors.
One big reason for this is that search_path is set to the schema of the
extension while running these scripts, and thus if a user with lower
privileges can create functions or operators in that schema they can do
all kinds of search_path confusion attacks if not every function and
operator that is used in the script is schema qualified. While doing
such schema qualification is possible, it relies on the author to never
make a mistake in any of the sql files. And sadly humans have a tendency
to make mistakes.
This patch adds a new "owned_schema" option to the extension control
file that can be set to true to indicate that this extension wants to
own the schema in which it is installed. What that means is that the
schema should not exist before creating the extension, and will be
created during extension creation. This thus gives the extension author
an easy way to use a safe search_path, while still allowing all objects
to be grouped together in a schema. The implementation also has the
pleasant side effect that the schema will be automatically dropped when
the extension is dropped.
---
doc/src/sgml/extend.sgml | 13 ++
doc/src/sgml/ref/create_extension.sgml | 2 +-
src/backend/commands/extension.c | 139 +++++++++++++-----
src/backend/utils/adt/pg_upgrade_support.c | 1 +
src/include/catalog/pg_extension.h | 1 +
src/include/commands/extension.h | 4 +-
.../expected/test_extensions.out | 50 +++++++
src/test/modules/test_extensions/meson.build | 4 +
.../test_extensions/sql/test_extensions.sql | 27 ++++
.../test_ext_owned_schema--1.0.sql | 2 +
.../test_ext_owned_schema.control | 5 +
...test_ext_owned_schema_relocatable--1.0.sql | 2 +
.../test_ext_owned_schema_relocatable.control | 4 +
13 files changed, 214 insertions(+), 40 deletions(-)
create mode 100644 src/test/modules/test_extensions/test_ext_owned_schema--1.0.sql
create mode 100644 src/test/modules/test_extensions/test_ext_owned_schema.control
create mode 100644 src/test/modules/test_extensions/test_ext_owned_schema_relocatable--1.0.sql
create mode 100644 src/test/modules/test_extensions/test_ext_owned_schema_relocatable.control
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 218940ee5ce..36dc692abef 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -809,6 +809,19 @@ RETURNS anycompatible AS ...
</listitem>
</varlistentry>
+ <varlistentry id="extend-extensions-files-owned-schema">
+ <term><varname>owned_schema</varname> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ An extension is <firstterm>owned_schema</firstterm> if it requires a
+ new dedicated schema for its objects. Such a requirement can make
+ security concerns related to <literal>search_path</literal> injection
+ much easier to reason about. The default is <literal>false</literal>,
+ i.e., the extension can be installed into an existing schema.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="extend-extensions-files-schema">
<term><varname>schema</varname> (<type>string</type>)</term>
<listitem>
diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml
index ca2b80d669c..240568f3166 100644
--- a/doc/src/sgml/ref/create_extension.sgml
+++ b/doc/src/sgml/ref/create_extension.sgml
@@ -102,7 +102,7 @@ CREATE EXTENSION [ IF NOT EXISTS ] <replaceable class="parameter">extension_name
<para>
The name of the schema in which to install the extension's
objects, given that the extension allows its contents to be
- relocated. The named schema must already exist.
+ relocated. The named schema must already exist if the extension's control file does not specify <literal>owned_schema</owned_schema>.
If not specified, and the extension's control file does not specify a
schema either, the current default object creation schema is used.
</para>
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 1643c8c69a0..a859ec2e043 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -83,6 +83,8 @@ typedef struct ExtensionControlFile
* MODULE_PATHNAME */
char *comment; /* comment, if any */
char *schema; /* target schema (allowed if !relocatable) */
+ bool owned_schema; /* if the schema should be owned by the
+ * extension */
bool relocatable; /* is ALTER EXTENSION SET SCHEMA supported? */
bool superuser; /* must be superuser to install? */
bool trusted; /* allow becoming superuser on the fly? */
@@ -561,6 +563,14 @@ parse_extension_control_file(ExtensionControlFile *control,
{
control->schema = pstrdup(item->value);
}
+ else if (strcmp(item->name, "owned_schema") == 0)
+ {
+ if (!parse_bool(item->value, &control->owned_schema))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("parameter \"%s\" requires a Boolean value",
+ item->name)));
+ }
else if (strcmp(item->name, "relocatable") == 0)
{
if (!parse_bool(item->value, &control->relocatable))
@@ -1547,8 +1557,11 @@ CreateExtensionInternal(char *extensionName,
*/
if (schemaName)
{
- /* If the user is giving us the schema name, it must exist already. */
- schemaOid = get_namespace_oid(schemaName, false);
+ /*
+ * If the user is giving us the schema name, it must exist already if
+ * the extension does not want to own the schema
+ */
+ schemaOid = get_namespace_oid(schemaName, control->owned_schema);
}
if (control->schema != NULL)
@@ -1570,7 +1583,10 @@ CreateExtensionInternal(char *extensionName,
/* Always use the schema from control file for current extension. */
schemaName = control->schema;
+ }
+ if (schemaName)
+ {
/* Find or create the schema in case it does not exist. */
schemaOid = get_namespace_oid(schemaName, true);
@@ -1591,8 +1607,22 @@ CreateExtensionInternal(char *extensionName,
*/
schemaOid = get_namespace_oid(schemaName, false);
}
+ else if (control->owned_schema)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_SCHEMA),
+ errmsg("schema \"%s\" already exists",
+ schemaName)));
+ }
+
}
- else if (!OidIsValid(schemaOid))
+ else if (control->owned_schema)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_SCHEMA),
+ errmsg("no schema has been selected to create in")));
+ }
+ else
{
/*
* Neither user nor author of the extension specified schema; use the
@@ -1659,6 +1689,7 @@ CreateExtensionInternal(char *extensionName,
*/
address = InsertExtensionTuple(control->name, extowner,
schemaOid, control->relocatable,
+ control->owned_schema,
versionName,
PointerGetDatum(NULL),
PointerGetDatum(NULL),
@@ -1671,6 +1702,16 @@ CreateExtensionInternal(char *extensionName,
if (control->comment != NULL)
CreateComments(extensionOid, ExtensionRelationId, 0, control->comment);
+ if (control->owned_schema)
+ {
+ ObjectAddress schemaAddress = {
+ .classId = NamespaceRelationId,
+ .objectId = schemaOid,
+ };
+
+ recordDependencyOn(&schemaAddress, &address, DEPENDENCY_EXTENSION);
+ }
+
/*
* Execute the installation script file
*/
@@ -1864,7 +1905,8 @@ CreateExtension(ParseState *pstate, CreateExtensionStmt *stmt)
*/
ObjectAddress
InsertExtensionTuple(const char *extName, Oid extOwner,
- Oid schemaOid, bool relocatable, const char *extVersion,
+ Oid schemaOid, bool relocatable, bool ownedSchema,
+ const char *extVersion,
Datum extConfig, Datum extCondition,
List *requiredExtensions)
{
@@ -1894,6 +1936,7 @@ InsertExtensionTuple(const char *extName, Oid extOwner,
values[Anum_pg_extension_extowner - 1] = ObjectIdGetDatum(extOwner);
values[Anum_pg_extension_extnamespace - 1] = ObjectIdGetDatum(schemaOid);
values[Anum_pg_extension_extrelocatable - 1] = BoolGetDatum(relocatable);
+ values[Anum_pg_extension_extownedschema - 1] = BoolGetDatum(ownedSchema);
values[Anum_pg_extension_extversion - 1] = CStringGetTextDatum(extVersion);
if (extConfig == PointerGetDatum(NULL))
@@ -2788,8 +2831,6 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
extensionOid = get_extension_oid(extensionName, false);
- nspOid = LookupCreationNamespace(newschema);
-
/*
* Permission check: must own extension. Note that we don't bother to
* check ownership of the individual member objects ...
@@ -2798,22 +2839,6 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_EXTENSION,
extensionName);
- /* Permission check: must have creation rights in target namespace */
- aclresult = object_aclcheck(NamespaceRelationId, nspOid, GetUserId(), ACL_CREATE);
- if (aclresult != ACLCHECK_OK)
- aclcheck_error(aclresult, OBJECT_SCHEMA, newschema);
-
- /*
- * If the schema is currently a member of the extension, disallow moving
- * the extension into the schema. That would create a dependency loop.
- */
- if (getExtensionOfObject(NamespaceRelationId, nspOid) == extensionOid)
- ereport(ERROR,
- (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
- errmsg("cannot move extension \"%s\" into schema \"%s\" "
- "because the extension contains the schema",
- extensionName, newschema)));
-
/* Locate the pg_extension tuple */
extRel = table_open(ExtensionRelationId, RowExclusiveLock);
@@ -2837,14 +2862,36 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
systable_endscan(extScan);
- /*
- * If the extension is already in the target schema, just silently do
- * nothing.
- */
- if (extForm->extnamespace == nspOid)
+ if (!extForm->extownedschema)
{
- table_close(extRel, RowExclusiveLock);
- return InvalidObjectAddress;
+ nspOid = LookupCreationNamespace(newschema);
+
+ /* Permission check: must have creation rights in target namespace */
+ aclresult = object_aclcheck(NamespaceRelationId, nspOid, GetUserId(), ACL_CREATE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_SCHEMA, newschema);
+
+ /*
+ * If the schema is currently a member of the extension, disallow
+ * moving the extension into the schema. That would create a
+ * dependency loop.
+ */
+ if (getExtensionOfObject(NamespaceRelationId, nspOid) == extensionOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot move extension \"%s\" into schema \"%s\" "
+ "because the extension contains the schema",
+ extensionName, newschema)));
+
+ /*
+ * If the extension is already in the target schema, just silently do
+ * nothing.
+ */
+ if (extForm->extnamespace == nspOid)
+ {
+ table_close(extRel, RowExclusiveLock);
+ return InvalidObjectAddress;
+ }
}
/* Check extension is supposed to be relocatable */
@@ -2917,6 +2964,13 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
}
}
+ /*
+ * We don't actually have to move any objects anything for owned
+ * schemas, because we simply rename the schema.
+ */
+ if (extForm->extownedschema)
+ continue;
+
/*
* Otherwise, ignore non-membership dependencies. (Currently, the
* only other case we could see here is a normal dependency from
@@ -2960,18 +3014,27 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
relation_close(depRel, AccessShareLock);
- /* Now adjust pg_extension.extnamespace */
- extForm->extnamespace = nspOid;
+ if (extForm->extownedschema)
+ {
+ RenameSchema(get_namespace_name(oldNspOid), newschema);
+ table_close(extRel, RowExclusiveLock);
+ }
+ else
+ {
- CatalogTupleUpdate(extRel, &extTup->t_self, extTup);
+ /* Now adjust pg_extension.extnamespace */
+ extForm->extnamespace = nspOid;
- table_close(extRel, RowExclusiveLock);
+ CatalogTupleUpdate(extRel, &extTup->t_self, extTup);
- /* update dependency to point to the new schema */
- if (changeDependencyFor(ExtensionRelationId, extensionOid,
- NamespaceRelationId, oldNspOid, nspOid) != 1)
- elog(ERROR, "could not change schema dependency for extension %s",
- NameStr(extForm->extname));
+ table_close(extRel, RowExclusiveLock);
+
+ /* update dependency to point to the new schema */
+ if (changeDependencyFor(ExtensionRelationId, extensionOid,
+ NamespaceRelationId, oldNspOid, nspOid) != 1)
+ elog(ERROR, "could not change schema dependency for extension %s",
+ NameStr(extForm->extname));
+ }
InvokeObjectPostAlterHook(ExtensionRelationId, extensionOid, 0);
diff --git a/src/backend/utils/adt/pg_upgrade_support.c b/src/backend/utils/adt/pg_upgrade_support.c
index c54b08fe180..ccd91d4c818 100644
--- a/src/backend/utils/adt/pg_upgrade_support.c
+++ b/src/backend/utils/adt/pg_upgrade_support.c
@@ -238,6 +238,7 @@ binary_upgrade_create_empty_extension(PG_FUNCTION_ARGS)
GetUserId(),
get_namespace_oid(text_to_cstring(schemaName), false),
relocatable,
+ false, /* TODO: Maybe change this??? */
text_to_cstring(extVersion),
extConfig,
extCondition,
diff --git a/src/include/catalog/pg_extension.h b/src/include/catalog/pg_extension.h
index cdfacc09303..ab20fff88ea 100644
--- a/src/include/catalog/pg_extension.h
+++ b/src/include/catalog/pg_extension.h
@@ -34,6 +34,7 @@ CATALOG(pg_extension,3079,ExtensionRelationId)
Oid extnamespace BKI_LOOKUP(pg_namespace); /* namespace of
* contained objects */
bool extrelocatable; /* if true, allow ALTER EXTENSION SET SCHEMA */
+ bool extownedschema; /* if true, schema is owned by extension */
#ifdef CATALOG_VARLEN /* variable-length fields start here */
/* extversion may never be null, but the others can be. */
diff --git a/src/include/commands/extension.h b/src/include/commands/extension.h
index c6f3f867eb7..8e7fa574032 100644
--- a/src/include/commands/extension.h
+++ b/src/include/commands/extension.h
@@ -36,7 +36,9 @@ extern ObjectAddress CreateExtension(ParseState *pstate, CreateExtensionStmt *st
extern void RemoveExtensionById(Oid extId);
extern ObjectAddress InsertExtensionTuple(const char *extName, Oid extOwner,
- Oid schemaOid, bool relocatable, const char *extVersion,
+ Oid schemaOid, bool relocatable,
+ bool ownedSchema,
+ const char *extVersion,
Datum extConfig, Datum extCondition,
List *requiredExtensions);
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index f357cc21aaa..c0a2b7b315e 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -626,3 +626,53 @@ SELECT test_s_dep.dep_req2();
DROP EXTENSION test_ext_req_schema1 CASCADE;
NOTICE: drop cascades to extension test_ext_req_schema2
+--
+-- Test owned schema extensions
+--
+CREATE SCHEMA test_ext_owned_schema;
+-- Fails for an already existing schema to be provided
+CREATE EXTENSION test_ext_owned_schema SCHEMA test_ext_owned_schema;
+ERROR: schema "test_ext_owned_schema" already exists
+-- Fails because a different schema is set in control file
+CREATE EXTENSION test_ext_owned_schema SCHEMA test_schema;
+ERROR: extension "test_ext_owned_schema" must be installed in schema "test_ext_owned_schema"
+DROP SCHEMA test_ext_owned_schema;
+CREATE EXTENSION test_ext_owned_schema;
+\dx+ test_ext_owned_schema;
+Objects in extension "test_ext_owned_schema"
+ Object description
+-----------------------------------------
+ function test_ext_owned_schema.owned1()
+ schema test_ext_owned_schema
+(2 rows)
+
+DROP EXTENSION test_ext_owned_schema;
+CREATE SCHEMA already_existing;
+-- Fails for an already existing schema to be provided
+CREATE EXTENSION test_ext_owned_schema_relocatable SCHEMA already_existing;
+ERROR: schema "already_existing" already exists
+-- Fails because no schema is set in control file
+CREATE EXTENSION test_ext_owned_schema_relocatable;
+ERROR: no schema has been selected to create in
+CREATE EXTENSION test_ext_owned_schema_relocatable SCHEMA test_schema;
+\dx+ test_ext_owned_schema_relocatable
+Objects in extension "test_ext_owned_schema_relocatable"
+ Object description
+-------------------------------
+ function test_schema.owned2()
+ schema test_schema
+(2 rows)
+
+-- Fails because schema already exists
+ALTER EXTENSION test_ext_owned_schema_relocatable SET SCHEMA already_existing;
+ERROR: schema "already_existing" already exists
+ALTER EXTENSION test_ext_owned_schema_relocatable SET SCHEMA some_other_name;
+\dx+ test_ext_owned_schema_relocatable
+Objects in extension "test_ext_owned_schema_relocatable"
+ Object description
+-----------------------------------
+ function some_other_name.owned2()
+ schema some_other_name
+(2 rows)
+
+DROP EXTENSION test_ext_owned_schema_relocatable;
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index c5f3424da51..52e8841480b 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -42,6 +42,10 @@ test_install_data += files(
'test_ext_req_schema3.control',
'test_ext_set_schema--1.0.sql',
'test_ext_set_schema.control',
+ 'test_ext_owned_schema--1.0.sql',
+ 'test_ext_owned_schema.control',
+ 'test_ext_owned_schema_relocatable--1.0.sql',
+ 'test_ext_owned_schema_relocatable.control',
)
tests += {
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 642c82ff5d3..136967db395 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -299,3 +299,30 @@ ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2; -- now ok
SELECT test_s_dep2.dep_req1();
SELECT test_s_dep.dep_req2();
DROP EXTENSION test_ext_req_schema1 CASCADE;
+
+--
+-- Test owned schema extensions
+--
+
+CREATE SCHEMA test_ext_owned_schema;
+-- Fails for an already existing schema to be provided
+CREATE EXTENSION test_ext_owned_schema SCHEMA test_ext_owned_schema;
+-- Fails because a different schema is set in control file
+CREATE EXTENSION test_ext_owned_schema SCHEMA test_schema;
+DROP SCHEMA test_ext_owned_schema;
+CREATE EXTENSION test_ext_owned_schema;
+\dx+ test_ext_owned_schema;
+DROP EXTENSION test_ext_owned_schema;
+
+CREATE SCHEMA already_existing;
+-- Fails for an already existing schema to be provided
+CREATE EXTENSION test_ext_owned_schema_relocatable SCHEMA already_existing;
+-- Fails because no schema is set in control file
+CREATE EXTENSION test_ext_owned_schema_relocatable;
+CREATE EXTENSION test_ext_owned_schema_relocatable SCHEMA test_schema;
+\dx+ test_ext_owned_schema_relocatable
+-- Fails because schema already exists
+ALTER EXTENSION test_ext_owned_schema_relocatable SET SCHEMA already_existing;
+ALTER EXTENSION test_ext_owned_schema_relocatable SET SCHEMA some_other_name;
+\dx+ test_ext_owned_schema_relocatable
+DROP EXTENSION test_ext_owned_schema_relocatable;
diff --git a/src/test/modules/test_extensions/test_ext_owned_schema--1.0.sql b/src/test/modules/test_extensions/test_ext_owned_schema--1.0.sql
new file mode 100644
index 00000000000..672ab8e607f
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_owned_schema--1.0.sql
@@ -0,0 +1,2 @@
+CREATE FUNCTION owned1() RETURNS text
+LANGUAGE SQL AS $$ SELECT 1 $$;
diff --git a/src/test/modules/test_extensions/test_ext_owned_schema.control b/src/test/modules/test_extensions/test_ext_owned_schema.control
new file mode 100644
index 00000000000..531c38daefd
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_owned_schema.control
@@ -0,0 +1,5 @@
+comment = 'Test extension with an owned schema'
+default_version = '1.0'
+relocatable = false
+schema = test_ext_owned_schema
+owned_schema = true
diff --git a/src/test/modules/test_extensions/test_ext_owned_schema_relocatable--1.0.sql b/src/test/modules/test_extensions/test_ext_owned_schema_relocatable--1.0.sql
new file mode 100644
index 00000000000..bfccaf4af82
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_owned_schema_relocatable--1.0.sql
@@ -0,0 +1,2 @@
+CREATE FUNCTION owned2() RETURNS text
+LANGUAGE SQL AS $$ SELECT 1 $$;
diff --git a/src/test/modules/test_extensions/test_ext_owned_schema_relocatable.control b/src/test/modules/test_extensions/test_ext_owned_schema_relocatable.control
new file mode 100644
index 00000000000..3cda1e12341
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_owned_schema_relocatable.control
@@ -0,0 +1,4 @@
+comment = 'Test extension with an owned schema'
+default_version = '1.0'
+relocatable = true
+owned_schema = true
base-commit: 8fea1bd5411b793697a4c9087c403887e050c4ac
--
2.34.1
