Re: Quick Extensions Question - Mailing list pgsql-hackers

Hi,

Tom Lane <tgl@sss.pgh.pa.us> writes:
> 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).

That what I had in mind for relaxing the superuser() checks, yes.

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

That is a much more complex problem, and I think it would require that
we have a way to mark shared objects as trustworthy.  But we sure don't
need that now.

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

Yeah, I don't want setuid extension scripts either.

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

Ok.

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

Well it's easy to read that the other way round.  Is superuser = true
means that I need to be a superuser or does it mean that the script will
get run as superuser no matter what?  Not a huge problem, but still.
What about using the PL terminology here, and calling the property
'trusted' (default false, so you have to be a superuser to load them)?

> 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 ;-)

Hehe.  We knew we would get there someday, it's been quicker than
expected though :)

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

In summary, +1 for this proposal.

Also, do we want pg_upgrade to automatically do the create extension
from unpackaged for us when upgrading a PL?  I think it makes sense.

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