X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A75CF475B8A for ; Sat, 1 Feb 2003 01:27:57 -0500 (EST) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 65420475BA0 for ; Sat, 1 Feb 2003 01:26:56 -0500 (EST) Received: (qmail 22242 invoked by uid 500); 1 Feb 2003 06:39:50 -0000 Date: Sat, 1 Feb 2003 00:39:50 -0600 From: Bruno Wolff III To: Matthew Horoschun Cc: pgsql-sql@postgresql.org Subject: Re: Controlling access to Sequences Message-ID: <20030201063950.GA21857@wolff.to> Mail-Followup-To: Matthew Horoschun , pgsql-sql@postgresql.org References: <2759DB60-351A-11D7-92E5-000393B3A702@canprint.com.au> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <2759DB60-351A-11D7-92E5-000393B3A702@canprint.com.au> User-Agent: Mutt/1.3.25i X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/4 X-Sequence-Number: 11402 On Fri, Jan 31, 2003 at 23:47:27 +1100, Matthew Horoschun 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.