Re: Disallow access from psql, or allow access only from specific client app

On Mon, Jul 25, 2011 at 6:38 AM, Achilleas Mantzios
<achill(at)matrix(dot)gatewaynet(dot)com> wrote:
> Στις Monday 25 July 2011 16:08:53 ο/η Sim Zacks έγραψε:
>> A lot of applications don't actually have a database role per user.
>>
>> There is an application user who logs into the database and the
>> application handles application logins through a users table in the
>> database. That way the only thing that the user has access to is the
>> application and not the database.
>>
>
> That was the dominating fashion for some 10+ years, but if you think of it,
> it is totally pathetic to have broken the link from the actual user to the underlying
> DB.
> We have reverted the whole security setup to using distinct user credentials/user, which are delegated
> by the application (jboss). So the user logs in once, and gets access with the same credential to both
> the app server and the DB.
> Now we can see exactly who is in, what is he doing, etc... what app is he/she running, etc...
>

Just as a side note, this has a very positive side-effect of reducing
degree to which the database must trust the client app (in this case
JBOSS). It means that JBOSS has no access to the database in the
absence of properly provided user credentials.

>> If you access the database through an application server, or server side
>> script, you can limit access to only that (and other specific) IP
>> addresses in the pg_hba.conf.
>
> I would support the same thing. Just enable only localhost access, prevent users from loging in this machine
> (by not .... making them any accounts) and you are done.
>
> This password hacking thing, has some problems, 1st it relies on security via obscurity,
> and second, it needs the authentication to be done programmatically from user code.
> What if the connections are in pools managed by pool managers?
> How easy would it be to hack the passwords/userids this way?

If it's in a connection pool, why would you go that route? Just
restrict access to whitelisted hosts in the pg_hba.conf.

Indeed middleware + whitelisted hosts would be my preferred solution
to the problem.

Best Wishes,
Chris Travers