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/
