Here's what I've come up with to avoid "permission denied" errors when a
RI trigger has to lock a PK table. Whenever the SELECT FOR UPDATE is
executed I temporarily switch the current user id to the owner of the PK
table. It's not the grand unified solution via setuid functions that was
envisioned now and then, but it does the same conceptually. For a
terminally elegant solution I can only suggest not using the SPI
interface.
I recommend this patch to be checked out by someone knowledgeable in the
RI area.
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
*** pgsql-cvs/src/backend/utils/adt/ri_triggers.c Tue May 30 18:10:12 2000
--- pgsql/src/backend/utils/adt/ri_triggers.c Wed Sep 20 00:26:33 2000
***************
*** 24,29 ****
--- 24,30 ----
#include "catalog/pg_operator.h"
#include "commands/trigger.h"
#include "executor/spi_priv.h"
+ #include "miscadmin.h"
/* ----------
*************** RI_FKey_check(PG_FUNCTION_ARGS)
*** 158,163 ****
--- 159,167 ----
bool isnull;
int i;
int match_type;
+ Oid save_uid;
+
+ save_uid = GetUserId();
ReferentialIntegritySnapshotOverride = true;
*************** RI_FKey_check(PG_FUNCTION_ARGS)
*** 252,260 ****
--- 256,268 ----
if (SPI_connect() != SPI_OK_CONNECT)
elog(NOTICE, "SPI_connect() failed in RI_FKey_check()");
+ SetUserId(RelationGetForm(pk_rel)->relowner);
+
if (SPI_execp(qplan, check_values, check_nulls, 1) != SPI_OK_SELECT)
elog(ERROR, "SPI_execp() failed in RI_FKey_check()");
+ SetUserId(save_uid);
+
if (SPI_processed == 0)
elog(ERROR, "%s referential integrity violation - "
"no rows found in %s",
*************** RI_FKey_check(PG_FUNCTION_ARGS)
*** 435,443 ****
--- 443,456 ----
* Now check that foreign key exists in PK table
* ----------
*/
+
+ SetUserId(RelationGetForm(pk_rel)->relowner);
+
if (SPI_execp(qplan, check_values, check_nulls, 1) != SPI_OK_SELECT)
elog(ERROR, "SPI_execp() failed in RI_FKey_check()");
+ SetUserId(save_uid);
+
if (SPI_processed == 0)
elog(ERROR, "%s referential integrity violation - "
"key referenced from %s not found in %s",
*************** RI_FKey_noaction_del(PG_FUNCTION_ARGS)
*** 508,513 ****
--- 521,529 ----
char del_nulls[RI_MAX_NUMKEYS + 1];
bool isnull;
int i;
+ Oid save_uid;
+
+ save_uid = GetUserId();
ReferentialIntegritySnapshotOverride = true;
*************** RI_FKey_noaction_del(PG_FUNCTION_ARGS)
*** 659,667 ****
--- 675,687 ----
* Now check for existing references
* ----------
*/
+ SetUserId(RelationGetForm(pk_rel)->relowner);
+
if (SPI_execp(qplan, del_values, del_nulls, 1) !=
SPI_OK_SELECT)
elog(ERROR, "SPI_execp() failed in
RI_FKey_noaction_del()");
+ SetUserId(save_uid);
+
if (SPI_processed > 0)
elog(ERROR, "%s referential integrity violation - "
"key in %s still referenced from %s",
*************** RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
*** 716,721 ****
--- 736,744 ----
char upd_nulls[RI_MAX_NUMKEYS + 1];
bool isnull;
int i;
+ Oid save_uid;
+
+ save_uid = GetUserId();
ReferentialIntegritySnapshotOverride = true;
*************** RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
*** 876,884 ****
--- 899,911 ----
* Now check for existing references
* ----------
*/
+ SetUserId(RelationGetForm(pk_rel)->relowner);
+
if (SPI_execp(qplan, upd_values, upd_nulls, 1) !=
SPI_OK_SELECT)
elog(ERROR, "SPI_execp() failed in
RI_FKey_noaction_upd()");
+ SetUserId(save_uid);
+
if (SPI_processed > 0)
elog(ERROR, "%s referential integrity violation - "
"key in %s still referenced from %s",
*************** RI_FKey_restrict_upd(PG_FUNCTION_ARGS)
*** 1570,1575 ****
--- 1597,1605 ----
char upd_nulls[RI_MAX_NUMKEYS + 1];
bool isnull;
int i;
+ Oid save_uid;
+
+ save_uid = GetUserId();
ReferentialIntegritySnapshotOverride = true;
*************** RI_FKey_restrict_upd(PG_FUNCTION_ARGS)
*** 1730,1737 ****
--- 1760,1771 ----
* Now check for existing references
* ----------
*/
+ SetUserId(RelationGetForm(pk_rel)->relowner);
+
if (SPI_execp(qplan, upd_values, upd_nulls, 1) !=
SPI_OK_SELECT)
elog(ERROR, "SPI_execp() failed in
RI_FKey_restrict_upd()");
+
+ SetUserId(save_uid);
if (SPI_processed > 0)
elog(ERROR, "%s referential integrity violation - "
