Permission; select currval('seq')

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

Ivan K <ivan_521521@yahoo.com> writes:

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?

Because there's no such command in 8.1.x.

IIRC you need "grant select on table ..." instead, but try reading
the appropriate version of the manual.

regards, tom lane

Yes, that was the ticket; the commands I needed to
execute are as follows:

GRANT UPDATE ON bla_bla_id_seq TO GROUP test_group_01;
GRANT SELECT ON bla_bla_id_seq TO GROUP test_group_01;
GRANT INSERT ON bla_bla_id_seq TO GROUP test_group_01;

Thanks!

--- On Sat, 1/2/10, Adrian Klaver <aklaver@comcast.net> wrote:

On Saturday 02 January 2010 3:57:40 pm Ivan K wrote:

Yes, that was the ticket; the commands I needed to
execute are as follows:

GRANT UPDATE ON bla_bla_id_seq TO GROUP test_group_01;
GRANT SELECT ON bla_bla_id_seq TO GROUP test_group_01;
GRANT INSERT ON bla_bla_id_seq TO GROUP test_group_01;

Thanks!

For completeness if you only need select currval() permissions, then you only
need to GRANT SELECT.

--
Adrian Klaver
aklaver@comcast.net