Обсуждение: Controlling access to Sequences
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: INSERT INTO x VALUES ( ( SELECT MAX( id ) + 1 FROM x ), some_field ); Is this safe to do? Or might this lead to undesirable problems if called concurrently? Any assistance would be appreciated. Matthew.
Matthew Horoschun <mhoroschun@canprint.com.au> writes:
> 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()
So? With enough time on your hands, you can apply nextval() often
enough to get from any point A to any point B. It seems illusory
to think that forbidding setval() makes things more secure.
regards, tom lane
Hi Tom, Thanks for the response. On Saturday, February 1, 2003, at 03:09 PM, Tom Lane wrote: > Matthew Horoschun <mhoroschun@canprint.com.au> writes: >> 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() > > So? With enough time on your hands, you can apply nextval() often > enough to get from any point A to any point B. It seems illusory > to think that forbidding setval() makes things more secure. Absolutely, You're right. nextval() is just as troublesome. I don't want to arbitrarily restrict access to setval(). I just want a safer way of handling automatic allocation of primary keys on certain tables. Should I just avoid SEQUENCES altogether and use the OIDs under normal circumstances and the MAX( id ) + 1 style thing when I need a human-usable number? Matthew.
Matthew Horoschun <mhoroschun@canprint.com.au> writes:
> Should I just avoid SEQUENCES altogether and use the OIDs under normal
> circumstances and the MAX( id ) + 1 style thing when I need a
> human-usable number?
I don't think so. MAX()+1 has more than enough problems of its own.
The real bottom line here is that you should not allow untrustworthy
users any sort of direct access to SQL commands of any kind. They
should only be allowed access to an application that issues suitably
restricted SQL commands on their behalf.
regards, tom lane
On Saturday, February 1, 2003, at 03:43 PM, Tom Lane wrote: > Matthew Horoschun <mhoroschun@canprint.com.au> writes: >> Should I just avoid SEQUENCES altogether and use the OIDs under normal >> circumstances and the MAX( id ) + 1 style thing when I need a >> human-usable number? > > I don't think so. MAX()+1 has more than enough problems of its own. > > The real bottom line here is that you should not allow untrustworthy > users any sort of direct access to SQL commands of any kind. They > should only be allowed access to an application that issues suitably > restricted SQL commands on their behalf. While I agree that would solve the problem, that is not the kind of solution we're looking for. We're in the process of porting a custom application from MySQL to PostgreSQL. The main reason for moving is that we can push all the business logic into the database and out of the application layer. That includes security. From my little experience it seems PostgreSQL has a pretty powerful security system if you apply VIEWS, RULES, SCHEMAS, and GRANT carefully. Excluding SEQUENCES, I can see no reason why you cannot lock down PostgreSQL to the point where you could allow users virtually direct access to PostgreSQL. Of course, if anybody can suggest why this isn't the case, I'm more than willing to hear why! Also, can anybody suggest in exactly what kind of situation MAX() + 1 will fail or cause problems? Excluding the performance hit (which I'm probably willing to live with). Thanks Matthew.
On Sat, 1 Feb 2003, Matthew Horoschun wrote: > > On Saturday, February 1, 2003, at 03:43 PM, Tom Lane wrote: > > > Matthew Horoschun <mhoroschun@canprint.com.au> writes: > >> Should I just avoid SEQUENCES altogether and use the OIDs under normal > >> circumstances and the MAX( id ) + 1 style thing when I need a > >> human-usable number? > > > > I don't think so. MAX()+1 has more than enough problems of its own. > > > > The real bottom line here is that you should not allow untrustworthy > > users any sort of direct access to SQL commands of any kind. They > > should only be allowed access to an application that issues suitably > > restricted SQL commands on their behalf. > > While I agree that would solve the problem, that is not the kind of > solution we're looking for. We're in the process of porting a custom > application from MySQL to PostgreSQL. The main reason for moving is > that we can push all the business logic into the database and out of > the application layer. That includes security. You can do some of the protections through security definer functions probably. > From my little experience it seems PostgreSQL has a pretty powerful > security system if you apply VIEWS, RULES, SCHEMAS, and GRANT > carefully. Excluding SEQUENCES, I can see no reason why you cannot lock > down PostgreSQL to the point where you could allow users virtually > direct access to PostgreSQL. > > Of course, if anybody can suggest why this isn't the case, I'm more > than willing to hear why! Well, there're always bugs. With direct access, it's not too hard to make a plan that returns trillions of rows or one that goes through a huge number of sort steps (each using up some amount of memory). There's still a fair level of trust going on. > Also, can anybody suggest in exactly what kind of situation MAX() + 1 > will fail or cause problems? Excluding the performance hit (which I'm > probably willing to live with). Concurrency problems. You pretty much need to either be willing to have unique key violations and retries or locking down to prevent two transactions from getting the same max()+1 value.
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.
On Sat, Feb 01, 2003 at 12:39:50AM -0600, Bruno Wolff III wrote: > On Fri, Jan 31, 2003 at 23:47:27 +1100, > Matthew Horoschun <mhoroschun@canprint.com.au> wrote: > > > > 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. Define your 'suid_nextval()' as Bruno suggests, then instead of using the 'serial' convience type, define your own that uses your function as the default, rather than nextval(). Ross