Re: How to allow users to log on only from my application not from pgadmin - Mailing list pgsql-general

Bruno Wolff III wrote:
> On Thu, Feb 01, 2007 at 10:24:51 +0900,
>   Paul Lambert <paul.lambert@autoledgers.com.au> wrote:
>> 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.
>
> This is not real security. Encrypting the data in the application only works
> if the application is running on a computer you control. If the "customer"
> can get their own copy of the client and run it on a computer they control
> then they can steal or borrow the applications credentials.

How?

If it is encrypted within the source code then the only way to steal the
credentials would be to reverse engineer the application. And if someone
is going to do that then you can be relatively assured that they are
going to do anything and everything to get around whatever other
security you can offer. At which point you could send the law after them
for breach of copyright or other such law - at least that is the case
down here in Australia.

We have an application which connects to a database in MySQL. Each user
has their own username/password to log onto the application which does
so through authenticating against a users table in the db. The
application itself has hard-coded within a username/password to get the
initial access to the database. With somewhere in the vicinity of 1,000
people using this particular application we've not seen a case of anyone
accessing it using anything other than our application.

>
> You want to either run the app on a computer you control

It's not always feasible to host the application main on your own
server. Depending on network distance, traffic, size of application,
number of users etc, it could require some extremely high spec hardware
to host and beefed up network connections. This is not possible for a
lot of service providers out there, not to mention that those willing to
reverse engineer the software (or run packet sniffers and decrypt
network traffic) to get the password out of it would still find a way of
determining the password your hosted app is using.

> or have a contract
> with the customers prohibiting them from connecting to the database other than
> by using the app.

If customers access a database hosted by a service provider it is
generally the norm to have some clauses in the contract pertaining to
data protection and ownership making "access to provider hosted data by
any means other than those authorised by the provider" a breach of contract.

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

If security is as big a concern as it appears to be for Andrus then I
would suggest that a bigger approach involving a number of security
steps need to be taken. The password used to connect to the db initially
  is just the first. My standard practice however is to never allow the
users to see the password that they connect to the db with.
Options as I see it:

1.) Hard-code the password (perhaps in encrypted form) within the source
of the application and do not supply that password to anyone else. This
app knows how to connect to the db, and then data access is determined
by credentials supplied by the user that authenticate against a users
table in the db.

2.) Allow the user to specify their own password, but hash the password
using some hashing algorithm - perhaps even your own custom written one
- before sending that in the create user command to Postgres. Connecting
via other means (pgAdminIII, M$ Access etc) would therefore fail as they
cannot provide the same hashed password.

3.) Do as Korry suggested in appending (or perhaps prepending) a 'secret
value' to the user supplied password.

4.) Combine all above options... Allow the user to specify their own
password to which you append a hard-coded 'secret value' which has been
encrypted in the source of your application and then you hash the
resultant password string before sending to Postgres.

My AUD$0.02

--
Paul Lambert
Database Administrator
AutoLedgers