Re: Row security violation error is misleading

On 7 April 2015 at 13:11, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> When attempting to insert a row that violates a row security policy that
> applies to writes, the error message emitted references WITH CHECK OPTION,
> even though (as far as the user knows) there's no such thing involved.
> If you understand the internals you'd know that row security re-uses the
> same logic as WITH CHECK OPTION, but it's going to be confusing for users.
>
> postgres=> INSERT INTO clients (account_name, account_manager) VALUES
> ('peters', 'peter'), ('johannas', 'johanna');
> ERROR: 44000: new row violates WITH CHECK OPTION for "clients"
> DETAIL: Failing row contains (7, johannas, johanna).
> LOCATION: ExecWithCheckOptions, execMain.c:1683
>
>
> ... yet "clients" is a table, not a view, and cannot have a WITH CHECK
> OPTION clause.
>
> There is no reference to the policy being violated or to the fact that it's
> row security involved.
>
> I think this is going to be very confusing for users. I was expecting to see
> something more like:
>
> ERROR: 44000: new row in table 'clients' violates row level security policy
> 'just_own_clients'
>

Yes, I agree - that's a bit confusing.

Note that it doesn't make sense to ask which RLS policy was violated.
There is a default deny policy in place, and each defined policy's
quals are combined using OR, so when there are multiple policies this
error indicates that none of the policies passed (in a sense, they
were all violated).

> Re-using the SQLSTATE 44000 is a bit iffy too. We should probably define
> something to differentiate this, like:
>
> 44P01 ROW SECURITY WRITE POLICY VIOLATION
>

Yes, that sounds sensible.

Regards,
Dean