Restrict connection from pgadmin.

On 2/2/20 10:47 PM, Pawan Sharma wrote:

Hi All,

Is there any way to restrict the connection only from pgadmin???  The
same user can connect through application but can't from pgadmin.. Or
any logon triggers..???

Not that I know of. pgAdmin is just another client so you just have the
methods available here:

https://www.postgresql.org/docs/12/auth-pg-hba-conf.html

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sun, Feb 2, 2020 at 11:48 PM Pawan Sharma <pawanpg0963@gmail.com> wrote:

Hi All,

Is there any way to restrict the connection only from pgadmin??? The same
user can connect through application but can't from pgadmin.. Or any logon
triggers..???

Why just pgAdmin? Why not psql? How do you expect to know the difference?
Its all the same protocol going back-and-forth no matter what kind of user
interface you provide to the user.

You can make it difficult for the user to gain direct access from their
machine while allowing a local application to do so, but I'm doubtful it
can be made hacker-proof. Specifically the user can be assumed to be
allowed to attempt a connection but if they lack valid credentials they
will be unable to get in. If you need connection-level security you want
to host the application on a separate machine that does have access to
PostgreSQL and only provide user interface access to the application to the
user. As they are never truly on the machine that has access to the
database they will be unable to establish their own connection.

You can log and monitor logon attempts but there is no server level
triggers. Again, the client sends along all of the validation information
and so can make up values for any parameters you might wish to check so
that they look like, i.e., your application as far as the authentication
code is concerned.

David J.

Not that I know of. pgAdmin is just another client so you just have the methods available here:

https://www.postgresql.org/docs/12/auth-pg-hba-conf.html

will the col application_name from pg_stat_activity list pgadmin as pgadmin. if yes, then the closest one can achieve
is to have a daemon process wake up every 1 min or so and terminate any pgadmin app.

On 2/3/20 9:15 AM, Ravi Krishna wrote:

Not that I know of. pgAdmin is just another client so you just have the methods available here:

https://www.postgresql.org/docs/12/auth-pg-hba-conf.html

will the col application_name from pg_stat_activity list pgadmin as pgadmin. if yes, then the closest one can achieve

Make a connection from pgAdmin and see.

is to have a daemon process wake up every 1 min or so and terminate any pgadmin app.

I can do a lot of damage in a minute.

--
Adrian Klaver
adrian.klaver@aklaver.com

Yes I will show the pgadmin in stat_activity but how can block that..

I think I need to create a job to terminate the pgadmin connections and
schedule it for every 5 min and so that I will check any new connections
from pgadmin.

On Mon, 3 Feb 2020, 11:13 pm Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:

Pawan Sharma <pawanpg0963@gmail.com> writes:

Yes I will show the pgadmin in stat_activity but how can block that..
I think I need to create a job to terminate the pgadmin connections and
schedule it for every 5 min and so that I will check any new connections
from pgadmin.

I think onlookers are still completely mystified as to why you consider
this a useful activity.

pgadmin is nothing but a GUI. Whatever can be done through it can be
done equally well through psql, or any other client software. So if
you're looking for security against unwanted SQL commands, you're going
about it the wrong way (better to look at preventing logins of privileged
accounts, and/or use of SQL permissions to limit what can be done).
If your objective is something else, you haven't explained what that is.

regards, tom lane

Tom Lane wrote:

Pawan Sharma <pawanpg0963@gmail.com> writes:

Yes I will show the pgadmin in stat_activity but how can block that..
I think I need to create a job to terminate the pgadmin connections and
schedule it for every 5 min and so that I will check any new connections
from pgadmin.

I think onlookers are still completely mystified as to why you consider
this a useful activity.

pgadmin is nothing but a GUI. Whatever can be done through it can be
done equally well through psql, or any other client software. So if
you're looking for security against unwanted SQL commands, you're going
about it the wrong way (better to look at preventing logins of privileged
accounts, and/or use of SQL permissions to limit what can be done).
If your objective is something else, you haven't explained what that is.

regards, tom lane

You could give normal/application users/roles very
limited permissions (i.e. just the ability to execute
pre-existing security-defining functions and nothing
else), and have a database owner user/role with all the
permissions to create those functions. That's my insane
setup and I love it. Then use pg_hba.conf to limit
which IP addresses the database owner user/role can log
in from. You could also uninstall pg_admin. :-)

Also, if you are worried about "doing a lot of damage
in a minute", always script everything and test it
first either in a transaction that will rollback or on
a test server before executing it in production. I
don't think a GUI is suitable for this.

cheers,
raf