Re: Simplify ACL handling for large objects and removal of superuser() checks

> On Nov 10, 2017, at 3:58 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> Michael, Tom,
>
> * Michael Paquier (michael(dot)paquier(at)gmail(dot)com) wrote:
>> On Fri, Nov 10, 2017 at 10:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>>>> I'm guessing no, which essentially means that *we* consider access to
>>>> lo_import/lo_export to be equivilant to superuser and therefore we're
>>>> not going to implement anything to try and prevent the user who has
>>>> access to those functions from becoming superuser. If we aren't willing
>>>> to do that, then how can we really say that there's some difference
>>>> between access to these functions and being a superuser?
>>>
>>> We seem to be talking past each other. Yes, if a user has malicious
>>> intentions, it's possibly to parlay lo_export into obtaining a superuser
>>> login (I'm less sure that that's necessarily true for lo_import).
>>> That does NOT make it "equivalent", except perhaps in the view of someone
>>> who is only considering blocking malevolent actors. It does not mean that
>>> there's no value in preventing a task that needs to run lo_export from
>>> being able to accidentally destroy any data in the database. There's a
>>> range of situations where you are concerned about accidents and errors,
>>> not malicious intent; but your argument ignores those use-cases.
>>
>> That will not sound much as a surprise as I spawned the original
>> thread, but like Robert I understand that getting rid of all superuser
>> checks is a goal that we are trying to reach to allow admins to have
>> more flexibility in handling permissions to a subset of objects.
>> Forcing an admin to give full superuser rights to one user willing to
>> work only on LOs import and export is a wrong concept.
>
> The problem I have with this is that 'full superuser rights' actually
> are being granted by this. You're able to read any file and write any
> file on the filesystem that the PG OS user can. How is that not 'full
> superuser rights'? The concerns about a mistake being made are just as
> serious as they are with someone who has superuser rights as someone
> could pretty easily end up overwriting the control file or various other
> extremely important bits of the system. This isn't just about what
> 'blackhats' can do with this.
>
> As I mentioned up-thread, if we want to make it so that non-superusers
> can use lo_import/lo_export, then we should do that in a way that
> allows the administrator to specify exactly the rights they really want
> to give, based on a use-case for them. There's no use-case for allowing
> someone to use lo_export() to overwrite pg_control. The use-case would
> be to allow them to export to a specific directory (or perhaps a set of
> directories), or to read from same. The same is true of COPY. Further,
> I wonder what would happen if we allow this and then someone comes along
> and re-proposes the 'CREATE DIRECTORY' concept that I had once proposed
> (ideally with things cleaned up and tightened up to avoid there being
> issues using it) to address the actual use-case for these functions to
> be available to a non-superuser. We wouldn't be able to change these
> functions to start checking the 'directory' rights or we would break
> existing non-superuser usage of them. I imagine we could create
> additional functions which check the 'directory' privileges, but that's
> hardly ideal, imv.
>
> I'm disappointed to apparently be in the minority on this, but that
> seems to be the case unless someone else wishes to comment. While I
> appreciate the discussion, I'm certainly no more convinced today than I
> was when I first saw this go in that allowing these functions to be
> granted to non-superusers does anything but effectively make them into
> superusers who aren't explicitly identified as superusers.

Just to help understand your concern, and not to propose actual features,
would it help if there were

(1) a function, perhaps pg_catalog.pg_exploiters(), which would return all
roles that have been granted exploitable privileges, such that it would be
easier to identify all superusers, including those whose superuserishness
derives from a known export, and

(2) a syntax change for GRANT that would require an extra token, so
that you'd have to write something like

GRANT EXPLOITABLE lo_export TO trusted_user_foo

so that you couldn't unknowingly grant a dangerous privilege.

Or is there more to it than that?

mark