Re: Proposal: template-ify (binary) extensions

Markus Wanner <markus(at)bluegap(dot)ch> writes:
>>>> - per-installation (not even per-cluster) DSO availability
>
> Not sure what the issue is, here, but I agree that should be possible.

For any extension where the new package version is shipping the same .so
file name, you can only have one module on the whole system for a given
PostgreSQL major version. The extensions are per-database, the DSO
modules are per major-version (not even per-cluster).

That's the trade-off we currently need to make to be able to ship with
the current security protections we're talking about.

> Requiring the sysadmin to install the extensions there, too, seems
> justified to me. Sounds like good advice, anyways.

Yes, installing the same extensions on any standby as the ones installed
on the primary should be *required*. My idea to enforce that would be
with using Event Triggers on the standby, but how to be able to run them
when when the standby replay currently isn't opening a session is quite
a problem.

The other way to address that is of course PL/C.

> lo_import() won't write a file for LOAD to load. An untrusted PL (or any
> other extension allowing the superuser to do that) is currently required
> to do that.

Ok, here's the full worked out example:

~# select lo_import('/path/to/local/on/the/client/adminpack.so');
lo_import
-----------
82153
(1 row)

~# select lo_export(82153, '/tmp/foo.so');
lo_export
-----------
1
(1 row)

~# LOAD '/tmp/foo.so';
LOAD

~# CREATE FUNCTION pg_catalog.pg_file_write(text, text, bool)
RETURNS bigint AS '/tmp/foo.so', 'pg_file_write'
LANGUAGE C VOLATILE STRICT;
CREATE FUNCTION

> Or to put it another way: Trusted PLs exist for a good reason. And some
> people just value security a lot and want that separation of roles.

Yeah, security is important, but it needs to be addressing real threats
to have any value whatsoever. We'not talking about adding new big holes
as extensions containing modules are using LANGUAGE C in their script
and thus are already restricted to superuser. That part would not
change.

> As someone mentioned previously, $PGDATA may well be mounted noexec, so
> that seems to be a bad choice.

I don't understand. You want good default security policy in place, and
you're saying that using PGDATA allows for a really easy policy to be
implemented by people who don't want the feature. It seems to me to be
exactly what you want, why would that be a bad choice then?

>> - upgrading an extension at the OS level
>>
>> Once you've done that, any new backend will load the newer module
>> (DSO file), so you have to be real quick if installing an hot fix in
>> production and the SQL definition must be changed to match the new
>> module version…
>
> I agree, that's a problem.
>
> Alternatively, we could solve that problem the other way around: Rather
> than template-ify the DSO, we could instead turn the objects created by
> the SQL scripts into something that's more linked to the script.
> Something that would reload as soon as the file on disk changes.

I think that's the wrong way to do it, because you generally want more
control over those two steps (preparing the template then instanciating
it) and you're proposing to completely lose the control and have them be
a single step instead.

Use case: shipping a new plproxy bunch of functions to 256 nodes. You
want to push the templates everywhere then just do the extension update
as fast as possible so that it appears to be about 'in sync'.

Pro-tip: you can use a plproxy function that runs the alter extension
update step using RUN ON ALL facility.

> (Note how this would make out-of-line extensions a lot closer to the
> in-line variant your recent patch adds? With the dependency between
> "template" and "instantiation"?)

Those dependencies are almost gone now except for being able to deal
with DROP TEMPLATE FOR EXTENSION and guaranteeing we can actually
pg_restore our pg_dump script.

I've been changing the implementation to be what you proposed because I
think it's making more sense (thanks!), and regression tests are
reflecting that. My github branch is up-to-date with the last changes
and I'm soon to send an updated patch that will be really as ready for
commit as possible without a commiter review.

> An attacker having access to a libpq connection with superuser rights
> cannot currently run arbitrary native code. He can try a DOS by

I think I showed a simple way to do that above in this very email.

> Well, an extension can certainly perform something akin to pg_dlopen().
> And thus load pretty much any code it wants. However, I don't think an
> extension can hook into CREATE EXTENSION, yet.

When you call a function, its MODULE_PATHNAME is getting loaded by the
backend. You would need to ship a dumb DSO module file so that this
works and arrange for the real DSO module file to be loaded via a hook
or an event trigger or something else (e.g. local_preload_libraries).

I don't see a way to be able to do that without changing the extension's
binary package, either replacing MODULE_PATHNAME in the sql script or in
the control file, or changing the DSO file itself and shipping the
genuine one in a new place.

In short, doing something different than the backend is currently doing
with respect to DSO modules looks like a core patch to me, and requires
a community agreement on the way to address the stated problems.

As it's all about security, reaching to an agreement strikes me as the
right thing to do.

> I agree. However, this also means that any kind of solution it offers is
> not a good one for the security conscious sysadmin.

Maybe we'll be able to find a way to offer PL/C in a "trusted" way.

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