Обсуждение: Restrict connection from pgadmin.
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..???
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:
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
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