After an insert statement, I need a the members of a user group
to get the recently inserted statement's primary key that was
created from a sequence, with the currval() function.
For example:

select currval('subject_subject_id_seq')

The table is "subject", the primary key name is "subject_id"
and the sequence is "subject_subject_id_seq".

The user group in question already has permission to
execute SELECT, INSERT, and UPDATE queries on the
table "subject".

I have read the documentation and searched on-line
but I have yet to figure out the correct syntax that the
"postgres" account must execute. The following is what
I have so far:

GRANT USAGE ON SEQUENCE subject_subject_id_seq TO GROUP
my_user_group;

but I get a syntax error.

Can someone help me out?

Thanks!

Ivan K. <ivan_521521 (AT) yahoo (DOT) com> wrote:
Works for me: (I'm a db-superuser)

test=# create table bla (id serial);
NOTICE: CREATE TABLE will create implicit sequence "bla_id_seq" for serial column "bla.id"
CREATE TABLE
Zeit: 438,431 ms
test=*# grant usage on bla_id_seq to kretschmer;
GRANT
Zeit: 0,400 ms
test=*# grant usage on sequence bla_id_seq to kretschmer;
GRANT
Zeit: 0,304 ms
test=*# create group my_group;
CREATE ROLE
Zeit: 15,966 ms
test=*# grant usage on sequence bla_id_seq to my_group;
GRANT
Zeit: 0,325 ms
test=*# grant usage on sequence bla_id_seq to group my_group;
GRANT
Zeit: 0,297 ms
test=*#

Please show us the *CORRECT* error-message and your pg-version, i have
8.4.2, the latest stable.




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

On Jan 1, 11:59*am, Andreas Kretschmer <akretsch... (AT) spamfence (DOT) net>
wrote:
On Jan 1, 11:59 am, Andreas Kretschmer <akretsch... (AT) spamfence (DOT) net>
wrote:
I am using 8.1.15. I execute:


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...


Recall that I need members of test_group_01 to execute the
following command to get the primary key of a recent insert
to table "bla":

select currval('bla_bla_id_seq');

Currently, members of test_group_01 cannot do this.

Thanks for taking a look!

Ivan K. wrote:
Try using
GRANT SELECT ON SEQUENCE bla_bla_id_seq TO GROUP test_group_01;

The documentation for 8.1 do not mention GRANT USAGE ON SEQUENCE, but
from 8.1 it does.

/Jens Henrik

On Jan 1, 7:08*pm, Jens Henrik Leonhard Jensen
<j... (AT) statsbiblioteket (DOT) dk> wrote:
The result:

GRANT SELECT ON SEQUENCE bla_bla_id_seq TO GROUP test_group_01;
ERROR: syntax error at or near "bla_bla_id_seq" at character 26
LINE 1: GRANT SELECT ON SEQUENCE bla_bla_id_seq TO GROUP
test_group_...

I just posted to the pgsql-general-owner (AT) postgresql (DOT) org
mailing list and got an answer:
http://archives.postgresql.org/pgsql...1/msg00031.php

The correct additional commands I needed to execute for 8.1.15
were:

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 to all who responded

Ivan K. wrote:
Apart from your original problem, which is solved now:
What if the INSERT has failed?
This technique does not look too stable to me...

Yours,
Laurenz Albe