Re: Quick Extensions Question - Mailing list pgsql-hackers

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> On the whole I'd rather spend time trying to fix the real problem, which
>> is allowing CREATE EXTENSION to non-superusers.  The general case of
>> that looks quite hard to me, but maybe we could get something that works
>> for the single case of an extension containing just a language.

> Then, what about a control file property to cover that?

>   pl_language = plpgsql

That doesn't fix the permissions problem, it's just a special-case
wart for PLs.

However, it does strike me that there is one simple case we could
support without a great deal of sweat.  Namely, what if we allow
non-superusers to create an extension if all the commands in the script
are ones they could execute anyway?  In particular, an extension
containing only CREATE LANGUAGE would work for exactly those users
who could execute CREATE LANGUAGE under the existing dispensations.
This might also make it less painful to use extensions that consist
purely of SQL (no underlying C functions).

This special case avoids two of the nastier problems that have been
bugging me with respect to the general case:

1. We don't have to worry about somehow kluging the permissions checks
for commands executed within the script, as we would have to do to
let a non-superuser create an extension that includes C functions for
instance.

2. We aren't opening a Pandora's box of security vulnerabilities, as
would certainly happen if extension scripts that effectively have
superuser privs were to be executed in an environment under the control
of a malicious non-superuser.

Offhand I don't see any security risks in this type of feature.
We'd be letting non-superusers examine the extension directory,
but we have enough safeguards in place already to be sure they
can't see the rest of the filesystem via the extension commands.

In the simplest form we could implement this by just removing the
superuser() check in CREATE EXTENSION.  But then people who tried
to load a superuser-only extension would get a permissions failure
on some random command within the extension, which might be thought
less than user-friendly.  Also it might be good to have a more
explicit marking of superuser-only extensions.  So I'm thinking it
might be best to invent a control file property along the lines of
superuser = false        -- default is true

which would presently do nothing except control whether to make a
superuser() permissions check before running the script.  (In future
it might do more, but only after a lot of careful thought.)

We'd also have to fix ALTER EXTENSION and DROP EXTENSION to check
for extension ownership instead of superuserness, but that would
be simple enough, since I already insisted on an extowner column ;-)

This looks like it would be at most a few hours' work to change,
and it would enable creation of extensions for the built-in languages
that can be loaded with the same permissions as before.  It would
not do anything towards allowing non-superusers to load languages that
aren't listed in pg_pltemplate, but it doesn't make things any worse
for non-core languages either: they can make extensions that are
superuser-loadable, which is the same permissions situation they are
in now.

Comments?
        regards, tom lane