Proposal: GRANT cascade to implicit sequences

I'd like to propose that certain GRANTs on a table cascade to the
table's implicit sequences. In the current implementation (as of
7.4.5 and 8.0.0beta3), a table owner must typically issue GRANT
statements on both the table and its sequences to allow other users
to insert records into the table. The GRANT on the sequences seems
superfluous.

Consider, for example, what's probably the most common use of
sequences: a SERIAL type representing a table's primary key:

CREATE TABLE foo (
id SERIAL PRIMARY KEY,
item VARCHAR(32) NOT NULL
);

The table owner might issue the following GRANT:

GRANT SELECT, INSERT ON foo TO otheruser;

When the other user attempts to insert a record into the table, the
insert fails:

=> INSERT INTO foo (item) VALUES ('first item');
ERROR: permission denied for sequence foo_id_seq

In addition to granting permission for the table, the table owner
must also grant permission for the sequence that represents the
primary key (UPDATE allows nextval(), SELECT allows currval()):

GRANT UPDATE, SELECT ON foo_id_seq TO otheruser;

The other user's insert now works:

=> INSERT INTO foo (item) VALUES ('first item');
INSERT 0 1

The need to issue a GRANT for the implicit sequence seems superfluous:
the ability to insert records into a table typically also implies
the ability to use the thing that generates the primary keys. I
haven't considered all cases, but it seems reasonable that at least
{GRANT | REVOKE} {INSERT | ALL} on a table should cascade to the
appropriate permissions on the table's implicit sequences.

Comments? Can anybody think of why cascading GRANT and REVOKE to
implicit sequences might be A Bad Idea?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Wed, Oct 13, 2004 at 12:37:35AM -0600, Michael Fuhr wrote:

> Comments? Can anybody think of why cascading GRANT and REVOKE to
> implicit sequences might be A Bad Idea?

In current devel sources, ALTER OWNER cascades to implicit sequences.
It may be a precedent for making GRANT and REVOKE do so too.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)

On Wed, Oct 13, 2004 at 00:37:35 -0600,
Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> Comments? Can anybody think of why cascading GRANT and REVOKE to
> implicit sequences might be A Bad Idea?

Since you can do odd things using explicit sequences, limiting implicit
sequences to make things convenient in the common case seems like a
reasonable goal.

If you go that route it may be a good idea to not allow direct grants and
revokes on implicit sequences and just have their access rights derived from
the tables.

I also think there is some merit in splitting the access rights for nextval
and setval, so that insert access grants access to nextval and update access
grants access to setval (or perhaps both nextval and setval). That way people
who can just insert in the table can't set the sequence number backwards.

Bruno Wolff III <bruno(at)wolff(dot)to> writes:

> I also think there is some merit in splitting the access rights for nextval
> and setval, so that insert access grants access to nextval and update access
> grants access to setval (or perhaps both nextval and setval). That way people
> who can just insert in the table can't set the sequence number backwards.

That might be a useful thing to do to sequences in general. Being able to
grant INSERT on a sequence to allow nextval without allowing setval could be
useful even for explicit sequences.

I can't think of a good approach for migration of old pg_dumps though, so
perhaps this is more trouble than it's worth.

Implicit sequences on the other hand can be migrated easily by ignoring all
explicit grants and just looking at the grants on the table.

--
greg

Greg Stark <gsstark(at)mit(dot)edu> writes:
> I can't think of a good approach for migration of old pg_dumps though, so
> perhaps this is more trouble than it's worth.

That would probably be the major objection to any redefinition of the
meanings of the individual sequence permissions. We could possibly
invent a couple of brand new permission bits though, and stipulate that
"UPDATE" incorporates them both.

> Implicit sequences on the other hand can be migrated easily by ignoring all
> explicit grants and just looking at the grants on the table.

It's not really that easy. Before we hack up the permissions system like
this I'd want to see a complete solution, which this is not, because it
doesn't work in the context of rules. Consider

CREATE TABLE t (id SERIAL ...);

CREATE VIEW v AS SELECT * FROM t;

CREATE RULE r AS ON INSERT TO v DO INSTEAD INSERT INTO t ...

GRANT INSERT ON v TO joeuser;

joeuser will be able to invoke the insertion rule, but nextval() will
still fail because it doesn't know about the rule context --- it'll
see joeuser as the current user, not the owner of the rule.

Eventually I'd like to replace the nextval('foo') notation with a parsed
construct foo.nextval, which is (a) Oracle compatible, (b) able to
withstand renamings of the foo sequence, and (c) amenable to having the
permissions check done during rangetable scanning, which would fix the
rule problem. There is some discussion of this in the pghackers archives.

regards, tom lane

I looked over this thread and can't see any TODO item. Having GRANT
just propogate from the table to a SERIAL sequence doesn't work because
an INSERT into a table is an UPDATE of the sequence.

Is there a TODO here?

---------------------------------------------------------------------------

Tom Lane wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > I can't think of a good approach for migration of old pg_dumps though, so
> > perhaps this is more trouble than it's worth.
>
> That would probably be the major objection to any redefinition of the
> meanings of the individual sequence permissions. We could possibly
> invent a couple of brand new permission bits though, and stipulate that
> "UPDATE" incorporates them both.
>
> > Implicit sequences on the other hand can be migrated easily by ignoring all
> > explicit grants and just looking at the grants on the table.
>
> It's not really that easy. Before we hack up the permissions system like
> this I'd want to see a complete solution, which this is not, because it
> doesn't work in the context of rules. Consider
>
> CREATE TABLE t (id SERIAL ...);
>
> CREATE VIEW v AS SELECT * FROM t;
>
> CREATE RULE r AS ON INSERT TO v DO INSTEAD INSERT INTO t ...
>
> GRANT INSERT ON v TO joeuser;
>
> joeuser will be able to invoke the insertion rule, but nextval() will
> still fail because it doesn't know about the rule context --- it'll
> see joeuser as the current user, not the owner of the rule.
>
> Eventually I'd like to replace the nextval('foo') notation with a parsed
> construct foo.nextval, which is (a) Oracle compatible, (b) able to
> withstand renamings of the foo sequence, and (c) amenable to having the
> permissions check done during rangetable scanning, which would fix the
> rule problem. There is some discussion of this in the pghackers archives.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073