Samuel Gilbert <samuel.gilbert@xxxxxxxx> writes: > I have encountered a problem with inserts failing because of permissions > issues when the table in which I try to insert has foreign key constraints to > tables for which UPDATE has been revoked. Yeah, this is because the insertion has to take a row lock on the referenced row, which it does with a SELECT FOR SHARE operation. You could argue that that should require weaker privilege than UPDATE; but on the other hand mere SELECT privilege doesn't seem like enough, since a SELECT FOR SHARE can block update/delete operations. Short of inventing a new privilege type just for SELECT FOR SHARE, it's unclear what to do. Thinking about it, I'm tempted to propose that maybe SELECT FOR SHARE should be allowed if you have either UPDATE or REFERENCES privilege on the target table. The implications would need a lot more thought than I've given it though; and it certainly wouldn't be a change we'd consider back-patching. 8.2's behavior is what it is, so you'll have to live with it. > This is a pretty severe issue for me since, I don't want users that need to > input data to also have the right to modify references tables. The privileges that are relevant for the FK action are those of the owner of the referencing table. I'd suggest you consider that your data entry users probably ought not be the owners of *any* tables. They ought to be separate accounts that just have insert/update privs on the tables you want them to touch. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
