Re: Extension Templates S03E11

Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> > Having a versioning notion (and whatever other meta data we, or an
>> > extension author, feels is useful) for what are otherwise simple containers
>> > (aka the schematic we already have..) makes sense and it would be great to
>> > provide support around that, but not this duplication of
>> > object definitions.
>>
>> I don't like duplication either, we've just been failing to find any
>> alternative with pg_restore support for the last 3 years.
>
> *That doesn't make this approach the right one*. If anything, I'm
> afraid we've ended up building ourselves a rube goldberg machine because
> of this constant struggle to fit a square peg into a round hole.

This duplication you're talking about only applies to CREATE EXTENSION.

I don't know of any ways to implement ALTER EXTENSION … UPDATE …
behaviour without a separate set of scripts to apply in a certain order
depending on the current and target versions of the extension.

If you know how to enable a DBA to update a set of objects in a database
only with information already found in the database, and in a way that
this information is actually *not* an SQL script, I'm all ears.

> That's basically what we already do with schemas today and hence is
> pretty darn close to what I'm proposing. Perhaps it'd be a way to
> simply version schemas themselves- heck, with that, we could even
> provide that oft-asked-for schema delta tool in-core by being able to
> deduce the differences between schema at version X and schema at
> version Y.

Given that at any moment you have a single version of the schema
installed, I don't know how you're supposed to be able to do that?

Maybe you mean by tracking the changes at update time? Well that at
least would be a good incentive to have Command String access in event
triggers, I guess.

>> That would work beautifully, and of course you would have to do that
>> again manually at pg_restore time after CREATE DATABASE and before
>> pg_restore, or you would need to change the fact that extensions objects
>> are not part of your pg_dump scripts, or you would have to name your new
>> thing something else than an extension.
>
> We would need a way to dump and restore this, of course.

Which is available in the current patch, of course.

> Having a management system for sets of objects is a *great* idea- and
> one which we already have through schemas. What we don't have is any
> kind of versioning system built-in or other metadata about it, nor do we
> have good tooling which leverages such a versioning or similar system.

Exactly.

How can we implement ALTER <OBJECT> … UPDATE TO <VERSION> without having
access to some SQL scripts?

The current patch offers a way to manage those scripts and apply them,
with the idea that the people managing the scripts (extension authors)
and the people applying them (DBAs) are not going to be the same people,
and that it's then possible to have to apply more than a single script
for a single UPDATE command.

> I really just don't see this as being either particularly useful nor
> feasible within a reasonable amount of effort. Shared libraries are
> really the perview of the OS packaging system. If you want to build
> some tool which is external to PG but helps facilitate the building and
> installing of shared libraries, but doesn't use the OS packaging system
> (and, instead, attempts to duplicate it) then go for it, but don't
> expect to ship or install that through the PG backend.

I'll give you that implementing Event Triggers just to be able to build
what you're talking about on top of it and out of core might not be
called “a reasonable amount of effort.”

>> The problem found here is that if a non privileged user installs an
>> extension template named “pgcyrpto” then the superuser installs what he
>> believes is the extension “pgcrypto”, the malicious unprivileged user
>> now is running his own code (extension install script) as a superuser.
>
> For my part, the problem here is this notion of extension templates in
> the PG catalog and this is just one symptom of how that's really not a
> good approach.

The only reason for that being the case is that you suppose that root on
the file system is more trustworthy as an entity than postgres on the
file system or any superuser in the PostgreSQL service.

As soon as you question that, then you might come to realise the only
difference in between file-system templates and catalog templates is our
ability to deal with the problem, rather than the problem itself.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support