Thread: PostgreSQL 7.1 and Sequences

I've recently upgraded to 7.1 and have the following situation which no
longer works:


As user "nmnadmin":

CREATE TABLE blah {
        id NOT NULL SERIAL,
        info text NOT NULL
};

GRANT SELECT, INSERT, UPDATE, DELETE ON blah TO nmnuser;


As user "nmnuser":

INSERT INTO blah (info) VALUES ('foo');



I get the following error:
blah_id_seq.nextval: you don't have permissions to set sequence blah_id_seq



Short of actually creating the sequence as "nmnuser", is there any way to
make this work?


Cheers,

--
Alastair D'Silva (mob: 0413 485 733)
Networking Consultant
New Millennium Networking (web: http://www.newmillennium.net.au)

"Alastair D'Silva" wrote:
  >I've recently upgraded to 7.1 and have the following situation which no
  >longer works:
 ...
  >As user "nmnuser":
  >
  >INSERT INTO blah (info) VALUES ('foo');
  >
  >
  >
  >I get the following error:
  >blah_id_seq.nextval: you don't have permissions to set sequence blah_id_seq
  >
  >
  >
  >Short of actually creating the sequence as "nmnuser", is there any way to
  >make this work?

GRANT UPDATE ON blah_id_seq TO nmnuser;

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If my people, which are called by my name, shall
      humble themselves, and pray, and seek my face, and
      turn from their wicked ways; then will I hear from
      heaven, and will forgive their sin, and will heal
      their land."    II Chronicles 7:14

"Alastair D'Silva" <deece@newmillennium.net.au> writes:
> I've recently upgraded to 7.1 and have the following situation which no
> longer works:
> As user "nmnadmin":
> CREATE TABLE blah {
>         id NOT NULL SERIAL,
>         info text NOT NULL
> };
> GRANT SELECT, INSERT, UPDATE, DELETE ON blah TO nmnuser;
> As user "nmnuser":
> INSERT INTO blah (info) VALUES ('foo');
> I get the following error:
> blah_id_seq.nextval: you don't have permissions to set sequence blah_id_seq

You should also do
    GRANT UPDATE ON blah_id_seq TO nmnuser;

I am not sure whether to regard the change from 7.0 behavior as a bug or
not.  The old handling of permission-checking for sequences was pretty
broken, and I don't want to revert to it.

            regards, tom lane