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

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/