Re: Controlling access to Sequences

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.