Re: Controlling access to Sequences

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.

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.