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

Hello,

I do not know if your clients are superusers on their machines, one thing you
can do is to remove psql client from these machines. In general, for example in
a company, the users do not have administration privileges.

Regards

________________________________
From: Kurt Buff <kurt(dot)buff(at)gmail(dot)com>
To: Lonni J Friedman <netllama(at)gmail(dot)com>
Cc: Mario Puntin <mariomop(at)gmail(dot)com>; pgsql-general(at)postgresql(dot)org
Sent: Mon, July 25, 2011 12:31:13 AM
Subject: Re: [GENERAL] Disallow access from psql, or allow access only from
specific client app

On Sun, Jul 24, 2011 at 14:48, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:
> On Sun, Jul 24, 2011 at 2:46 PM, Kurt Buff <kurt(dot)buff(at)gmail(dot)com> wrote:
>> On Sun, Jul 24, 2011 at 14:36, Mario Puntin <mariomop(at)gmail(dot)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.