Обсуждение: Permission denied for sequece...

Hey,

I'm in the process of porting an app from MySQL to PostgreSQL and am
pretty new with PostgreSQL and am having permission problems.

First, a little background info.
- The postmaster is set to startup with -i and i'm able to actually
connect without an exception.
- I've added the following lines to the pg_hba.conf files (for testing purposes)
local all all trust
host all all 127.0.0.1 255.255.255.255 trust

I create my db, as follows:
createdb testdb

then i log into psql and run the following
CREATE USER testuser PASSWORD 'default';
GRANT ALL ON DATABASE testdb TO testuser;
GRANT ALL ON [my table names] TO testuser;

In my app i connect to the db with the following, which is run without issue.
connection = DriverManager.getConnection("jdbc:postgresql:testdb",
"testuser", "default");

I then call the following insert
INSERT INTO Property (Name,Value) VALUES ("some name", "some value")

and get the following error

org.postgresql.util.PSQLException: ERROR: permission denied for
sequence property_propertyid_seq

The sql used to create the property table is

CREATE TABLE Property
    (PropertyID SERIAL PRIMARY KEY,
    Name VARCHAR(64) UNIQUE NOT NULL,
    Value VARCHAR(255) NOT NULL);

Can someone tell me what would cause this error message to occur and
suggest ways that i could fix it?

Thanks,

Jon Horsman

On Mon, 2006-08-21 at 14:03, Jon Horsman wrote:
> Hey,
>
> I'm in the process of porting an app from MySQL to PostgreSQL and am
> pretty new with PostgreSQL and am having permission problems.
>
> First, a little background info.
> - The postmaster is set to startup with -i and i'm able to actually
> connect without an exception.
> - I've added the following lines to the pg_hba.conf files (for testing purposes)
> local all all trust
> host all all 127.0.0.1 255.255.255.255 trust
>
> I create my db, as follows:
> createdb testdb
>
> then i log into psql and run the following
> CREATE USER testuser PASSWORD 'default';
> GRANT ALL ON DATABASE testdb TO testuser;
> GRANT ALL ON [my table names] TO testuser;
>
> In my app i connect to the db with the following, which is run without issue.
> connection = DriverManager.getConnection("jdbc:postgresql:testdb",
> "testuser", "default");
>
> I then call the following insert
> INSERT INTO Property (Name,Value) VALUES ("some name", "some value")
>
> and get the following error
>
> org.postgresql.util.PSQLException: ERROR: permission denied for
> sequence property_propertyid_seq
>
> The sql used to create the property table is
>
> CREATE TABLE Property
>     (PropertyID SERIAL PRIMARY KEY,
>     Name VARCHAR(64) UNIQUE NOT NULL,
>     Value VARCHAR(255) NOT NULL);
>
> Can someone tell me what would cause this error message to occur and
> suggest ways that i could fix it?

run psql to your db and do a \d on that table.  You should see something
like this:

 Column |  Type   |                      Modifiers
--------+---------+------------------------------------------------------
 id     | integer | not null default nextval('public.real_id_seq'::text)

Note that my table, real, has a sequence named read_id_seq.  You need to
grant all on that as well...

>  Column |  Type   |                      Modifiers
> --------+---------+------------------------------------------------------
>  id     | integer | not null default nextval('public.real_id_seq'::text)
>
> Note that my table, real, has a sequence named read_id_seq.  You need to
> grant all on that as well...

That did it.  Thanks a lot.  I _never_ would have guessed that and
don't recall seeing it in the docs, perhaps i missed it.

While on the topic.  Is there one blanket command that can be run to
grant the user permissions to do everything to the db, without making
that user the owner or a superuser?

Originally i would have thought 'GRANT ALL ON dbname TO user' would
have done this for me, but its obvious it doesn't work that way.

Jon.

On Mon, 2006-08-21 at 17:27, Jon Horsman wrote:
> >  Column |  Type   |                      Modifiers
> > --------+---------+------------------------------------------------------
> >  id     | integer | not null default nextval('public.real_id_seq'::text)
> >
> > Note that my table, real, has a sequence named read_id_seq.  You need to
> > grant all on that as well...
>
> That did it.  Thanks a lot.  I _never_ would have guessed that and
> don't recall seeing it in the docs, perhaps i missed it.

I do think it's in the docs, but I'm not prepared to bet on it.  :)  It
is a rather inobvious thing though.

> While on the topic.  Is there one blanket command that can be run to
> grant the user permissions to do everything to the db, without making
> that user the owner or a superuser?

No, there is no single statement.  But there is pl/pgsql.  If you search
the archives there are examples of some fairly simple pl/pgsql scripts
that do just that.  There might be provisions for such a built in in
8.2, due out soon.  I haven't kept up on new pgsql development since
taking a rather busy job working on 7.4 machines.  I'm really hoping to
get an 8.1/8.2 server up and running for work here as a data warehouse
though...