From: Bruno Wolff III <bruno@wolff.to>
To: Matthew Horoschun <mhoroschun@canprint.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Controlling access to Sequences
Date: Sat, 1 Feb 2003 00:39:50 -0600
Message-ID: <20030201063950.GA21857@wolff.to> (raw)
In-Reply-To: <2759DB60-351A-11D7-92E5-000393B3A702@canprint.com.au>
References: <2759DB60-351A-11D7-92E5-000393B3A702@canprint.com.au>
On Fri, Jan 31, 2003 at 23:47:27 +1100,
Matthew Horoschun <mhoroschun@canprint.com.au> wrote:
> Hi All,
>
> I'm implementing a system where I need to be reasonably careful about
> security. One thing that worries me is SEQUENCES.
>
> My understanding is that I need to GRANT the UPDATE privilege on the
> SEQUENCE if I want a user to be able to to use nextval() on it. The
> trouble is, if they can do a nextval() they can also do a setval()
> which would allow them to set the sequence to any value they felt like
> and potentially cause problems for other user's use of the SEQUENCE.
>
> I've considered forcing the user to use the nextval() by using a RULE
> on a VIEW, but it appear as though the nextval() function still runs as
> the logged-in user not the owner of the VIEW.
>
> Is there any effective method for controlling access to a SEQUENCE? or
> should I do something like in the view:
You can limit access to nextval by only allowing access through a user
defined function that runs as the definer. If you only want them to use
the function when doing an insert into a specific table, then I am not
sure if you can do this easily. At worst you could write a function
that does the insert.
Note that there is a real issue with letting people have access to setval
over nextval. nextval can only increase the value of the sequence, while
setval can decrease it (in additon to being able to increase it). In some
cases decreasing a sequence might cause a lot more problems then a large
increase would.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, mhoroschun@canprint.com.au
Subject: Re: Controlling access to Sequences
In-Reply-To: <20030201063950.GA21857@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox