Обсуждение: psql remote shell command
Is it possible to execute command in on system the is hosting postgresql remotely using psql or other mechanism? I know I can use \! in psql but that executes the commands on the host where I am running psql from. Also, is it possible for a postgres login/user to stop or restart a running postgres instance from in side psql or similar client. e.g. psql -c “pg_ctl stop -D some_data_dir”
On Fri, Jun 3, 2016 at 8:30 AM, Dennis <dennisr@visi.com> wrote: > Is it possible to execute command in on system the is hosting postgresql > remotely using psql or other mechanism? I know I can use \! in psql but > that executes the commands on the host where I am running psql from. Also, > is it possible for a postgres login/user to stop or restart a running > postgres instance from in side psql or similar client. e.g. psql -c “pg_ctl > stop -D some_data_dir” Not directly. You could implement an extension that wraps a function available at SQL level taking some actions, however for the restart the error handling is tricky if you just have control via psql. How could you start a node that has been stopped during a restart but could not boot up. Take the example of an incorrect parameter value that has been added when the node was up via ALTER SYSTEM... -- Michael
Is it possible to execute command in on system the is hosting postgresql remotely using psql or other mechanism? I know I can use \! in psql but that executes the commands on the host where I am running psql from. Also, is it possible for a postgres login/user to stop or restart a running postgres instance from in side psql or similar client. e.g. psql -c “pg_ctl stop -D some_data_dir”
ssh user@hostname ?
You should ask your system administrator for help. Odds are if you cannot do "ssh user@hostname" then the person hosting the server doesn't want you to be able to execute arbitrary commands on the host.
David J.
Not directly. You could implement an extension that wraps a functionOn Fri, Jun 3, 2016 at 8:30 AM, Dennis <dennisr@visi.com> wrote:
> Is it possible to execute command in on system the is hosting postgresql
> remotely using psql or other mechanism? I know I can use \! in psql but
> that executes the commands on the host where I am running psql from. Also,
> is it possible for a postgres login/user to stop or restart a running
> postgres instance from in side psql or similar client. e.g. psql -c “pg_ctl
> stop -D some_data_dir”
available at SQL level taking some actions, however for the restart
the error handling is tricky if you just have control via psql. How
could you start a node that has been stopped during a restart but
could not boot up. Take the example of an incorrect parameter value
that has been added when the node was up via ALTER SYSTEM...
I was focused on admin task due to the pg_ctl (not sure you'd want to run that via psql...) but if you have shell script applications you want to run you could consider:
I used it a while back successfully but have since gone a more conventional route by embedding psql in shell and not the other way around.
David J.
On Fri, Jun 3, 2016 at 8:48 AM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Thu, Jun 2, 2016 at 7:39 PM, Michael Paquier <michael.paquier@gmail.com> > wrote: > I was focused on admin task due to the pg_ctl (not sure you'd want to run > that via psql...) but if you have shell script applications you want to run > you could consider: > > https://github.com/petere/plsh This one is interesting... -- Michael
* Dennis (dennisr@visi.com) wrote: > Is it possible to execute command in on system the is hosting postgresql remotely using psql or other mechanism? I knowI can use \! in psql but that executes the commands on the host where I am running psql from. Also, is it possible fora postgres login/user to stop or restart a running postgres instance from in side psql or similar client. e.g. psql -c“pg_ctl stop -D some_data_dir” You can use COPY with PROGRAM, assuming you're a superuser. COPY (select 1) TO PROGRAM 'whatever command'; You could use that to issue a pg_ctl stop, though you'll then lose your connection to the database. You can 'reload' the running configuration by using: select pg_reload_conf(); Not all parameters can be changed while PG is running, but most of them can be. Parameters in postgresql.conf can be updated via ALTER SYSTEM through psql also. Issuing a 'pg_ctl restart' via COPY PROGRAM isn't a good idea and may not work, though I suppose you could try if you really wish to. Thanks! Stephen
Вложения
* David G. Johnston (david.g.johnston@gmail.com) wrote: > On Thu, Jun 2, 2016 at 7:30 PM, Dennis <dennisr@visi.com> wrote: > > Is it possible to execute command in on system the is hosting postgresql > > remotely using psql or other mechanism? I know I can use \! in psql but > > that executes the commands on the host where I am running psql from. Also, > > is it possible for a postgres login/user to stop or restart a running > > postgres instance from in side psql or similar client. e.g. psql -c “pg_ctl > > stop -D some_data_dir” > > ssh user@hostname ? > > You should ask your system administrator for help. Odds are if you cannot > do "ssh user@hostname" then the person hosting the server doesn't want you > to be able to execute arbitrary commands on the host. To be fair, if the administrator really didn't want you to have access to the postgres unix user account on the system, they shouldn't give you a PG superuser account. Thanks! Stephen
Вложения
* Dennis (dennisr@visi.com) wrote:
> Is it possible to execute command in on system the is hosting postgresql remotely using psql or other mechanism? I know I can use \! in psql but that executes the commands on the host where I am running psql from. Also, is it possible for a postgres login/user to stop or restart a running postgres instance from in side psql or similar client. e.g. psql -c “pg_ctl stop -D some_data_dir”
You can use COPY with PROGRAM, assuming you're a superuser.
COPY (select 1) TO PROGRAM 'whatever command';
That's thinking outside the box.
Took me a while to find the documentation of the superuser limitation buried in the notes.
I see why SELinux and its ilk are necessary evils in situations where finer grained control is needed.
David J.
On 6/2/2016 4:42 PM, David G. Johnston wrote:
ssh user@hostname ?
^ ++
-- john r pierce, recycling bits in santa cruz
Wow, thanks for all the feedback. The question about whether a superuser could do something like this came up at the office today in the context of security issues and handing out superuser to the application users (not necessarily to the accounts/roles that would be used from the applications however.) This thread will be good food for thought with my team. If the possibility existed at all, I figured it would be something “esoteric” as using the copy command seems esoteric to me.
Thanks
On Jun 2, 2016, at 7:56 PM, John R Pierce <pierce@hogranch.com> wrote:On 6/2/2016 4:42 PM, David G. Johnston wrote:ssh user@hostname ?^ ++
-- john r pierce, recycling bits in santa cruz
On Thu, Jun 2, 2016 at 6:54 PM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Fri, Jun 3, 2016 at 8:48 AM, David G. Johnston > <david.g.johnston@gmail.com> wrote: >> On Thu, Jun 2, 2016 at 7:39 PM, Michael Paquier <michael.paquier@gmail.com> >> wrote: >> I was focused on admin task due to the pg_ctl (not sure you'd want to run >> that via psql...) but if you have shell script applications you want to run >> you could consider: >> >> https://github.com/petere/plsh > > This one is interesting... CREATE OR REPLACE FUNCTION shexec(_cmd TEXT) RETURNS TEXT AS $$#!/bin/bash eval $1 $$ LANGUAGE plsh; cds2=# select shexec('df -h'); shexec ──────────────────────────────────────────────────────────── Filesystem Size Used Avail Use% Mounted on ↵ /dev/mapper/vg_root-lv_root ↵ 20G 5.1G 14G 28% / ↵ tmpfs 1.9G 0 1.9G 0% /dev/shm ↵ /dev/sda1 190M 83M 98M 47% /boot ↵ /dev/mapper/vg_db-lv_db ↵ 246G 161G 74G 69% /var/lib/pgsql↵ rcdmpsfsnfs001.realpage.com:/exports/ybai ↵ :-) merlin