Re: Extending grant insert on tables to sequences

Abhijit Menon-Sen <ams(at)oryx(dot)com> writes:
> But I notice that nobody else has commented on whether they want this
> feature or not. Does anyone particularly dislike the idea?

I think it's probably reasonable as long as we keep the implicitly
granted rights as narrow as possible. INSERT on the parent table
would normally be hard to use correctly if you can't nextval() the
sequence, so automatically allowing nextval() seems pretty reasonable.
I think the case for granting anything more than that is weak ---
even without considering backwards-compatibility arguments.

A fairly important practical problem is whether this will keep pg_dump
from correctly reproducing the state of a database. Assume that someone
did revoke the implicitly-granted rights on the sequence --- would a
dump and reload correctly preserve that state? It'd depend on the order
in which pg_dump issued the GRANTs, and I'm not at all sure pg_dump
could be relied on to get that right. (Even if we fixed it to account
for the issue today, what of older dump scripts?)

Another issue is the interaction with the planned column-level GRANT
feature. AFAICS, the obvious-sounding rule that usage of the sequence
should be granted consequent to granting INSERT on the owning column
would be exactly backwards. It's when you have *not* got INSERT on
that column that you *must* rely on the default for it, and hence you'd
better have the ability to do nextval() or your alleged insert
privileges on other columns are worthless. So it seems that sequence
usage should be granted if any column INSERT is granted, and revoked
only when all column INSERT privileges are revoked --- and that latter
rule is going to be hard to implement with this type of patch, because
it doesn't know what column privileges are going to remain.

I thought for a bit about abandoning the proposed implementation and
instead having nextval/currval check at runtime: IOW, if the check for
ACL_USAGE on the sequence fails, look to see if the sequence is "owned"
and if so look to see if the user has ACL_INSERT on the parent table.
(This seems a bit slow but maybe it wouldn't be a problem, or maybe we
could arrange to cache the lookup results.) This would avoid the
"action at a distance" behavior in GRANT and thereby cure both of
the problems mentioned above. However, it would mean that it'd be
impossible to grant INSERT without effectively granting sequence USAGE
--- revoking USAGE on the sequence wouldn't stop anything. Plus, \z on
the sequence would fail to tell you about those implicitly held rights.
So I'm not sure I like this way any better.

regards, tom lane