Permission, select currval(seq)

[Ivan K.]

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!

[Andreas Kretschmer]

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°

[Ivan K.]

On Jan 1, 11:59 am, Andreas Kretschmer <akretsch...@spamfence.net>
wrote:

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

On Jan 1, 11:59 am, Andreas Kretschmer <akretsch...@spamfence.net>
wrote:

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

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!

[Jens Henrik Leonhard Jensen]

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

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

[Ivan K.]

On Jan 1, 7:08 pm, Jens Henrik Leonhard Jensen

<j...@statsbiblioteket.dk> wrote:
>
> Try using
> GRANT SELECT ON SEQUENCE bla_bla_id_seq TO GROUP test_group_01;

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

[Ivan K.]

I just posted to the pgsql-gen...@postgresql.org
mailing list and got an answer:
http://archives.postgresql.org/pgsql-general/2010-01/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

[Laurenz Albe]

Ivan K. wrote:
> 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')

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