Solution for RI permission problem
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 peter_e@gmx.net http://yi.org/peter-e/
Attachments:
ri-patchtext/plain; CHARSET=US-ASCII; NAME=ri-patchDownload
*** 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 - "
As a question, since I don't have a source tree available here at work,
will there be an issue if an elog occurs between the various two user id
sets? Just wondering, because most of those statements are do some
SPI thing or elog.
Stephan Szabo
sszabo@bigpanda.com
On Wed, 20 Sep 2000, Peter Eisentraut wrote:
Show quoted text
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.
On Wed, 20 Sep 2000, Peter Eisentraut wrote:
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.
It seems to be working on my system (and you don't need to give any access
to the pk table to the user).
With that, I do have a general question though. Are referential actions
supposed to be limited by the permissions of the user executing the query?
So, if you for example have write access on the pk table, but not to the
fk table, and there is a on cascade delete relationship, should that user
not be able to delete from the pk table?
Stephan Szabo writes:
With that, I do have a general question though. Are referential actions
supposed to be limited by the permissions of the user executing the query?
So, if you for example have write access on the pk table, but not to the
fk table, and there is a on cascade delete relationship, should that user
not be able to delete from the pk table?
Then you could delete records that are not in relation to the foreign keys
in your table. So I suppose not. Of course there does seem to be a very
limited range of usefulness of such a setup, but we shouldn't extrapolate
something potentially more useful from that.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Sun, 1 Oct 2000, Peter Eisentraut wrote:
Stephan Szabo writes:
With that, I do have a general question though. Are referential actions
supposed to be limited by the permissions of the user executing the query?
So, if you for example have write access on the pk table, but not to the
fk table, and there is a on cascade delete relationship, should that user
not be able to delete from the pk table?Then you could delete records that are not in relation to the foreign keys
in your table. So I suppose not. Of course there does seem to be a very
limited range of usefulness of such a setup, but we shouldn't extrapolate
something potentially more useful from that.
Actually, I'm mostly confused about what the spec wants done. The section
on the referential actions says things like "the rows are marked for
deletion" without and I can't find something there that says whether or
not you are actually supposed to pay attention to the associated privs.
Stephan Szabo wrote:
On Sun, 1 Oct 2000, Peter Eisentraut wrote:
Stephan Szabo writes:
With that, I do have a general question though. Are referential actions
supposed to be limited by the permissions of the user executing the query?
So, if you for example have write access on the pk table, but not to the
fk table, and there is a on cascade delete relationship, should that user
not be able to delete from the pk table?Then you could delete records that are not in relation to the foreign keys
in your table. So I suppose not. Of course there does seem to be a very
limited range of usefulness of such a setup, but we shouldn't extrapolate
something potentially more useful from that.Actually, I'm mostly confused about what the spec wants done. The section
on the referential actions says things like "the rows are marked for
deletion" without and I can't find something there that says whether or
not you are actually supposed to pay attention to the associated privs.
I think the user deleting (or updating) the PK table must not
have DELETE or UPDATE permissions on the FK table. Another
user, who had ALTER permission for the FK table implicitly
granted that right due to the CASCADE definition.
The point is IMHO, that the user with the ALTER permission
for the FK table must have REFERENCE permission to the PK
table at the time he sets up the constraint. Otherwise, he
could insert references to all PK items without specifying
CASCADE and thus, deny operations on the PK table.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
On Wed, 4 Oct 2000, Jan Wieck wrote:
Stephan Szabo wrote:
On Sun, 1 Oct 2000, Peter Eisentraut wrote:
Stephan Szabo writes:
With that, I do have a general question though. Are referential actions
supposed to be limited by the permissions of the user executing the query?
So, if you for example have write access on the pk table, but not to the
fk table, and there is a on cascade delete relationship, should that user
not be able to delete from the pk table?Then you could delete records that are not in relation to the foreign keys
in your table. So I suppose not. Of course there does seem to be a very
limited range of usefulness of such a setup, but we shouldn't extrapolate
something potentially more useful from that.Actually, I'm mostly confused about what the spec wants done. The section
on the referential actions says things like "the rows are marked for
deletion" without and I can't find something there that says whether or
not you are actually supposed to pay attention to the associated privs.I think the user deleting (or updating) the PK table must not
have DELETE or UPDATE permissions on the FK table. Another
user, who had ALTER permission for the FK table implicitly
granted that right due to the CASCADE definition.The point is IMHO, that the user with the ALTER permission
for the FK table must have REFERENCE permission to the PK
table at the time he sets up the constraint. Otherwise, he
could insert references to all PK items without specifying
CASCADE and thus, deny operations on the PK table.
Actually, right now it may be denying non-owners the right to make
constraint at all. You have to be a super user or owner of each
side. I just noticed this yesterday on my CVS copy that it wouldn't
let me log in as a different user and create a table that references
another table my other user created. I haven't looked, but my guess
from the notices is that it won't let the other user place triggers
on the PK table.
I assume that you're voting on the side of if you set up a cascade you're
implicitly giving permission to modify the table through the cascade
relationship. I figure I can make it do either thing easily, it's like
four lines of code in each of the action triggers to do the change
ownership now, so I want to get an idea of what people think is the right
behavior.