Thread: Permissions vs SERIAL columns

Permissions vs SERIAL columns

From
"Magnus Hagander"
Date:
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


Re: Permissions vs SERIAL columns

From
"Jim C. Nasby"
Date:
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_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


Re: Permissions vs SERIAL columns

From
Bruce Momjian
Date:
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