Re: Blocked inserts on tables with FK to tables for which UPDATE has been revoked

Samuel Gilbert <samuel(dot)gilbert(at)ec(dot)gc(dot)ca> 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