Re: Extension Templates S03E11 - Mailing list pgsql-hackers

Stephen Frost <sfrost@snowman.net> writes:
> These wouldn't be PG "extensions" really though, which it seems folks
> are pretty hung up on.  There would also be no support for binary or
> untrusted components, which is a bit frustrating, as you'd like to be
> able to support those if you're a superuser.  Trying to build both into
> one "extension template" structure, or what-have-you, seems to be
> causing us to fail to make any progress on either use-case.

So, plenty of points to address separately here:
 - why do we want extensions to manage PL user code?
 - what about managing extensions with modules from the protocol   directly, if we have Extension Templates ?
 - is it possible to open the feature to non-superusers ?

Let's have at it.

# Why do we want extensions to manage PL user code?

Extensions allows to manage any number of database objects as a single
entity with a very clear and simple enough life cycle management
tooling:
 create extension … alter extension … update to … drop extension … [cascade]
 \dx \dx+ pg_available_extensions() pg_available_extension_versions()

Plus, you can handle inter-extension dependencies, albeit currently in a
very limited way, but that's still something.

Now that we have those tools, I want to be able to use them when dealing
with a bunch of PL code because I didn't find a better option in
PostgreSQL for dealing with such code.

It could well be that the “package” thing that I couldn't find tonight
in the SQL standard documents are better suited to manage user code, but
after having had to write management code and use extensions, I know I
want to deal with PL user code the same way I deal with extensions.

Of course, it's already possible to do so, packaging the user code into
a set of files that you then need to install at the right place on the
filesystem (a place owned by root in most cases), so I already did have
the pleasure to use extensions for user code. And I also had to write
all the packaging myself, then ship it to several nodes, etc.

I think that opening Extensions to be easy to use without requiring root
level access to the database server's filesystem would be a great idea,
even if it would take, I don't know, say, 3 years of development to get
there.

# Extension Templates and Binary Modules

Then as soon as we are able to CREATE EXTENSION mystuff; without ever
pre-installing files on the file system as root, then we would like to
be able to do just that even with binary modules.

The current documentation of the dynamic_library_path GUC makes me
believe it's possible already without patching the backend. We will need
to patch the extension's CREATE FUNCTION statements not to hardcode
$libdir in there of course. That could be done automatically by some
separate distribution packaging software.

Some cases would still need more work before being supported within that
frame:
 - Hot Standby - Modules that depend on other libs being installed.

# Extension Templates and Superusers

The problem found here is that if a non privileged user installs an
extension template named “pgcyrpto” then the superuser installs what he
believes is the extension “pgcrypto”, the malicious unprivileged user
now is running his own code (extension install script) as a superuser.

The current patch will prioritize file-based templates when an extension
is provided both from the file system and the catalogs, so for the
situation to happen you need to have forgotten to install the proper
system package. Still.

I've been asked (if memory serves) to then limit the Extension Templates
feature to superuser, which is a very bad thing™.

What I now think we should do is only grant superusers the privileges to
install an extension from a template they own or is owned by another
superuser.

The general rule being that only the role who did install the template
would be allowed to install an extension with it, so that you can't have
a privilege escalation happening, IIUC.

As a superuser, use SET ROLE first.

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