BUG #17598: EXTENSION can no longer create it's own schema! (Create Schema IF NOT EXISTS XXX)
The following bug has been logged on the website:
Bug reference: 17598
Logged by: Kirk Wolak
Email address: wolakk@gmail.com
PostgreSQL version: 14.5
Operating system: Ubuntu 14.5-0ubuntu0.22.04.1
Description:
I ran into this with Login_hook. I've simplified it with my own example.
This worked in 14.3... (the script runs fine in 14.3), we jumped straight to
14.5
Effectively if FEELS like the check for an schema/extension
"owning/belonging to" a schema/extension is failing.
In this case, the schema DOES NOT EXIST. And the create if not exists
FAILS! (Which feels like an edge case.
You cannot tell who owns the object or who it belongs to, if it does not
exist).
// WORK AROUND: Create the schema manually, and COMMENT out the offending
code (Ouch) [in the extension!]
/* Actual Session: This is what I get when I try to install this simple
extension */
ncblah=# create extension simple_extension;
ERROR: schema simple_extension is not a member of extension
"simple_extension"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one
that it already owns.
ncblah=# create extension simple_extension schema simple_extension;
ERROR: schema "simple_extension" does not exist
-- ATTEMPT TO Create the Schema First (which SHOULD PROBABLY TRIGGER this
message)...
ncblah=# create schema simple_extension;
CREATE SCHEMA
ncblah=# create extension simple_extension schema simple_extension;
ERROR: schema simple_extension is not a member of extension
"simple_extension"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one that it already owns.
ncblah=#
Thanks in Advance...
-- Simple enough code to reproduce:
/* simple_extension.control */
# simple_extension to show problem creating schema!
comment = 'simple_extension - creates a schema (which fails) and tries to
create a version function'
encoding = 'UTF-8'
default_version = '1.0'
superuser = true
relocatable = false
schema = 'simple_extension'
/* simple_extension--1.0.sql */
create schema if not exists simple_extension;
comment on schema simple_extension is 'Belongs to the simple_extension
extension';
grant usage on schema simple_extension to public;
create or replace function simple_extension.get_simple_extension_version()
returns text
language sql return '1.0 -- But hardly Provable!';
grant execute on function simple_extension.get_simple_extension_version() to
public;
PG Bug reporting form <noreply@postgresql.org> writes:
Effectively if FEELS like the check for an schema/extension
"owning/belonging to" a schema/extension is failing.
In this case, the schema DOES NOT EXIST. And the create if not exists
FAILS! (Which feels like an edge case.
This is an intentional change to close a security hole: it is unsafe for
an extension script to use CREATE IF NOT EXISTS this way. (What if
a hostile attacker created the schema? Now you are depending on a
schema that the attacker has ownership privileges on.) Drop the
IF NOT EXISTS clause, if you're expecting the extension to create the
schema.
regards, tom lane
Tom,
I understand the reason for the CVE, it was UNCLEAR that ALL "create
schema" requests within extensions could be affected.
So, following your advice, I commented out the "IF NOT EXISTS" part of the
create schema...
I get the following error: (As if the create extension is seeing that I
expect a certain schema, and it is creating it for me)
create extension simple_extension;
ERROR: schema "simple_extension" already exists
STATEMENT: create extension simple_extension;
STRANGE ENOUGH, if I completely comment out the CREATE SCHEMA. This
example works.
So, is it more accurate to say:
1) Define the schema in the .control file
2) Let the CREATE EXTENSION command create the schema [No extra syntax,
just create extension simple_extension]
Or am I still doing something wrong?
Finally, now I see another side-effect. *When I drop the extension, it
does not drop the schema!*
BUT it allows me to recreate the extension (with the schema present)
without errors.
Worse, I can drop the extension. Change the owner on the schema to someone
else. And recreate the extension.
Something seems off... OR I am clearly doing things incorrectly.
Thanks again.
Kirk Out!
On Mon, Aug 29, 2022 at 9:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
PG Bug reporting form <noreply@postgresql.org> writes:
Effectively if FEELS like the check for an schema/extension
"owning/belonging to" a schema/extension is failing.
In this case, the schema DOES NOT EXIST. And the create if not exists
FAILS! (Which feels like an edge case.This is an intentional change to close a security hole: it is unsafe for
an extension script to use CREATE IF NOT EXISTS this way. (What if
a hostile attacker created the schema? Now you are depending on a
schema that the attacker has ownership privileges on.) Drop the
IF NOT EXISTS clause, if you're expecting the extension to create the
schema.regards, tom lane
The convention on these lists is to inline or bottom-post with trimming of
excess reply quoting.
On Mon, Aug 29, 2022 at 9:00 AM Kirk Wolak <wolakk@gmail.com> wrote:
OR I am clearly doing things incorrectly.
You seem to be trying to hard-code the schema instead of letting the
extension creation infrastructure handle that for you by delegating to the
instance-specific reference @extschema@
https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION
https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE
Even if you want to force a fixed schema you need to do it in the way
described in the documentation.
David J.
On Mon, Aug 29, 2022 at 1:55 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
The convention on these lists is to inline or bottom-post with trimming of
excess reply quoting.
Thank you!
On Mon, Aug 29, 2022 at 9:00 AM Kirk Wolak <wolakk@gmail.com> wrote:
OR I am clearly doing things incorrectly.
You seem to be trying to hard-code the schema instead of letting the
extension creation infrastructure handle that for you by delegating to the
instance-specific reference @extschema@
TBH, I created a simple example that showed the problem.
[A Third Party Extension is what actually Broke]
Could I recommend this page:
https://www.postgresql.org/support/security/CVE-2022-2625/
Add these two references (below)??? (ie, because we updated how things
work, please review this to make sure you have it correct)
https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION
https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE
Even if you want to force a fixed schema you need to do it in the way
described in the documentation.David J.
Again, Thanks. I see it clearly now! Apologies!