psql remote shell command

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

David J.
​

On Thu, Jun 2, 2016 at 7:39 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:

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...

​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

​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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

* 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';

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

On Thu, Jun 2, 2016 at 7:54 PM, Stephen Frost <sfrost@snowman.net> wrote:

* 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.

https://www.postgresql.org/docs/devel/static/sql-copy.html

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 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general