Re: [EXAMPLE] Overly zealous security of schemas...

Sean Chittenden <sean(at)chittenden(dot)org> writes:
> Ah, I had this backwards: I thought SECURITY DEFINER wasn't setting
> something that'd allow the foreign keys to run as the owner of the
> function.

Nah; by the time the RI triggers run, you're out of the function
entirely. So they have to fend for themselves.

Here's the 7.3 version of the patch (it's a bit ugly because I had to
back-port a couple of changes that are in CVS tip).

regards, tom lane

*** src/backend/utils/adt/ri_triggers.c.orig Thu Mar 27 14:25:52 2003
--- src/backend/utils/adt/ri_triggers.c Sat Apr 26 18:12:16 2003
***************
*** 58,74 ****
#define RI_KEYS_SOME_NULL 1
#define RI_KEYS_NONE_NULL 2

!
#define RI_PLAN_CHECK_LOOKUPPK_NOCOLS 1
#define RI_PLAN_CHECK_LOOKUPPK 2
! #define RI_PLAN_CASCADE_DEL_DODELETE 1
! #define RI_PLAN_CASCADE_UPD_DOUPDATE 1
! #define RI_PLAN_NOACTION_DEL_CHECKREF 1
! #define RI_PLAN_NOACTION_UPD_CHECKREF 1
! #define RI_PLAN_RESTRICT_DEL_CHECKREF 1
! #define RI_PLAN_RESTRICT_UPD_CHECKREF 1
! #define RI_PLAN_SETNULL_DEL_DOUPDATE 1
! #define RI_PLAN_SETNULL_UPD_DOUPDATE 1

#define MAX_QUOTED_NAME_LEN (NAMEDATALEN*2+3)
#define MAX_QUOTED_REL_NAME_LEN (MAX_QUOTED_NAME_LEN*2)
--- 58,75 ----
#define RI_KEYS_SOME_NULL 1
#define RI_KEYS_NONE_NULL 2

! /* queryno values must be distinct for the convenience of ri_PerformCheck */
#define RI_PLAN_CHECK_LOOKUPPK_NOCOLS 1
#define RI_PLAN_CHECK_LOOKUPPK 2
! #define RI_PLAN_CASCADE_DEL_DODELETE 3
! #define RI_PLAN_CASCADE_UPD_DOUPDATE 4
! #define RI_PLAN_NOACTION_DEL_CHECKREF 5
! #define RI_PLAN_NOACTION_UPD_CHECKREF 6
! #define RI_PLAN_RESTRICT_DEL_CHECKREF 7
! #define RI_PLAN_RESTRICT_UPD_CHECKREF 8
! #define RI_PLAN_SETNULL_DEL_DOUPDATE 9
! #define RI_PLAN_SETNULL_UPD_DOUPDATE 10
! #define RI_PLAN_KEYEQUAL_UPD 11

#define MAX_QUOTED_NAME_LEN (NAMEDATALEN*2+3)
#define MAX_QUOTED_REL_NAME_LEN (MAX_QUOTED_NAME_LEN*2)
***************
*** 149,154 ****
--- 150,159 ----
static void ri_InitHashTables(void);
static void *ri_FetchPreparedPlan(RI_QueryKey *key);
static void ri_HashPreparedPlan(RI_QueryKey *key, void *plan);
+ static void *ri_PlanCheck(char *querystr, int nargs, Oid *argtypes,
+ RI_QueryKey *qkey, Relation fk_rel, Relation pk_rel,
+ bool cache_plan);
+

/* ----------
* RI_FKey_check -
***************
*** 264,269 ****
--- 269,277 ----
fk_rel, pk_rel,
tgnargs, tgargs);

+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect() failed in RI_FKey_check()");
+
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
{
char querystr[MAX_QUOTED_REL_NAME_LEN + 100];
***************
*** 278,297 ****
snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x FOR UPDATE OF x",
pkrelname);

! /*
! * Prepare, save and remember the new plan.
! */
! qplan = SPI_prepare(querystr, 0, NULL);
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
}

/*
* Execute the plan
*/
- if (SPI_connect() != SPI_OK_CONNECT)
- elog(WARNING, "SPI_connect() failed in RI_FKey_check()");
-
SetUserId(RelationGetForm(pk_rel)->relowner);

if (SPI_execp(qplan, check_values, check_nulls, 1) != SPI_OK_SELECT)
--- 286,299 ----
snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x FOR UPDATE OF x",
pkrelname);

! /* Prepare and save the plan */
! qplan = ri_PlanCheck(querystr, 0, NULL,
! &qkey, fk_rel, pk_rel, true);
}

/*
* Execute the plan
*/
SetUserId(RelationGetForm(pk_rel)->relowner);

if (SPI_execp(qplan, check_values, check_nulls, 1) != SPI_OK_SELECT)
***************
*** 420,426 ****
* The query string built is
* SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding FK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 422,428 ----
* The query string built is
* SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding FK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 440,451 ****
}
strcat(querystr, " FOR UPDATE OF x");

! /*
! * Prepare, save and remember the new plan.
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
}

/*
--- 442,450 ----
}
strcat(querystr, " FOR UPDATE OF x");

! /* Prepare and save the plan */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, fk_rel, pk_rel, true);
}

/*
***************
*** 625,631 ****
* The query string built is
* SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding FK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 624,630 ----
* The query string built is
* SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding FK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 645,656 ****
}
strcat(querystr, " FOR UPDATE OF x");

! /*
! * Prepare, save and remember the new plan.
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
}

/*
--- 644,652 ----
}
strcat(querystr, " FOR UPDATE OF x");

! /* Prepare and save the plan */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, pk_rel, pk_rel, true);
}

/*
***************
*** 834,840 ****
* The query string built is
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 830,836 ----
* The query string built is
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 854,865 ****
}
strcat(querystr, " FOR UPDATE OF x");

! /*
! * Prepare, save and remember the new plan.
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
}

/*
--- 850,858 ----
}
strcat(querystr, " FOR UPDATE OF x");

! /* Prepare and save the plan */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, fk_rel, pk_rel, true);
}

/*
***************
*** 1075,1081 ****
* The query string built is
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 1068,1074 ----
* The query string built is
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 1095,1106 ****
}
strcat(querystr, " FOR UPDATE OF x");

! /*
! * Prepare, save and remember the new plan.
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
}

/*
--- 1088,1096 ----
}
strcat(querystr, " FOR UPDATE OF x");

! /* Prepare and save the plan */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, fk_rel, pk_rel, true);
}

/*
***************
*** 1288,1294 ****
* The query string built is
* DELETE FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 1278,1284 ----
* The query string built is
* DELETE FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 1307,1318 ****
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
}

! /*
! * Prepare, save and remember the new plan.
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
}

/*
--- 1297,1305 ----
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
}

! /* Prepare and save the plan */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, fk_rel, pk_rel, true);
}

/*
***************
*** 1511,1517 ****
* UPDATE ONLY <fktable> SET fkatt1 = $1 [, ...]
* WHERE fkatt1 = $n [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 1498,1504 ----
* UPDATE ONLY <fktable> SET fkatt1 = $1 [, ...]
* WHERE fkatt1 = $n [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 1537,1548 ****
}
strcat(querystr, qualstr);

! /*
! * Prepare, save and remember the new plan.
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs * 2, queryoids);
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
}

/*
--- 1524,1532 ----
}
strcat(querystr, qualstr);

! /* Prepare and save the plan */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs * 2, queryoids,
! &qkey, fk_rel, pk_rel, true);
}

/*
***************
*** 1741,1747 ****
* The query string built is
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 1725,1731 ----
* The query string built is
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 1761,1772 ****
}
strcat(querystr, " FOR UPDATE OF x");

! /*
! * Prepare, save and remember the new plan.
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
}

/*
--- 1745,1753 ----
}
strcat(querystr, " FOR UPDATE OF x");

! /* Prepare and save the plan */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, fk_rel, pk_rel, true);
}

/*
***************
*** 1975,1981 ****
* The query string built is
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 1956,1962 ----
* The query string built is
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 1995,2006 ****
}
strcat(querystr, " FOR UPDATE OF x");

! /*
! * Prepare, save and remember the new plan.
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
}

/*
--- 1976,1984 ----
}
strcat(querystr, " FOR UPDATE OF x");

! /* Prepare and save the plan */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, fk_rel, pk_rel, true);
}

/*
***************
*** 2195,2201 ****
* UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
* WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 2173,2179 ----
* UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
* WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 2220,2231 ****
}
strcat(querystr, qualstr);

! /*
! * Prepare, save and remember the new plan.
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
}

/*
--- 2198,2206 ----
}
strcat(querystr, qualstr);

! /* Prepare and save the plan */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, fk_rel, pk_rel, true);
}

/*
***************
*** 2445,2451 ****
* UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
* WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 2420,2426 ----
* UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
* WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 2481,2499 ****
strcat(querystr, qualstr);

/*
! * Prepare the new plan.
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
!
! /*
! * Save and remember the plan if we're building the
* "standard" plan.
*/
! if (use_cached_query)
! {
! qplan = SPI_saveplan(qplan);
! ri_HashPreparedPlan(&qkey, qplan);
! }
}

/*
--- 2456,2467 ----
strcat(querystr, qualstr);

/*
! * Prepare the plan. Save it only if we're building the
* "standard" plan.
*/
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, fk_rel, pk_rel,
! use_cached_query);
}

/*
***************
*** 2682,2688 ****
* UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
* WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 2650,2656 ----
* UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
* WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 2707,2716 ****
}
strcat(querystr, qualstr);

! /*
! * Prepare the plan
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);

/*
* Scan the plan's targetlist and replace the NULLs by
--- 2675,2683 ----
}
strcat(querystr, qualstr);

! /* Prepare the plan, don't save it */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, fk_rel, pk_rel, false);

/*
* Scan the plan's targetlist and replace the NULLs by
***************
*** 2942,2948 ****
* UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
* WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
--- 2909,2915 ----
* UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
* WHERE fkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
***************
*** 2977,2986 ****
}
strcat(querystr, qualstr);

! /*
! * Prepare the plan
! */
! qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);

/*
* Scan the plan's targetlist and replace the NULLs by
--- 2944,2952 ----
}
strcat(querystr, qualstr);

! /* Prepare the plan, don't save it */
! qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
! &qkey, fk_rel, pk_rel, false);

/*
* Scan the plan's targetlist and replace the NULLs by
***************
*** 3124,3130 ****
case RI_MATCH_TYPE_UNSPECIFIED:
case RI_MATCH_TYPE_FULL:
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
! 0,
fk_rel, pk_rel,
tgnargs, tgargs);

--- 3090,3096 ----
case RI_MATCH_TYPE_UNSPECIFIED:
case RI_MATCH_TYPE_FULL:
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
! RI_PLAN_KEYEQUAL_UPD,
fk_rel, pk_rel,
tgnargs, tgargs);

***************
*** 3160,3165 ****
--- 3126,3177 ----
* ----------
*/

+
+ /*
+ * Prepare execution plan for a query to enforce an RI restriction
+ *
+ * If cache_plan is true, the plan is saved into our plan hashtable
+ * so that we don't need to plan it again.
+ */
+ static void *
+ ri_PlanCheck(char *querystr, int nargs, Oid *argtypes,
+ RI_QueryKey *qkey, Relation fk_rel, Relation pk_rel,
+ bool cache_plan)
+ {
+ void *qplan;
+ Relation query_rel;
+ Oid save_uid;
+
+ /*
+ * The query is always run against the FK table except
+ * when this is an update/insert trigger on the FK table itself -
+ * either RI_PLAN_CHECK_LOOKUPPK or RI_PLAN_CHECK_LOOKUPPK_NOCOLS
+ */
+ if (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK ||
+ qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK_NOCOLS)
+ query_rel = pk_rel;
+ else
+ query_rel = fk_rel;
+
+ /* Switch to proper UID to perform check as */
+ save_uid = GetUserId();
+ SetUserId(RelationGetForm(query_rel)->relowner);
+
+ /* Create the plan */
+ qplan = SPI_prepare(querystr, nargs, argtypes);
+
+ /* Restore UID */
+ SetUserId(save_uid);
+
+ /* Save the plan if requested */
+ if (cache_plan)
+ {
+ qplan = SPI_saveplan(qplan);
+ ri_HashPreparedPlan(qkey, qplan);
+ }
+
+ return qplan;
+ }

/*
* quoteOneName --- safely quote a single SQL name