Re: Proposal: template-ify (binary) extensions

Markus Wanner <markus@bluegap.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
majorversion), let   alone database, with PostGIS 2.x, even for migration assessment   purposes. The "By Design™" part
isreally 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
thestandby… 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
OSsetup and will   provide with system packages (compiled extensions and the like), and   DBA are never root on the OS.
Theycan 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
longerthe only   one. Depending on who you talk to, in some circles it's not even a   relevant model anymore: user
actionsshould 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
withour "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