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.
>
