Re: How should row-security affects ON UPDATE RESTRICT / CASCADE ?

On 10/29/2013 10:01 PM, Tom Lane wrote:
> As I recall, I've been saying since day one that row-level security cannot
> sensibly coexist with foreign-key constraints, and I've been told that the
> potential users of such a feature don't care. I'm glad to see somebody
> else complaining.

I'm concerned, rather than complaining.

It seems other DBMS vendors just say "FK constraints are exempt from
RLS". In the absence of a more consistent way to do it this might be the
best option.

My concern is that right now the superuser is still affected by RLS
because triggers on tables owned by non-superusers run with the rights
(and therefore RLS visibility) of those users even in response to
operations invoked by the superuser. If the superuser can see a row, but
when they insert a row into another table that references it they get an
error, that seems just plain wrong to me.

I'd kind of like to see FK constraints affected by RLS for
non-superusers, at least as an option. It'd be really handy when you do
have consistent RLS visibility rules across a set of tables. Problems
only arise when the RLS visibility rules _differ_ between referrer and
referee.

> Here's another example wherein there basically isn't a sensible solution:
> suppose you have delete rights on table A, and there is a table B
> with a foreign-key reference to A, and RLS says that there are rows in
> B that you can't see. You try to delete some row in A that is referenced
> by an invisible-to-you row in B. There are only two possible outcomes:
> the system refuses your request, and thereby exposes to you the fact that
> a referencing row exists; or the system allows the FK constraint to be
> violated.

Yep, that's the flip-side of the ON DELETE CASCADE.

> As far as the points you're making go, I think we must say that RLS checks
> are not applied during FK trigger queries, ie the FK triggers can always
> see everything even though they don't run as superuser.

I think that's the sane way to go for now.

If we can come up with a way of making FK constraints or some RLS-aware
variant of them work, I tend to think that's a separate job to
implementing the core of RLS.

> Otherwise you're
> going to end up with constraint violations, and as a database weenie
> I consider that unacceptable.

Yeah, and we can't re-check FKs as every combination of user for every
FK reference whenever any FK or any RLS rule changes, not sanely anyway.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services