Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA86594; Fri, 19 May 2000 13:44:21 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id NAA08841; Fri, 19 May 2000 13:44:15 -0400 (EDT) To: Peter Eisentraut cc: Jan Wieck , PostgreSQL Development , pgsql-sql@postgresql.org Subject: Re: Foreign keys breaks tables permissions In-reply-to: References: Comments: In-reply-to Peter Eisentraut message dated "Fri, 19 May 2000 19:39:14 +0200" Date: Fri, 19 May 2000 13:44:15 -0400 Message-ID: <8838.958758255@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200005/85 Peter Eisentraut writes: >> 1. Why is RI_FKey_check() using SELECT FOR UPDATE and not plain SELECT? >> 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. I don't like it either, but if an FK check must use SELECT FOR UPDATE then anyone who can trigger an FK check has the ability to create a write-class lock on the referenced table. Wrapping the FK check in a SETUID trigger doesn't change that fundamental fact; it'll just mean that the user triggering the check is now able to create a lock that he doesn't have the privileges to create directly. This is perhaps the least undesirable of the choices we have, but it's still a security hole. regards, tom lane