Re: WITH CHECK and Column-Level Privileges

On 29 September 2014 16:46, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> Well, I think that's an acceptable approach from the point of view of
>> fixing the security exposure, but it's far from ideal. Good error
>> messages are important for usability. I can live with this as a
>> short-term fix, but in the long run I strongly believe we should try
>> to do better.
>

Yes agreed, error messages are important, and longer term it would be
better to report on the specific columns the user has access to (for
all constraints), rather than the all-or-nothing approach of the
current patch. However, for WCOs, I don't think the executor has the
information it needs to work out how to do that because it doesn't
even know which view the user updated, because it's not necessarily
the same one as failed the WCO.

> It certainly wouldn't be hard to add the same check around the WITH
> OPTION case that's around my proposed solution for the other issues-
> just check for SELECT rights on the underlying table.

I guess that would work well for RLS, since the user would typically
have SELECT rights on the table they're updating, but I'm not
convinced it would do much good for auto-updatable views.

Another question
> is if we could/should limit this to the UPDATE case. With the INSERT
> case, any columns not provided by the user would be filled out by
> defaults, which can likely be seen in the catalog, or the functions in
> the catalog for the defaults or for any triggers might be able to be
> run by the user executing the INSERT anyway to see what would have been
> used in the resulting row. I'm not completely convinced there's no risk
> there though..
>

I think it's conceivable that a trigger could populate a column hidden
to the user with some confidential information, possibly pulled from
another table that the user doesn't have access to, so the fix has to
apply to INSERTs as well as UPDATEs.

Regards,
Dean