Actually in theory it is possible to completely secure a database by
putting all your business logic in stored procedures/functions and
allowing only raw database access to administrators. Plenty of people
do this. In fact if I were designing something that had lots of users
who had relatively simple and repetitive interactions with my database,
for instance a financial banking system, I would probably design it that
way.
Actually I don't know a lot about banking systems. I'm just guessing
that the types of stuff I do at ATMs and online at my bank are not as
complex as ERP and billing systems that I design for some of my
customers. Banking systems I would think have problems of massive
amounts of users, replication, synchronization, fault tolerance, and
security which are different than problems of pure business logic.
But anyway, you can hide any table you want completely from any role in
any applications with proper use of the Grant statement. You then meter
your access through your procedures. This, again, is an issue of where
you want your business logic to reside, in your client application or on
the server. As I have said in a previous thread, I prefer to code
client applications in languages I'm very familiar with like C++ and
Java. However, I wouldn't go as far as to say that's the "right" way to
do it.
>>
>> 1. Each user has a postgresql role in a way that I mentioned in a
>> previous thread concerning the limit on number of users. You'd also
>> have to secure your database via stored procedures and individual
>> table role based access.
>
> This solution won't help the initial problem of users being able to
> connect with programs other then the original posters application. If
> the user has a role in Postgres and they know the username/password -
> which surely they will - then they will be able to connect using
> pgAdminIII, M$ Access, M$ Excel, any other program that can open an
> ODBC connection to look at and update a db which would then bypass any
> business rules that have been built into the main application.
>
