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@comcast.net