From: | Adrian Klaver <aklaver(at)comcast(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Ivan K <ivan_521521(at)yahoo(dot)com> |
Subject: | Re: Permission; select currval('seq') |
Date: | 2010-01-02 23:14:26 |
Message-ID: | 201001021514.26910.aklaver@comcast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Saturday 02 January 2010 2:44:34 pm Ivan K wrote:
> I need to have members of a particular user
> group insert rows into a table and then determine the
> recently inserted statement's primary key that was
> created from a sequence with the currval() function:
>
> select currval('bla_bla_id_seq');
>
> I have been unable to set these permissions.
> I am using 8.1.15 and as the db superuser "postgres".
> I execute the following:
>
>
> test=# CREATE GROUP test_group_01;
> CREATE ROLE
>
> test=# ALTER GROUP test_group_01 ADD USER ivan;
> ALTER ROLE
>
> test=# CREATE TABLE bla (bla_id serial);
> NOTICE: CREATE TABLE will create implicit sequence "bla_bla_id_seq"
> for serial column "bla.bla_id"
> CREATE TABLE
>
> test=# GRANT USAGE ON SEQUENCE bla_bla_id_seq TO GROUP test_group_01;
> ERROR: syntax error at or near "bla_bla_id_seq" at character 25
> LINE 1: GRANT USAGE ON SEQUENCE bla_bla_id_seq TO GROUP
> test_group_0...
> ^
> test=# GRANT SELECT ON bla TO GROUP test_group_01;
> GRANT
> test=# GRANT INSERT ON bla TO GROUP test_group_01;
> GRANT
>
> test=# GRANT USAGE ON SEQUENCE bla_bla_id_seq TO GROUP test_group_01;
> ERROR: syntax error at or near "bla_bla_id_seq" at character 25
> LINE 1: GRANT USAGE ON SEQUENCE bla_bla_id_seq TO GROUP
> test_group_0...
>
> Why is the "GRANT USAGE ON SEQUENCE" statement failing?
> User "ivan" can insert and update table "bla" but cannot execute
> "select currval('bla_bla_id_seq');" What does the db superuser
> need to execute?
>
> Thank you for your help!
From here;
http://www.postgresql.org/docs/8.1/interactive/sql-grant.html
GRANT USAGE is for procedural languages and SCHEMA.
You will need to use the GRANT SELECT|INSERT ON TABLE bla_bla_id_seq form.
Sequences being a type of table and not having their own GRANT form until later
versions.
--
Adrian Klaver
aklaver(at)comcast(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-01-02 23:18:14 | Re: Permission; select currval('seq') |
Previous Message | John R Pierce | 2010-01-02 22:55:55 | Re: please help |