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

2013/10/30 Craig Ringer <craig(at)2ndquadrant(dot)com>:
> On 10/30/2013 10:50 AM, Tom Lane wrote:
>> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>>> > I'd kind of like to see FK constraints affected by RLS for
>>> > non-superusers, at least as an option.
>> I think that's a complete nonstarter. Aside from the fact that such a
>> constraint will have no definable semantics, even the possibility that a
>> constraint doesn't mean what it appears to mean will prevent us from
>> making use of FK constraints for optimization --- something that's
>> pretty high on the planner to-do list.
>
> Good point. That's another good argument for FK constraints to disregard
> RLS. In which case, is there actually any way to determine when an SPI
> query is being invoked directly from an FK constraint? We'll need a way
> to tell so RLS can skip adding the row-security check predicate.
>
For your reference, my implementation patches on ri_PerformCheck()
as follows. It didn't skip all the case (only when PK is modified), however,
its overall idea can be common.

--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -3008,6 +3008,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
int spi_result;
Oid save_userid;
int save_sec_context;
+ int temp_sec_context;
Datum vals[RI_MAX_NUMKEYS * 2];
char nulls[RI_MAX_NUMKEYS * 2];

@@ -3087,8 +3088,18 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,

/* Switch to proper UID to perform check as */
GetUserIdAndSecContext(&save_userid, &save_sec_context);
+
+ /*
+ * Row-level security should be disabled in case when foreign-key
+ * relation is queried to check existence of tuples that references
+ * the primary-key being modified.
+ */
+ temp_sec_context = save_sec_context | SECURITY_LOCAL_USERID_CHANGE;
+ if (source_is_pk)
+ temp_sec_context |= SECURITY_ROW_LEVEL_DISABLED;
+
SetUserIdAndSecContext(RelationGetForm(query_rel)->relowner,
- save_sec_context | SECURITY_LOCAL_USERID_CHANGE);
+ temp_sec_context);

/* Finally we can run the query. */
spi_result = SPI_execute_snapshot(qplan,

--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>