Re: Extending grant insert on tables to sequences

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > * Jaime Casanova (jcasanov(at)systemguards(dot)com(dot)ec) wrote:
> >> updating the patch with one that only extends inserts. though, i
> >> haven't look at the col level privs patch yet.
>
> > At least initially I wasn't planning to support column-level privileges
> > for sequences, so I don't think it will affect you much. Do people
> > think it makes sense to try and support that?
>
> USAGE certainly wouldn't be column-level in any case --- it'd be a
> privilege on the sequence as such. That end of it isn't the problem;
> the problem is that column-level privileges on the table make it hard to
> decide when to grant rights on the sequence, as I pointed out last time
> round:
> http://archives.postgresql.org/pgsql-hackers/2008-07/msg00624.php

Ah, obviously I hadn't read far enough back about this patch. I agree
that sequence USAGE should be granted when insert is granted on any
column. One suggestion is that as the SQL spec indicates that a
table-level revoke implies a revoke on all columns, we could have the
revokation of the sequence permissisons done only on table-level
revokation of insert and not on any individual column-level insert, even
if that was the last column which insert rights were granted on.

I have to admit that I'm not a big fan of that though because a given
state on the table wouldn't imply a particular state for the sequence-
it would depend on how you got there. The way the code is currently
laid out for the column-level privileges, it wouldn't be that difficult
to go through all of the other columns and check if this was the last
insert being revoked, but I don't particularly like that either, and
it strikes me as 99% of the time being wasted effort. I guess if we
could check for and only go through that effort when there is a sequence
in place with implicit grants it might not be too bad.

> > As your patch appears more ready-for-commit than the column-level
> > privileges patch, I wouldn't worry about what code might have to move
> > around, that'll be for me to deal with in a re-sync with HEAD once your
> > patch is committed.
>
> I think that's backwards. The above message raises serious concerns
> about whether the USAGE-granting patch can be implemented at all in the
> presence of column-level privileges. I think the right thing is to get
> column privileges in and then see if it's possible to implement
> USAGE-granting compatibly. I don't want to commit a patch that is
> clearly going to be broken when (not if) column privileges arrive.

Now that I understand the situation better, I agree with you on this. I
hadn't realized this patch was about implicit grants on sequnces. Sorry
for the noise.

Thanks,

Stephen