Re: Controlling access to Sequences

On Sat, 1 Feb 2003, Matthew Horoschun wrote:

>
> On Saturday, February 1, 2003, at 03:43 PM, Tom Lane wrote:
>
> > Matthew Horoschun <mhoroschun(at)canprint(dot)com(dot)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.