Re: Proposal: template-ify (binary) extensions

On 07/17/2013 08:52 PM, Dimitri Fontaine wrote:
> 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.

On Debian, that should be well possible. Certainly installing Postgres
9.1 w/ postgis-1.5 in parallel to Postgres 9.2 w/ postgis-2.0 is. I
designed it to be.

On distributions that do not allow parallel installation of multiple
Postges major versions, it's certainly not the extension's fault.

I think I'm misunderstanding the problem statement, here.

>   - 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.

Agreed, that's an area where Postgres could do better. I'd argue this
should be possible without relaxing the security guarantees provided,
though. Because there likely are people wanting both.

Can CREATE EXTENSION check if the standbys have the extension installed?
And refuse creation, if they don't?

>   - 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.

I'm sure you are aware that even without this clear separation of roles,
the guarantee means we provide an additional level of security against
attackers.

>     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.

I'd even think it's a minority who actually uses the guarantee we're
talking about. Mostly because of the many and wide spread untrusted PLs
(which undermine the guarantee). And thus even before the rise of the cloud.

None the less, the "safe by default" has served us well, I think.

> 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 usually associate "cloud" with (increased) "security concerns", but that's an entirely different story. ]

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

It's extensions that undermine the guarantee, at the moment. But yeah,
it depends a lot on what kind of "relaxed behavior" you have in mind.

>> Andres made two contrib-free suggestions: with COPY TO BINARY, you get a
> 
> Well, what about using lo_import()?

That only reads from the file-system. You probably meant lo_export(),
which is writing. Although not on the server's, but only on the (libpq)
client's file-system. No threat to the server.

> 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.

If the sysadmin wants to disallow arbitrary execution of native code to
postgres (the process), any kind of embedded compiler likely is equally
unwelcome.

Regards

Markus Wanner