Permissions vs SERIAL columns
Haven't seen this discussed in a while, but I do recall it being
mentioned sometime before...
The problem:
testdb=# create table mytable (id serial, txt text);
testdb=# grant insert on mytable to user2;
GRANT
testdb=# \connect testdb user2
You are now connected to database "testdb" as user "user2".
testdb=> insert into mytable (txt) values ('foobar');
ERROR: permission denied for sequence mytable_id_seq
What I'd like to happen is for the grant for INSERT on the table to
cascade into an UPDATE permission on the sequence (when associated with
a SERIAL column only, of course).
Coming from a different database, such as MSSQL, makes people forget
this very easily, and it becomes very annoying.
Is this something that can be done without too much work? Anything you
can do in current pg even, just me not knowing how?
//Magnus
If nothing else, this should at least be documented in
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
On Fri, Dec 30, 2005 at 07:32:18PM +0100, Magnus Hagander wrote:
Haven't seen this discussed in a while, but I do recall it being
mentioned sometime before...The problem:
testdb=# create table mytable (id serial, txt text);
testdb=# grant insert on mytable to user2;
GRANT
testdb=# \connect testdb user2
You are now connected to database "testdb" as user "user2".
testdb=> insert into mytable (txt) values ('foobar');
ERROR: permission denied for sequence mytable_id_seqWhat I'd like to happen is for the grant for INSERT on the table to
cascade into an UPDATE permission on the sequence (when associated with
a SERIAL column only, of course).Coming from a different database, such as MSSQL, makes people forget
this very easily, and it becomes very annoying.Is this something that can be done without too much work? Anything you
can do in current pg even, just me not knowing how?//Magnus
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote:
If nothing else, this should at least be documented in
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
I looked at this URL and just didn't see a good place to talk about
SERIAL sequence permissions, so I added something to the GRANT manual
page, attached.
---------------------------------------------------------------------------
On Fri, Dec 30, 2005 at 07:32:18PM +0100, Magnus Hagander wrote:
Haven't seen this discussed in a while, but I do recall it being
mentioned sometime before...The problem:
testdb=# create table mytable (id serial, txt text);
testdb=# grant insert on mytable to user2;
GRANT
testdb=# \connect testdb user2
You are now connected to database "testdb" as user "user2".
testdb=> insert into mytable (txt) values ('foobar');
ERROR: permission denied for sequence mytable_id_seqWhat I'd like to happen is for the grant for INSERT on the table to
cascade into an UPDATE permission on the sequence (when associated with
a SERIAL column only, of course).Coming from a different database, such as MSSQL, makes people forget
this very easily, and it becomes very annoying.Is this something that can be done without too much work? Anything you
can do in current pg even, just me not knowing how?//Magnus
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Attachments:
/bjm/difftext/plainDownload
Index: doc/src/sgml/ref/grant.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.51
diff -c -c -r1.51 grant.sgml
*** doc/src/sgml/ref/grant.sgml 21 Jan 2006 02:16:18 -0000 1.51
--- doc/src/sgml/ref/grant.sgml 14 Feb 2006 03:30:42 -0000
***************
*** 376,381 ****
--- 376,388 ----
</para>
<para>
+ Granting permission on a table does not automatically extend
+ permissions to any sequences used by the table, including
+ sequences tied to <type>SERIAL</> columns. Permissions on
+ sequence must be set separately.
+ </para>
+
+ <para>
Currently, <productname>PostgreSQL</productname> does not support
granting or revoking privileges for individual columns of a table.
One possible workaround is to create a view having just the desired