Thread: 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