Re: Foreign keys breaks tables permissions - Mailing list pgsql-sql

From Peter Eisentraut
Subject Re: Foreign keys breaks tables permissions
Date
Msg-id Pine.LNX.4.21.0005191309580.489-100000@localhost.localdomain
Whole thread Raw
In response to Re: Foreign keys breaks tables permissions  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Foreign keys breaks tables permissions
List pgsql-sql
Tom Lane writes:

> I looked at this some more because people were complaining that it
> was still broken in 7.0.  AFAICT, it's got nothing to do with SETUID
> triggers or anything so hairy, it's just a question of what permissions
> we think ought to be required for which actions.

Since the foreign keys are implemented in semi-userspace the triggers will
either have to abide by the userspace privilege rules (not really good,
see below), circumvent the privilege system (e.g., not use SPI, but scan
the table yourself; probably no good), or be given special privileges,
i.e., setuid or similar.

In SQL land the privilege required for a foreign key *definition* is
REFERENCES, once you have it set up, no further privileges are required to
do the referencing. That makes some sense because changes to the FK table
never change the PK table, only the other way around.

> 1. Why is RI_FKey_check() using SELECT FOR UPDATE and not plain SELECT?

AFAIU this function checks upon changes to the FK table whether a PK
exists. In don't think you need to lock the PK table for that because once
you know the PK existed at some point during the insert/update you have
satisfied the requirement. If someone mangles the PK while you're still
running then any ignited delete or update on the FK table will block with
the normal lock mechanisms.

> 2. What permissions should SELECT FOR UPDATE require?

UPDATE seems reasonable. SELECT is no good because it would give read-only
users the locking power of users with write access.

> If the existing code is correct on both these points, then I think the
> answer is that there is no bug: updating a table that has a foreign
> key reference will require update rights on the master as well.

I don't think that's acceptable.


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: SQL command speed
Next
From: Peter Eisentraut
Date:
Subject: Re: Re[2]: lower() for varchar data by creating an index