Re: Extending grant insert on tables to sequences

Sorry for the delay in the answer but i was busy with 2 projects and a talk...

On Sat, Jul 12, 2008 at 3:50 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.
>

ok. at least two more reviewers make questions against the SELECT permission...
my point was that if keep INSERT and UPDATE permissions then keep
SELECT as well... but let *only* INSERT's seems enough to me...

> 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?)
>

good point! a simple test make me think that yes, i will try some
complex cases to be sure (actually i think it should be a problem
here)

> Another issue is the interaction with the planned column-level GRANT
> feature.
>

Although that is a feature we want, is a WIP one... do we stop patches
because it can conflict with a project we don't know will be applied
soon?

In any case, i will review that patch to see where we are on that and
to try make those two compatible...

>
> 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.

seems like a hackish... do we want this? comments?

i will work on this patch for the next days...

--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157