RE: Ability to reference other extensions by schema in extension scripts

>
> On Thu, Dec 15, 2022 at 08:04:22AM -0500, Regina Obe wrote:
> > > On Tue, Nov 22, 2022 at 11:24:19PM -0500, Regina Obe wrote:
> > >
> > > > If an extension is required by another extension and that required
> > > > extension schema is referenced in the extension scripts using the
> > > > @extschema:extensionname@ syntax, then ideally we should prevent
> > > > the required extension from being relocatable. This would prevent
> > > > a user from accidentally moving the required extension, thus
> > > > breaking the dependent extensions.
> > > >
> > > > I didn't add that feature cause I wasn't sure if it was
> > > > overstepping the bounds of what should be done, or if we leave it
> > > > up to the user to just know better.
> > >
> > > An alternative would be to forbid using @extschema:extensionname@ to
> > > reference relocatable extensions. DBA can toggle relocatability of
> > > an extension to allow it to be referenced.
> >
> > That would be hard to do in a DbaaS setup and not many users know they
> > can fiddle with extension control files.
> > Plus those would get overwritten with upgrades.
>
> Wouldn't this also be the case if you override relocatability ?
> Case:
>
> - Install fuzzystrmatch, marked as relocatable
> - Install ext2 depending on the former, which is them marked
> non-relocatable
> - Upgrade database -> fuzzystrmatch becomes relocatable again
> - Change fuzzystrmatch schema BREAKING ext2
>

Somewhat. It would be an issue if someone does

ALTER EXTENSION fuzzystrmatch UPDATE;

And

ALTER EXTENSION fuzzystrmatch SET SCHEMA a_different_schema;

Otherwise the relocatability of an already installed extension wouldn't
change even during upgrade. I haven't checked pg_upgrade, but I suspect it
wouldn't change there either.

It's my understanding that once an extension is installed, it's relocatable
status is recorded in the pg_extension table. So it doesn't matter at that
point what the control file says. However if someone does update the
extension, then yes it would look at the control file and make it updatable
again.

I just tested this fiddling with postgis extension and moving it and then
upgrading.

UPDATE pg_extension SET extrelocatable = true where extname = 'postgis';
ALTER EXTENSION postgis SET schema postgis;

ALTER EXTENSION postgis UPDATE;
e.g. if the above is already at latest version, get notice
NOTICE: version "3.3.2" of extension "postgis" is already installed
(and the extension is still relocatable)

-- if the extension can be upgraded
ALTER EXTENSION postgis UPDATE;

-- no longer relocatable (because postgis control file has relocatable =
false)

But honestly I don't expect this to be a huge issue, more of just an extra
safety block.
Not a bullet-proof safety block though.

> Allowing to relocate a dependency of other extensions using the
> @extschema@ syntax is very dangerous.
>
> I've seen that PostgreSQL itself doesn't even bother to replace
@extschema@
> IF the extension using it doesn't mark itself as non-relocatable. For
consistency
> this patch should basically refuse to expand @extschema:fuzzystrmatch@ if
> "fuzzystrmatch" extension is relocatable.
>
> Changing the current behaviour of PostgreSQL could be proposed but I don't
> think it's to be done in this thread ?
>
> So my suggestion is to start consistent (do not expand if referenced
extension
> is relocatable).
>
>
> --strk;

I don't agree. That would make this patch of not much use.
For example lets revisit my postgis_tiger_geocoder which is a good bulk of
the reason why I want this.

I use indexes that use postgis_tiger_geocoder functions that call
fuzzystrmatch which causes pg_restore to break on reload and other issues,
because I'm not explicitly referencing the function schema. With your
proposal now I got to demand the postgresql project to make fuzzystrmatch
not relocatable so I can use this feature. It is so rare for people to go
around moving the locations of their extensions once set, that I honestly
don't think
the ALTER EXTENSION .. UPDATE hole is a huge deal.

I'd be more annoyed having to beg an extension provider to mark their
extension as not relocatable so that I could explicitly reference the
location of their extensions.

And even then - think about it. I ask extension provider to make their
extension schema relocatable. They do, but some people are using a version
before they marked it as schema relocatable. So now if I change my code,
users can't install my extension, cause they are using a version before it
was schema relocatable and I'm using the new syntax.

What would be more bullet-proof is having an extra column in pg_extension or
adding an extra array element to pg_extension.extcondition[] that allows you
to say "Hey, don't allow this to be relocatable cause other extensions
depend on it that have explicitly referenced the schema."

Thanks,
Regina