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

2013/10/30 Craig Ringer <craig@2ndquadrant.com>:
> On 10/30/2013 10:50 AM, Tom Lane wrote:
>> Craig Ringer <craig@2ndquadrant.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@kaigai.gr.jp>