Re: WITH CHECK and Column-Level Privileges

* Dean Rasheed (dean(dot)a(dot)rasheed(at)gmail(dot)com) wrote:
> On 30 September 2014 16:52, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > If the user only has column-level privileges on the table then I'm not
> > really sure how useful the detail will be.
>
> One of the main things that detail is useful for is identifying the
> failing row in a multi-row update. In most real-world cases, I would
> expect the column-level privileges to include the table's PK, so the
> detail would meet that requirement. In fact the column-level
> privileges would pretty much have to include sufficient columns to
> usefully identify rows, otherwise updates wouldn't be practical.

That may or may not be true- the user needs sufficient information to
identify a row, but that doesn't mean they have access to all columns
make up a unique constraint. It's not uncommon to have a surrogate key
for identifying the rows and then an independent uniqueness constraint
on some natural key for the table, which the user may not have access
to.

> > I've been focusing on the 9.4 and back-branches concern, but as for RLS,
> > if we're going to try and include the detail in this case then I'd
> > suggest we do so only if the user has SELECT rights and RLS is disabled
> > on the relation.
>
> Huh? If RLS is disabled, isn't the check option also disabled?

Not quite. If RLS is disabled on the relation then the RLS policies
don't add to the with check option, but a view overtop of a RLS table
might have a with check option. That's the situation which I was
getting at when it comes to the with-check option. The other cases of
constraint violation which we're discussing here would need to be
handled also though, so it's not just the with-check case.

> Otherwise, we'd have to check that the row being
> > returned actually passes the SELECT policy. I'm already not really
> > thrilled that we are changing error message results based on user
> > permissions, and that seems like it'd be worse.
>
> That's one of the things I never liked about allowing different RLS
> policies for different commands --- the idea that the user can UPDATE
> a row that they can't even SELECT just doesn't make sense to me.

The reason for having the per-command RLS policies was that you might
want a different policy applied for different commands (ie: you can see
all rows, but can only update your row); the ability to define a policy
which allows you to UPDATE rows which are not visible to a normal SELECT
is also available through that but isn't really the reason for the
capability. That said, I agree it isn't common to define policies that
way, but not unheard of either.

> > What do you think about returning just what the user provided in the
> > first place in both of these cases..? I'm not quite sure what that
> > would look like in the UPDATE case but for INSERT (and COPY) it would be
> > the subset of columns being inserted and the values originally provided.
> > That may not be what the actual conflict is due to, as there could be
> > before triggers changing things in the middle, or the conflict could be
> > on default values, but at least the input row could be identified and
> > there wouldn't be this information leak risk. Not sure how difficult
> > that would be to implement though.
>
> I could see that working for INSERTs, but for UPDATEs I don't think
> that would be very useful in practice, because the columns most likely
> to be useful for identifying the failing row (e.g., key columns) are
> also the columns least likely to have been updated.

I'm not sure that I follow this- if you're not updating the key columns
then you're not likely to be having a conflict due to them...

Thanks,

Stephen