From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Removing pg_pltemplate and creating "trustable" extensions
Date: Thu, 07 Nov 2019 13:54:12 -0500
Message-ID: <31658.1573152852@sss.pgh.pa.us> (raw)
In-Reply-To: <20191107183856.GY6962@tamriel.snowman.net>
References: <5889.1566415762@sss.pgh.pa.us>
<c15c0f58-79a9-4051-1f43-8fc06564df69@2ndquadrant.com>
<27727.1573052802@sss.pgh.pa.us>
<20191107183856.GY6962@tamriel.snowman.net>
Stephen Frost <sfrost@snowman.net> writes:
>> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>>> Using GUCs to control some of this creates yet another place where
>>> permission information is kept, and with it questions about how to get
>>> to it, how to edit it, or to back it up and restore it, etc. Also,
>>> list-based parameters are particularly hard to manage by automated
>>> tools. I think we can do this within the existing permission system,
>>> for example with pre-defined roles (for example, GRANT
>>> pg_create_trusted_extension ...). Also, CREATE EXTENSION should somehow
>>> be controlled by the CREATE privilege on the containing database, so a
>>> separate setting for database owner vs. regular user might not be
>>> necessary. Regular users would need both the role membership (given by
>>> the overall superuser) and the privilege within the database (given by
>>> the database owner).
> Two things- first, this doesn't actually cover everything that the
> proposed GUCs do- specifically, the proposed GUCs give you a way to
> limit what specific extensions are allowed to be installed, and by whom.
> Moving to a GRANT-based system removes the extension specificity and
> leaves with just "is user X allowed to install extensions".
True. But do we care? We did not have that flexibility before, either.
I'd still keep the "trustable" property (probably renamed to "trusted"
for simplicity) for extensions, so in the worst case, an admin could
edit extension control files to add or remove the per-extension flag.
> Second,
> this approach is requiring that a user who is allowed to create
> extensions must also be allowed to create schemas on the database in
> question.
That doesn't seem like a big objection from here. We could fix it
by making a separate privilege bit, but I doubt that it's worth using
up one of our limited set of spare bits for.
>> I agree with the idea of requiring a DB-level privilege as well as
>> the overall role. Is it okay to re-use the CREATE privilege (which
>> today only allows for CREATE SCHEMA), or do we need another one?
> If we just created another one, wouldn't that remove the need to have a
> database role?
No, because then being DB owner would be alone be enough to let you
install extensions (since as owner, you could certainly grant yourself
all privileges on the DB, even if this were somehow not the default).
We'd have to mangle GRANT's behavior to avoid that, and I don't think
we should. Nor do I think that DB ownership ought to be enough
privilege by itself.
>> I think re-using CREATE is probably all right, since it would only be
>> useful for this purpose to users who also have "pg_install_extensions".
> With this, you couldn't have a user who is able to create extensions but
> not able to create schemas though. That kind of combining of privileges
> together really goes against the general principle of 'least privilege',
> unless the action associated with one necessairly requires the other,
> but I don't believe that's the case here.
A point here is that many extensions involve creating their own schemas
anyway. Also, the ability to "relocate" an extension to a different
schema is pretty meaningless if you can't create a schema to put it in.
If I thought that there were a use-case for letting someone create
extensions but not schemas, I'd be more eager to invent a new bit.
But I'm having a *really* hard time envisioning a live use-case
for that. Granting extension-creation ability requires a whole lot
more trust in the grantee than the ability to make new schemas
(which, in themselves, have about zero impact on anybody else).
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, sfrost@snowman.net, peter.eisentraut@2ndquadrant.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Removing pg_pltemplate and creating "trustable" extensions
In-Reply-To: <31658.1573152852@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox