Обсуждение: Sequence permission suggestion

Today it seems that the following  rules governs the permissions of sequences:

SELECT - allow you to select * from seq_name;
INSERT - no effect
UPDATE - allows nextval, setval - and yes implicitly currval
DELETE - no effect

I suggest nextval is moved into the insert permission as this is quite natural - normally you only need to call nextval
whenyou insert a new value into a table. I think this issue is important because setting the sequence to a value lower
thanthe max value in a table will cause errors for other users.
 

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

"Aasmund Midttun Godal" <postgresql@envisity.com> writes:
> Today it seems that the following  rules governs the permissions of sequences:
> SELECT - allow you to select * from seq_name;
> INSERT - no effect
> UPDATE - allows nextval, setval - and yes implicitly currval
> DELETE - no effect

> I suggest nextval is moved into the insert permission as this is quite
> natural

It looks like an update to me; in particular, it allows you to determine
the old state of the sequence, and thus has read+write semantics not
only write semantics.

> normally you only need to call nextval when you insert a new value
> into a table. I think this issue is important because setting the
> sequence to a value lower than the max value in a table will cause
> errors for other users.

This seems to be exceedingly SERIAL-centric thinking; there are other
uses for sequences than serials.

It might make sense to allow either INSERT or UPDATE permission to
grant nextval() access, while still requiring UPDATE for setval().
But I'm not convinced yet ...
        regards, tom lane

I cannot argue this on a philosophical level, because I have no idea what the SQL92 papers say or don't say, and all
thedatabase books I have read generally talk very little about permissions. However allowing someone to nextval and
allowingsetval is to radically different things - perhaps comparable to inserting or updating a table, even though
both,technically, are updates. 
 

There are many ways to attack this issue one could have different set of permission names on a sequence than on tables
andviews etc. A functional and practical approach would be to do as you suggested. I would be very interested to know
whatphilosophy there is about security in postgres in general - I have looked around on the lists but I can't find much
discussionon this (except the TODO.privileges) - personally I find it an integral part of a database solution - what
canbe done and who can do it. If anyone can shed any light on this issue, I would greatly appreciate it!
 

Regards,

Aasmund




On Sun, 25 Nov 2001 20:19:22 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Aasmund Midttun Godal" <postgresql@envisity.com> writes:
> 
> 
> It looks like an update to me; in particular, it allows you to determine
> the old state of the sequence, and thus has read+write semantics not
> only write semantics.
> 
> 
> This seems to be exceedingly SERIAL-centric thinking; there are other
> uses for sequences than serials.
> 
> It might make sense to allow either INSERT or UPDATE permission to
> grant nextval() access, while still requiring UPDATE for setval().
> But I'm not convinced yet ...
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46