Thread: Permission; select currval('seq')

Permission; select currval('seq')

From

Ivan K

Date:

02 January 2010, 21:49:31

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!

Re: Permission; select currval('seq')

From

Adrian Klaver

Date:

02 January 2010, 22:14:37

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

Re: Permission; select currval('seq')

From

Tom Lane

Date:

02 January 2010, 22:18:28

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

Re: Permission; select currval('seq')

From

Adrian Klaver

Date:

02 January 2010, 23:20:51

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

Re: Permission; select currval('seq')

From

Ivan K

Date:

03 January 2010, 01:46:15

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