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

[Mark Walker <furface@xxxxxxxxxxxx>]

If hiding the password in your application is an option, i.e. you only 
have one database your application will ever connect to, then at least 
scramble the password within your application with some complex algorithm. 

If you can't hide the password in your application, then you need to 
deny access to whatever parts of the database you don't want people to 
look at and only allow access through stored procedures.  For instance, 
you obviously wouldn't want access to your user/password table.  Grant 
access to that only to an administrator.  Then create a function like 
"checkPassword" for people to logon to your database through your 
application.  You need to do something like this for every table/row you 
want to hide.  If you don't want ANY raw access to your database, then 
create your application with stored procedures, functions, and triggers, 
basically have no raw sql in your application.  Call those procedures 
and functions from your application without any raw sql.  That's the 
only way to deal with it.

The other alternative, which I mentioned in another thread, is to create 
a proxy server application where you put your sql calls in a server that 
sits between your application and postgresql.   I'm actually designing 
such a server for Internet users who want to access my application.  
I'll probably open source it, so let me know if your interested in the 
source.  I know this is a controversial subject.  I personally prefer to 
code my applications with C++ and Java instead of server side sql, but 
of course, that's just me, and there are different situations where 
different methodologies are appropriate or not.



> If you hide the database username and password within your application 
> (i.e. encrypted within the source code) so they cannot see the 
> credentials that you connect to the database with internally then they 
> have no means by which to connect to it using any other programs.
>
> What I gather is users in your case are set up as database users 
> rather then having a users table on which your application 
> authenticates. The downside of doing it the way you are doing it is 
> always going to be that any user with a database username and password 
> can connect to the database by any means they come by. I'm no Postgres 
> expert, but I'm sure like any other RDBMS, postgres does not know, nor 
> care, what application is doing the connection but rather just accepts 
> an ODBC connection and the credentials that are passed to it.
>
> Regards,
> Paul.