Bug or feature?

Olivier PRENANT <ohp@pyrenet.fr> writes:

I've just noticed that (after a upgrade from 7.0.3 to 7.1) the following
did'nt work anymore:

create tabla a (n1 serial, n2 int);
grant all on a to nobody;

<reconnect as user nobody>

insert into a (n2) value (1);
n1.nextval: you don't have permission to set sequence n1

It worked on 7.0.3

You'll have to grant update rights on the sequence object to nobody ...

regards, tom lane

Hi Tom,

Thanks for your quick reply. However, I knew (and did that). My post were
more ... philosophical:

Shoudn't postgres extend priviledges to the sequences generated by a
create table ???

Regards,

On Fri, 11 May 2001, Tom Lane wrote:

Olivier PRENANT <ohp@pyrenet.fr> writes:

I've just noticed that (after a upgrade from 7.0.3 to 7.1) the following
did'nt work anymore:

create tabla a (n1 serial, n2 int);
grant all on a to nobody;

<reconnect as user nobody>

insert into a (n2) value (1);
n1.nextval: you don't have permission to set sequence n1

It worked on 7.0.3

You'll have to grant update rights on the sequence object to nobody ...

regards, tom lane

--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

Olivier PRENANT <ohp@pyrenet.fr> writes:

Shoudn't postgres extend priviledges to the sequences generated by a
create table ???

That's not clear. The sequence is an independent object. Had you
explicitly done

CREATE SEQUENCE myseq;

CREATE TABLE mytab (f1 int default nextval('myseq'));

would you expect that granting permissions on mytab automatically
grants them on myseq as well? I think you might consider that
surprising. But there isn't any difference between this and what
CREATE TABLE does.

There have been suggestions in the past that SERIAL should be a "real
data type" with the sequence object being hidden more effectively than
it is now --- including auto-dropping it at table deletion, etc.
If that were to happen then the permissions issue would probably go away
too. It doesn't seem to be a very high priority for anyone, though.

regards, tom lane

On Sat, 12 May 2001, Tom Lane wrote:

Olivier PRENANT <ohp@pyrenet.fr> writes:

Shoudn't postgres extend priviledges to the sequences generated by a
create table ???

That's not clear. The sequence is an independent object. Had you
explicitly done

CREATE SEQUENCE myseq;

CREATE TABLE mytab (f1 int default nextval('myseq'));

would you expect that granting permissions on mytab automatically
grants them on myseq as well? I think you might consider that
surprising. But there isn't any difference between this and what
CREATE TABLE does.

I'm aware of that.

There have been suggestions in the past that SERIAL should be a "real
data type" with the sequence object being hidden more effectively than
it is now --- including auto-dropping it at table deletion, etc.
If that were to happen then the permissions issue would probably go away
too. It doesn't seem to be a very high priority for anyone, though.

IMHO, this would be "cleanner".
1) When you have lots of auto generated sequence, it becomes diffcult to
track the ones you have to drop if you drop tables.
2) This ACL problem could disapear if serial were a real type.

Anyway what I'm concerned with is that I had no problems until I dumped
from 7.0.3 and reloaded if 7.1.

Regards

regards, tom lane

--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)