Re: How to allow users to log on only from my application not from pgadmin

[Mark Walker <furface@xxxxxxxxxxxx>]

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.