Re: Permissions vs SERIAL columns

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_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
> >
> > ---------------------------(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 19073
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