Re: [EXAMPLE] Overly zealous security of schemas... - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: [EXAMPLE] Overly zealous security of schemas... |
| Date | |
| Msg-id | 21461.1051395858@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: [EXAMPLE] Overly zealous security of schemas... (Sean Chittenden <sean@chittenden.org>) |
| Responses |
Re: [EXAMPLE] Overly zealous security of schemas...
|
| List | pgsql-hackers |
Sean Chittenden <sean@chittenden.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
identifysome 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
identifysome 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
identifysome 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
identifysome 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
parsercannot 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
parsercannot 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>
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could * eventually fail if the
parsercannot identify some way * how to compare these two types by '='. * ----------
--- 1068,1074 ---- * The query string built is * SELECT 1 FROM ONLY <fktable>
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could * eventually fail if the
parsercannot 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
parsercannot 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
parsercannot 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 [, ...] *
WHEREfkatt1 = $n [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could * eventually fail if the
parsercannot identify some way * how to compare these two types by '='. * ----------
--- 1498,1504 ---- * UPDATE ONLY <fktable> SET fkatt1 = $1 [, ...] *
WHEREfkatt1 = $n [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could * eventually fail if the
parsercannot 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>
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could * eventually fail if the
parsercannot identify some way * how to compare these two types by '='. * ----------
--- 1725,1731 ---- * The query string built is * SELECT 1 FROM ONLY <fktable>
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could * eventually fail if the
parsercannot 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>
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could * eventually fail if the
parsercannot identify some way * how to compare these two types by '='. * ----------
--- 1956,1962 ---- * The query string built is * SELECT 1 FROM ONLY <fktable>
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could * eventually fail if the
parsercannot 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 [, ...] *
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could * eventually fail if the
parsercannot identify some way * how to compare these two types by '='. * ----------
--- 2173,2179 ---- * UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...] *
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could * eventually fail if the
parsercannot 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 [, ...] *
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could * eventually fail if the
parsercannot identify some way * how to compare these two types by '='. * ----------
--- 2420,2426 ---- * UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...] *
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could * eventually fail if the
parsercannot 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 [, ...] *
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could * eventually fail if the
parsercannot identify some way * how to compare these two types by '='. * ----------
--- 2650,2656 ---- * UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...] *
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could * eventually fail if the
parsercannot 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
theplan'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'stargetlist and replace the NULLs by
***************
*** 2942,2948 **** * UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...] *
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, SPI_prepare could * eventually fail if the
parsercannot identify some way * how to compare these two types by '='. * ----------
--- 2909,2915 ---- * UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...] *
WHEREfkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the
! * corresponding PK attributes. Thus, ri_PlanCheck could * eventually fail if the
parsercannot 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
theplan'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'stargetlist 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
pgsql-hackers by date: