Extensions and 9.2

Hi,

I've sent a first patch to improve extensions for 9.2, and intend on
sending a few more which I'll briefly present here. The point of this
email is to figure out how to branch the development, as all the patch
are going to conflict somehow (change the same parts of the code).

Either I develop them separately, with separate branches derived from
the master one, or I develop them as a stack, one on top of the other.
The difference is my ability to provide a patch for one of the features
that can be applied to master directly compared to how much time I have
to spend cooking one patch or the other (merge conflicts, etc).

If we are going to try and commit all of those for 9.2, then I can stack
them all atop of each other and have an easier development time. Here's
the list:

- extension features (requires / provides)

as already sent, allows fine grained dependency management

- SQL only extensions

the goal here is to be able to install an SQL only extension without
having to be granted OS shell access on the PostgreSQL server, or
other arrangement allowing you to ship files (.control, .sql) in a
place where usually only “root” has write access.

meaning that the control file property that says “superuser = false”
can be true for the distribution of extension too.

- extension modules

the goal here is to be able to list all the modules that are loaded
by an extension — the install script will install all functions and
will be loading all related .so, it's easy enough to keep track of
them at creating_extension time and “register” that module list.

that helps with systems auditing when you're called to understand a
crash after the fact. Of course even better would be to only allow
loading modules that as part of extensions, and to be able to list
implemented hooks (and which extension is implementing which hook),
but that would/could be some follow-up patches.

- extension whitelisting

the goal here is to grant non superuser to install extensions from a
restricted list, introducing a specific “sudo” like behavior when the
extension is implemented in C or some other non trusted language.

that could be easily done with the current command trigger patch and
a trigger procedure that is security definer, and doesn't need the
parsetree at all, but that could easily drift away from 9.2, so maybe
a specific implementation would be better here

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

On Tue, Dec 20, 2011 at 10:01 AM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Either I develop them separately, with separate branches derived from
> the master one, or I develop them as a stack, one on top of the other.
> The difference is my ability to provide a patch for one of the features
> that can be applied to master directly compared to how much time I have
> to spend cooking one patch or the other (merge conflicts, etc).

Personally, I hate patches that do more than one thing. For me, the
time required to verify a patch goes as about O(n^2) in its size.
Furthermore, putting more than one feature into a patch means that it
has to be rejected (or revised by the committer) if any one of those
features looks half-baked. I can't speak to the preferences of any
other contributor.

>  - extension whitelisting
>
>   the goal here is to grant non superuser to install extensions from a
>   restricted list, introducing a specific “sudo” like behavior when the
>   extension is implemented in C or some other non trusted language.

Who creates this list?

If the answer is "the superuser", then why not just let them create a
suitable SECURITY DEFINER function if they are so inclined, wrapping
CREATE EXTENSION? We've occasionally had requests for "DDL
permissions" so that you could, for example, grant a given user the
right to ANALYZE a table (but nothing else). But it's not entirely
clear to me that it's worth doing that. Assuming the command in
question can be stuffed inside a function, the most you're gaining is
a little notational convenience, and I'm not convinced it's worth
building the amount of infrastructure that this will require for that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Personally, I hate patches that do more than one thing. For me, the
> time required to verify a patch goes as about O(n^2) in its size.

That's exactly why I'm opening that discussion. The main difference
between the approaches I can take is the time it takes to export each
patch against the merge conflicts to solve at each minor revision.

>>  - extension whitelisting
>
> Who creates this list?
>
> If the answer is "the superuser", then why not just let them create a

Yes.

> suitable SECURITY DEFINER function if they are so inclined, wrapping
> CREATE EXTENSION? We've occasionally had requests for "DDL

The goal is that users don't know about the whitelisting in most cases,
they just do CREATE EXTENSION and don't have to care about it, which
means it works the same on the laptop and the production environment.

That's what you easily can get with the command trigger patch.
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Wed, Dec 21, 2011 at 5:46 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:>
> Assuming the command in
> question can be stuffed inside a function, the most you're gaining is
> a little notational convenience

I can answer that one (why a full-blown mechanism for a notational convenience).

It has occurred to me to use this mechanism to support extensions, but
I find the prospect of having to teach people special operators to
understand how to use the standard extension feature an unstomachable
prospect. The single biggest problem is that pg_restore will not know
to call this function rather than run CREATE EXTENSION, and then two
databases, prepared exactly the same cannot be pg_dump-ed and restored
in a reasonable way. If there's a definable whitelist, then this
vital functionality will work.

There are other sicknesses imposed if one has to hack up how to
delegate extension creation to non-superusers:

* The postgres manual, wiki, and ecosystem of recipes on the web and
internet at large basically are not going to work without
modification. Death by a thousand cuts.

* "\df" in psql displays some new operators that you aren't familiar
with. Also, putting aside your pg_dump/pg_restore generated SQL will
not work, they will look funny. This is an eyesore.

* If one tells someone else "yeah, the system supports extensions",
they're going to type CREATE EXTENSION. And then it's not going to
work, and then they're either going to give up, yak shave for a few
hours figuring out what they were "supposed" to do for their provider
or organization, or maybe worst of all hack their way around
functionality the extension could have provided in a cleaner way had
it just worked nice and tidy to begin with.

I find this functionality basically vital because it greatly decreases
the friction between people, organizations, and software in the domain
of deploying, reasoning, and communicating about the installation and
status of Postgres extensions in a database.

--
fdr

On Fri, Dec 23, 2011 at 5:45 AM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
> On Wed, Dec 21, 2011 at 5:46 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:>
>> Assuming the command in
>> question can be stuffed inside a function, the most you're gaining is
>> a little notational convenience
>
> I can answer that one (why a full-blown mechanism for a notational convenience).
>
> It has occurred to me to use this mechanism to support extensions, but
> I find the prospect of having to teach people special operators to
> understand how to use the standard extension feature an unstomachable
> prospect.  The single biggest problem is that pg_restore will not know
> to call this function rather than run CREATE EXTENSION, and then two
> databases, prepared exactly the same cannot be pg_dump-ed and restored
> in a reasonable way.  If there's a definable whitelist, then this
> vital functionality will work.
>
> There are other sicknesses imposed if one has to hack up how to
> delegate extension creation to non-superusers:
>
> * The postgres manual, wiki, and ecosystem of recipes on the web and
> internet at large basically are not going to work without
> modification.  Death by a thousand cuts.
>
> * "\df" in psql displays some new operators that you aren't familiar
> with.  Also, putting aside your pg_dump/pg_restore generated SQL will
> not work, they will look funny.  This is an eyesore.
>
> * If one tells someone else "yeah, the system supports extensions",
> they're going to type CREATE EXTENSION.  And then it's not going to
> work, and then they're either going to give up, yak shave for a few
> hours figuring out what they were "supposed" to do for their provider
> or organization, or maybe worst of all hack their way around
> functionality the extension could have provided in a cleaner way had
> it just worked nice and tidy to begin with.
>
> I find this functionality basically vital because it greatly decreases
> the friction between people, organizations, and software in the domain
> of deploying, reasoning, and communicating about the installation and
> status of Postgres extensions in a database.

OK, I'll buy that. I think we need to consider the design of the
mechanism carefully, though, or we'll end up with something messy and
inconvenient.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company