Re: [SQL] Foreign keys breaks tables permissions

"Stephan Szabo" <sszabo(at)kick(dot)com> writes:
> I believe the reason that the trigger does a select for update was
> because otherwise there could exist a case that we select and see it
> and then have the row go away afterwards because nothing stops the
> delete.

Hmm, good point. And I think I see the reason for the protection
logic as well: if you can do SELECT FOR UPDATE then you can acquire
a lock that will block a competing writer. Therefore, even though
you can't modify the table, you can create the same sort of denial-
of-service attack that someone with real UPDATE privileges could
create, just by leaving your transaction open.

So, either we live with update requiring update rights on the
table referenced as a foreign key, or we break something else.
Grumble.

Probably the denial-of-service argument is the weakest of the three
points. Is anyone in favor of reducing SELECT FOR UPDATE to only
requiring "SELECT" rights, and living with the possible lock-that-
you-shouldn't-really-have-been-able-to-get issue?

regards, tom lane