Re: Proposal: template-ify (binary) extensions

Markus Wanner <markus(at)bluegap(dot)ch> writes:
> But okay, you're saying we *have* and *want* a guarantee that even a
> superuser cannot execute arbitrary native code via libpq (at least in
> default installs w/o extensions).

There are several problems confused into that sentence already. I think
the next step of this discussion should be about talking about the
problems we have and figuring out *if* we want to solve them, now that
we managed to explicitely say what we want as a project.

- per-installation (not even per-cluster) DSO availability

If you install PostGIS 1.5 on a system, then it's just impossible to
bring another cluster (of the same PostgreSQL major version), let
alone database, with PostGIS 2.x, even for migration assessment
purposes. The "By Design™" part is really hard to explain even to
security concious users.

- hot standby and modules (aka DSO)

As soon as you use some functions in 'language C' you need to
carefully watch your external dependencies ahead of time. If you do
CREATE EXTENSION hstore;, create an hstore column and a GiST index
on it, then query the table on the standby… no luck. You would tell
me that it's easy enough to do and that it's part of the job, so see
next point.

- sysadmin vs dba, or PostgreSQL meets the Cloud

The current model of operations is intended for places where you
have separate roles: the sysadmin cares about the OS setup and will
provide with system packages (compiled extensions and the like), and
DBA are never root on the OS. They can CREATE EXTENSION and maybe
use the 'postgres' system account, but that's about it.

Given the current raise of the Cloud environements and the devops
teams, my understanding is that this model is no longer the only
one. Depending on who you talk to, in some circles it's not even a
relevant model anymore: user actions should not require the
intervention of a "sysadmin" before hand.

While I appreciate that many companies still want the old behavior
that used to be the only relevant model of operations, I think we
should also provide for the new one as it's pervasive enough now for
us to stop ignoring it with our "I know better" smiling face.

Now it should be possible to solve at least some of those items while
still keeping the restriction above, or with an opt-in mechanism to
enable the "works by default, but you have to solve the security
implications yourself" behaviour. A new GUC should do it, boolean,
defaults false:

runs_in_the_cloud_with_no_security_concerns = false

I don't think the relaxed behaviour we're talking about is currently
possible to develop as an extension, by the way.

> Andres made two contrib-free suggestions: with COPY TO BINARY, you get a

Well, what about using lo_import()?

>> Things aren't quite so bad if we write the bits to a file first and
>> then dynamically load the file. That way at least noexec or similar
>> can provide protection. But it still seems like a pretty dangerous
>> direction.
>
> I agree now. Thanks for elaborating.

Yes it's dangerous. It's also solving real world problems that I see no
other way to solve apart from bypassing the need to ever load a DSO
file, that is embedding a retargetable C compiler in the backend.

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