pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Sequence permission suggestion
3+ messages / 2 participants
[nested] [flat]

* Sequence permission suggestion
@ 2001-11-26 00:55  Aasmund Midttun Godal <postgresql@envisity.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Aasmund Midttun Godal @ 2001-11-26 00:55 UTC (permalink / raw)
  To: pgsql-sql

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 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.

Aasmund Midttun Godal

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Sequence permission suggestion
@ 2001-11-26 01:19  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Aasmund Midttun Godal <postgresql@envisity.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2001-11-26 01:19 UTC (permalink / raw)
  To: Aasmund Midttun Godal <postgresql@envisity.com>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Sequence permission suggestion
@ 2001-11-26 05:52  Aasmund Midttun Godal <postgresql@envisity.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Aasmund Midttun Godal @ 2001-11-26 05:52 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql

I cannot argue this on a philosophical level, because I have no idea what the SQL92 papers say or don't say, and all the database books I have read generally talk very little about permissions. However allowing someone to nextval and allowing setval 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 and views etc. A functional and practical approach would be to do as you suggested. I would be very interested to know what philosophy there is about security in postgres in general - I have looked around on the lists but I can't find much discussion on this (except the TODO.privileges) - personally I find it an integral part of a database solution - what can be 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-11-26 05:52 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-26 00:55 Sequence permission suggestion Aasmund Midttun Godal <postgresql@envisity.com>
2001-11-26 01:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-26 05:52   ` Aasmund Midttun Godal <postgresql@envisity.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox