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

On Sun, Jul 24, 2011 at 14:36, Mario Puntin <mariomop@gmail.com> wrote:
>
> Hi everybody:
> I searched the web trying to find an answer to this, but found none. I have
> a postgresql server and a database, and I granted access to some users.
> However I want them to access the data only through some specific client
> application. I do not want them to have access through psql or some other
> kind of client app. But, as I created them a user/pass they could use them.
> What would you do?
> Thanks in advance.

Make a web front end, and present them with only the queries you want
them to have, via a dropdown list and a set of fields for which you
sanitize the input.

Alternatively, build a GUI app that does the same thing, but if your
users are sophisticated and/or malicious you'll also have to build the
GUI with some sort of application authentication and encryption.

Kurt

On Sun, Jul 24, 2011 at 2:46 PM, Kurt Buff <kurt.buff@gmail.com> wrote:
> On Sun, Jul 24, 2011 at 14:36, Mario Puntin <mariomop@gmail.com> wrote:
>>
>> Hi everybody:
>> I searched the web trying to find an answer to this, but found none. I have
>> a postgresql server and a database, and I granted access to some users.
>> However I want them to access the data only through some specific client
>> application. I do not want them to have access through psql or some other
>> kind of client app. But, as I created them a user/pass they could use them.
>> What would you do?
>> Thanks in advance.
>
> Make a web front end, and present them with only the queries you want
> them to have, via a dropdown list and a set of fields for which you
> sanitize the input.
>
> Alternatively, build a GUI app that does the same thing, but if your
> users are sophisticated and/or malicious you'll also have to build the
> GUI with some sort of application authentication and encryption.

Don't mean to butt in, but how does this meet Mario's requirement of
blocking access from psql and/or only allowing access from a specific
client?

On Sun, Jul 24, 2011 at 2:36 PM, Mario Puntin <mariomop@gmail.com> wrote:
>
> Hi everybody:
> I searched the web trying to find an answer to this, but found none. I have
> a postgresql server and a database, and I granted access to some users.
> However I want them to access the data only through some specific client
> application. I do not want them to have access through psql or some other
> kind of client app. But, as I created them a user/pass they could use them.
> What would you do?
> Thanks in advance.
>
>
The key thing is that you are going to have to have some way to
restrict access.  The protocol itself does not include any way of
authenticating the application per se to be an approved one (and that
is a challenging task if attempted, probably impossible to do
securely).

So what that leaves is the ability to restrict access based on
incoming connection characteristics.  This probably means some sort of
middleware serving the app, and other connections being denied in the
pg_hba.conf

Best Wishes,
Chris Travers

On Sun, Jul 24, 2011 at 14:48, Lonni J Friedman <netllama@gmail.com> wrote:
> On Sun, Jul 24, 2011 at 2:46 PM, Kurt Buff <kurt.buff@gmail.com> wrote:
>> On Sun, Jul 24, 2011 at 14:36, Mario Puntin <mariomop@gmail.com> wrote:
>>>
>>> Hi everybody:
>>> I searched the web trying to find an answer to this, but found none. I have
>>> a postgresql server and a database, and I granted access to some users.
>>> However I want them to access the data only through some specific client
>>> application. I do not want them to have access through psql or some other
>>> kind of client app. But, as I created them a user/pass they could use them.
>>> What would you do?
>>> Thanks in advance.
>>
>> Make a web front end, and present them with only the queries you want
>> them to have, via a dropdown list and a set of fields for which you
>> sanitize the input.
>>
>> Alternatively, build a GUI app that does the same thing, but if your
>> users are sophisticated and/or malicious you'll also have to build the
>> GUI with some sort of application authentication and encryption.
>
> Don't mean to butt in, but how does this meet Mario's requirement of
> blocking access from psql and/or only allowing access from a specific
> client?


The way I read OPs requirements is that he doesn't want them to be
able to use queries to pgsql directly or through a pgsql client. This
requires some other kind of client, which he explicitly stated[1]. The
implication is that he wants to limit their ability to manipulate the
data in certain ways - either to read data that to which they should
not have access, or to modify or delete data. To do that requires an
application that presents and enforces the choices that his design
requirements dictate.

Kurt

[1] One thing that is a bit ambiguous is his use of the term 'client'.
If by that he means a software application, my comments stand. If by
that instead he means a host or set of hosts, then my comments carry
even more freight, because he's going to have to validate from which
hosts the traffic is coming.

On 25/07/2011 5:36 AM, Mario Puntin wrote:
>
> Hi everybody:
> I searched the web trying to find an answer to this, but found none. I
> have a postgresql server and a database, and I granted access to some
> users.
> However I want them to access the data only through some specific
> client application. I do not want them to have access through psql or
> some other kind of client app. But, as I created them a user/pass they
> could use them.

You have a few options. Possibly the easiest is to set the PostgreSQL
server up to require an x.509 client certificate during SSL
authentication. Embed that certificate in your application. Users won't
have direct access to the certificate and won't be able to get in using
any other tool or application. Sniffing the wire protocol with wireshark
or the like won't help them, since they can only see the SSL handshake
and that doesn't ever transmit the private key part of the certificate.
To break this, they'd have to unpack the binary of your application and
extract the certificate and private key from your application binary.
While not especially hard, it's going to be beyond 99.99% of users. To
make it even harder, you can regenerate your certificate with every
update and revoke the cert for the update-before-last, thus forcing old
clients to update to regain access, and invalidating any extracted
certificate.

If you use a client certificate approach you can use your own private
CA. You don't need to have a CA trusted by anybody but you, so there's
no need to pay for official certs from some big name. OpenSSL can make a
perfectly good CA and sign server- and client-certificates based on it.
There are plenty of instructions on how around the web.

If you don't want to deal with client certificates, the password
transformation idea given by Chris Curvey makes sense. Rather than
simply appending something, I'd probably append something then hash the
password though, just to make it less obvious what I was doing. Be aware
that a user on the client machine will be able to easily sniff out the
transformed password from network traffic using wireshark or the like
unless you force use of SSL, so use of SSL is pretty much mandatory if
you take this approach. Even then they could extract it, but they'd have
to be able to extract the session key from the app's memory, and again
that's beyond the vast majority of users.

Personally, I'd probably use both of the above approaches, but I'm kind
of paranoid.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

On 07/24/11 2:36 PM, Mario Puntin wrote:
>
> Hi everybody:
> I searched the web trying to find an answer to this, but found none. I
> have a postgresql server and a database, and I granted access to some
> users.
> However I want them to access the data only through some specific
> client application. I do not want them to have access through psql or
> some other kind of client app. But, as I created them a user/pass they
> could use them.
> What would you do?
> Thanks in advance.
>

I would put ALL the database access in a webservice, running on a
secured server, which their client program accesses and ONLY that
webservice has the access credentials to connect to the database.

so, the user runs a client program, it has credentials to connect to
this webservice (tomcat, or whatever), but no direct access to the
database at all.   the user's workstations are flat denied any access to
the database even if they did have the credentials.

the webservice has all the business logic that queries the database.
so if the client app needs to edit a customer record, for example, it
makes a remote procedure call to the webservice
"EditClientRecord(....)", and that in turn connects to SQL and does the
dirty work.




--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

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.

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.


Sim


On 07/25/2011 02:56 PM, Mario Puntin wrote:

> Thanks a lot to everybody for the replies.
>
> Kurt: by the term "client" I meant an application, like psql, from
> which users could connect to the database, as they have a
> user/password, and manipulate data. I want them to access from
> certain, specific application, but I don't want them to install psql,
> for example, and have also access through it.
>
> I liked Chris Carver idea of modifing user/password: quite simple and
> efective.
> But thank you all people, you gave me some fine ideas.
>
> Best regards,
> Mario
>

Στις 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...

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

>
>
> Sim
>
>
> On 07/25/2011 02:56 PM, Mario Puntin wrote:
>
> > Thanks a lot to everybody for the replies.
> >
> > Kurt: by the term "client" I meant an application, like psql, from
> > which users could connect to the database, as they have a
> > user/password, and manipulate data. I want them to access from
> > certain, specific application, but I don't want them to install psql,
> > for example, and have also access through it.
> >
> > I liked Chris Carver idea of modifing user/password: quite simple and
> > efective.
> > But thank you all people, you gave me some fine ideas.
> >
> > Best regards,
> > Mario
> >
>
>



--
Achilleas Mantzios

On Mon, Jul 25, 2011 at 6:38 AM, Achilleas Mantzios
<achill@matrix.gatewaynet.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