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

Mark Walker wrote:
> OK, I've thought about this a bit more and have come to the conclusion
> that storing the password locally in any way is completely insecure.
> Here are simple ways of hacking it:
>
> 1. If you use libpq in a shared lib(dll, etc).  Replace PQconnectdb with
> your own version, rebuild and use your new dll to snatch the password.
>
> 2. If you use libpq in a static lib.  Search for the PQconnectdb's image
> and do the same as #1.
>
> 3. If you don't use libpq.  Search for strings that contain things like
> "host = ", "user = ", "password = ", etc and hack in your own code.
> I think there are really only 2 ways to securely deal with this:
>
> 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.

>
> 2. Proxy server method.

If it came down to it, a proxy server method would be preferable.

>
> Storing passwords locally would be anywhere from trivial to moderately
> difficult to hack.
>
>

I accept your reasonings in not wanting the password held within the
application. However that wouldn't rule out one of my suggestions in
supplying Postgres a hashed password. I.e. a new user is set up and they
specify their password as 'changeme'. If they know their password is
'changeme' they can open up pgAdminIII and log in with that password, or
the same with access, excel et al. If however your application hashes
that password and when doing the create role in PG, it sends the
password through as xH6&_33pq (made up hashing, not generated by any
formula) using md5 to further encrypt and PG stores that, then the user
can log in to your application which will do the translation in sending
the md5 encoded ODBC connect request, however 'changeme' will not work
in trying to connect from anything else, as that is not what PG has been
given as the users password.

Potential points of finding the password inappropriately will always
exist. Regardless of what you do to secure your data, there's a fair
chance that someone is going to get it. The question is, will the users
of your application have the ability/tools to reverse engineer a program
to find out its hashing algorithm or sniff network traffic and decrypt
it to find the password being used in the connection string.

As I keep telling my customers, the easiest way for me to secure your
data is to unplug the network cable on the server. ;)


--
Paul Lambert
Database Administrator
AutoLedgers