Thread: Granting permission on a sequence to a group

Granting permission on a sequence to a group

From

"Walker, Jed S"

Date:

21 April 2005, 23:17:42

Hi,

I have a sequence seq_page owned by user vrnsys. Vrnsys has granted "select" on seq_page to group vrn_admin. vrn_admin group has had user vrngui added to it, but vrngui is not able to access the sequence (permission denied for sequence seq_page).

1. Any ideas why this isn't working?

Thanks in advance,

Jed S. Walker

Re: Granting permission on a sequence to a group

From

Michael Fuhr

Date:

21 April 2005, 23:46:32

On Thu, Apr 21, 2005 at 03:50:04PM -0600, Walker, Jed S wrote:
>
> I have a sequence seq_page owned by user vrnsys. Vrnsys has granted "select"
> on seq_page to group vrn_admin. vrn_admin group has had user vrngui added to
> it, but vrngui is not able to access the sequence (permission denied for
> sequence seq_page).

vrngui is probably trying to execute nextval('seq_page').  SELECT
privilege on a sequence allows currval(), but nextval() requires
UPDATE privilege.  Try this:

  GRANT SELECT, UPDATE ON seq_page TO GROUP vrn_admin;

For more information, see the documentation for GRANT:

http://www.postgresql.org/docs/8.0/interactive/sql-grant.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Granting permission on a sequence to a group

From

"Walker, Jed S"

Date:

22 April 2005, 14:21:52

I found a note in a book that states that using "nextval" can't be done with only select privileges, but it doesn't say what else is needed. Can someone tell me (hopefully I find it eventually)?

_____________________________________________
From: Walker, Jed S
Sent:   Thursday, April 21, 2005 3:50 PM
To:     'pgsql-novice@postgresql.org'
Subject:        Granting permission on a sequence to a group

Hi,

1. Any ideas why this isn't working?

Thanks in advance,

Jed S. Walker

Re: Granting permission on a sequence to a group

From

"Walker, Jed S"

Date:

22 April 2005, 14:30:14

OK, I think I figured this out, but if someone can confirm that this is the correct way to do this I'd appreciate it.

Since a sequence in postgres is actually a table, I realized that to pull a value off and have it increment you must be able to update the table. So I granted select and update on the sequence to the group, and now the user's in the group are able to use nextval().

Is this definitely the correct way to handle this?

The thing I really don't like about this is that now the user's in that group can perform setval() on the sequence! That's seem like a big risk.

If someone can confirm my solution or give me the proper way to do this I would greatly appreciate it.

Thanks again,

Jed.

_____________________________________________
From: Walker, Jed S
Sent:   Friday, April 22, 2005 8:21 AM
To:     Walker, Jed S; 'pgsql-novice@postgresql.org'
Subject:        RE: Granting permission on a sequence to a group

I found a note in a book that states that using "nextval" can't be done with only select privileges, but it doesn't say what else is needed. Can someone tell me (hopefully I find it eventually)?

Hi,

1. Any ideas why this isn't working?

Thanks in advance,

Jed S. Walker

Re: Granting permission on a sequence to a group

From

Bruno Wolff III

Date:

22 April 2005, 14:54:50

On Fri, Apr 22, 2005 at 08:29:35 -0600,
  "Walker, Jed S" <Jed_Walker@cable.comcast.com> wrote:
> correct way to do this I'd appreciate it.
>
> Since a sequence in postgres is actually a table, I realized that to pull a
> value off and have it increment you must be able to update the table. So I
> granted select and update on the sequence to the group, and now the user's
> in the group are able to use nextval().
>
> Is this definitely the correct way to handle this?
>
> The thing I really don't like about this is that now the user's in that
> group can perform setval() on the sequence! That's seem like a big risk.
>
> If someone can confirm my solution or give me the proper way to do this I
> would greatly appreciate it.

This has been discussed in the past. My memory is that people were amenable
to using INSERT to give access to nextval and UPDATE to give access to
setval. But I don't think anyone volunteered to do this.

Re: Granting permission on a sequence to a group

From

tövis

Date:

22 April 2005, 15:01:41

I loose the track! What you need first, evaluate sequence or evaluate table by user?

Somewhere - on Debian packet management - I have seen a contributed sw to disable/enable modify even a sinlge column (work on Linux).

Users are mostly can modify anything through your application, is not it? Sequencies are specifik to PostgreSQL no third parti tools for manipulate them such as MS-Access...

----- Original Message -----
From: Walker, Jed S
To: 'pgsql-novice@postgresql.org'
Sent: Friday, April 22, 2005 4:29 PM
Subject: Re: [NOVICE] Granting permission on a sequence to a group

OK, I think I figured this out, but if someone can confirm that this is the correct way to do this I'd appreciate it.
Since a sequence in postgres is actually a table, I realized that to pull a value off and have it increment you must be able to update the table. So I granted select and update on the sequence to the group, and now the user's in the group are able to use nextval().
Is this definitely the correct way to handle this?
The thing I really don't like about this is that now the user's in that group can perform setval() on the sequence! That's seem like a big risk.
If someone can confirm my solution or give me the proper way to do this I would greatly appreciate it.
Thanks again,
Jed.
_____________________________________________
From: Walker, Jed S
Sent:   Friday, April 22, 2005 8:21 AM
To:     Walker, Jed S; 'pgsql-novice@postgresql.org'
Subject:        RE: Granting permission on a sequence to a group
I found a note in a book that states that using "nextval" can't be done with only select privileges, but it doesn't say what else is needed. Can someone tell me (hopefully I find it eventually)?
_____________________________________________
From: Walker, Jed S
Sent:   Thursday, April 21, 2005 3:50 PM
To:     'pgsql-novice@postgresql.org'
Subject:        Granting permission on a sequence to a group
Hi,
I have a sequence seq_page owned by user vrnsys. Vrnsys has granted "select" on seq_page to group vrn_admin. vrn_admin group has had user vrngui added to it, but vrngui is not able to access the sequence (permission denied for sequence seq_page).
1. Any ideas why this isn't working?
Thanks in advance,
Jed S. Walker

Re: Granting permission on a sequence to a group

From

"Walker, Jed S"

Date:

22 April 2005, 16:47:43

That seems like a good way to handle it. Thanks for the info!

-----Original Message-----
From: Bruno Wolff III [mailto:bruno@wolff.to]
Sent: Friday, April 22, 2005 9:14 AM
To: Walker, Jed S
Cc: 'pgsql-novice@postgresql.org'
Subject: Re: Granting permission on a sequence to a group

On Fri, Apr 22, 2005 at 08:29:35 -0600,
  "Walker, Jed S" <Jed_Walker@cable.comcast.com> wrote:
> correct way to do this I'd appreciate it.
>
> Since a sequence in postgres is actually a table, I realized that to
> pull a value off and have it increment you must be able to update the
> table. So I granted select and update on the sequence to the group,
> and now the user's in the group are able to use nextval().
>
> Is this definitely the correct way to handle this?
>
> The thing I really don't like about this is that now the user's in
> that group can perform setval() on the sequence! That's seem like a big
risk.
>
> If someone can confirm my solution or give me the proper way to do
> this I would greatly appreciate it.

This has been discussed in the past. My memory is that people were amenable
to using INSERT to give access to nextval and UPDATE to give access to
setval. But I don't think anyone volunteered to do this.